写的DataReader行到Excel文件 [英] Writing DataReader Rows to Excel File

问题描述

我在SQL Server 2000中得到的数据，并有一个超链接进入直通形式，其code-背后将输出数据到Excel文件。我一直在关注这个教程：

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

我已经成功地从DataReader的输出部分样本值。第一个问题，我遇到的是，有1.1没有DataTable的Load方法。我有数据回来通过DataReader的，但我需要帮助的是如何创建的标题和它们输出，以及数据行，到Excel文件...

  Response.Clear（）;
Response.ClearContent（）;
Response.ClearHeaders（）;
将Response.Buffer =真;

字符串连接
    =附件;文件名= Report_+ DateTime.Now.ToString（）+的.xl​​s;
Response.AddHeader（内容处置，附件）;

Response.Charset的=的String.Empty;
Response.Cache.SetCacheability（System.Web.HttpCacheability.Public）;
Response.ContentType =应用程序/ MS-Excel的;

数据表DT =新的DataTable（）;
dt.Columns.Add（「本公司」）;
dt.Columns.Add（地址1）;
dt.Columns.Add（地址2）;
dt.Columns.Add（城市）;
dt.Columns.Add（国家）;
dt.Columns.Add（ZIP code）;

SqlConnection的CON =新的SqlConnection（）;
SqlCommand的COM =新的SqlCommand（）;
con.ConnectionString =myconnstring;
com.Connection = CON;
com.CommandText
    =SELECT DISTINCT公司，地址1，地址，城市，州，邮编code+
      从Vendor_View;
con.Open（）;

SqlDataReader的博士= com.ExecuteReader（）;
而（dr.Read（））
{
    //如何抓住和输出数据到Excel？
}
 

解决方案

如果是简单的数据，那么就发出一个CSV文件。 Excel中可以配置轻松打开这些pretty的。

类似以下内容将让你开始：

  response.ContentType =文/ CSV;
response.AddHeader（内容处置，附件;文件名= report.csv;）;
response.AddHeader（杂，无缓存）;
response.AddHeader（过期，0）;

// 1.输出列
布尔addComma = FALSE;
回复于（\）;
的foreach（在_dataToProcess.Columns的DataColumn列）{
    如果（addComma）{
        回复于（\，\）;
    } 其他 {
        addComma =真;
    }
    RESPONSE.WRITE（column.ColumnName.ToString（））;
} //的foreach列
回复于（\）;


RESPONSE.WRITE（System.Environment.NewLine）;

// 2.输出数据
的foreach（DataRow的行_dataToProcess.Rows）{
    addComma = FALSE;
    回复于（\）;
    的foreach（在row.ItemArray对象的值）{
        //处理任何嵌入引号。
        字符串outValue = Convert.ToString（值）.Replace（\的String.Empty）;
        如果（addComma）{
            回复于（\，\）;
        } 其他 {
            addComma =真;
        }
        RESPONSE.WRITE（outValue）;
    }
    回复于（\）;
    RESPONSE.WRITE（System.Environment.NewLine）;
} //的foreach行
 

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

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? 
}

解决方案

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屋！