Export data to Excel by using an HTTPHandler, XSLT, and ASP.NET
ASP.NET Support Voice Column: Export data to Excel by using an HTTPHandler, XSLT, and ASP.NET
For this installment, I decided to share a situation that I came across a while back. Basically, I required a reusable method to transform some data into a Microsoft Excel spreadsheet. The data was already being made available as a DataSet. All that was necessary was to take the same data and transform it into an Excel spreadsheet. I had the idea of using XSLT to transform the data, but I had not yet decided how I wanted it to be exposed. One important factor was the requirement that the approach must make it easy to reuse later on if necessary. To see if there was already an example of doing this in the Knowledge Base, I did a quick search by using the following keywords:
If you view the article, you will find the most interesting parts to be the HttpHandler code and the XSLT file that is used to transform the data. The solution in the article describes the use of a DataSet, but you can also apply this kind of solution to simple XML documents. This solution creates an XmlDataDocument class that is based on the DataSet, and then the solution performs the transform on the XmlDataDocument class. If you are not familiar with the XmlDataDocument class, you can still implement the solution with almost no difficulty. I have included some additional links that discuss the XmlDataDocument class at the end of this column.
As you view the article, try to consider that the code offers an idea to get you started without unnecessarily complicating the solution. You can take the solution as it is, but you will want to consider using stored procedures and providing some additional error handling logic in your code. You may also find that you want to return the result as an Excel workbook that contains multiple worksheet pages. While the article does not cover this directly, if you examine how the DataSet relates to the XSLT code, you will notice that it is pretty simple to return the results this way. You can add additional DataTables to the DataSet. Then, you can add the transform logic in the XSLT code for each DataTable by using its name. The sample is doing this already, but only for a single DataTable. For more information, see the "XML Spreadsheet Reference" link later in this article.
You may also notice that the code in article 319180 was written in Microsoft Visual Basic .NET. This is fine, but I typically write in Microsoft Visual C# .NET. Converting the code from Visual Basic .NET to Visual C# .NET is pretty straight forward. To keep you from having to do that, I will create a duplicate version in Visual C# .NET that will be cross referenced in 319180. We generally try to cover Visual Basic .NET and Visual C# .NET with all the "How-To" articles, but sometimes some may be overlooked. For all the articles that are featured in this column, we will make every attempt to make sure that they are supported in at least both of these languages.
Well, that is about it. Feel free to submit any ideas that you have about your support requirements. Someone will actually read them, I promise. Also, do not forget to check out the keyword search tips that are listed at the end of the article. Each edition of this column will end with some search tips that are related to the particular Knowledge Base article that is being highlighted. You can save lots of time and trouble if you understand how to search the Knowledge Base.
Knowledge Base search tips
- When you visit http://support.microsoft.com and you click Search the Knowledge Base, try to limit your search by selecting the most relevant product. For example, click ASP.NET and not Visual Studio .NET or All Products in the Select a Microsoft Product list.
- When Knowledge Base articles are created, they have specific "product elements" and keywords that are associated with them. If you know the keywords that are associated with a product or technology, try using those instead of their more generic English terms during your initial searches. For example, to find article 319180, we used the search string kbaspnetsearch and transform and kbexcelsearch and kbhowtomaster.
- The keywords kbaspnetsearch and kbexcelsearch relate to all versions of Excel and ASP.NET. You can also use a specific version keyword to limit the results. For example, the keywords kbASPNet100 and kbASPNet110 narrow the articles that are returned to ASP.NET 1.0 and ASP.NET 1.1.
- The kbhowtomaster keyword is used limit the search to articles based on the "How-To" format. Other article formats include the following:
- Informational (kbinfo)
- Bug (kbbug)
- Hotfix (kbqfe)
Article ID: 871040 - Last Review: 05/18/2007 02:45:07 - Revision: 2.4
- kbasp kbhowto KB871040