c# NPOI 导出 23 万条记录耗时 12 秒

先上测试代码:

string connectionString = "Server=localhost;Initial Catalog=******;User ID=sa;Password=******;";
            List<TestData> datas = null;
            using (SqlConnection db = new SqlConnection(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($"{DateTime.Now} 内存 : {GC.GetTotalMemory(false) / 1000 / 1000} M");
                    Thread.Sleep(1000);
                }
            });
            Stopwatch sw = new Stopwatch();
            sw.Start();
            byte[] bytes = ExcelHandlerFactory.CreateHandler(datas).CreateExcelBytes();
            sw.Stop();
            System.Console.WriteLine($"{DateTime.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);
        }
    }
}
View Code

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;
                    }
                    CreateDataRow(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 CreateDataRow(int rowIndex, object entity)
        {
            IRow dataRow = _sheet.CreateRow(rowIndex);
            for (int i = 0; i <_columnInfos.Length; i++)
            {
                ICell cell = dataRow.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 "DateTime":
                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;
        }
    }
}
View Code

using System.Reflection;
namespace Wjire.Excel
{
    /// <summary>
    /// 列信息
    /// </summary>
    public class ColumnInfo
    {
        internal PropertyInfo PropertyInfo { get; set; }
        internal CellDisplayAttribute CellDisplayAttribute { get; set; }
    }
}
View Code

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;
        }
    }
}
View Code

using NPOI.SS.UserModel;
namespace Wjire.Excel
{
    /// <summary>
    /// 单元格样式提供器接口
    /// </summary>
    public interface ICellStyleProvider
    {
        ICellStyle CreateCellStyle(IWorkbook workbook);
    }
}
View Code

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;
        }
    }
}
View Code

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;
        }
    }
}
View Code

几点说明:

1.NPOI 用的最新版本: 2.4.1;

2. 代码中用的 HSSFWorkbook , 不仅仅是为了兼容 word2003, 在测试的时候发现, 如果用 XSSFWorkbook , 耗时慢了 N 个数量级, 不知道是不是哪里姿势不对;

3. 单元格的宽度只在标题栏设置了, 所以导出来的 Excel 可能比较丑. 原因是:

1) 如果根据单元格内容的长度来调整的话, 由于每一个单元格内容的长度都肯能不一样, 太耗时, 没必要, 不如鼠标点两下来得快;

2) 虽然 NPOI 有个功能可以在一个 sheet 的数据填充完后, 设置单元格的宽度自适应, 但是测试了下, 太太太太慢了. 估计是在遍历所有的单元格, 一个一个计算;

3) 还有一个折中的办法, 就是根据第一行数据的各个单元格内容来调整宽度, 因为有些时候, 数据对象每个属性的值的长度都不会差太多. 但是当长度不一的时候, 会让人误以为那些长的单元格的内容已经显示完了, 所以也舍弃了这个功能.

4. 测试的时候发现, 如果把某一列的单元格设置成超链接, 点击可以打开浏览器查看那种, 非常非常非常非常慢. 惨不忍睹. 不知道是不是姿势不对, 所以也舍弃了该功能.

来源: https://www.cnblogs.com/refuge/p/11371080.html