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);
}
}
}效果
原文地址：https://blog.csdn.net/wyh2943455568/article/details/143640141
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！