Question

c#怎么把查询出来的数据写到excel中

Answer 1

将sql数据导出excel表格

protected void Button1_Click(object sender, EventArgs e)
{
string strsql = "select * from TreeViewTemp where parentID=0";
DataTable dt = db.GetDataTable(strsql);
StringWriter sw = new StringWriter();
//StreamWriter sw = new StreamWriter("d:\\test.xls",false,System.Text.Encoding.Default); 本地保存
ExportExcel(dt, sw);
Response.AddHeader("Content-Disposition", "attachment; filename=PDSDATA.xls");//远程下载
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
public void ExportExcel(DataTable dt, StringWriter w)
{
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
w.Write(dt.Columns[i]);
w.Write('\t');
}
w.Write("\r\n");
object[] values = new object[dt.Columns.Count];
foreach (DataRow dr in dt.Rows)
{
values = dr.ItemArray;
for (int i = 0; i < dt.Columns.Count; i++)
{
w.Write(values[i]);
w.Write('\t'); //修改语句
}
w.Write("\r\n"); //修改语句
}
w.Flush();
w.Close();
}
catch
{
w.Close();
}
}