导出excel，自定义合并单元格

白--

已于 2024-10-29 11:21:25 修改

阅读量512

点赞数 2

分类专栏：笔记文章标签： c# visual studio

于 2024-09-23 11:18:33 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44975338/article/details/142452789

版权

笔记专栏收录该内容

2 篇文章

订阅专栏

导出excel，自定义合并单元格

公司需要开发一个可以自定义合并单元格的的导出功能，要求基于MiniExcel
插件开发，在捣鼓两天后终于搞定了，记录一下

        /// <summary>
        /// 列表数据转换工作页面
        /// </summary>
        /// <typeparam name="T">数据类型(目前只支持类)</typeparam>
        /// <param name="listDate">数据列表</param>
        /// <returns></returns>
        public static IWorkbook CreateSheetAndCell_BaseOne<T>(DataTable table, T t) where T : class
        {
            //创建workbook，说白了就是在内存中创建一个Excel文件
            //IWorkbook workbook = new HSSFWorkbook();//（本工作页可以自行选择使用哪个版本，需要注意不同版本在控制器方面的写法存在一定的差异）
            IWorkbook workbook = new XSSFWorkbook();
            if (table == null || table.Rows.Count <= 0)
            {
                return workbook;
            }
            //添加一个Sheet页
            ISheet sheet1 = workbook.CreateSheet("sheet1");
            //获取数据类型
            Type type = t.GetType();
            //获取所有列名
            var columns = GetProperties<T>(t);
            //首行筛选
            sheet1.SetAutoFilter(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            // 字体设置
            IFont timeFont = workbook.CreateFont();
            timeFont.FontHeightInPoints = 11;
            //timeFont.IsBold = true; //字体加粗
            timeFont.FontName = "Calibri";
            ICellStyle timeCsellStyle = workbook.CreateCellStyle();
            timeCsellStyle.SetFont(timeFont);
            //timeCsellStyle.Alignment = HorizontalAlignment.Center;       // 水平居中
            timeCsellStyle.VerticalAlignment = VerticalAlignment.Center; // 垂直居中
            //循环生成对应数据数量的行(行循环)
            for (int i = 0; i < table.Rows.Count; i++)
            {
                //创建新行
                IRow row1 = sheet1.CreateRow(i);
                //获取第i行数据
                var row = table.Rows[i];
                //列循环
                for (int k = 0; k < columns.Count; k++)
                {
                    var dataK = columns[k];
                    //给第1行添加第1个单元格
                    ICell cell1 = row1.CreateCell(k);
                    //设置数据
                    cell1.SetCellValue(i == 0 ? dataK.Describe : row[k].ToStr());
                    //数据相同时合并
                    if (i >= 2 && row[k].ToStr() == table.Rows[i - 1][k].ToStr() && dataK.Attribute.Contains("ExcelMerge"))
                    {
                        var firsRow = RemoveMergedRegion(sheet1, i, k);
                        sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firsRow > 0 ? firsRow : i - 1, i , k == 0 ? 0 : k, k ));
                    }
                    //单元格设置样式
                    var timeCell = row1.GetCell(k);
                    timeCell.CellStyle = timeCsellStyle;
                }
            }
            //返回工作页
            return workbook;
        }

通过反射获取类的所有属性

        /// <summary>
        /// 获取类中的属性
        /// </summary>
        /// <returns>所有属性名称</returns>
        private static List<Properties> GetProperties<T>(T t)
        {
            List<Properties> ListStr = new List<Properties> ();
            if (t == null)
            {
                return ListStr;
            }
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return ListStr;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                //初始化
                Properties prop = new Properties();
                //名称
                prop.Name = item.Name;
                //值
                prop.Value = item.GetValue(t, null);
                //描述
                IEnumerable<Attribute> attrs = item.GetCustomAttributes();
                //获取描述（中文名） 自定义合并表头特征
                foreach (Attribute attr in attrs)
                {
                    //excel指定列名(通过描述获取)
                    if (attr.GetType() == typeof(DescriptionAttribute))
                    {
                        prop.Describe = ((DescriptionAttribute)attr).Description;
                        prop.Attribute.Add("Description");
                    }
                    //类导出是否合并
                    else if (attr.GetType() == typeof(ExcelMergeAttribute))
                    {
                        if (attr is ExcelMergeAttribute)
                        {
                            var excelMergeAttribute = attr as ExcelMergeAttribute;
                            if (excelMergeAttribute.IsMerge == false)
                            {
                                prop.Attribute.Add("ExcelMerge");
                            }
                        }
                    }
                    //else if (attr.GetType() == typeof(ExcelColumnAttribute))
                    //{
                    //    if (attr is ExcelColumnAttribute)
                    //    {
                    //        var excelMergeAttribute = attr as ExcelColumnAttribute;
                    //        prop.Describe = excelMergeAttribute.ColumnName;
                    //        prop.Attribute.Add("ExcelColumn");
                    //    }
                    //}
                }
                ListStr.Add(prop);
            }
            return ListStr;
        }

合并单元格，通过不断检索已合并单元格位置来重新设置需要合并的单元格，如果不重新设置会报错已经合并的单元格无法再次合并

        /// <summary>
        /// 去除已合并单元格,返回需合并单元格第一行
        /// </summary>
        /// <param name="sheet">工作页</param>
        /// <param name="rowIndex">当前行</param>
        /// <returns></returns>
        private static int RemoveMergedRegion(ISheet sheet, int rowIndex, int colIndex)
        {
            int firstRow = 0;
            int MergedCount = sheet.NumMergedRegions;
            for (int i = MergedCount - 1; i >= 0; i--)
            {
                //CellRangeAddress对象属性有：FirstColumn，FirstRow，LastColumn，LastRow 进行操作 取消合并单元格
                var temp = sheet.GetMergedRegion(i);
                if (temp.LastRow == rowIndex - 1 && temp.LastColumn == colIndex )
                {
                    firstRow = temp.FirstRow;
                    sheet.RemoveMergedRegion(i);
                }
            }
            return firstRow;
        }

控制是否合并类中的某个字段，在我们需要用的属性上打上该特征即可

    /// <summary>
    /// 类导出是否合并列
    /// </summary>
    [Description("ExcelMergeAttribute")]
    public class ExcelMergeAttribute: Attribute
    {
        /// <summary>
        /// 是否合并，默认是
        /// </summary>
        public bool IsMerge;
        public ExcelMergeAttribute()
        {
            IsMerge = false;
        }
    }

调用

            //列表转Table
            var table = CommomMethod.ToDataTable<StockReportPageListOutput>(stockList);
            //获取转换后的工作页数据
            var range = CommomMethod.CreateSheetAndCell_BaseOne<StockReportPageListOutput>(table, new StockReportPageListOutput());
            // 导出数据
            MemoryStreamHelper memoryStream = new MemoryStreamHelper();
            memoryStream.AllowClose = false;
            range.Write(memoryStream);
            memoryStream.Flush();
            memoryStream.Position = 0;
            memoryStream.AllowClose = true;
            return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                FileDownloadName = $"库存数据{input.Time:yyyyMMdd}.xlsx",
            };