Report generation under .NET (3)

3. Effective Excel Report Enterprise Application often depends on the Excel report to interact with other ERP systems as JD Edwards. Projects affected by finance and direct sales often use an Excel form. Excel objects in the client environment often use Excel objects. But this part focuses on how to use Excel in the ASP.NET environment. It is not so difficult to execute Excel operations in the server side. But it is not a simple job that makes this process expandable and configurable. When using an Excel object in an ASP.NET environment, a large number of useless threads are created during long run. In fact, Microsoft does not support or recommend using Office operations on the server side. Six major issues are required when using Office operations in the server side. Designers should know how to handle these effects and minimize their impact. In operation, Excel or Office is cautious, because there is no solution to handle all problems, and different design needs to consider from different angles. a. User identity: Office program assumes the existence of a user, even if they are automated. They will try to start the initialization toolbar, directory, option, and some additional executable-based registry settings. ASP.NET runs under ASP.NET users, which only has limited access, so Office is likely to fail when starting initialization properties, and other functions will not be used. So we have to complete a virtual process through a code or web configuration file, and the use of code executing this virtual process will also have security issues. b. Interact with the desktop: Office application assumes that they are running on a interactive desktop and sometimes there is some automatic functionality. If there is an unexpected error, or when a parameter that does not set is required to complete the function, the Office is designed to pop up a dialog asking how users do. The dialog box is not disappeared on an unhappy desktop, which will cause the thread to be hanged. Although some code can help reduce the occurrence of similar conditions, they can't solve all problems. c. Reusable and scalability: Server-side components require high-regency, multi-thread COM components, while serving at the same time with minimal expenditure and maximum access. For Office applications, all of them are opposite. They are designed to provide a variety of servers-based auto-based auto-based auto-based auto-based auto-based automatic servers. They provide a small scalability as a server solution and limit some important elements, such as memory is not modified in the configuration. More importantly, they use global resources (such as memory plan files and shared automatic servers), if they are configured in a multi-client environment, these resources limit the number of instances running simultaneously. It is hoped that developers who have to run multiple Office application instances have to consider "program pool" or sequentially access Office applications to avoid potential deadlocks and data errors. d. Restore Capacity and Stability: Office 2000, Office XP, and Office 2003 use Microsoft Windows Installation (MSI) technology to make user installation and self-fixing easier. MSI introduces a concept of installation for the first time, which allows some functions to be dynamically installed or set at runtime (for system or special users). In the server environment, this will reduce performance and add some Some Dialogs to ask the user to confirm the installation or provide a possible mounting disk. Although it is designed to increase the restoration capability of Office's Office's Office, the MSI performance executed by Office will only play back in server environments. Also, the stability is not guaranteed at runtime on the server because it is not designed or tested for this type. Using Office as a service component on the web server may reduce the stability of the machine.

If we plan to automatically run Office on the server side, try to separate these programs to dedicated machines without affecting the core, you can restart your machine when needed. e. Server-side security: Office application has never considered it in server-side, so there is no more consideration of security issues faced by the distributed components. Office does not review the request, and does not protect the unintentional macro, or start another server that may run macros through the server-side code. Don't open the file uploaded from an anonymous website to the server! Based on the safe setting of the previous configuration, the server can run macro in all permissions of the administrator or system. In addition, Office uses many client components (such as simple MAPI, MSDAIPP), which can cache the client's authentication information to increase the speed. If Office is automatically running on the server side, an example may serve multiple customers. Since the authentication information has been cached to the previous process, it is possible to let another customer use a cached another customer authentication information, then simulate other users. Get unlicensed access. f. Performance Cost: Use Excel objects in the server-side ASP.NET code to automatically run Excel relative to alternatives to reduce performance. Each Excel operation contains a COM call, and these also include running to call the intermediate layer, which significantly affects the speed when generating a relatively large report. In addition to these technological considerations, designers should consider this flexibility on the license. Existing licenses prevent Office applications from serving client requests unless these clients have Office license. Using the server side automatically runs to provide Office features that are not licensed workstations in the end user license instruction. Alternative Microsoft's strong recommendation developers should look for alternatives to automatic operation when developing server-side solutions. Due to the design limitations, the direct modified configuration is not enough to solve all problems. Microsoft recommends some alternatives that do not need to be installed on server-side, which can complete some common tasks more quickly and more quickly. The most recommended approach is to generate an XML file or an HTML file that can be used to express the spreadsheet. Because of Office2000 and the subsequent version support HTML as a local document format, most documents can be created in an HTML format or using XML tags when needed, and then send it to the client using the MIME type, so the client's text will be in Office It is shown in it. The document can be edited, saved or even returned to the server when needed, and all all of this can be fully implemented using ASP.NET. For earlier Office versions, you can use some other easy-to-operate text format. Some local binary format files can be edited using Office Web Components or ActiveX Data Objects (ADOs) with fast speed and scalability. Office Web Components can be used to generate limited fabrics. However, the performance cost of these components is relatively small to use Office components. The properties of the document can be viewed or changed without automatic operation, and the authentication and version of the authentication and version system using the FrontPage server are possible to perform file management and version management. When the basic function is automatically run, most of the tasks are downloaded to the client, providing better stability and scalability for the system, because each user uses its own settings to run the task. Case Study In our last project, they let us generate an Excel report to integrate with JD Edwards. Start us using traditional methods to generate an Excel report. These reports contain a large number of formulas, note, and formats that users can work directly on these Excel reports. The following code describes how we used to generate an Excel report:

Excel.Application excelApplication; Excel._Workbook excelWorkBook; Excel._Worksheet excelWorkSheet; Excel.Range excelRange; // Perform garbage collection to free existing resources.C.Collect (); // Create the excel objectexcelApplication = new Excel.Application (); Excelapplication.visible = false;

// Create a new workbook.excelworkbook = (Excel._Workbook) (Excelworkbook.Workbooks.Add (Type.Missing));

// Get the Active ShetexcelWorksheet = (Excel._Worksheet) Excelworkbook.activesheet;

// print the detailsfor (int index = 0; Index

// code to perform more formatting}

// autofit columns a: z.ExcelRange = Excelworksheet.get_range ("a1", "z1"); ExcelRange.ntireColumn.autofit ();

Excelapplication.visible = false; ExceLapplication.userControl = false;

Excelworkbook.saveas (TemporaryFileName, Excel.xLfileformat.xLworkbookNormal, Null, Null, False, False, Excel.xlsaveasaccessMode.xlshared, False, False, Null, Null, NULL);

// Need All Following Code to Clean Up and Free All References !!! Excelworkbook.close (Null, Null, Null); Excelapplication.Workbooks.Close ();

// free the resourceESEXCELAPPLICATION.QUIT ();

System.Runtime.InteropServices.Marshal.ReleaseComObject (excelRange); System.Runtime.InteropServices.Marshal.ReleaseComObject (excelApplicatn) System.Runtime.InteropServices.Marshal.ReleaseComObject (excelWorkShet); System.Runtime.InteropServices.Marshal.ReleaseComObject (excelWorkBook) ;

// set the object values ​​to nullexcelworksheet = null; excelworkbook = null; Excelapplication = NULL;

// force garbage collectiongc.collect (); Excel object used in the code is "Microsoft Excel 11 Library", which identifies most of the format containing the RCW call. When printing about 6,000 lines of data, it takes about 2-3 minutes on a 700 MHz Pentium IV machine. The performance of the report depends on the customer's expectation, so we have begun to consider other possible scenarios. Fortunately, we found such an alternative before the project submitted to the customer. We found this scenario from an MSDN link. We generate the XML files supported by Office XP to replace the Excel report in place of the Excel object. The Excel file can be saved as XML or HTML format in Office XP. We have also thought about generating an HTML file rather than an XML file. Although HTML is an ideal file format that displays Excel data on a web browser, in order to meet Excel's special features and make it better on a web browser, this interlEL HTML is particularly complex. The result is that the generated file can only be identified by Excel. XML can help separate data and formats, you can generate a cleaner, richer and more important file format allows Excel data to be used by other programs. We have to decide (or forced) testing this new method. Everything happens smoothly, and we have found that this new approach is a very stable and a method of performance-driven. We can generate (more than 10,000 rows) reports containing large amounts of data within 30-40 seconds. The XML method features some XML execution features in Excel and Microsoft Office XP spreadsheet components including: EXCEL and spreadsheet messages open non-Excel, non-spreadsheet components to open and organize special or The neat XML file opens handwritten XML spreadsheet documents From Excel Publish Data to the spreadsheet component export data from the spreadsheet components to Excel copy and paste data to spreadsheet components, and vice versa use XML-based query in Excel Table objects use XML in Excel and spreadsheet components Use XML limitations to use the limit of the XML method, which does not support Excel macros, may have more limitations, but for generating complicated and good reports, the XML method is the middle Good one. Because there are many code used by the XML file to be repetitive, we recommend writing the part of the XML generated into a class library so that you can use in a similar item, save your time and cost. 4. Safety is the most important part of the security in architecture a report system. The security of managing report data is an important part. The .NET architecture is fully supported Dynamic Directory (AD), allowing developers to easily applying AD permissions in the report system and assigns permission to report the report to those groups with special data access, so all security Issues (including reporting systems) can be managed by managing group employees. Creating an Excel report in .NET, the automatic operation provided by Office is not a good way. If you still prefer to use it, it is recommended to consider security vulnerabilities in the report architecture and carefully manage an anonymous accounts and ASPNET accounts. The safety method used in a secure database system is to set a separate report server on the basis of the configuration server. It enhances performance and guarantee data security. The data will be published in the way in the way, and then these views will use SQL / Windows verification mechanism. 5. Conclusion Selecting the right tool to generate a report depends on the type of application and cost. The selection of the architecture, the components that are ready to use, depend on your configuration policies and the application architecture. Consider the configuration policy before deciding a report policy. The EXCEL automatic operation is unwise to generate an Excel report. The way Excel reports are generated in XML files will be better from scalability and performance perspectives. It is not difficult to generate a PDF report and crystal report under .NET, it is necessary to consider a good license in a distributed environment. The security of the report can be achieved by dynamic directory services and IIS.