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);
导出结果如下
仅个人记录
参考文章:ExcelHelper:生成复杂表头,导出数据