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 your users.

 

By Steve C. Orr

 

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 <identity impersonate="true"/> to your web.config file or configure your app to run under an appropriate user account. For this code to work, you also might need to grant write privileges to your Web directory for this account (IUSR_machinename if you use identity impersonation).

 

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:

 

<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, after the 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 the work. 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 the HTML onto the first spreadsheet (see Figure 6).

 

Figure 6. Modify a couple of code lines, and voila! - your data is now output as a Word document instead of Excel.

 

It's as simple as that. Now set this Web form as your start page and give it a whirl. Notice there's no postback when you click on the button; all the work stays on the client side and your server is never disturbed for data that it distributed already.

 

It's important to note that if you have multiple pages of data in your DataGrid, only the current page will be exported to Excel. To solve the problem, simply turn off paging by setting your DataGrid's AllowPaging property to False.

 

Theoretically you could take advantage of preformatted Excel spreadsheet templates here as you could with the COM Interop technique I discussed at the beginning of this article. As I mentioned, this opens up new possibilities such as automatic Excel charts and graphs, multiple sheets, VBA modules, and so on. The only difference is the Interop that's happening this time is between the user's browser and an instance of Excel on their machine. One of your first challenges would be to think of a way to distribute a template Excel file so it'll be available from the client in a known location when your code needs it.

 

Hopefully you're not too overwhelmed with all the different options you now have for exporting your data to Excel. But knowledge is power. All the techniques I've described have their trade-offs, but now you should be able to weigh each of the techniques for their merits in relation to your present and future development projects. Good luck!

 

The sample code in this article is available for download.

 

Steve C. Orr is an MCSD from the Seattle area who has been programming in various languages for nearly 20 years. He's worked on numerous 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.net or 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 2003 can save and load natively to and from XML format. Theoretically, you could generate and output this XML format pretty much the same way the Tab Delimited and HTML examples work. Of course, all your users will need Office 2003 installed on their systems before you can take advantage of this, so the details shall remain left to your imagination for now.

 

Right now you can use some third-party .NET tools you can use to assist your exports  if you're so inclined. I haven't used them and therefore can't endorse them, but I submit them to you for your investigation. 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 existing Excel files, templates, scripts, and any kind of data source. Another intriguing feature is the ability for the users to edit the spreadsheet and submit it back to your Web site. If the scalability and rich feature set lives up to its claims, you might be able to overlook the fact that this software is COM-based. Try it for free.

 

Aspose.Excel (http://www.aspose.com) could be worth investigating if you want to try doing things the other way around, such as generating a DataTable from cells in an Excel spreadsheet. You can download a free evaluation copy, so all you've got to lose is some tinkering time.