大佬教程收集整理的这篇文章主要介绍了c# NPOI 导出23万条记录耗时12秒,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
先上测试代码:
String connectionString = "Server=localhost;Initial Catalog=******;User ID=sa;password=******;"; List<TestData> datas = null; using (@L_801_1@Connection db = new @L_801_1@Connection(connectionString)) { datas = db.Query<TestData>("SELECT * FROM testData").ToList(); } System.Console.WriteLine($"数据源对象 {typeof(TestData).GetProperties().Length} 个字段,共 {datas.Count} 条记录,大小 {BinaryserializeHelper.serializeToBytes(datas).Length/1000/1000} M"); Task.Run(() => { while (true) { System.Console.WriteLine($"{datetiR_58_11845@e.Now} 内存 : {GC.Get@R_821_10586@lMemory(false) / 1000 / 1000} M"); Thread.Sleep(1000); } }); Stopwatch sw = new Stopwatch(); sw.Start(); byte[] bytes = ExcelHandlerFactory.CreateHandler(datas).CreateExcelBytes(); sw.Stop(); System.Console.WriteLine($"{datetiR_58_11845@e.Now} 数据源转Excel文件字节数组耗时 : "+sw.ElapsedMilliseconds / 1000 +" 秒"); String path = @"C:\Users\Administrator\Desktop\1.xLSX"; FileStream fs = new FileStream(path,FileMode.openOrCreate,FileAccess.WritE); fs.Write(bytes); fs.Dispose(); System.Console.ReadKey();
测试结果:
就是这内存占用有点高......
源码:
using System.Collections.Generic; namespace Wjire.Excel { /// <sumMary> /// ExcelHandler工厂 /// </sumMary> public static class ExcelHandlerFactory { /// <sumMary> /// 创建ExcelHandler /// </sumMary> /// <typeparam name="T"></typeparam> /// <param name="sources">数据源</param> /// <param name="choosedFields">需要导出的字段,可不传,则导出所有字段</param> /// <returns></returns> public static ExcelHandler<T> CreateHandler<T>(IEnumerable<T> sources,HashSet<String> choosedFields = null) { return new ExcelHandler<T>(sources,choosedFields); } } }
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; namespace Wjire.Excel { /// <sumMary> /// 报表导出处理者 /// </sumMary> public sealed class ExcelHandler<Tsource> { /// <sumMary> /// 数据源 /// </sumMary> private readonly IEnumerable<Tsource> _sources; /// <sumMary> /// 需要导出的列信息 /// </sumMary> private readonly columnInfo[] _columnInfos; /// <sumMary> /// 工作簿 /// </sumMary> private IWorkbook _workbook; /// <sumMary> /// 工作页 /// </sumMary> private ISheet _sheet; /// <sumMary> /// 单元格样式 /// </sumMary> private ICellStyle _cellStyle; /// <sumMary> /// 单元格样式提供器 /// </sumMary> private ICellStyleProvider _provider; internal ExcelHandler(IEnumerable<Tsource> sources,HashSet<String> choosedFields) { _sources = sources; _columnInfos = GetcolumnInfosOfExport(choosedFields); } /// <sumMary> /// 数据源转字节 /// </sumMary> /// <returns></returns> public byte[] CreateExcelBytes() { using (var ms = CreateExcelStream()) { return ms.ToArray(); } } /// <sumMary> /// 数据源转excel流 /// </sumMary> /// <returns></returns> public MemoryStream CreateExcelStream() { try { _workbook = new HSSFWorkbook(); _cellStyle = (_provider ?? DefaultCellStyleProvider.Singleton.value).CreateCellStyle(_workbook); int sheeTindex = 1; CreateSheetWithHeader(sheeTindeX); int rowIndex = 1; foreach (Tsource entity in _sources) { //03版 excel 一个 _sheet 最多 65535 行 if (rowIndex == 65535) { sheeTindex++; CreateSheetWithHeader(sheeTindeX); rowIndex = 1; } CreateDa@R_268_2301@w(rowIndex,entity); rowIndex++; } MemoryStream ms = new MemoryStream(); _workbook.Write(ms); return ms; } finally { _workbook?.Close(); } } /// <sumMary> /// 创建Sheet及列头 /// </sumMary> private void CreateSheetWithHeader(int sheeTindeX) { _sheet = _workbook.CreateSheet("第 " + sheeTindex + " 页"); //冻结首行首列 _sheet.CreateFreezePane(0,1); IRow header = _sheet.CreateRow(0); for (int i = 0; i < _columnInfos.Length; i++) { ICell cell = header.CreateCell(i); cell.SetCellValue(_columnInfos[i].CellDisplayAttribute.Name); cell.CellStyle = _cellStyle; //自适应宽度 _sheet.AutoSizecolumn(i); } } /// <sumMary> /// 创建数据行 /// </sumMary> /// <param name="rowIndex">行索引</param> /// <param name="entity">数据</param> private void CreateDa@R_268_2301@w(int rowIndex,object entity) { IRow da@R_268_2301@w = _sheet.CreateRow(rowIndeX); for (int i = 0; i < _columnInfos.Length; i++) { ICell cell = da@R_268_2301@w.CreateCell(i); cell.CellStyle = _cellStyle; object value = _columnInfos[i].PropertyInfo.GetValue(entity,null); SetCellValue(value,cell); } } /// <sumMary> /// 设置单元格值 /// </sumMary> /// <param name="value"></param> /// <param name="cell"></param> private void SetCellValue(object value,ICell cell) { if (value == null) { cell.SetCellValue(String.Empty); return; } Type type = value.GetType(); switch (type.Name) { case "datetiR_58_11845@e": case "String": case "Boolean": cell.SetCellValue(value.ToString()); break; case "Byte": case "Int16": case "Int32": case "Int64": case "Single": case "Double": case "decimal": cell.SetCellValue(Convert.ToDouble(value)); break; default: cell.SetCellValue(String.Empty); break; } } /// <sumMary> /// 设置excel单元格样式提供器 /// </sumMary> /// <param name="provider"></param> /// <returns></returns> public ExcelHandler<Tsource> SetCellStyleProvider(ICellStyleProvider provider) { _provider = provider; return this; } /// <sumMary> /// 获取需要导出的列信息 /// </sumMary> /// <param name="choosedFields"></param> /// <returns></returns> private columnInfo[] GetcolumnInfosOfExport(HashSet<String> choosedFields) { columnInfo[] columnInfos = columnInfoContainer.GetcolumnInfo(typeof(TsourcE)); return choosedFields?.Count > 0 ? columnInfos.Where(w => choosedFields.Contains(w.PropertyInfo.Name)).ToArray() : columnInfos; } } }
@H_293_874@
using System.Reflection; namespace Wjire.Excel { /// <sumMary> /// 列信息 /// </sumMary> public class columnInfo { internal PropertyInfo PropertyInfo { get; set; } internal CellDisplayAttribute CellDisplayAttribute { get; set; } } }
using System; namespace Wjire.Excel { /// <sumMary> /// excel 单元格数据显示自定义特性类 /// </sumMary> [AttributeUsage(AttributeTargets.Property)] public sealed class CellDisplayAttribute : Attribute { /// <sumMary> /// 自定义列名 /// </sumMary> public String Name { get; set; } /// <sumMary> /// 构造函数 /// </sumMary> /// <param name="name">自定义列名</param> public CellDisplayAttribute(String Name) { Name = name; } } }
using NPOI.SS.UserModel; namespace Wjire.Excel { /// <sumMary> /// 单元格样式提供器接口 /// </sumMary> public interface ICellStyleProvider { ICellStyle CreateCellStyle(IWorkbook workbook); } }
using System; using System.Collections.Generic; using System.Linq; using System.Reflection; namespace Wjire.Excel { /// <sumMary> /// 数据源列信息容器 /// </sumMary> internal static class columnInfoContainer { private static readonly Dictionary<Type,columnInfo[]> Container = new Dictionary<Type,columnInfo[]>(); /// <sumMary> /// 获取数据源列信息 /// </sumMary> /// <param name="sourceType">数据源类类型</param> /// <returns></returns> internal static columnInfo[] GetcolumnInfo(Type sourceTypE) { if (Container.TryGetValue(sourceType,out columnInfo[] infos)) { return infos; } infos = sourceType .GetProperties(BindingFlags.Public | BindingFlags.InstancE) .Where(propertyInfo => propertyInfo.GetCustomAttribute<CellDisplayAttribute>(true) != null) .SELEct(propertyInfo => new columnInfo { PropertyInfo = propertyInfo,CellDisplayAttribute = propertyInfo.GetCustomAttribute<CellDisplayAttribute>() }).ToArray(); Container.Add(sourceType,infos); return infos; } } }
using NPOI.SS.UserModel; using System; namespace Wjire.Excel { /// <sumMary> /// 默认单元格样式提供器 /// </sumMary> internal class DefaultCellStyleProvider : ICellStyleProvider { internal static Lazy<DefaultCellStyleProvider> Singleton = new Lazy<DefaultCellStyleProvider>(() => new DefaultCellStyleProvider()); private DefaultCellStyleProvider() { } /// <sumMary> /// 创建单元格样式 /// </sumMary> /// <param name="workbook"></param> /// <returns></returns> public ICellStyle CreateCellStyle(IWorkbook workbook) { ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; //cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中非常影响效率,不建议打开该功能 IFont font = workbook.CreateFont(); font.FontHeighTinPoints = 11; //font.boldweight = 700; cellStyle.SetFont(font); //边框 //cellStyle.borderBottom = Borderstyle.Thin; //cellStyle.borderLeft = Borderstyle.Thin; //cellStyle.borderRight = Borderstyle.Thin; //cellStyle.borderTop = Borderstyle.Thin; return cellStyle; } } }
几点说明:
1.NPOI 用的最新版本:2.4.1;
2.代码中用的 HSSFWorkbook,不仅仅是为了兼容 word2003,在测试的时候发现,如果用 XSSFWorkbook,耗时慢了N个数量级,不知道是不是哪里姿势不对;
3.单元格的宽度只在标题栏设置了,所以导出来的Excel可能比较丑.原因是:
1)如果根据单元格内容的长度来调整的话,由于每一个单元格内容的长度都肯能不一样,太耗时,没必要,不如鼠标点两下来得快;
2)虽然NPOI有个功能可以在一个sheet的数据填充完后,设置单元格的宽度自适应,但是测试了下,太太太太慢了.估计是在遍历所有的单元格,一个一个计算;
3)还有一个折中的办法,就是根据第一行数据的各个单元格内容来调整宽度,因为有些时候,数据对象每个属性的值的长度都不会差太多.但是当长度不一的时候,会让人误以为那些长的单元格的内容已经显示完了,所以也舍弃了这个功能.
4.测试的时候发现,如果把某一列的单元格设置成超@L_674_52@,点击可以打开浏览器查看那种,非常非常非常非常慢.惨不忍睹.不知道是不是姿势不对,所以也舍弃了该功能.
以上是大佬教程为你收集整理的c# NPOI 导出23万条记录耗时12秒全部内容,希望文章能够帮你解决c# NPOI 导出23万条记录耗时12秒所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。