C# .NET8使用NPOI导出数据到Excel文件
C# .NET8使用NPOI导出数据到Excel文件
网上找的C#例子,改良后分享给大家。
NPOIExportExcel 工具类
C# 全选
/// <summary>
/// NPOI导出数据到Excel文件
/// </summary>
/// <typeparam name="T"></typeparam>
public class NPOIExportExcel<T> where T : class
{
private readonly string _savePath;
private readonly bool _firstRowIsHeader;
private readonly List<T> _list;
private readonly ISheet _sheet;
private readonly IWorkbook _workbook;
private readonly List<Action<T, ICell>> _actions = new List<Action<T, ICell>>();
/// <summary>
/// NPOI工具构造器
/// </summary>
/// <param name="list">数据源</param>
/// <param name="excelfile">导出的excel文件</param>
/// <param name="firstRowIsHeader">首行是标题行</param>
public NPOIExportExcel(List<T> list, string excelfile, bool firstRowIsHeader)
{
_firstRowIsHeader = firstRowIsHeader;
_savePath = excelfile;
_list = list;
if (_list == null || _list.Count == 0)
{
throw new Exception("导出数据不能为空");
}
var file = new FileStream(_savePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
string fileExt = Path.GetExtension(excelfile).ToLower();
//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
_workbook = new XSSFWorkbook(file);
}
else if (fileExt == ".xls")
{
_workbook = new HSSFWorkbook(file);
}
else
{
throw new Exception("不支持的文件格式");
}
_sheet = _workbook.GetSheetAt(0);
}
/// <summary>
/// 添加列的取值逻辑及数据类型
/// </summary>
/// <param name="func">取值逻辑</param>
/// <returns></returns>
public NPOIExportExcel<T> AddField(Func<T, object> func, Type dataType)
{
_actions.Add((item, cell) =>
{
//取字段的值
var o = func(item);
if (o != null)
{
var value = System.ComponentModel.TypeDescriptor.GetConverter(dataType).ConvertFromString(o.ToString());
//SetCellValue方法仅支持的值类型
if (false) { }
else if (dataType == typeof(System.String)) cell.SetCellValue((String)value);
else if (dataType == typeof(Boolean)) cell.SetCellValue((Boolean)value);
else if (dataType == typeof(DateTime)) cell.SetCellValue((DateTime)value);
else if (dataType == typeof(Double)) cell.SetCellValue((Double)value);
}
else
{
cell.SetCellValue(String.Empty);
}
});
return this;
}
/// <summary>
/// 保存Excel
/// </summary>
public int Save()
{
int rowIndex = _firstRowIsHeader ? 1 : 0; //因为第一行是表头,特殊表头需要单独处理
foreach (var item in _list)
{
IRow row = _sheet.GetRow(rowIndex) ?? _sheet.CreateRow(rowIndex);
for (int i = 0; i < _actions.Count; i++)
{
ICell cell = row.GetCell(i) ?? row.CreateCell(i);
_actions[i](item, cell);
}
rowIndex++;
}
using (var fileStream = new FileStream(_savePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
{
_workbook.Write(fileStream);
_workbook.Close();
}
return _list.Count;
}
}
使用方法
C# 全选
var listTmp = _BLL.GetSource();
var targetFile = @"c:\abc.xlsx";
//导出数据
var export = new NPOIExportExcel<ImportLanguageData_DataModel>(listTmp, targetFile, true);
var rows = export.AddField(x => x.ObjectID, typeof(String))
.AddField(x => x.ObjectType, typeof(String))
.AddField(x => x.SourceLanType, typeof(String))
.AddField(x => x.SourceLanData, typeof(String))
.AddField(x => x.TargetLanType, typeof(String))
.AddField(x => x.TargetLanData, typeof(String))
.Save();
导出后的Excel文件
版权声明:本文为开发框架文库发布内容,转载请附上原文出处连接
NewDoc C/S框架网