C# 使用NPOI 导出文件到Excel.支持分页及自定义排序

导出帮助类

using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.OpenXmlFormats.Vml;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Text;
using System.Threading;

namespace Test.Test.BusinessCommon
{
    /// <summary>
    /// 导出Excel帮助类
    /// </summary>
    public class ExportExcelHelper<T> where T : class
    {
        private readonly int _page;
        private readonly int _pageSize;
        private bool _horizontalCenter;
        private bool _verticalCenter;
        private readonly List<T> _list;
        private readonly ISheet _sheet;
        private readonly bool _isExistFile;
        private readonly string _savePath;

        private readonly ICellStyle _style;
        private readonly HSSFWorkbook _hSSFWorkbook;
        private readonly List<Action<T, ICell>> _actions = new List<Action<T, ICell>>();
        public ExportExcelHelper(ExportParam<T> exportParam)
        {
            _list = exportParam.ExportDatas;
            _isExistFile = exportParam.IsExistFile;
            _savePath = exportParam.SavePath;
            _page = exportParam.Page;
            _pageSize = exportParam.PageSize;


            if (_list == null || _list.Count == 0)
            {
                throw new Exception("导出数据不能为空");
            }


            if (!_isExistFile)
            {
                _hSSFWorkbook = new HSSFWorkbook();
                _sheet = _hSSFWorkbook.CreateSheet("Sheet1");
            }
            else
            {
                var file = new FileStream(_savePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
                _hSSFWorkbook = new HSSFWorkbook(file);
                _sheet = _hSSFWorkbook.GetSheetAt(0);
            }
            _style = _hSSFWorkbook.CreateCellStyle();
        }

        [Obsolete("还没写完")]
        public ExportExcelHelper<T> SetFormat()
        {
            return this;
        }
        /// <summary>
        /// 设置水平居中
        /// </summary>
        /// <returns></returns>
        public ExportExcelHelper<T> SetHorizontalCenter()
        {
            _style.Alignment = HorizontalAlignment.Center;
            _horizontalCenter = true;
            return this;
        }

        /// <summary>
        /// 设置水平居中
        /// </summary>
        /// <returns></returns>
        public ExportExcelHelper<T> SetVerticalCenter()
        {
            _style.VerticalAlignment = VerticalAlignment.Center;
            _verticalCenter = true;
            return this;
        }

        /// <summary>
        /// 添加列
        /// </summary>
        /// <param name="func"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public ExportExcelHelper<T> AddField(Func<T, object> func, string fieldName, int columnWidth = 0)
        {
            if (!_isExistFile)
            {
                IRow headerRow = _sheet.GetRow(0) ?? _sheet.CreateRow(0);
                ICell headerCell = headerRow.CreateCell(_actions.Count);
                headerCell.SetCellValue(fieldName);

                if (columnWidth != 0)
                {
                    _sheet.SetColumnWidth(_actions.Count, columnWidth);
                }

                if (_horizontalCenter || _verticalCenter)
                {
                    headerCell.CellStyle = _style;
                }
            }
            _actions.Add((item, cell) =>
            {
                cell.SetCellValue(Convert.ToString(func(item)));
            });
            return this;
        }

        /// <summary>
        /// 保存
        /// </summary>
        public void Save()
        {
            int rowIndex = _isExistFile ? ((_page - 1) * _pageSize) + 1 : 1;//+1 是因为第一行是表头 如果是特殊的表头需要单独处理
            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);

                    if (_horizontalCenter || _verticalCenter)
                    {
                        cell.CellStyle = _style;
                    }
                }
                rowIndex++;
            }

            using (var fileStream = new FileStream(_savePath, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                _hSSFWorkbook.Write(fileStream);
            }
        }
    }
}

输入参数

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

namespace Test.test.BusinessCommon.Model
{
    /// <summary>
    /// 导出参数
    /// </summary>
    public class ExportParam<T> where T : class
    {
        /// <summary>
        /// 需要导出的数据
        /// </summary>
        public List<T> ExportDatas { get; set; }
        /// <summary>
        /// 文件路径
        /// </summary>
        public string FilePath { set; get; }
        /// <summary>
        /// 文件名
        /// </summary>
        public string FileName { set; get; }
        /// <summary>
        /// 分页Index
        /// </summary>
        public int Page { set; get; }
        /// <summary>
        /// 分页大小
        /// </summary>
        public int PageSize { set; get; }
        /// <summary>
        /// 是否存在文件
        /// </summary>
        public bool IsExistFile => File.Exists(SavePath);
        /// <summary>
        /// 保存的全路径
        /// </summary>
        public string SavePath => Path.Combine(FilePath, FileName);
    }
}

调用的地方

                var param = new ExportParam<Model>
                {
                    ExportDatas = new List<Model>,
                    Page = page,//分页情况下需要传入
                    PageSize = rows,//分页情况下需要传入
                    FilePath = "",
                    FileName = "xxx.xls",
                };
                new ExportExcelHelper<Model>(param)
                                .SetHorizontalCenter()
                                .SetVerticalCenter()
                                .AddField(x => x.Property1, "列名", 20 * 256)//属性-列名-列宽
                                .AddField(x => x.Property2, "列名", 20 * 256)
                                .AddField(x => x.Property3, "列名", 20 * 256)
                                .AddField(x => x.Property4, "列名", 20 * 256)
                                .AddField(x => x.Property5, "列名", 20 * 256)
                                .AddField(x => x.Property6, "列名", 20 * 256)
                                .AddField(x => x.Property7, "列名", 20 * 256)
                                .Save();

• 支持前端循环调用,处理数据量很大的场景
• 可随意调整导出列的顺序,自定义列名及列宽
• 只支持列名下面直接是数据的场景,如果需要合并表头等负责操作,就需要重新扩展