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.