OmegaMan's Musings

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();
   }