Asp.Net C# Creating an Excel Document From Data without using Office Interops

Posted by OmegaMan at August 3, 2009

Category: ASP.Net, Excel, Microsoft Office

Tags: ,

Excel It is a common misconception that one needs the office interops to create Excel documents for the user to download when dealing with Asp.net on a server. Well this article shows one how to create an Excel document from just the data at hand, say from Linq entities, without being tied to any control nor having to use office interops!

How Does one Send the Data to the User?

Here is the shell which will encompass the code which creates the document. Basically one uses the response class to send the data back to the user, say on a button click or some other relevant event. By specifying that this object will be a ms-excel file and it has an .xls extension, that is half the battle right there.

Response.Clear();
Response.Buffer      = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader( "Content-Disposition", "attachment;filename=registrationData.xls" );
this.EnableViewState = false;

//  Code here to generate the xls document.
...

HttpContext.Current.Response.Write( /* String Buffer to Here */ );
HttpContext.Current.Response.End();

That was pretty easy to send the information to the user using a few C# classes which are globally exposed to the code-behind of our web page.

Generate an Excel document from Data in C#

Our goal is not to be tied to any control on the web page, though some controls such as the GridView have the ability to easily export a table which can be passed on. But for our purposes we want to simply extract the data from a linq query and generate the document. By keeping this example generic, one can adapt it to many different situations.

// We will be creating html on the fly via the
// string and html writers to create a table on the fly.
using ( StringWriter sw = new StringWriter() )
    using ( HtmlTextWriter htw = new HtmlTextWriter( sw ) )
    {
        //  Create a table to contain the grid
        Table table = new Table();

        //  Gridlines to box the cells
        table.GridLines = System.Web.UI.WebControls.GridLines.Both;

        // We are going to add the header row to the data first.
        List columns = new List() {
        "Sent", "Sent Date", "User Name", "Last Name", "First Name",
        "Work Phone", "Work Email",    "Number of Tickets", "Street Address 1",
        "Street Address 2",    "City", "State", "Zip Code" };

        // Each row will need to be declared upfront and cells added later.
        TableRow tRow = new TableRow();
        string value;

        foreach ( string name in columns )
            tRow.Cells.Add( new TableCell() { Text = name } );

        table.Rows.Add( tRow ); // Done! Header row added.


        // Note TableCell has a property BackColor. Once can set the
        // that within the cell such as BackColor = System.Drawing.Color.Blue
        // and that will carry into the Excel Document!

        // UserData is our Linq entity list and we will enumerate it to fill the into cells of the row
        // and subsequently into the table.
        foreach ( var usr in UserData )
        {
            tRow = new TableRow();

            value = ( usr.ticketsSent == null ) ? "N" : usr.ticketsSent.Value.ToString();
            tRow.Cells.Add( new TableCell() { Text = value } );

            value = ( usr.ticketsSentDate == null ) ? string.Empty : usr.ticketsSentDate.Value.ToShortDateString();

            tRow.Cells.Add( new TableCell() { Text = value } );
            tRow.Cells.Add( new TableCell() { Text = usr.userName } );
            tRow.Cells.Add( new TableCell() { Text = usr.lastName } );
            tRow.Cells.Add( new TableCell() { Text = usr.firstName } );
            tRow.Cells.Add( new TableCell() { Text = usr.workNumber } );
            tRow.Cells.Add( new TableCell() { Text = usr.workEmail } );
            tRow.Cells.Add( new TableCell() { Text = usr.ticketsRequested.Value.ToString() } );
            tRow.Cells.Add( new TableCell() { Text = usr.address1 } );
            tRow.Cells.Add( new TableCell() { Text = usr.address2 } );
            tRow.Cells.Add( new TableCell() { Text = usr.city } );
            tRow.Cells.Add( new TableCell() { Text = usr.state } );
            tRow.Cells.Add( new TableCell() { Text = usr.zipCode } );

            table.Rows.Add( tRow ); // We add each row to the table.
        }

        //  Translate/Render the table into the htmlwriter
        table.RenderControl( htw );

        //  Response needs the Htmlwriter
        HttpContext.Current.Response.Write( sw.ToString() );
        HttpContext.Current.Response.End();
       }

By simply creating a mock table we can load it and ship it out to the user as an automatic download.

Full Code

Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader( "Content-Disposition", "attachment;filename=registrationData.xls" );
this.EnableViewState = false;

using ( StringWriter sw = new StringWriter() )
    using ( HtmlTextWriter htw = new HtmlTextWriter( sw ) )
    {
        //  Create a table to contain the grid
        Table table = new Table();

        //  Gridline to box the cells
        table.GridLines = System.Web.UI.WebControls.GridLines.Both;

        List columns = new List() {
        "Sent", "Sent Date", "User Name", "Last Name", "First Name",
        "Work Phone", "Work Email",    "Number of Tickets", "Street Address 1",
        "Street Address 2",    "City", "State", "Zip Code" };

        TableRow tRow = new TableRow();
        string value;

        foreach ( string name in columns )
            tRow.Cells.Add( new TableCell() { Text = name } );

        table.Rows.Add( tRow );


        // BackColor = System.Drawing.Color.Blue
        foreach ( var usr in UserData )
        {
            tRow = new TableRow();

            value = ( usr.ticketsSent == null ) ? "N" : usr.ticketsSent.Value.ToString();
            tRow.Cells.Add( new TableCell() { Text = value } );

            value = ( usr.ticketsSentDate == null ) ? string.Empty : usr.ticketsSentDate.Value.ToShortDateString();

            tRow.Cells.Add( new TableCell() { Text = value } );
            tRow.Cells.Add( new TableCell() { Text = usr.userName } );
            tRow.Cells.Add( new TableCell() { Text = usr.lastName } );
            tRow.Cells.Add( new TableCell() { Text = usr.firstName } );
            tRow.Cells.Add( new TableCell() { Text = usr.workNumber } );
            tRow.Cells.Add( new TableCell() { Text = usr.workEmail } );
            tRow.Cells.Add( new TableCell() { Text = usr.ticketsRequested.Value.ToString() } );
            tRow.Cells.Add( new TableCell() { Text = usr.address1 } );
            tRow.Cells.Add( new TableCell() { Text = usr.address2 } );
            tRow.Cells.Add( new TableCell() { Text = usr.city } );
            tRow.Cells.Add( new TableCell() { Text = usr.state } );
            tRow.Cells.Add( new TableCell() { Text = usr.zipCode } );

            table.Rows.Add( tRow );
        }

        //  Htmlwriter into the table
        table.RenderControl( htw );

        //  Htmlwriter into the response
        HttpContext.Current.Response.Write( sw.ToString() );
        HttpContext.Current.Response.End();
   }
Share

10 Comments

  1. Asp.Net C# Creating an Excel Document From Data without using Office Interops « Jasper Blog says

    […] Read more: OmegaMan’s Musings […]

    Reply
  2. Ranadhir Dey says

    Please suggest me how can I use this code for excel 2007(.xlsx file).

    Reply
  3. omegaman says

    Have you tried changing the name from .xls to .xlsx?

    Reply
  4. Anzart says

    I try to open with office 2007 for generated xls files.
    the file you are trying to open is different format than specified by extension.
    And when i see the generated xls files with text document, its only table html tag.
    So everytime i open with excel it will be show the same error.
    Thx

    Reply
  5. pradyumna kumar singh says

    how to save the generated excel sheet in Sever.MapPath
    EX=file.saveAs(server.mapPath(“EXCELL/ “+ filename);

    Reply
  6. asp.net, c#,javascript says

    asp.net, c#,javascript…

    […]Asp.Net C# Creating an Excel Document From Data without using Office Interops « OmegaMan's Musings[…]…

    Reply
  7. Link.fr says

    Thanks for sharing!
    We can do the same thing with rss feeds if we want to build them on the fly from a LINQ query for instance.

    Reply
  8. lee suko says
  9. Rajesh says

    Hi,

    I want to generate the excel sheet from windows console application.Instead of using Response command
    Response.Clear(); Response.Buffer = true; Response.ContentType = “application/vnd.ms-excel”; Response.AddHeader( “Content-Disposition”, “attachment;filename=registrationData.xls” ); this.EnableViewState = false;

    what else we can use it.

    Reply
  10. Anvesh says

    Thanks for your article. It helps me a lot.

    Reply

Leave a comment

(required)
(required) (will not be published)

This site uses Akismet to reduce spam. Learn how your comment data is processed.