C#封装EPPlus库为Excel导出工具

1，添加NUGet包

2，封装工具类

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;namespace GMWPF.utils
{public class ExcelUtil<T>{/// <summary>/// excel打印/// </summary>/// <param name="datas">需要打印的数据集合</param>/// <param name="columnNames">表头列名</param>/// <param name="columnIgnores">实体类中需要忽略的列名</param>/// <param name="filePath">文件保存路径</param>/// <param name="fileName">文件名</param>public static void ExportExcel(List<T> datas,List<string> columnNames, List<string>columnIgnores,string filePath,string fileName){ExcelPackage.LicenseContext = LicenseContext.NonCommercial;using (ExcelPackage package = new ExcelPackage()){// 添加工作表var worksheet = package.Workbook.Worksheets.Add("Sheet1");for (int i = 0; i < columnNames.Count; i++){// 添加列标题worksheet.Cells[1, i+1].Value = columnNames[i];}// 填充数据for (int i = 0; i < datas.Count; i++){for (int j = 0; j < columnNames.Count+1; j++){// 通过反射获取相应属性值PropertyInfo[] propertyInfos = typeof(T).GetProperties();string propertyName = propertyInfos[j].Name;if (!columnIgnores.Contains(propertyName)){// 获取属性值object value = propertyInfos[j].GetValue(datas[i]);worksheet.Cells[i + 2, j].Value = value; // 数据从第二行开始填充}}}// 设置列宽自适应worksheet.Cells.AutoFitColumns();// 保存 Excel 文件FileInfo fileInfo = new FileInfo(filePath+"\\"+fileName);package.SaveAs(fileInfo);}}public static void ExportExcel<T>(List<T> datas, Dictionary<string, string> Column2Property, List<string> columnIgnores, string filePath, string fileName){ExcelPackage.LicenseContext = LicenseContext.NonCommercial;using (ExcelPackage package = new ExcelPackage()){// 添加工作表var worksheet = package.Workbook.Worksheets.Add("Sheet1");// 写入列标题int colIndex = 1;foreach (var col in Column2Property){if (!columnIgnores.Contains(col.Key)){worksheet.Cells[1, colIndex].Value = col.Value; // 列标题colIndex++;}}// 填充数据int rowIndex = 2; // 数据从第二行开始填充foreach (var data in datas){int currentColIndex = 1;foreach (var col in Column2Property){if (!columnIgnores.Contains(col.Key)){// 使用反射获取属性值PropertyInfo propertyInfo = data.GetType().GetProperty(col.Key);if (propertyInfo != null){object value = propertyInfo.GetValue(data);worksheet.Cells[rowIndex, currentColIndex].Value = value;}currentColIndex++;}}rowIndex++;}// 设置列宽自适应worksheet.Cells.AutoFitColumns();// 保存 Excel 文件FileInfo fileInfo = new FileInfo(Path.Combine(filePath, fileName + ".xlsx"));package.SaveAs(fileInfo);}}/// <summary>/// txt文本导出/// </summary>/// <param name="datas">需要导出的数据集合</param>/// <param name="propertyName">实体类中需要导出属性</param>/// <param name="filePath">文件保存路径</param>public static void ExportTxt(List<T> datas, string propertyName, string filePath, string fileName){// 构建完整的文件路径string fullPath = Path.Combine(filePath, fileName);// 使用 StreamWriter 写入 TXT 文件using (StreamWriter writer = new StreamWriter(fullPath)){// 遍历数据集合foreach (var data in datas){// 通过反射获取指定属性的值PropertyInfo propertyInfo = typeof(T).GetProperty(propertyName);if (propertyInfo != null){object value = propertyInfo.GetValue(data);// 将值写入文件，值之间以逗号分隔writer.WriteLine(value?.ToString());}else{// 如果指定的属性不存在，抛出异常throw new ArgumentException($"Property '{propertyName}' does not exist on type '{typeof(T).Name}'.");}}}}}
}

调用案例1

传入columnNames集合，工具类会遍历columnNames创建表头，然后遍历传入的泛型T的属性一个一个复制

如果columnNames和泛型T的属性的顺序从上到下一一对应则可以用

 private void ExportExcel(){List<string> columnNames = new List<string>{#region 记录"生产订单号","请购单号","采购单号","料号","品名","规格描述","现存量","可用量","含税单价","含税金额","单位","采购数量_计量","收货数量_计量","已退货数_计价","实际入库数","入库单号","已暂收数量_计量","最后交货日期","品质异常报告日期","品质问题描述","处理结论","未交数量_计量","交货结案日期","请购日期","PMC交单日期","采购日期","采购周期","理论交期","要求交期","订单回传日期","采购回复交期","色板_模板_图纸提供情况","异常反馈","预付款比例","预付款支付日期","尾款比例","采购到货日期","财务交单日期","尾款支付日期","请购制单人","请购人","请购单备注","采购制单人","采购员","采购单备注","供应商名称","逾期天数_负数未到期","采购订单结案状态","采购单行号","修改日期_请购","修改日期_采购","PMC要求交货日期"#endregion};List<string> columnIgnores = new List<string> { "isSelected" };// 打开保存文件对话框Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog{Filter = "Excel Files (*.xlsx)|*.xlsx",FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx" // 默认文件名};if (saveFileDialog.ShowDialog() == true) // 用户点击“保存”{string filePath = saveFileDialog.FileName; // 用户选择的文件路径try{// 调用 Excel 导出功能ExcelUtil<ToolStr50>.ExportExcel(Ibo, columnNames, columnIgnores, Path.GetDirectoryName(filePath), Path.GetFileName(filePath));// 通知用户导出完成//MessageBox.Show("导出Excel成功", "成功", MessageBoxButton.OK, MessageBoxImage.Information);}catch (Exception ex){// 错误处理//MessageBox.Show($"导出Excel失败: {ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);}}}

效果

调用案例2

传入Column2Property--列名和属性对应关系，不会担心数据和属性顺序问题

 private void ExportExcel(){Dictionary<string, string> Column2Property = new Dictionary<string, string>{{ "toolStr24", "请购日期" },{ "toolStr54", "标准周期" },{ "toolStr55", "采购标准周期" },{ "toolStr1", "生产订单号" },{ "toolStr4", "料号" },{ "toolStr5", "品名" },{ "toolStr6", "规格描述" },{ "toolStr11", "单位" },{ "toolStr46", "供应商名称" },{ "toolStr26", "采购建立日期" },{ "toolInt10", "采购周期" },{ "toolStr31", "采购回复交期" },{ "toolStr53", "采购说明" },{ "toolStr2", "来源单号" },{ "toolStr3", "采购订单号" },{ "toolStr49", "行号" },{ "toolInt2", "现存量" },{ "toolInt3", "采购数量(计量)" },{ "toolInt4", "收货数量(计量)" },{ "toolInt5", "已退货数(计量)" },{ "toolInt6", "实际入库数(计量)" },{ "toolDouble1", "含税单价" },{ "toolDouble2", "含税金额" },{ "toolStr16", "入库单号" },{ "toolStr44", "采购员" },{ "toolStr40", "请购人" },{ "toolStr43", "采购制单人" },//{ "toolStr40", "请购制单人" },{ "toolStr42", "请购单备注" },{ "toolStr48", "采购订单结案状态" },{ "toolInt7", "已暂收数量(计量)" },{ "toolStr18", "最后交货日期" },{ "toolInt8", "逾期天数(负数未到期)" },{ "toolStr19", "品质异常报告日期" },{ "toolStr20", "品质问题描述" },{ "toolStr21", "处理结论" },{ "toolInt9", "未交数量(计量)" },{ "toolStr23", "交货结案日期" },{ "toolStr25", "PMC交单日期" },{ "toolStr28", "采购理论交期" },{ "toolStr29", "要求交期" },{ "toolStr30", "订单回传日期" },{ "toolStr32", "色板_模板_图纸提供情况" },{ "toolStr33", "异常反馈" },{ "toolInt11", "预付款比例" },{ "toolStr35", "预付款支付日期" },{ "toolInt12", "尾款比例" },{ "toolStr39", "尾款支付日期" },{ "toolStr45", "备注" },{ "toolStr8", "可用量" },{ "toolStr37", "采购到货日期" },{ "toolStr38", "财务交单日期" },{ "toolStr50", "修改日期_请购" },{ "toolStr51", "修改日期_采购" },{ "toolStr52", "PMC要求交货日期" },//{ "toolStr54", "标准周期" },};List<string> columnIgnores = new List<string> { "isSelected" };// 打开保存文件对话框Microsoft.Win32.SaveFileDialog saveFileDialog = new Microsoft.Win32.SaveFileDialog{Filter = "Excel Files (*.xlsx)|*.xlsx",FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx" // 默认文件名};if (saveFileDialog.ShowDialog() == true) // 用户点击“保存”{string filePath = saveFileDialog.FileName; // 用户选择的文件路径try{// 调用 Excel 导出功能ExcelUtil<ToolStr50>.ExportExcel(Ibo, Column2Property, columnIgnores, Path.GetDirectoryName(filePath), Path.GetFileName(filePath));// 通知用户导出完成//MessageBox.Show("导出Excel成功", "成功", MessageBoxButton.OK, MessageBoxImage.Information);}catch (Exception ex){// 错误处理//MessageBox.Show($"导出Excel失败: {ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);}}}

效果