从ASP.NET网站将查询结果发送到Excel [英] Send query results to Excel from ASP.NET website

问题描述

我们让用户在我们的网站上创建即时查询。我们希望让用户选择他们的标准，然后单击提交，并将结果自动流式传输到Excel。我有应用程序填充一个DataTable，然后使用datatable创建一个制表符分隔的字符串。问题出在excel中。

将数据流传输到Excel是最好的方法？首先，我们不必在点击提交按钮后使用户关闭空白窗口。

解决方案

更改页面的文件类型要excel，并且只能将HTML所需的流传输到页面。 这里的代码< a>

  //用于演示目的，让我们创建一个小型的datatable&使用虚拟数据填充
 System.Data.DataTable workTable = new System.Data.DataTable（）; 
 
 //这里指定的tablename将被设置为生成的Excel文件的工作表名称。 
 workTable.TableName =Customers; 
 workTable.Columns.Add（Id）; 
 workTable.Columns.Add（Name）; 
 System.Data.DataRow workRow; （int i = 0; i< = 9; i ++）
 
 
 {
 workRow = workTable.NewRow（）; 
 workRow [0] = i; 
 workRow [1] =CustName+ i.ToString（）; 
 workTable.Rows.Add（workRow）; 
} 
 
 //...and让DataTable2ExcelString工作
 string strBody = DataTable2ExcelString（workTable）; 
 
 Response.AppendHeader（Content-Type，application / vnd.ms-excel）; 
 Response.AppendHeader（Content-disposition，attachment; filename = my.xls）; 
 Response.Write（strBody）; 
  

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.

解决方案

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

这篇关于从ASP.NET网站将查询结果发送到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！