Nearly every developer needs to export data to an Excel-compatible format at one point or another. Even if you've done so before, you might be surprised to learn how many different ways there are to accomplish such a task. In this article I'll cover all the major techniques, along with some minor variations. Because no technique is perfect for every situation, I'll point out the pros and cons of each to help you decide which to use for your Web application.
Make Excel Do the Work
One of the most obvious ways to generate an Excel document is to have Excel generate it for you. Indeed this can be done, but you'll soon find out that it's more complex than most other options and has many strings attached. Nevertheless, it's an interesting exercise, so let's take a look at the details.
For starters, you need Excel
installed on the server. To give ASP.NET the permissions it needs to use Excel,
you might need to add the line
Start by opening a new ASP.NET Web project in Visual Studio .NET. Because the Microsoft Office code is still based in the old, unmanaged world, you need to use COM Interop to facilitate communication with it. Look under the Project dropdown menu, select Add Reference, then click on the COM tab. Select the Microsoft Excel Object Library, add it to your references, and click on OK to close the dialog.
Open the code behind the file of WebForm1 and enter this imports statement at the top of the file:
Imports System.Runtime.InteropServices.Marshal
Then, replace the default Page_Load event with the code in Figure 1.
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim oExcel As New Excel.Application()
Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
Dim oCells As Excel.Range
Dim sFile As String, sTemplate As String
Dim dt As DataTable = _
CType(Application.Item("MyDataTable"), DataTable)
sFile = Server.MapPath(Request.ApplicationPath) & _
"\MyExcel.xls"
sTemplate = Server.MapPath(Request.ApplicationPath) & _
"\MyTemplate.xls"
oExcel.Visible = False : oExcel.DisplayAlerts = False
'Start a new workbook
oBooks = oExcel.Workbooks
oBooks.Open(Server.MapPath(Request.ApplicationPath) & _
"\MyTemplate.xls") 'Load colorful template with chart
oBook = oBooks.Item(1)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item(1), Excel.Worksheet)
oSheet.Name = "First Sheet"
oCells = oSheet.Cells
DumpData(dt, oCells) 'Fill in the data
oSheet.SaveAs(sFile) 'Save in a temporary file
oBook.Close()
'Quit Excel and thoroughly deallocate everything
oExcel.Quit()
ReleaseComObject(oCells) : ReleaseComObject(oSheet)
ReleaseComObject(oSheets) : ReleaseComObject(oBook)
ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : oSheet = Nothing : oCells = Nothing
System.GC.Collect()
Response.Redirect(sFile) 'Send the user to the file
End Sub
'Outputs a DataTable to an Excel Worksheet
Private Function DumpData(ByVal _
dt As DataTable, ByVal oCells As Excel.Range) As String
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer
'Output Column Headers
For iCol = 0 To dt.Columns.Count - 1
oCells(2, iCol + 1) = dt.Columns(iCol).ToString
Next
'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToString
Response.Write(ary(iCol).ToString & vbTab)
Next
Next
End Function
Figure 1. COM Interop with Excel usually isn't the best option for exporting your data, but it can be done.
To start, the code declares all the necessary variables, loads some data, and generates a temporary filename. Then it tells Excel not to pop up any message boxes (or other UI) and to create a blank worksheet. It then loops through the DataTable and outputs each data item to a cell in the worksheet. Next, the it saves the worksheet to the temporary file on the Web server. Finally, the code thoroughly closes and de-allocates everything to minimize potential memory issues and redirects the user to the file.
If you look closely at the code, you'll notice it takes advantage of a unique feature available with this COM Interop technique: the ability to use a preformatted Excel file as a template. The code opens up an existing, attractively preformatted Excel file and fills in the data. In Figure 2, the data is placed into a spreadsheet that had columns, headings, colors, and a chart all laid out in advance. You even can use this technique to prepare formulas, VBA modules, complex multiple sheet books, and so on.
Figure 2. You can lay out complex formats in advance using a template
Excel file.
Be forewarned, however, that Excel was never designed to be used as a multiuser server component; its threading model is geared toward a single-user desktop application. Therefore, the code in Figure 1 isn't scalable and is appropriate only for certain situations. For example, if your app is used only on an intranet and no more than one or two people will generate Excel reports concurrently, this code could work well for you. But if you want to squeeze out some extra scalability, you'll likely need to build a custom wrapper for this code. I envision the wrapper using a queue to ensure Excel is used only by one process at a time or farming out the job to multiple dedicated backed servers.
You might have heard of Visual Studio Tools for Office 2003, which is a powerful new .NET toolset. Although you might be tempted to use it to minimize COM Interop, the same old Excel code is running underneath it all and, therefore, you've still got all the same scalability issues. For true scalability, your best bet is to avoid interacting with Excel on the server at all. So, from here on out I'll concentrate on more scalable techniques that don't require Excel to be installed on your server.
Simply Compatible: Tab Delimited Format
Tab Delimited Format (TDF) is the lowest common denominator of data formats. There's nothing fancy about it: No charts, graphs, images, or fancy fonts are available. It's simply plain-text data columns separated by tab characters, with carriage returns at the end of each row. The best thing about TDF is its wide compatibility. I've tested it successfully with Excel 95, so rest assured it'll work for all your users no matter how antiquated their office software might be.
You could save your tab-delimited data into a file on the Web server with an XLS extension, then Response.Redirect your user to that file as in the COM Interop example. Although this could prove to be useful if you want to save the data on the server for future reference, security and file-management issues complicate it. Therefore, you generally should avoid saving the data on the server's hard drive and instead generate the file dynamically in memory and output it to the user directly.
To try this: Open a new Web form and enter the code in Figure 3 into your codebehind. Then, because we want to output data instead of HTML, open your Web form in HTML view and delete all the HTML, leaving only the first yellow line at the top of the file.
Private Sub Page_Load(ByVal sender As _
System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim dt As DataTable = _
CType(Application.Item("MyDataTable"), DataTable)
Response.ContentType = "application/ms-excel"
Response.AddHeader("Content-Disposition", _
"inline;filename=test.xls")
Response.Write(ConvertDtToTDF(dt))
End Sub
'Converts a DataTable to Tab Delimited Format
Private Function ConvertDtToTDF(ByVal _
dt As DataTable) As String
Dim dr As DataRow, ary() As Object, i As Integer
Dim iCol As Integer
'Output Column Headers
For iCol = 0 To dt.Columns.Count - 1
Response.Write(dt.Columns(iCol).ToString & vbTab)
Next
Response.Write(vbCrLf)
'Output Data
For Each dr In dt.Rows
ary = dr.ItemArray
For i = 0 To UBound(ary)
Response.Write(ary(i).ToString & vbTab)
Next
Response.Write(vbCrLf)
Next
End Function
Figure 3. It doesn't take much code to output tab-delimited data to Excel.
In the Page Load event, the first thing you need to do is grab your data. In this example, a DataTable is retrieved from the Application object. The next step is to set the Content Type of the output to application/ms-excel. This important step tells the browser the data is intended to be opened in Excel. As an added compatibility measure, a header also is added that gives the data a default filename with an XLS extension. The final step is to coerce the DataTable into a tab-delimited string and write it out to the response stream.
Now set your new form as the start page and run the app. Notice that Excel treats the data as a regular spreadsheet and you can sort it, sum it, format it, and so on. Your users should know their way around Excel to take advantage of these features, though, because you can't do any of it for them automatically with this plain-text approach.
Figure 4. TDF is nice and simple, and it's compatible with everything
even though the output is not very attractive.
Beautify Your Output With HTML
TDF is fine for basic needs, but as you can see in Figure 4, it's just so darn ugly. If you can count on your users having at least Excel 97, you can use HTML format instead of TDF and still get all the formatting niceties that go along with it.
To begin, add a new Web form to your project. There's no need to delete the HTML from the Web form this time because HTML is what you intend to output. Next, drag a DataGrid control onto your Web form from the toolbar. Then right-click on the grid and choose Auto Format from the context menu. You'll see a dialog box appear from which you can choose an attractive format for your data. Once you've selected a suitable format, click on OK to close the dialog box, go to your codebehind, and enter this code, which will output a rich, nicely formatted spreadsheet:
Protected Overrides Sub Render(ByVal _
writer As System.Web.UI.HtmlTextWriter)
Dim dt As DataTable = _
CType(Application.Item("MyDataTable"), DataTable)
Response.ContentType = "application/ms-excel"
Response.AddHeader("Content-Disposition", _
"inline;filename=test.xls")
DataGrid1.DataSource = dt
DataGrid1.DataBind()
DataGrid1.RenderControl(writer)
End Sub
The first several lines should look familiar to you because they're identical to the TDF example. Now, it's simply a matter of binding the DataGrid to your DataTable and rendering the output.
Once again, if you set your new form as the start page and try it out, you'll see that Excel still treats the data as a regular spreadsheet your users can sort, sum, format, and so forth. Figure 5 shows that the formatting options available in this scenario are much richer than those in the previous TDF example.
Figure 5. The simplicity and wide variety of formatting options with the
HTML output technique make it a desirable option.
It's worth noting here that you just as easily can output to Microsoft Word instead of Excel with only a couple of basic changes. This code shows that you need only to change the Content Type and the file extension:
Protected Overrides Sub Render(ByVal _
writer As System.Web.UI.HtmlTextWriter)
Dim dt As DataTable = _
CType(Application.Item("MyDataTable"), DataTable)
Response.ContentType = "application/ms-word"
Response.AddHeader("Content-Disposition", _
"inline;filename=test.doc")
DataGrid1.DataSource = dt
DataGrid1.DataBind()
DataGrid1.RenderControl(writer)
End Sub
Take a Walk on the Client Side
I expect the HTML approach is the best option for most projects. But there's something appealing about harnessing the horsepower available on the client. After all, these aren't dumb clients we're talking about; the user likely is looking at the data on a Web page already when they click on your Export to Excel link. In this case, the control returns to the server - which gets the data all over again - regenerates the data in an Excel-compatible format, and sends it all back to the client. This seems like a lot of work considering the data was formatted nicely on the user's computer screen already. I'll now explain another approach that simply keeps it there and hands it off to Excel.
This approach is best for an intranet environment because it requires a recent version of Internet Explorer and it might also require modifying the browser security settings to permit the client-side code to do all the work. You can find the setting you'll most likely need to adjust in the Internet Options dialog under the Tools dropdown menu in Internet Explorer. Go to the Security tab, select Local intranet, and click on the Custom Level button. Find the setting titled "Initialize and script ActiveX controls not marked as safe" and change the selection either to Enable or Prompt. The security implications speak for themselves here, so you might want to get permission from your manager before you implement large-scale changes.
The plus side to this technique is it reduces server load and network traffic while it takes advantage of the workstation's power. To begin, add a new Web form to your project and drag a DataGrid onto it. Then, put some basic data-binding code into your codebehind's Page_Load event:
Dim dt As DataTable = _
CType(Application.Item("MyDataTable"), DataTable)
DataGrid1.DataSource = dt
DataGrid1.DataBind()
Next, go into the HTML view of your page and add a button beneath the DataGrid with this definition:
Now add this client-side VBScript at the very end, after the closing