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

To customize this column to your needs, we want to invite you to submit your ideas about topics that interest you and issues that you want to see addressed in future Knowledge Base articles and Support Voice columns. You can submit your ideas and feedback using the
Ask For It form. There's also a link to the form at the bottom of this column.
This article was originally published as an ASP.NET Support Voice column in March 2004. This is not a standard KB article.


Welcome to the Microsoft ASP.NET Developer Support Center! For the last few months, the Developer Support Content team has been throwing around the idea of providing a more personal voice for some of the developer support centers on the Microsoft Support Web site: In the end, we decided to pilot a couple columns for the ASP.NET and Office Developer technology areas. Our goal is to provide an easy and informative way to bring the more interesting support-related content and high customer impact issues to your attention, while at the same time offering search tips on how to better take advantage of the Microsoft Knowledge Base. We would also like to extend an open invitation to submit any ideas about how we can improve this discussion and about any issues that you want to see addressed in future Knowledge Base articles. We hope that you will find this information to be both a useful and an efficient use of your time.
As you may already know, searching the Knowledge Base is not always the fruitful experience that we want it to be. In essence, this is the core purpose for this column. The column will highlight specific Knowledge Base articles that are based around a particular theme, will elaborate on the information that is discussed in the articles, and then it will wrap up with some information about how to find related content in the Knowledge Base. That way, you can take the information and can apply it to better suit your requirements.

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:
  • kbaspnetsearch
  • transform
  • kbexcelsearch
  • kbhowtomaster
This search returned the following article as one of the results:
319180 How to transform a DataSet to spreadsheet XML for Excel by using Visual Basic .NET and ASP.NET

The article covered one of the solutions that I was already considering. That solution was writing an HttpHandler to do the job. The nice thing about using the HttpHandler approach is that it pretty much did one thing for me: it took care of transforming the data into a format that Excel can open. In my case, the target was Microsoft Excel 2002. How I actually gathered my data was a task that could be offloaded to some other data access component in the application, and the differentiating query values could be retrieved by using QueryString property variables. Using QueryString property variables makes reusing the solution quite easy.

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.

More Information

XmlDataDocument ClassXmlResolver Class
XslTransform Class
XslTransform.Transform MethodXML Spreadsheet Reference

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)

ID do Artigo: 871040 - Última Revisão: 14 de jul de 2008 - Revisão: 1