将 DataReader 行写入 Excel 文件 [英] Writing DataReader Rows to Excel File

问题描述

我在 SQL Server 2000 中有数据，并且有一个超链接，它转到一个传递表单，其代码隐藏会将数据输出到 Excel 文件.我一直在关注本教程:

I've got data in SQL Server 2000 and have a HyperLink that goes to a pass-through form whose code-behind will output the data to an Excel file. I've been following this tutorial:

http://www.dzone.com/links/r/export_gridview_to_excelcsv_in_net_using_c.html

我已经成功地从 DataReader 输出了一些样本值.我遇到的第一个问题是 1.1 中没有 DataTable Load 方法.我有通过 DataReader 返回的数据，但我需要帮助的是如何创建标题并将它们与数据行一起输出到 Excel 文件...

I have succeeded in outputting some sample values from the DataReader. First problem I'm encountering is that there is no DataTable Load method in 1.1. I have data coming back via the DataReader but what I need help with is how to create the headers and output them, along with the rows of data, to the Excel file...

Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;

string attachment 
    = "attachment;filename=Report_" + DateTime.Now.ToString() + ".xls"; 
Response.AddHeader("content-disposition", attachment);

Response.Charset = string.Empty;
Response.Cache.SetCacheability(System.Web.HttpCacheability.Public);
Response.ContentType = "application/ms-excel";

DataTable dt = new DataTable();
dt.Columns.Add("Company");
dt.Columns.Add("Address1");
dt.Columns.Add("Address2");
dt.Columns.Add("City");
dt.Columns.Add("State");
dt.Columns.Add("ZipCode");

SqlConnection con = new SqlConnection();
SqlCommand com = new SqlCommand();
con.ConnectionString = "myconnstring";
com.Connection = con;
com.CommandText 
    = "SELECT DISTINCT  Company, Address1, Address2, City, State, ZipCode" + 
      " FROM Vendor_View";
con.Open();

SqlDataReader dr = com.ExecuteReader();
while(dr.Read())
{
    // how to grab and output data to Excel? 
}

推荐答案

如果是简单数据，那么就发出一个 CSV 文件.可以将 Excel 配置为非常轻松地打开它们.

If it's simple data, then just emit a CSV file. Excel can be configured to open those pretty easily.

以下内容可以帮助您入门:

Something like the following would get you started:

response.ContentType = "text/csv";
response.AddHeader("Content-Disposition", "attachment;filename=report.csv;");
response.AddHeader("Pragma", "no-cache");
response.AddHeader("Expires", "0");

// 1. output columns
Boolean addComma = false;
response.Write(""");
foreach (DataColumn column in _dataToProcess.Columns) {
    if (addComma) {
        response.Write("","");
    } else {
        addComma = true;
    }
    response.Write(column.ColumnName.ToString());
} // foreach column
response.Write(""");


response.Write(System.Environment.NewLine);

// 2. output data
foreach (DataRow row in _dataToProcess.Rows) {
    addComma = false;
    response.Write(""");
    foreach (Object value in row.ItemArray) {
        // handle any embedded quotes.
        String outValue = Convert.ToString(value).Replace(""", String.Empty);
        if (addComma) {
            response.Write("","");
        } else {
            addComma = true;
        }
        response.Write(outValue);
    }
    response.Write(""");
    response.Write(System.Environment.NewLine);
} // foreach row

这篇关于将 DataReader 行写入 Excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！