1、优先通过管理Nuget程序包添加NPOI引用
2、添加引用
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util;
3、帮助类
public class ExcelHelper { /// <summary> /// 导出数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="filePath">文件目录</param> /// <param name="fileName">文件名，为空时默认：新建Excel.xlsx</param> /// <param name="headers">表格头</param> /// <param name="data">表格数据</param> /// <returns></returns> public static string ExportExcel<T>(string filePath, string fileName, IEnumerable<ExcelHeader> headers, ExcelData<T> data) { //导出的excel文件地址 string excelPath = string.Empty; #region 文件名称处理 //为空时设置默认文件名 if (string.IsNullOrWhiteSpace(fileName)) { fileName = "新建Excel.xlsx"; } //目录为空时取项目基目录 if (string.IsNullOrWhiteSpace(filePath)) { filePath = AppContext.BaseDirectory; } if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } if (!filePath.EndsWith("\\") && !filePath.EndsWith("/")) { filePath = filePath + "\\"; } excelPath = $"{filePath}{fileName}"; #endregion //初始化 IWorkbook workbook = new HSSFWorkbook(); //工作簿 ISheet sheetTable = workbook.CreateSheet(); //生成表头 #region 表头 foreach (var item in headers) { IRow headerRow = sheetTable.GetRow(item.FirstRow); if (headerRow == null) { headerRow = sheetTable.CreateRow(item.FirstRow); //行高，避免自动换行的内容将行高撑开 headerRow.HeightInPoints = 24; } ICell headerCell = headerRow.CreateCell(item.FirstCol); headerCell.SetCellValue(item.Value); //设置跨行 if (item.FirstRow != item.LastRow || item.LastCol != item.FirstCol) { //CellRangeAddress(开始行,结束行,开始列,结束列) //行列索引由0开始 var region = new CellRangeAddress(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol); sheetTable.AddMergedRegion(region); } headerCell.CellStyle = HeaderStyle(workbook); } #endregion #region 表格数据 var type = data.Data.First().GetType();//获取列表的字段的属性 var properties = type.GetProperties();//筛选出需要 //加载数据 foreach (var item in data.Data) { IRow dataRow = sheetTable.GetRow(data.StartRow); if (dataRow == null) { dataRow = sheetTable.CreateRow(data.StartRow); //行高，避免自动换行的内容将行高撑开 dataRow.HeightInPoints = 20; } var startCol = data.StartCol; foreach (var item1 in properties) { ICell dataCell = dataRow.CreateCell(startCol); var dataValue = item1.GetValue(item); dataCell.CellStyle = DataDefautStyle(workbook); IDataFormat dataformat = workbook.CreateDataFormat(); //设置内容格式 if (dataValue?.GetType() == typeof(int) || dataValue?.GetType() == typeof(decimal) || dataValue?.GetType() == typeof(double) || dataValue?.GetType() == typeof(float)) { double dataValueD = 0; double.TryParse(dataValue?.ToString(), out dataValueD); dataCell.SetCellValue(dataValueD); //精确到小数点后两位 //dataCell.CellStyle.DataFormat = dataformat.GetFormat("0.00"); //添加千分位分割,并保留两位小数 dataCell.CellStyle.DataFormat = dataformat.GetFormat("#,##0.00"); //金钱格式-千分位分割,并保留两位小数 //dataCell.CellStyle.DataFormat = dataformat.GetFormat("￥#,##0.00"); //中文大写(会有四舍五入的情况) //dataCell.CellStyle.DataFormat = dataformat.GetFormat("[DbNum2][$-804]0"); //科学计数法 //dataCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); } else if (dataValue?.GetType() == typeof(DateTime)) { DateTime.TryParse(dataValue?.ToString(), out DateTime date); dataCell.SetCellValue(date); dataCell.CellStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd hh:mm:ss"); } else { dataCell.SetCellValue(dataValue?.ToString()); dataCell.CellStyle.DataFormat = dataformat.GetFormat("text"); } startCol++; } startCol = data.StartCol; data.StartRow++; } #endregion #region 生成文件 FileStream fs = File.Open(excelPath, FileMode.Create, FileAccess.Write); workbook.Write(fs); sheetTable = null; workbook = null; fs.Close(); fs.Dispose(); #endregion return excelPath; } /// <summary> /// 数据单元格样式 /// </summary> private static ICellStyle DataDefautStyle(IWorkbook workbook) { ICellStyle style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; //居中 style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 style.WrapText = true;//自动换行 边框 //style.BorderBottom = BorderStyle.Thin; //style.BorderLeft = BorderStyle.Thin; //style.BorderRight = BorderStyle.Thin; //style.BorderTop = BorderStyle.Thin; // 字体 IFont font = workbook.CreateFont(); font.FontHeightInPoints = 12; font.FontName = "宋体"; style.SetFont(font); return style; } /// <summary> /// 表头样式 /// </summary> public static ICellStyle HeaderStyle(IWorkbook workbook) { ICellStyle style = workbook.CreateCellStyle(); //居中 style.Alignment = HorizontalAlignment.Center; //垂直居中 style.VerticalAlignment = VerticalAlignment.Center; //自动换行 style.WrapText = true; //边框 //style.BorderBottom = BorderStyle.Thin; //style.BorderLeft = BorderStyle.Thin; //style.BorderRight = BorderStyle.Thin; //style.BorderTop = BorderStyle.Thin; //边框颜色 //style.TopBorderColor = HSSFColor.Black.Index; //style.BottomBorderColor = HSSFColor.Black.Index; //style.RightBorderColor = HSSFColor.Black.Index; //style.LeftBorderColor = HSSFColor.Black.Index; //字体 IFont font = workbook.CreateFont(); font.FontHeightInPoints = 14; font.FontName = "宋体"; font.IsBold = true; style.SetFont(font); return style; } } /// <summary> /// 表头格式 /// </summary> public class ExcelHeader { /// <summary> /// 标题 /// </summary> public string Value { get; set; } /// <summary> /// 开始行，索引0开始 /// </summary> public int FirstRow { get; set; } /// <summary> /// 结束行，索引0开始 /// </summary> public int LastRow { get; set; } /// <summary> /// 开始列，索引0开始 /// </summary> public int FirstCol { get; set; } /// <summary> /// 结束列，索引0开始 /// </summary> public int LastCol { get; set; } } /// <summary> /// 表数据 /// </summary> /// <typeparam name="T"></typeparam> public class ExcelData<T> { /// <summary> /// 表数据起始行，索引0开始 /// </summary> public int StartRow { get; set; } /// <summary> /// 表数据起始列，索引0开始 /// </summary> public int StartCol { get; set; } /// <summary> /// 行数据 /// </summary> public IEnumerable<T> Data { get; set; } }
4、数据实体类
public class EvectionData { /// <summary> /// 姓名 /// </summary> public string Name { get; set; } /// <summary> /// 出发地 /// </summary> public string StartLocation { get; set; } /// <summary> /// 目的地 /// </summary> public string EndLocation { get; set; } /// <summary> /// 交通工具 /// </summary> public string Vehicle { get; set; } /// <summary> /// 交通费 /// </summary> public decimal Transportation { get; set; } /// <summary> /// 总金额 /// </summary> public decimal Amount { get; set; } /// <summary> /// 备注 /// </summary> public string Remark { get; set; } /// <summary> /// 时间 /// </summary> public DateTime? ItemDate { get; set; } }
5、调用
var filePath = $"C:\\Users\\Administrator\\Desktop\\"; var fileName = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; var header = new List<ExcelHeader>() { new ExcelHeader (){ FirstCol=0, LastCol=7, FirstRow=0, LastRow=0, Value="xxxxxxxxxx公司" },//(第一行)(跨列0-6) new ExcelHeader (){ FirstCol=0, LastCol=7, FirstRow=1, LastRow=1, Value="费用报销明细表" },//(第二行)(跨列0-6) new ExcelHeader (){ FirstCol=0, LastCol=0, FirstRow=2, LastRow=3, Value="姓名" },//第一列，跨两行(2-3) new ExcelHeader (){ FirstCol=1, LastCol=2, FirstRow=2, LastRow=2, Value="行程" },//跨两列(1,2),同一行(第三行) new ExcelHeader (){ FirstCol=1, LastCol=1, FirstRow=3, LastRow=3, Value="出发地" }, new ExcelHeader (){ FirstCol=2, LastCol=2, FirstRow=3, LastRow=3, Value="目的地" }, new ExcelHeader (){ FirstCol=3, LastCol=4, FirstRow=2, LastRow=2, Value="交通费" }, new ExcelHeader (){ FirstCol=3, LastCol=3, FirstRow=3, LastRow=3, Value="交通工具"}, new ExcelHeader (){ FirstCol=4, LastCol=4, FirstRow=3, LastRow=3, Value="交通费"}, new ExcelHeader (){ FirstCol=5, LastCol=5, FirstRow=2, LastRow=3, Value="总金额"}, new ExcelHeader (){ FirstCol=6, LastCol=6, FirstRow=2, LastRow=3, Value="备注"}, new ExcelHeader (){ FirstCol=7, LastCol=7, FirstRow=2, LastRow=3, Value="时间"}, }; var listData = new List<EvectionData>() { new EvectionData (){ Name="姓名1", StartLocation="长沙", EndLocation= "深圳",Vehicle="高铁", Transportation=388.5M, Amount=388.5M, Remark="备注1",ItemDate=DateTime.Now }, new EvectionData (){ Name="姓名2", StartLocation="清远", EndLocation= "长沙",Vehicle="高铁", Transportation=279M, Amount=279M, Remark="备注1" }, }; var data = new ExcelData<EvectionData>{ StartCol = 0, StartRow = 4, Data = listData }; ExcelHelper.ExportExcel(filePath, fileName, header, data);
6、结果
参考文章：ExcelHelper: 生成复杂表头，导出数据 (gitee.com)