Send query results to Excel from ASP.NET website

Question

We let users create ad-hoc queries in our website. We would like to have the user select their criteria, then click submit and have the results streamed automatically to Excel. I have the application populating a DataTable, then using the datatable to create a tab delimited string. The problem is getting that to excel.

What is the best way to stream data to Excel? Preferrably, we wouldn't have to make users close an empty window after clicking the submit button.

Answer 1

Change the page's file type to excel, and only stream the HTML necessary to build a table to the page. code from here

//for demo purpose, lets create a small datatable & populate it with dummy data
System.Data.DataTable workTable = new System.Data.DataTable();

//The tablename specified here will be set as the worksheet name of the generated Excel file. 
workTable.TableName = "Customers";
workTable.Columns.Add("Id");
workTable.Columns.Add("Name");
System.Data.DataRow workRow;

for (int i = 0; i <= 9; i++)
{
workRow = workTable.NewRow();
workRow[0] = i;
workRow[1] = "CustName" + i.ToString();
workTable.Rows.Add(workRow);
}

//...and lets put DataTable2ExcelString to work
string strBody = DataTable2ExcelString(workTable);

Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");
Response.Write(strBody);

Answer 2

I'd recommend using a filehandler (.ashx) The only issue is creating the excel file from the DataTable. There are a lot of third party products that will do this for you (e.g. Infragistics provides a component that does just this).

One thing I highly recommend against is using the Excel interop on your server...it's very heavyweight and isn't supported.

Answer 3

If you create a page that is just a table with the results and set the page's content type to "application/vnd.ms-excel", then the output will be in Excel.

Response.ContentType = "application/vnd.ms-excel";

If you want to force a save, you would do something like the following:

Response.AddHeader("Content-Disposition", "attachment; filename=somefilename.xls");

Answer 4

Once you have your Dataset you can convert it to an object[,] and insert it into an Excel document. Then you can save the document to disk and stream it to the user.

        //write the column headers
        for (int cIndex = 1; cIndex < 1 + columns; cIndex++)
            sheet.Cells.set_Item(4, cIndex, data.Columns[cIndex - 1].Caption);
        if (rows > 0)
        {

            //select the range where the data will be pasted
            Range r = sheet.get_Range(sheet.Cells[5, 1], sheet.Cells[5 + (rows - 1), columns]);

            //Convert the datatable to an object array
            object[,] workingValues = new object[rows, columns];

            for (int rIndex = 0; rIndex < rows; rIndex++)
                for (int cIndex = 0; cIndex < columns; cIndex++)
                    workingValues[rIndex, cIndex] = data.Rows[rIndex][cIndex].ToString();

            r.Value2 = workingValues;
         }

Answer 5

I would use a handler for the .xls file extension and a free component to convert the DataTable to native xls format. The component from this site http://www.csvreader.com/ does more that the URL implies. The newest version of excel will complain about an HTML formatted XLS file. Also keep in mind the size of the data being returned. Your web server should use compression for this extension and your code should check if the number of rows returned is greater than what excel can display in one worksheet; multiple sheets may be required. http://www.mrexcel.com/archive2/23600/26869.htm

Answer 6

I got a utils function that does this already. Once you put it into a datatable, you can export it with the Response using

        public static void DataTabletoXLS(DataTable DT, string fileName)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Charset = "utf-16";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xls", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        string tab = "";
        foreach (DataColumn dc in DT.Columns)
        {
            HttpContext.Current.Response.Write(tab + dc.ColumnName.Replace("\n", "").Replace("\t", ""));
            tab = "\t";
        }
        HttpContext.Current.Response.Write("\n");

        int i;
        foreach (DataRow dr in DT.Rows)
        {
            tab = "";
            for (i = 0; i < DT.Columns.Count; i++)
            {
                HttpContext.Current.Response.Write(tab + dr[i].ToString().Replace("\n", "").Replace("\t", ""));
                tab = "\t";
            }
            HttpContext.Current.Response.Write("\n");
        }
        HttpContext.Current.Response.End();
               }

Answer 7

Hi, I have a problem. One of the datatable colums value is a string value '001200' for example. When the Excel document creats the value became '1200'. How can I keep a data format as is? I'm working with ASP.NET 1.1.

The part of the code is:

private void lnkExport_Click( object sender, System.EventArgs e ) {

Response.Clear();

Response.Buffer= true;

Response.ContentType = "application/vnd.ms-excel";

Response.AddHeader( "Content-Disposition", "attachment; filename=" + "CartsList.xls" );

Response.Charset = "iso-8859-8";

Response.Cache.SetCacheability( HttpCacheability.Public );

Response.ContentEncoding = System.Text.Encoding.UTF7;

this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter( oStringWriter );

this.ClearControls( dtgCarts );

dtgCarts.RenderControl( oHtmlTextWriter );

Response.Write( oStringWriter.ToString() );

Response.End();

}

Thank you.

Answer 8

Kindly use this code to resolve your problem.This code will convert excel sheet to text format.Hope this will solve your problem

    grdSrcRequestExport.RenderControl(oHtmlTextWriter);
    string s = "";
    s=oStringWriter.ToString().Replace("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">", "");
    s="<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\"&gt;&lt;head&gt;&lt;meta http-equiv=Content-Type content=\"text/html; charset=us-ascii\"><meta name=ProgId content=Excel.Sheet><meta name=Generator content=\"Microsoft Excel 11\"><table x:str border=0 cellpadding=0 cellspacing=0 width=560 style='border-collapse: collapse;table-layout:fixed;width:420pt'>"+s.ToString()+"</table></body></html>";
    //Byte[] bContent = System.Text.Encoding.GetEncoding("utf-8").GetBytes();
    Response.Write(s);