.net、c#，datatable导出文件到excel

准备工作：

在Nuget包中安装NPOI到项目中，引入：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;

其他需要的自己引入。

1.创建一个excel

/// <summary>
/// 创建一个Excel
/// Yakecan
/// </summary>
/// <returns>返回一个空表格</returns>
public static HSSFWorkbook InitializeWorkBook()
{
HSSFWorkbook workBook = new HSSFWorkbook();
return workBook;
}

2.把指定的数据导出到excel

/// <summary>
/// 把指定的DataTable导出Excel
/// Yakecan
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="path">导出的路径(包含文件的名称及后缀名)</param>
/// <param name="tittle">Sheet的名称</param>
public static void Export(DataTable dt, string path, string tittle)
{
HSSFWorkbook workbook = InitializeWorkBook();
ISheet sheet1 = workbook.CreateSheet(tittle);
IRow titleRow = sheet1.CreateRow(0);
titleRow.Height = (short)20 * 25;
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = (short)16;
titleStyle.SetFont(font);
NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count);
sheet1.AddMergedRegion(region); // 添加合并区域
ICell titleCell = titleRow.CreateCell(0);
titleCell.CellStyle = titleStyle;
titleCell.SetCellValue(tittle);
IRow headerRow = sheet1.CreateRow(1);
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = (short)11;
titleFont.FontName = "宋体";
headerStyle.SetFont(titleFont);
headerRow.CreateCell(0).SetCellValue("序号");
headerRow.GetCell(0).CellStyle = headerStyle;
for (int i = 0; i < dt.Columns.Count; i++)
{
headerRow.CreateCell(i + 1).SetCellValue(dt.Columns[i].ColumnName);
headerRow.GetCell(i + 1).CellStyle = headerStyle;
sheet1.SetColumnWidth(i, 256 * 18);
}
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.BorderBottom = BorderStyle.Thin;
bodyStyle.BorderLeft = BorderStyle.Thin;
bodyStyle.BorderRight = BorderStyle.Thin;
bodyStyle.BorderTop = BorderStyle.Thin;
for (int r = 0; r < dt.Rows.Count; r++)
{
IRow bodyRow = sheet1.CreateRow(r + 2);
bodyRow.CreateCell(0).SetCellValue(r + 1);
bodyRow.GetCell(0).CellStyle = bodyStyle;
bodyRow.GetCell(0).CellStyle.Alignment = HorizontalAlignment.Center;
for (int c = 0; c < dt.Columns.Count; c++)
{
bodyRow.CreateCell(c + 1).SetCellValue(dt.Rows[r][c].ToString());
bodyRow.GetCell(c + 1).CellStyle = bodyStyle;
}
}
sheet1.CreateFreezePane(1, 2);
FileStream fs = new FileStream(path, FileMode.Create);
workbook.Write(fs);
fs.Flush();
fs.Position = 0;
sheet1 = null;
headerRow = null;
workbook = null;
//OutPutExcelStreamOnClient(ms, xlsName);
fs.Dispose();
}

3.调用方法导出
//调用方法：Export(ds.Tables[0], System.AppDomain.CurrentDomain.BaseDirectory.ToString()+"/123.xls", "这是我的测试数据");