asp.netNOW Q&A
Fun With Excel
Learn how to export data from the Web to Excel.
By Josef Finsel
Q: I want to send my DataGrid to Excel. The problem is that when I have a cell in the DataGrid containing the text 078, Excel presents the data as the number 78. How can I send the grid to Excel and force it to treat everything as text?
A: I love spreadsheets. I cut my programming teeth creating macros for VisiCalc and Lotus 1-2-3 before Excel even existed. In this case, it all comes down to formatting and dealing with virtual data. Take a number, for example 1.25. Now, what exactly is that? It could be $1.25 or 125 percent without clues, it s hard to say. Excel requires those same clues to load data. Take a simple DataGrid filled with data(see Figure 1). All it does is create 15 rows with two columns; the first column is a text column with 0 in front of the number, the second is a regular number.
Dim dt As DataTable = New DataTable()
Dim dr As DataRow
Dim iLoop As Int16
dt.Columns.Add(New DataColumn("StringCol", GetType(String)))
dt.Columns.Add(New DataColumn("NumericCol", GetType(Integer)))
For iLoop = 0 To 15
dr = dt.NewRow()
dr(0) = "0" & (iLoop * 10).ToString()
dr(1) = iLoop * 10
dt.Rows.Add(dr)
Next
DataGrid1.DataSource = dt
DataGrid1.DataBind()
Figure 1. First, load your grid with some data.
When you load this page in the browser, it appears absolutely correct. When you add the additional code in Figure 2 to spit it out as an Excel spreadsheet, however, you get two identical columns. This is because Excel is acting just the way it s supposed to. Try opening Excel and enter 035 into a column. By default it will be saved as 35; Excel is simply doing its job.
Dim tw As New StringWriter()
Dim hw As New HtmlTextWriter(tw)
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False
DataGrid1.RenderControl(hw)
Response.Write(tw.ToString())
Figure 2. This code forces the page in Figure 1 to reproduce the information in an Excel spreadsheet.
The trick to making Excel prepend the 0 is to tell it explicitly that this should be a string. To do this, you need to modify the data in the first column like this:
dr(0) = = 0 & (iLoop * 10).ToString &
Although I have no doubt that this looks a bit confusing, it breaks down easily. If you comment out the part that creates the spreadsheet, you ll see that the first column now looks like ="010" instead of 010. When this is exported to Excel, it s as though you typed ="010" into the cell and it shows up correctly.
Q: I have an .aspx page with a rather large (1,000 sq. cells) DataGrid that I somehow need to save as an Excel spreadsheet, but it doesn t necessarily need to be viewed in the browser. Any ideas? It seems Excel only likes HTML to be imported. Also, the DataGrid will be refreshed with new data automatically every two to five minutes, so the spreadsheet needs to be overwritten that often as well.
A: This is a task for something slightly different. To do this. you need to write a middle-tier component that queries the Web server every 10 to 15 minutes to retrieve the new data and writes it out to a file, which could be locked if someone has it open. But, if you have Office 2000 you have a much simpler choice. Create a copy of the Web page I created earlier (see Figures 1 and 2), but don t include the last lines to output it to a spreadsheet. Instead, leave it in the DataGrid, build it, and browse to ensure the data displays in the table properly.
Next, open Excel and select Data | Import External Data | New Web Query. This loads your home page by default, but instead paste the URL pointing to your new Web page and select the arrow next to your table (see Figure 3). Click on Import and you re done loading the data into the spreadsheet. There should be a small External Data toolbar floating around inside your spreadsheet. If you click on the second icon (named Data Range Properties), it will bring up a property box that allows you to adjust settings such as how often the data will refresh.
Figure 3. Select the table from the
Web query to load into Excel.
Keep your ASP.NET questions coming! E-mail me at [email protected].
The code referenced in this article is available for download.
Josef Finsel is a software consultant with a global consulting firm and specializes in .NET and SQL Server. He has published a number of VB, .NET, and SQL Server related articles, and when he isn t hanging around the aspenetpro forums, you can find him working on the syntax for FizzBin.NET, a programming language that works the way programmers have always suspected. He s also author of The Handbook for Reluctant Database Administrators (Apress).
