C# 实现NPOI的Excel导出

技术点：

1.自定义attribute属性

2.通过反射取类及其属性的attribute属性值

3.NPOI包常用属性及方法(我也仅仅知道用到过的，陌生的要么见名知意，要么百度查)

实现功能点：

List类对象的模板导出，实用场景例子见最后代码块

（emm...还是比较抽象，代码见）

EXCEL导出类DTO超类

public abstract class ExcelSuper

{

  // 所有excel导出类DTO必须要继承的方法

  // 限制比较弱，主要还是用来区分DTO用在何处

}

定义继承导出类DTO的特性说明类

/// <summary>

    /// 导出类 类特性

    /// </summary>

    [AttributeUsage(AttributeTargets.Class)]

    public class ExcelExpClassAttribute : Attribute

    {

        public ExcelExpClassAttribute(int colSplit, int rowSplit, int leftmostColumn, int topRow)

        {

            this.colSplit = colSplit;

            this.rowSplit = rowSplit;

            this.leftmostColumn = leftmostColumn;

            this.topRow = topRow;

        }

        /// <summary>

        /// 冻结的列数

        /// </summary>

        public int colSplit { get; set; }

        /// <summary>

        /// 冻结的行数

        /// 只冻结列时为0

        /// </summary>

        public int rowSplit { get; set; }

        /// <summary>

        /// 右边区域可见的首列序号，从1开始计算

        /// </summary>

        public int leftmostColumn { get; set; }

        /// <summary>

        /// 边区域可见的首行序号，从1开始计算，

        /// 只冻结列时为0

        /// </summary>

        public int topRow { get; set; }

    }

    /// <summary>

    /// 导出类 属性特性

    /// </summary>

    [AttributeUsage(AttributeTargets.Property)]

    public class ExcelExpAttribute : Attribute

    {

        /// <summary>

        /// 是否隐藏，与SortIndex搭配使用

        /// </summary>

        public bool IsHidden { get; set; } = false;

        /// <summary>

        /// 排序索引（保持连贯性、不可重复）

        ///   - 对应dataTable的列排序 [0,1...]

        ///   - 对应Excel的列索引 [0,1...]

        /// </summary>

        public int SortIndex { get; set; }

        /// <summary>

        /// Excel列名

        /// </summary>

        public string ColName { get; set; }

        /// <summary>

        /// 是否动态列

        /// </summary>

        public bool IsdynamicColName { get; set; }

        /// <summary>

        /// 是否合并行 -- 预留，后补

        /// </summary>

        public bool IsRowMerge { get; set; } = false;

        /// <summary>

        /// 合并行依据 -- 预留，后补

        /// </summary>

        public string RowMergeBasis { get; set; }

    }

Excel帮助类

    /// <summary>

    /// Excel帮助类

    /// </summary>

    public static class ExcelHelper

    {

        /// <summary>

        ///

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <param name="workbook"></param>

        /// <param name="dtSource"></param>

        /// <param name="sheetNum"></param>

        /// <param name="useAttributeColName"> 添加一行列名，取类自定义属性列名称：ExcelExpAttribute.ColName</param>

        /// <returns></returns>

        private static IWorkbook OutputExcel<T>(this IWorkbook workbook, IEnumerable<T> dtSource, int sheetNum, bool useAttributeColName = false)

        {

            // 读取sheet

            ISheet sheet = workbook.GetSheetAt(sheetNum);

            int rowIndex = sheet.LastRowNum + 1;// 获取写入行初始值

            if (useAttributeColName)

            {

                // 待补充

            }

            Type objType = typeof(T);

            // 取类上的自定义特性

            object[] objs = objType.GetCustomAttributes(typeof(ExcelExpClassAttribute), true);

            foreach (object obj in objs)

            {

                ExcelExpClassAttribute attr = obj as ExcelExpClassAttribute;

                if (attr != null)

                {

                    sheet.CreateFreezePane(attr.colSplit, attr.rowSplit, attr.leftmostColumn, attr.topRow);// 设置冻结行、列

                    break;

                }

            }

            // 循环添加数据

            foreach (T item in dtSource)

            {

                IRow rowi = sheet.CreateRow(rowIndex);

                // 设置自适应宽度,9为Excel列数，根据需要自已修改

                for (int columnNum = 0; columnNum <= rowi.LastCellNum; columnNum++)

                {

                    int columnWidth = sheet.GetColumnWidth(columnNum) / 256;

                    sheet.SetColumnWidth(columnNum, columnWidth * 256);

                }

                // 取属性上的自定义特性

                foreach (PropertyInfo propInfo in objType.GetProperties())

                {

                    object[] objAttrs = propInfo.GetCustomAttributes(typeof(ExcelExpAttribute), true);

                    if (objAttrs.Length > 0)

                    {

                        ExcelExpAttribute attr = objAttrs[0] as ExcelExpAttribute;

                        if (attr != null)

                        {

                            if (attr.IsHidden)

                                continue;

                            int colIndex = attr.SortIndex;

                            var name = propInfo.Name;// 实例名称

                            var value = propInfo.GetValue(item);// 实例值

                            #region 判断值类型并填充

                            var newCell = rowi.CreateCell(colIndex);

                            switch (propInfo.PropertyType.ToString())

                            {

                                case "System.String"://字符串类型

                                    newCell.SetCellValue(value == null ? "" : value.ToString());

                                    break;

                                case "System.DateTime"://日期类型

                                    DateTime.TryParse(value.ToString(), out DateTime dateV);

                                    newCell.SetCellValue(dateV);

                                    break;

                                case "System.Boolean"://布尔型

                                    bool.TryParse(value.ToString(), out bool boolV);

                                    newCell.SetCellValue(boolV);

                                    break;

                                case "System.Int16"://整型

                                case "System.Int32":

                                case "System.Int64":

                                case "System.Byte":

                                    int.TryParse(value.ToString(), out int intV);

                                    newCell.SetCellValue(intV);

                                    break;

                                case "System.Decimal"://浮点型

                                case "System.Double":

                                    double.TryParse(value.ToString(), out double doubV);

                                    newCell.SetCellValue(doubV);

                                    break;

                                case "System.DBNull"://空值处理

                                    newCell.SetCellValue("");

                                    break;

                                default:

                                    newCell.SetCellValue("");

                                    break;

                            }

                            #endregion

                        }

                    }

                }

                rowIndex++;

            }

            return workbook;

        }

        /// <summary>

        /// 导出模板Excel

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <param name="iEnumerable">数据源</param>

        /// <param name="fileFullPath">文件全路径（包含文件及后缀名）</param>

        /// <returns>文件流</returns>

        public static MemoryStream ExportToExcel<T>(IEnumerable<T> iEnumerable, string fileFullPath)

            where T : ExcelSuper

        {

            // 打开模板文件并写入

            var workbook = GetIWorkbook(fileFullPath, out ExcelTypeEnum type);

            if (type == ExcelTypeEnum.XLS)

            {

                workbook = (HSSFWorkbook)workbook.OutputExcel(iEnumerable, 0, false);

            }

            else

            {

                workbook = (XSSFWorkbook)workbook.OutputExcel(iEnumerable, 0, false);

            }

            try

            {

                using (MemoryStream ms = new MemoryStream())

                {

                    workbook.Write(ms);

                    ms.Flush();

                    ms.Position = 0;

                    // sheet.Dispose();

                    // workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet

                    return ms;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        #region Private method

        private static IWorkbook GetIWorkbook(string fileFullPath, out ExcelTypeEnum type)

        {

            string filename = Path.GetFileNameWithoutExtension(fileFullPath);// 文件名称

            string extension = Path.GetExtension(fileFullPath);// 后缀名 带点(.)

            try

            {

                if (!File.Exists(fileFullPath))

                {

                    throw new Exception($"模板：{filename + extension}不存在");

                }

                FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.ReadWrite);

                if (".xls".Equals(extension.ToLower()))

                {

                    type = ExcelTypeEnum.XLS;

                    return new HSSFWorkbook(fs);// Excel2003以前版本

                }

                else

                {

                    type = ExcelTypeEnum.XLSX;

                    return new XSSFWorkbook(fs);// Excel2007后的版本

                }

            }

            catch (Exception ex)

            {

                throw new Exception("获取文件错误：" + ex);

            }

        }

        #endregion

        enum ExcelTypeEnum

        {

            XLS,

            XLSX

        }

    }

这部分要讲的点其实挺多的，关键就是EXCEL导出所用到的数据源是强类型的。

可以看出来list其实是EF的Queryable toList()后的类集合，作为数据源存在；// 比较喜欢强类型，直接点出来的属性让人放心ヾ(•ω•`)o

里面的DTO DesWeeklyReportExcExp继承ExcelSuper，特性分别加在类及属性上。

public class XXXXController : CoreController

{

    // 控制器内部

    [HttpPost]

    public ActionResult export()

    {

        // 控制器接口

        var list = op

                    .GetPagedQuery(PageModel)

                    .Select(s => new DesWeeklyReportExcExp

                    {

                        col1 = s.Project.ProjName,

                        col2 = s.ColAttROPDate1?.ToString("yyyy.MM.dd"),

                        col3 = (s.ColAttROPDate2 == null ? "无" : s.ColAttROPDate2.Value.ToString("yyyy.MM.dd"))

                                                       + "/"

                                                       + (s.ColAttROPDate3 == null ? "无" : s.ColAttROPDate3.Value.ToString("yyyy.MM.dd")),

                        col4 = s.ColAttROPDate4?.ToString("yyyy.MM.dd")

                    }).ToList();

         string filePath = Server.MapPath("~/download/[这是模板名称].xlsx");

         string filename = Path.GetFileNameWithoutExtension(filePath);// 文件名称

         string extension = Path.GetExtension(filePath);// 后缀名 带点(.)

         string fileDownloadName = filename + extension;

         var fs = ExcelHelper.ExportToExcel(list, filePath).ToArray();

         return File(fs, "application/ms-excel", fileDownloadName);

    }

}

[ExcelExpClassAttribute(2, 0, 2, 0)]

public class DesWeeklyReportExcExp : ExcelSuper

{

    /// <summary>

    /// 列1

    /// </summary>

    [ExcelExp(SortIndex = 0, ColName = "列1")]

    public string col1 { get; set; }

    /// <summary>

    /// 列2

    /// </summary>

    [ExcelExp(SortIndex = 0, ColName = "列2")]

    public string col2 { get; set; }

    /// <summary>

    /// 列3

    /// </summary>

    [ExcelExp(SortIndex = 0, ColName = "列3")]

    public string col3 { get; set; }

    /// <summary>

    /// 列4

    /// </summary>

    [ExcelExp(SortIndex = 0, ColName = "列4")]

    public string col4 { get; set; }

}

部分拙见，大部分还需要补充，比如设置合并列，比如数据源支持DataTable导出等等，还有现有的代码可能不够完善，看到的多多提下宝贵意见吧

巴特西

C# 实现NPOI的Excel导出

最新文章

热门文章