1.用nuget命令行安装NPOI引用：

Install-Package NPOI -Version 2.4.0；

2.方法：

public void ToExcelFile(string filename, DataTable dt, KeyValuePair<string, string>[] column_names)
{
try
{
// 根据模板文件创建副本
//string filePath = Server.MapPath(this.TemporaryFolder + Path.DirectorySeparatorChar + Guid.NewGuid().ToString() + “.xls”);
MemoryStream ms = new MemoryStream();
var book = new HSSFWorkbook();
var sheet = book.CreateSheet(Path.GetFileNameWithoutExtension(filename));

var row_h = sheet.CreateRow(0);
var style_h = book.CreateCellStyle();
for (int i = 0; i < column_names.Length; i++)
{
var cell = row_h.CreateCell(i, NPOI.SS.UserModel.CellType.String);
cell.SetCellValue(column_names[i].Value);
style_h.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style_h.FillPattern = NPOI.SS.UserModel.FillPattern.FineDots;
style_h.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cell.CellStyle = style_h;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
var row = sheet.CreateRow(i + 1);
for (int j = 0; j < column_names.Length; j++)
{
NPOI.SS.UserModel.ICell cell = null;

if (dt.Columns[column_names[j].Key].DataType == typeof(decimal) || dt.Columns[column_names[j].Key].DataType == typeof(Int64) || dt.Columns[column_names[j].Key].DataType == typeof(Int32) || dt.Columns[column_names[j].Key].DataType == typeof(Int16) || dt.Columns[column_names[j].Key].DataType == typeof(double) || dt.Columns[column_names[j].Key].DataType == typeof(float))
{
cell = row.CreateCell(j, NPOI.SS.UserModel.CellType.Numeric);
if (!(dt.Rows[i][column_names[j].Key] == DBNull.Value || dt.Rows[i][column_names[j].Key] == null))
{
cell.SetCellValue((double)decimal.Parse(dt.Rows[i][column_names[j].Key].ToString()));
}
}
else
{
cell = row.CreateCell(j, NPOI.SS.UserModel.CellType.String);
if (!(dt.Rows[i][column_names[j].Key] == DBNull.Value || dt.Rows[i][column_names[j].Key] == null))
{
cell.SetCellValue(dt.Rows[i][column_names[j].Key].ToString());
}
}
}
}
book.Write(ms);
ms.Flush();

// 输出副本的二进制字节流
this.Response.Clear();
this.Response.ClearContent();
this.Response.ClearHeaders();

this.Response.ContentType = “application/ms-excel”;
this.Response.AppendHeader(“Content-Disposition”, “attachment;filename=” + this.Server.UrlPathEncode(filename));
this.Response.BinaryWrite(ms.ToArray());
// 删除副本
//this.Response.End();
book.Close();
}
catch (Exception)
{
throw;
}
}

3.调用：

KeyValuePair<string, string>[] column_names = new KeyValuePair<string, string>[2];
//设置数据表列名和导出的excel列名对应关系
column_names[0] = new KeyValuePair<string, string>(“id”,”序号”);
column_names[1] = new KeyValuePair<string, string>(“faultnumber”, “缺陷单号”);

ToExcelFile( “123.xls”, dt4, column_names);