昨天临时要做一个把dataset中的数据导出到excel的功能,同事给了一段代码,功能实现,现分享:
在网上又看到一篇更全面的文章,分享地址:http://www.cnblogs.com/xiaotao823/archive/2008/09/26/1299364.html
现在常用的方式是参考:http://excelpackage.codeplex.com/
实例方法:
string sql = string.Format("select * from ...{0}}", SearchTerms);
string name = "Search" + System.DateTime.Now.ToString("yyMMddHHmmss") + ".xlsx";//文件名
DataTable dt = Biz.csFormDateReport(sql);//数据源
FileInfo template = new FileInfo(Server.MapPath("/Controls/CustomerService/Report/TemplateExcel/MonthFormTemplate.xlsx"));//模板文件
if (dt.Rows.Count > 0)
{
if (!template.Exists) throw new Exception("Template file does not exist!");
using (ExcelPackage pck = new ExcelPackage(template, true))
{
ExcelWorksheet ws = pck.Workbook.Worksheets["FormList"];
if (ws != null)
{
const int startRow = 3;//开始插入数据的行
int row = startRow;
foreach (DataRow dr in dt.Rows)
{
//产品名称 工单属性 类别 结案 未结案 详细描述 个案总结
if (row > startRow) ws.InsertRow(row, 1);
ws.Cells[row, 1].Value = dr["ProjectName"].ToString();
........//填充数据 row++;
}
//delete the two spare rows we have in the template
ws.DeleteRow(row, 1);
row--;
ws.Cells[row + 1, 4].CreateArrayFormula("=SUM(D" + startRow + ":D" + row + ")");//添加计算公式
ws.Cells[row + 1, 5].CreateArrayFormula("=SUM(E" + startRow + ":E" + row + ")");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "Application/vnd.ms-Excel";
Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8) + "");
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();
}
}
}
else
{ this.labInfo.Text = "无数据!"; return; }