欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C# 将datatable导出成Excel

程序员文章站 2022-04-19 15:35:20
public void Result( ){try{StringBuilder sql = new StringBuilder();List parameters = new List(); sql.Append(@"sql 查询语句");Da ......

public void result( )
{

try
{
stringbuilder sql = new stringbuilder();
list<sqlparameter> parameters = new list<sqlparameter>();

sql.append(@"sql 查询语句");
datatable dt = dbhelpersql.query(sql.tostring()).tables[0];
stringbuilder tablehead = new stringbuilder();
stringbuilder tablefoot = new stringbuilder();
tablehead.append("<tr style=\"font-weight: bold; white-space: nowrap;\">")
.append("<td rowspan =\"2\"></td></tr>")
.append("<tr></tr>");
tablefoot.append("<tr><td colspan = \"4\">导出时间:" + datetime.now.tostring("yyyy年mm月dd日 hh:mm:ss") + "</td></tr>");
datatabletoexcel(dt, tablehead.tostring(), tablefoot.tostring(), name + "汇总_" + datetime.now.tostring("yyyymm"));

}
catch (exception ex)
{

}
}

/// <summary>
/// datatable导出excel
/// </summary>
/// <param name="data">集合</param>
/// <param name="charthead">表头</param>
/// <param name="filename">文件名称</param>
public static void datatabletoexcel(datatable data, string charthead, string chartfoot, string filename)
{
httpcontext.current.response.contenttype = "application/vnd.ms-excel";
httpcontext.current.response.contentencoding = encoding.utf8;
httpcontext.current.response.charset = "utf-8";
httpcontext.current.response.appendheader("content-disposition", "attachment;filename=" + httputility.urlencode(filename + ".xls", encoding.utf8));
stringbuilder sbhtml = new stringbuilder();
sbhtml.appendline("<meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\">");
sbhtml.appendline("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
//写出表头
sbhtml.appendline(charthead);
string numberastextexp = "vnd.ms-excel.numberformat:@";
//写数据
foreach (datarow row in data.rows)
{
sbhtml.append("<tr>");
for (int i = 0; i < row.itemarray.count(); i++)
{
if (row[i].gettype() == typeof(string))
{
sbhtml.append("<td style='" + numberastextexp + "'>").append(row[i]).append("</td>");
}
else
{
sbhtml.append("<td>").append(row[i]).append("</td>");
}
}
sbhtml.appendline("</tr>");
}
//写汇总行
sbhtml.appendline(chartfoot);
sbhtml.appendline("</table>");
httpcontext.current.response.write(sbhtml.tostring());
httpcontext.current.response.end();
}