ASP.NET Tip: Exporting Data to Excel

A common request of users is to be able to download data for use in Microsoft Excel. This is actually fairly easy to accomplish without a lot of extra work. I do most of my database work with DataTable objects, as I don't need the overhead of a DataSet. I also have a Database wrapper class that is able to easily retrieve data from a SQL Server database. Once I use my function to get my DataTable, I can start exporting the data. For this example, however, I'll use the traditional SqlDataAdapter method to retrieve the data initially.

There are a few tricks to making this work. First, you have to create an ASPX page with no HTML content in it. The only thing that should be in the ASPX page is the Page directive at the top. The second trick is to clear the existing content and send down a new content type. For Excel, you use a content type of application/vnd.ms-excel. This tells your Web browser to expect something that can be handled within Excel. The third trick is to send down the data tab-delimited per column and a newline at the end of each row. After that, Excel does the rest of the work for you.

Here's the code:

protected void Page_Load(object sender, EventArgs e)
{
   SqlConnection cn = new SqlConnection("yourconnectionstring");
   cn.Open();
   SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", cn);
   DataTable dt = new DataTable();
   da.Fill(dt);
   cn.Close();

   Response.Clear();
   Response.ContentType = "application/vnd.ms-excel";
   string sep = "";
   foreach (DataColumn dc in dt.Columns)
   {
      Response.Write(sep + dc.ColumnName);
      sep = "\t";
   }
   Response.Write("\n");

   int i;
   foreach (DataRow dr in dt.Rows)
   {
      sep = "";
      for (i = 0; i < dt.Columns.Count; i++)
      {
         Response.Write(sep + dr[i].ToString());
         sep = "\t";
      }
      Response.Write("\n");
   }

}

After I open my connection and retrieve my data, I clear the output buffer and send down a new content type, since the default type of text/HTML isn't appropriate in this case. I then loop through the columns and write out the column names, separated by tabs. At the end of the field list, I send down a newline character.

I then loop through the rows of the table and, within each row, loop through the fields. Each field value is printed out in its default format under the appropriate column header. If you have data types, such as Booleans, that should be printed in a different format, you can look at the dt.Columns collection to help determine which data type each field is and use an appropriate conversion function.

The result of running this page is a prompt to open or save the Excel spreadsheet. You can use this code with any query and any database connection. Once you've got the content cleared and the content type set, Excel does the hard work of formatting for you.

About the Author

Eric Smith is the owner of Northstar Computer Systems, a Web-hosting company based in Indianapolis, Indiana. He is also a MCT and MCSD who has been developing with .NET since 2001. In addition, he has written or contributed to 12 books covering .NET, ASP, and Visual Basic. Send him your questions and feedback via e-mail at questions@techniquescentral.com.



Comments

  • Encoding for characters > &H7F

    Posted by ChrisN on 09/26/2006 03:53pm

    That's fine as far as it goes. However if you use characters above &H7F as is common in Europe you will need to encode the data to have Excel display it correctly.  Here's an excerpt showing how to encode into Windows-1252. My data happened to have 16 columns. Variable sQuote is just a convenience to embed a quotation mark.
    
                While oSqlDataReader.Read
                    Dim nUpperBound As Integer = 16
                    For i As Integer = 1 To nUpperBound
                        Dim sFieldName As String = "Data" & i.ToString
                        Dim sUnicodeString As String = sQuote & oSqlDataReader(sFieldName) & sQuote & IIf(i < nUpperBound, ",", vbCrLf)
                        Dim eEncoding As Encoding = Encoding.GetEncoding("Windows-1252")
                        Dim eUnicode As Encoding = Encoding.Unicode
                        Dim byUnicodeBytes As Byte() = eUnicode.GetBytes(sUnicodeString)
                        Dim byEncodedBytes As Byte() = Encoding.Convert(eUnicode, eEncoding, byUnicodeBytes)
                        Response.BinaryWrite(byEncodedBytes)
                    Next
                End While
                Response.End()

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: September 17, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Another day, another end-of-support deadline. You've heard enough about the hazards of not migrating to Windows Server 2008 or 2012. What you may not know is that there's plenty in it for you and your business, like increased automation and performance, time-saving technical features, and a lower total cost of ownership. Check out this upcoming eSeminar and join Rich Holmes, Pomeroy's practice director of virtualization, as he discusses the …

  • This ESG study by Mark Peters evaluated a common industry-standard disk VTl deduplication system (with 15:1 reduction ratio) versus a tape library with LTO-5, drives with full nightly backups, over a five-year period.  The scenarios included replicated systems and offsite tape vaults.  In all circumstances, the TCO for VTL with deduplication ranged from about 2 to 4 times more expensive than the LTO-5 tape library TCO. The paper shares recent ESG research and lots more. 

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds