DataTable 导出Excel 下载 (NPOI)

 

 

public class  ExcelHelper

{

   public void DownLoadExcelNew(System.Data.DataTable data, Hashtable h, string fileName)
 {
MemoryStream m = DataTableToMemoryNew(data, h);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.Buffer = false;
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", m.Length.ToString());
m.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();

}
public MemoryStream DataTableToMemoryNew(System.Data.DataTable data, Hashtable h)
{
IWorkbook wk = new HSSFWorkbook();
ISheet sheet = wk.CreateSheet("work");
IRow rowCloumn = sheet.CreateRow(0);

int count = 0;
for (int i = 0; i < data.Columns.Count; i++)
{
if (h.ContainsKey(data.Columns[i].ColumnName.ToLower()))
{
rowCloumn.CreateCell(count).SetCellValue(h[data.Columns[i].ColumnName.ToLower()].ToString());
count++;
}
}


for (int r = 0; r < data.Rows.Count; r++)
{
//创建行
IRow row = sheet.CreateRow(r + 1);
count = 0;
for (int c = 0; c < data.Columns.Count; c++)
{
if (h.ContainsKey(data.Columns[c].ColumnName.ToLower()))
{
row.CreateCell(count).SetCellValue(data.Rows[r][c].ToString());
count++;
}
}
}
MemoryStream memoryStream = new MemoryStream();
wk.Write(memoryStream);
return memoryStream;


}


}