ASP.NET – Generating an Excel Report on the Fly Using the Interop.Excel Namespace

As a developer, a commonly requested feature is the ability to export data from the Web into MS Excel.

Choosing the best way to export your data from an ASP.NET Web page to Excel for your users is never an easy task. There are several ways to generate your reports, with each way having its pros and cons. For starters you’ll have to take into consideration if you are developing a Web based application that needs to enforce strong security, or a more permissive Web-based application that will allow you to write temporary files to your server’s hard drive.

In an earlier article I covered one of the more common ways: how to take an existing report that has already been generated as an HTML table and stream it as Excel to the client browser. This is a great technique, but leaves it up to Excel to interpret the HTML that you are sending its way. Furthermore, Microsoft seems to be deprecating the markup that was previously so amazingly convenient when formatting HTML streamed to Excel. The article also goes into depth about what markup commands are still useful and what markup has been deprecated. It’s definitely worth reading this article if you are considering streaming HTML as an Excel document using the command: Response.ContentType = “application/ms-excel”

Before I go into further details on using the Interop.Excel namespace to generate Excel files, I’d like to point out that I am providing a fully functional example at the end of this article. If you are primarily interested in the code, then you can skip to the bottom of this article and you can copy and paste the code sample into a Visual Studio project where it should run without any problems.

The Benefits of Using the Interop.Excel Namespace to Generate an Excel Document

In this article I will cover a way of handling your exports to Excel from a Web page using the Interop.Excel namespace. In this case, the idea is not to copy an already rendered HTML report to Excel, but instead to re-run the report and then to render it as an actual Excel document. This has the benefit of giving you, the developer, in-depth programmatic control over the work sheets, including detailed control over the cells as they are rendered.

To the user, this method is transparent. You can provide them with something as simple as a button on a Web page that will generate the Excel when it is clicked. Once a user has clicked the button, your code will generate the Excel document from scratch and the user will be prompted to save it to their hard-drive. The Excel file generated won’t have the traditional problems that files generated from HTML can have and your users will thank you for it.

A point to note about this method is that it does require saving to the filesystem of the Server. Furthermore, you will need to configure your Web server to allow DCOM calls from your ASP.NET site. For detailed steps on how to set up your Web server to be able to serve up Excel files using this technique, please see the follow-up article I have linked.

Downsides of Using this Method of Generating an Excel Document

Naturally, this method of creating an Excel document also has its drawbacks. Because you need to iterate through each cell as you render the content of your document, the document generation process is slowed down significantly with larger data sets. So although it is a benefit that you have very granular control over how each cell renders, this process creates a significant performance hit that may be frustrating to your users when they are working with large result-sets.

For example, if you are returning 4,000 rows of data containing five columns, you can expect the document generation time to take a whopping 18 seconds. So if you do use this strategy to generate your Excel documents, you will need to make sure that either the user will only work with smaller data sets, or that they are made aware that the process will not be instant.

Reviewing the Code

Now that we have reviewed the pros and cons of using the Interop.Excel namespace to create Excel documents in your ASP.NET Web applications it’s time to look at how to actually write your code. For starters, the Microsoft.Office.Interop.Excel namespace is not automatically a part of Visual Studio, so you may need to add a reference to this namespace manually. You can find out more about Visual Studio Tools for Office and referencing the proper namespace from this MSDN article. Bottom line, though, is that you will need to reference the Microsoft.Office.Interop.Excel namespace in your code as well as to add a reference to it in your Visual Studio project.

As you can see from the code example below, the process of generating your Excel document is quite straightforward.

1. First you need to query your database. In the example I have put together a direct inline SQL call using barebones System.Data SQL syntax that populates a DataTable.
2. Once you have your DataTable in place, it’s time to start up your Excel document.
   1. The first step is to instantiate your Excel document as an Excel.Application object
   2. Then you create a WorkBooks collection for your Application object
   3. Then you instantiate a specific WorkBook in your WorkBooks collection
   4. Finally you can instantiate your new WorkSheet within your WorkBook
3. When you have a reference to your new WorkSheet, you need to go about populating the content.
4. In the case of this example, we just start up a simple loop through the rows of our DataTable, and we have a sub-loop through the columns in order to read each cell in our resultset so that we can copy it into our WorkSheet.
5. Once you have finished looping through your DataTable, you need to complete your work on the Excel document. To do so, call the Quit() method on your Application object.

That’s all there is to it. You can see that as I mentioned, the process is quite straightforward. The only catch with this method is the performance issue that crops up when your results start exceeding 4,000 records. However, the granular control over the contents of your document that this technique gives is quite valuable as well. You will need to weigh the pros and cons depending on your project’s user requirements and make a decision accordingly.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="Microsoft.Office.Interop.Excel" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 Protected m_connStr As String = String.Empty
Protected Sub Page_Load(sender As Object, e As System.EventArgs)
 Dim tmpTbl As System.Data.DataTable = getSearchResultsTable()

 Dim appExcel As New Microsoft.Office.Interop.Excel.Application()
 Dim wbExcelBooks As Microsoft.Office.Interop.Excel.Workbooks = CType(appExcel.Workbooks, Workbooks)
 Dim wbExcelNewBook As Microsoft.Office.Interop.Excel.Workbook = wbExcelBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
 Dim wsWorkingSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(wbExcelNewBook.Worksheets(1), Worksheet)

 wsWorkingSheet.Cells(1, 1) = "Generated Excel Document"

 For colCnt As Int32 = 0 To tmpTbl.Columns.Count - 1
 For rowCnt As Int32 = 0 To tmpTbl.Rows.Count - 1
 wsWorkingSheet.Cells(rowCnt + 2, colCnt + 1) = tmpTbl.Rows(rowCnt)(colCnt)
 Next
 Next
appExcel.Quit()
 End Sub
 Private Function getSearchResultsTable() As System.Data.DataTable
 Dim returnValue As System.Data.DataTable = Nothing

 Dim dvSource As New DataView()
 Dim da As New SqlDataAdapter()
 Dim ds As New DataSet()
 Dim conn As New SqlConnection()
 m_connStr = ConfigurationManager.ConnectionStrings("ConnDb").ConnectionString
 conn = New SqlConnection()
 conn.ConnectionString = m_connStr
 Try
 conn.ConnectionString = m_connStr
 conn.Open()
 Dim cmd As New SqlCommand()
 cmd.Connection = conn
 cmd.CommandType = CommandType.Text
 cmd.CommandText = "SELECT [Id] " & _
 ",[Name] " & _
 ",[Alias] " & _
 ",[Desc] " & _
 ",[Status] " & _
 " FROM [MyDB].[dbo].[tblExample]"
da = New SqlDataAdapter()
 da.SelectCommand = cmd
 da.Fill(ds)
 returnValue = ds.Tables(0)
 Catch ex As Exception
 Finally
 ds.Dispose()
 ds = Nothing
 If Not da Is Nothing Then
 da.Dispose()
 da = Nothing
 End If
 conn.Close()
 conn.Dispose()
 conn = Nothing
 End Try

 Return returnValue
 End Function
</script>