asp:cover story
LANGUAGES: VB .NET
ASP.NET VERSIONS: 1.0 | 1.1
Export to Excel
Use ASP.NET to send functionally rich reports to yourusers.
By Steve C. Orr
Nearly every developer needs to export data to anExcel-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 accomplishsuch a task. In this article I'll cover all the major techniques, along withsome 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 foryour Web application.
Make Excel Do the Work
One of the most obvious ways togenerate an Excel document is to have Excel generate it for you. Indeed thiscan be done, but you'll soon find out that it's more complex than most otheroptions and has many strings attached. Nevertheless, it's an interestingexercise, so let's take a look at the details.
For starters, you need Excelinstalled on the server. To give ASP.NET the permissions it needs to use Excel,you might need to add the line <identity impersonate="true"/>to your web.config file or configure your app to run under an appropriate useraccount. For this code to work, you also might need to grant write privileges toyour Web directory for this account (IUSR_machinename if you use identityimpersonation).
Start by opening a new ASP.NET Webproject in Visual Studio .NET. Because the Microsoft Office code is still basedin the old, unmanaged world, you need to use COM Interop to facilitatecommunication with it. Look under the Project dropdown menu, select AddReference, then click on the COM tab. Select the Microsoft Excel ObjectLibrary, add it to your references, and click on OK to close the dialog.
Open the code behind the file ofWebForm1 and enter this imports statement at the top of the file:
ImportsSystem.Runtime.InteropServices.Marshal
Then, replacethe default Page_Load event with the code in Figure 1.
Private Sub Page_Load(ByVal sender AsSystem.Object, _
ByVal e AsSystem.EventArgs) Handles MyBase.Load
Dim oExcel As NewExcel.Application()
Dim oBooks AsExcel.Workbooks, oBook As Excel.Workbook
Dim oSheets AsExcel.Sheets, oSheet As Excel.Worksheet
Dim oCells AsExcel.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 ExcelWorksheet
Private Function DumpData(ByVal _
dt As DataTable, ByValoCells 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 Todt.Columns.Count - 1
oCells(2, iCol +1) = dt.Columns(iCol).ToString
Next
'Output Data
For iRow = 0 Todt.Rows.Count - 1
dr =dt.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 ToUBound(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 thebest option for exporting your data, but it can be done.
To start, the code declares all thenecessary variables, loads some data, and generates a temporary filename. Thenit tells Excel not to pop up any message boxes (or other UI) and to create ablank worksheet. It then loops through the DataTable and outputs each data itemto a cell in the worksheet. Next, the it saves the worksheet to the temporaryfile on the Web server. Finally, the code thoroughly closes and de-allocateseverything to minimize potential memory issues and redirects the user to thefile.
If you look closely at the code,you'll notice it takes advantage of a unique feature available with this COMInterop technique: the ability to use a preformatted Excel file as a template.The code opens up an existing, attractively preformatted Excel file and fillsin the data. In Figure 2, the data is placed into a spreadsheet that hadcolumns, headings, colors, and a chart all laid out in advance. You even canuse this technique to prepare formulas, VBA modules, complex multiple sheetbooks, and so on.
Figure 2. You can lay out complex formats in advance using a templateExcel file.
Be forewarned, however, that Excelwas never designed to be used as a multiuser server component; its threadingmodel is geared toward a single-user desktop application. Therefore, the codein Figure 1 isn't scalable and is appropriate only for certain situations. Forexample, if your app is used only on an intranet and no more than one or twopeople will generate Excel reports concurrently, this code could work well foryou. But if you want to squeeze out some extra scalability, you'll likely needto build a custom wrapper for this code. I envision the wrapper using a queueto ensure Excel is used only by one process at a time or farming out the job tomultiple dedicated backed servers.
You might have heard of Visual StudioTools for Office 2003, which is a powerful new .NET toolset. Although you mightbe tempted to use it to minimize COM Interop, the same old Excel code isrunning underneath it all and, therefore, you've still got all the samescalability issues. For true scalability, your best bet isto avoid interacting with Excel on the server at all. So, from here on outI'll concentrate on more scalable techniques that don't require Excel to beinstalled on your server.
Simply Compatible: Tab DelimitedFormat
Tab Delimited Format (TDF) is thelowest common denominator of data formats. There's nothing fancy about it: Nocharts, graphs, images, or fancy fonts are available. It's simply plain-textdata columns separated by tab characters, with carriage returns at the end ofeach row. The best thing about TDF is its wide compatibility. I've tested itsuccessfully with Excel 95, so rest assured it'll work for all your users nomatter how antiquated their office software might be.
You could save your tab-delimiteddata into a file on the Web server with an XLS extension, thenResponse.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 theserver for future reference, security and file-management issues complicate it.Therefore, you generally should avoid saving the data on the server's harddrive and instead generate the file dynamically in memory and output it to theuser directly.
To try this: Open a new Web formand enter the code in Figure 3 into your codebehind. Then, because we want tooutput data instead of HTML, open your Web form in HTML view and delete all theHTML, leaving only the first yellow line at the top of the file.
Private Sub Page_Load(ByVal sender As_
System.Object, ByVal eAs 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 TabDelimited Format
Private Function ConvertDtToTDF(ByVal _
dt As DataTable) AsString
Dim dr As DataRow,ary() As Object, i As Integer
Dim iCol As Integer
'Output Column Headers
For iCol = 0 Todt.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 outputtab-delimited data to Excel.
In the Page Load event, the firstthing you need to do is grab your data. In this example, a DataTable isretrieved from the Application object. The next step is to set the Content Typeof the output to application/ms-excel. This important step tells the browserthe 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 XLSextension. The final step is to coerce the DataTable into a tab-delimitedstring and write it out to the response stream.
Now set your new form as the startpage and run the app. Notice that Excel treats the data as a regularspreadsheet and you can sort it, sum it, format it, and so on. Your usersshould 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-textapproach.
Figure 4. TDF is nice and simple, and it's compatible with everythingeven though the output is not very attractive.
Beautify Your Output With HTML
TDF is fine for basic needs, but asyou can see in Figure 4, it's just so darn ugly. If you can count on your usershaving at least Excel 97, you can use HTML format instead of TDF and still getall the formatting niceties that go along with it.
To begin, add a new Web form toyour project. There's no need to delete the HTML from the Web form this timebecause HTML is what you intend to output. Next, drag a DataGrid control ontoyour Web form from the toolbar. Then right-click on the grid and choose AutoFormat from the context menu. You'll see a dialog box appear from which you canchoose an attractive format for your data. Once you've selected a suitableformat, click on OK to close the dialog box, go to your codebehind, and enterthis code, which will output a rich, nicely formatted spreadsheet:
Protected Overrides Sub Render(ByVal_
writer AsSystem.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 lookfamiliar to you because they're identical to the TDF example. Now, it's simply amatter of binding the DataGrid to your DataTable and rendering the output.
Once again, if you set your newform as the start page and try it out, you'll see that Excel still treats thedata 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 muchricher than those in the previous TDF example.
Figure 5. The simplicity and wide variety of formatting options with theHTML output technique make it a desirable option.
It's worth noting here that youjust as easily can output to Microsoft Word instead of Excel with only a coupleof basic changes. This code shows that you need only to change the Content Typeand the file extension:
Protected Overrides Sub Render(ByVal_
writer AsSystem.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 thebest option for most projects. But there's something appealing about harnessingthe horsepower available on the client. After all, these aren't dumb clientswe're talking about; the user likely is looking at the data on a Web pagealready when they click on your Export to Excel link. In this case, the controlreturns to the server - which gets the data all over again - regenerates thedata in an Excel-compatible format, and sends it all back to the client. Thisseems like a lot of work considering the data was formatted nicely on theuser's computer screen already. I'll now explain another approach that simplykeeps it there and hands it off to Excel.
This approach is best for anintranet environment because it requires a recent version of Internet Explorerand it might also require modifying the browser security settings to permit theclient-side code to do all the work. You can find the setting you'll mostlikely need to adjust in the Internet Options dialog under the Tools dropdownmenu in Internet Explorer. Go to the Security tab, select Local intranet, andclick on the Custom Level button. Find the setting titled "Initialize andscript ActiveX controls not marked as safe" and change the selection either toEnable or Prompt. The security implications speak for themselves here, so youmight want to get permission from your manager before you implement large-scalechanges.
The plus side to this technique isit reduces server load and network traffic while it takes advantage of theworkstation's power. To begin, add a new Web form to your project and drag aDataGrid onto it. Then, put some basic data-binding code into your codebehind'sPage_Load event:
Dim dt As DataTable = _
CType(Application.Item("MyDataTable"), DataTable)
DataGrid1.DataSource = dt
DataGrid1.DataBind()
Next, go into the HTML view of yourpage and add a button beneath the DataGrid with this definition:
<BUTTON id="exportbutton"onclick="exportbutton_onclick"
name="exportbutton" type="button"value="Button">
Export To Excel</BUTTON>
Now add this client-side VBScript at the very end, afterthe closing </HTML> tag:
<script language="vbscript">
Sub exportbutton_onclick
Dim sHTML, oExcel, oBook
sHTML =document.all.item("DataGrid1").outerhtml
Set oExcel =CreateObject("Excel.Application")
Set oBook =oExcel.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
oBook.HTMLProject.RefreshDocument
oExcel.Visible = true
oExcel.UserControl =true
End Sub
</script>
This is the code that does all thework. When you click on the button, this VBScript code executes in the users'browser. It grabs the HTML that is your grid, opens Excel, and deposits theHTML onto the first spreadsheet (see Figure 6).
Figure 6. Modify a couple of code lines, and voila! - your datais now output as a Word document instead of Excel.
It's as simple as that. Now setthis Web form as your start page and give it a whirl. Notice there's nopostback when you click on the button; all the work stays on the client sideand your server is never disturbed for data that it distributed already.
It's important to note that if youhave multiple pages of data in your DataGrid, only the current page will beexported to Excel. To solve the problem, simply turn off paging by setting yourDataGrid's AllowPaging property to False.
Theoretically you could takeadvantage of preformatted Excel spreadsheet templates here as you could withthe COM Interop technique I discussed at the beginning of this article. As Imentioned, this opens up new possibilities such as automatic Excel charts andgraphs, multiple sheets, VBA modules, and so on. The only difference is theInterop that's happening this time is between the user's browser and aninstance of Excel on their machine. One of your first challenges would be tothink of a way to distribute a template Excel file so it'll be available fromthe client in a known location when your code needs it.
Hopefully you're not toooverwhelmed with all the different options you now have for exporting your datato Excel. But knowledge is power. All the techniques I've described have theirtrade-offs, but now you should be able to weigh each of the techniques fortheir merits in relation to your present and future development projects. Goodluck!
The sample code in thisarticle is available for download.
Steve C. Orr is an MCSD from the Seattle area who hasbeen programming in various languages for nearly 20 years. He's worked onnumerous projects for Microsoft and currently works with The Cadmus Group Inc.,a company focused on software development and ecological issues. Find him at http://Steve.Orr.netor e-mail him at mailto:Steve@Orr.net.
Alternatives
Microsoft Office 2003 (http://www.microsoft.com/office/preview/developer/default.asp)holds a lot of promise for the future. For example, both Word and Excel 2003can save and load natively to and from XML format. Theoretically, you couldgenerate and output this XML format pretty much the same way the Tab Delimitedand HTML examples work. Of course, all your users will need Office 2003installed on their systems before you can take advantage of this, so thedetails shall remain left to your imagination for now.
Right now you can use somethird-party .NET tools you can use to assist your exports if you're so inclined. I haven't used themand therefore can't endorse them, but I submit them to you for yourinvestigation. Neither requires Excel to be installed on the server.
SoftArtisans ExcelWriter (http://excelwriter.softartisans.com)claims to simplify Web reporting by generating Excel spreadsheets from existingExcel files, templates, scripts, and any kind of data source. Anotherintriguing feature is the ability for the users to edit the spreadsheet andsubmit it back to your Web site. If the scalability and rich feature set livesup to its claims, you might be able to overlook the fact that this software isCOM-based. Try it for free.
Aspose.Excel (http://www.aspose.com) could be worthinvestigating if you want to try doing things the other way around, such asgenerating a DataTable from cells in an Excel spreadsheet. You can download afree evaluation copy, so all you've got to lose is some tinkering time.