Send query results to Excel from ASP.NET website

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.

标签： asp.net excel

7条回答

放荡不羁爱自由

2楼-- · 2020-02-10 08:32

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

0人赞添加讨论(0) 举报

劳资没心，怎么记你

3楼-- · 2020-02-10 08:32

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\"><head><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);

0人赞添加讨论(0) 举报

别忘想泡老子

4楼-- · 2020-02-10 08:33

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

0人赞添加讨论(0) 举报

淡お忘

5楼-- · 2020-02-10 08:42

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.

0人赞添加讨论(0) 举报

你好瞎i

6楼-- · 2020-02-10 08:45

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

0人赞添加讨论(0) 举报

混吃等死

7楼-- · 2020-02-10 08:45

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");

0人赞添加讨论(0) 举报

1 2 下一页

Send query results to Excel from ASP.NET website

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间