Microsoft Excel for Data Analysis and Reporting in ASP.NET

Overview

Excel is a very powerful tool for data analysis and reporting. With built-in support for Visual Basic language there is so much that can be done in Excel. People who work in the financial industry know that one way or the other Excel spreadsheets are used heavily to this day. When it comes to working with Excel, business users are more skilled than developers. Software development for a long time was gearing towards distributed computation and n-tier architecture. At the time when data was stored in Access database or flat files, manually entering data into Excel files and manipulating data used to be the way to do business. In these days of automation where Internet is the way to go, this article focuses on how to get the best of both the worlds.

Excel and ASP.NET

Excel Automation or writing Excel macros in VB is not the topic of this article. There are numerous websites and resources available to get details on Excel automation and how to write Visual Basic code inside Excel. This article focuses only on how to take advantage of Excel in current Internet environment (to be specific with ASP.NET).

Objective

The objective of this article is to mainly expose data analysis functionality of Excel through ASP.NET. The solution provided in this article focuses more on "How to get the best of existing Excel templates in any business and expose data quickly over Internet".

Regarding the sample

This article is mainly for educational purposes and not all approaches followed in the sample code may be recommended in real production environment. The focus of this article is on exposing few techniques and concepts when dealing with ASP.NET and Excel. Please read through the 'Additional Resources' section to get better understanding of the subject. The sample code for this case study is provided at the end. Please feel free to use it at your own risk. Feel free to modify the code to suit your requirements.

Fig. 1: Sample - Excel Report Demo (HTML export) [Sample code provided with the article]

Security

This article and sample code is based on server-side automation of Excel. There are few serious drawbacks and considerations to this approach. Please read through 'Additional Resources' section for more details. If Excel automation is not used correctly on the web server, there is a possibility of getting the web-server into inconsistent state. Please be aware of these issues and take necessary precautions to avoid this.

Note about Excel 2003: Excel 2003 has a better interface for Microsoft .net. There are numerous articles on Excel 2003 and .net on the Microsoft web site. Few links are also provided in this article. If you already have Excel 2003, this article may only help you to give the conceptual idea.

Quick & Dirty?

With the help of many 3^rd part libraries it is possible to build nice Internet application to provide charts/graphs or reports. Approach suggested in this article is more suitable for businesses where Excel is already used heavily and there is a significant use of Excel based reporting. By using the existing Excel reporting template as-is and exposing them through Internet we get best of both the worlds, without sweating a lot in developing reports from ground-up. Ideally this approach should be considered as quick solution to get business up-to speed while more long-term solutions are being developed.

Sample - Excel Reporting Demo

The sample provided in this article creates reports from existing Excel file. The sample also uses Pie and 3D-Column charts from Excel. The final report can be produced in either Excel format or HTML format.

Installing and running the sample:

• Download the sample code (362 KB) and extract the sample files to the local machine.
• Using IIS MMC, Create virtual directory 'ExcelReportDemo' and map it to 'ExcelReportDemo' folder from the extracted files above.
• From command prompt type "dcomcnfg.exe" [DCOM configuration tool]
• You will see 'Distributed COM Configuration Properties' window.
• On 'Applications' tab from 'Applications' list box, double click on 'Microsoft Excel Application'
• Select 'Security' tab
• Select 'Use Custom Access Permission' radio button option and click on 'Edit' button.
• On the 'Registry Value Permissions' window make sure to add 'ASPNET' local user OR user running ASP.NET process on local machine.
• Select OK after adding ASPNET user above
• On the same tab (Security) select 'Use Custom Launch Permissions' and click on 'Edit'
• On the 'Registry Value Permissions' window make sure to add 'ASPNET' local user OR user running ASP.NET process on local machine.

NOTE: DCOMCNFG.EXE tool is to configure DCOM applications. In this sample ASP.NET uses EXCEL automation and invokes Excel Application. By default ASPNET local windows user, owns ASP.NET process (assuming no impersonation features of .net are used). So it is important to give correct access to ASPNET user for DCOM entry 'Microsoft Excel Application'. Otherwise while running the sample, 'Access Denied' exception occurs when Excel Application object is invoked.

Software Requirements for the sample:

• Excel 2000 (Version 9.0) [If you have Excel 2002 (Version 10.0) please see the commented sections in the code on changes required].
• ASP.NET / .net Framework [Version 1.0 or later]

Workflow for the sample:

• Business user gives the copy of Excel spreadsheet he/she uses for reporting, to the developer.
• Developer with the help of business user will eliminate any direct database access from excel file and will drive the reporting through static sheet data on the same Excel file.
• Developer keeps this file under 'ReportTemplates' folder of 'ExcelReportDemo' project.
• Developer after understanding the source of the data, queries the data at run-time and replaces static data on the Excel sheet with actual data.
• Excel does the rest of the work for us.
• If end-user requests for HTML format, Excel export feature is used to generate report in HTML format.

Techniques used in the sample:

• ASP.NET uses Excel Automation to invoke Excel
• DataSet is loaded from the data from the XML file (XML used only for simplicity). In reality data can come from any database on SQL Server, ORACLE etc.
• Since Excel 2002 is used in this sample, DataSet data is copied to Excel, cell by cell. There are many efficient methods of transferring data but this approach is only taken for simplicity.
• It is possible to access Chart objects through Excel automation and change any properties of the chart. This sample does not use Excel automation to this extent.

Fig. 2 : Excel Report Demo - Report Request page

Testing the sample: (Fig. 2 shows report request screen for the sample)

• Please verify that all instructions in 'Installing the sample' are followed before testing the sample
• Type http://localhost/ExcelReportDemo/RunReports.aspx in the Browser's address bar.
• Select 'Report Format' [Excel or HTML]
• Click on 'Run Report' to run the report.
• Click on 'Additional Resources' button to see additional resources related to Server-side automation and Excel programming.
• Fig. 1 shows sample report in HTML format.

Temporary Files generated

The sample produces few temporary files each time a report is requested. Sample code does not come with any automated clean-up scripts. Please delete them manually or write WMI scripts/.bat files, which can be scheduled using windows scheduler to do automated clean-up. The sample code or article does not address this part for simplicity.

Acknowledgements

I would like to thank my wife Sheela Tallamraju for editing this article. I also want to thank Tom Montgomery and Praveen Ray for their support in writing this article.

Additional Resources:

• Considerations for Server-side Automation
• Server-side charting
• Excel data from ADO/ASP
• Using Worksheet functions on web page
• Office Web Components samples for web
• Limitations of Office web components on server-side
• ADO.net And EXCEL (2003)

About the Author

Jayram Tallamraju is a Software Architect/ Sr. Programmer Analyst for Bisys Hedge Fund Services in Boston, MA. He is MCP of .net, MCSD (Microsoft Certified Solution Developer) and SCJD (Sun Certified Java Developer). Jay holds an MS in Electronics and has been working in the software industry for around 10 years. He is focused more in building server architecture and in building reusable business components. His current area of expertise is in Microsoft technologies such as .NET, C#, Web services, ASP.NET, VC++/VB, COM/DCOM, ASP/IIS.

Email: tjayram@yahoo.com

Articles from the same author:

• Multi-Column Sort Using the ASP.NET DataGrid (.net article)
• .NET Buzzword Reality (.net article)
• Building a .NET Application Configuration Editor (.net article)
• How to Share Session/Application State Across Different ASP.NET Web Applications (.net article)
• Data Import Functionality Using SQL Server (SQL Server DTS article)
• Distributed Processing (.net article)