运用Aspose.Cells组件将数据导至Execl

　　工作了几年，却一直没有做过将数据导出至Execl，想想也很奇葩。

　　最近我们在做一个供应链系统，这类系统，里面自然会涉及到操作Execl的功能，正好我所负责的功能里面有这个。

　　导出Execl的需求大概是这样的，一个主表，一个从表，关系是一对多，主从表关联，导出的数据是主表有多少条数据，那么Execl里面就有多少行数据，这里自然会涉及到合并单元格的动作，并且数据里面还有图片列，但看这需求总结出两点：1) 合并单元格，2) 显示图片。

　　最后导出的效果图截取部分：

　　

　　部门里面的架构组提供的导出Execl组建不支持对指定列合并单元格功能，依赖于NOPI，所以后来选择了Aspose.Cells这玩意，听说网上代码一大堆，就找了找，不过么有找到合适的，之后就去官网下了示例代码看了一些，有些代码片段还是有参考价值的。

　　下面就说自己的思路了。

　　1.原始数据准备

　　　　因为主从关系，且导出行数以主表数据行数决定，所以这里用到了oracle里面的行列转换函数wm_concat，其要点是：

select id, wm_concat(to_char(colorname)) from tb group by id

 　　　因为colorname是中文，如果不加to_char函数的话，查询出来的数据会是乱码。

　　2. 图片显示与合并单元格

　　　　因为这里的图片都存在图片服务器上面，这个组件不支持网络图片，需要先将图片转换成MemoryStream，我觉得吧，这个很鸡肋。

方法是sheet.Pictures.Add，前两个参数是起始行列，行列索引默认是从1开始的，这个需要注意下。合并单元格的方法就是sheet.Cells.Merge，前两个参数是起始行列，索引是从0开始，不知为什么要这样设计？

　　　　还有一个我觉得很不友好的是，给单元格加边框的时候竟然分上下左右4个部分加边线，很是奇怪，下面有代码。

　　3. 代码设计

　　　　这里设计了一个接口IExeclDataExporter，里面只有一个方法void DataExport(ExporterParams exporterParams); 代码如下：

　　/// <summary>
    /// Execl数据导出服务(依赖于Aspose.Cells组件)
    /// </summary>
    internal interface IExeclDataExporter
    {
        void DataExport(ExporterParams exporterParams);
    }

    /// <summary>
    /// 参数
    /// </summary>
    internal sealed class ExporterParams
    {
        /// <summary>
        /// Execl列头
        /// </summary>
        public IList<DataHeader> ExeclDataHeader { get; set; }

        /// <summary>
        /// Execl数据体
        /// </summary>
        public DataTable ExeclDataBody { get; set; }

        public string ExeclTitle { get; set; }

        /// <summary>
        /// 不需要合并列
        /// </summary>
        public string NonMergeColumnCode { get; set; }

        /// <summary>
        /// 图片列
        /// </summary>
        public string ImageColumnCode { get; set; }

        /// <summary>
        /// 导出(绝对路径)
        /// </summary>
        public string DocumentFileName { get; set; }
    }

    /// <summary>
    /// 列头
    /// </summary>
    internal sealed class DataHeader
    {
        /// <summary>
        /// 列显示名称
        /// </summary>
        public string DisplayName { get; set; }

        /// <summary>
        /// 列编码
        /// </summary>
        public string ColumnCode { get; set; }

        /// <summary>
        /// 列宽度
        /// </summary>
        public int ColumnWidth { get; set; }
    }

　　　　实现该接口的类是ExeclDataExporter，代码是：

internal class ExeclDataExporter:IExeclDataExporter
    {
        public void DataExport(ExporterParams eParams)
        {
            Guard.AgainstNull(eParams.ExeclDataHeader, "ExeclDataHeader");

            //1. 变量定义,样式
            Workbook workbook = new Workbook(); 
            DefineStyle(workbook);

            //2. 设置表头数据
            SetExeclHeadData(workbook, eParams.ExeclTitle, eParams.ExeclDataHeader);

            //3. 设置表体数据
            SetExeclBodyData(workbook, eParams);

            //4. 保存
            workbook.Save(eParams.DocumentFileName);

        }

        private void SetExeclHeadData(Workbook workbook, string title, IList<DataHeader> ExeclDataHeader)
        {
            Worksheet sheet = workbook.Worksheets[0];

            sheet.Cells.Merge(0, 0, 1, ExeclDataHeader.Count);
            sheet.Cells[0, 0].PutValue(title);

            Style style1 = workbook.Styles["Font25Center"];
            sheet.Cells[0, 0].SetStyle(style1);

            Style style2 = workbook.Styles["FontTitleCenter"];
            for (var i = 0; i < ExeclDataHeader.Count; i++)
            {
                var head = ExeclDataHeader[i];

                sheet.Cells.SetColumnWidth(i, head.ColumnWidth);

                sheet.Cells[1, i].PutValue(head.DisplayName);
                sheet.Cells[1, i].SetStyle(style2);
            }

            //设置当前数据行索引
            CurrentRowIndex = 2;
        }

        private void SetExeclBodyData(Workbook workbook, ExporterParams eParams)
        {
            var dtBody = eParams.ExeclDataBody;

            Style style = workbook.Styles["FontCenter"];

            for (var i = 0; i < dtBody.Rows.Count; i++)
            {
                var row = dtBody.Rows[i];

                //填充行数据
                FillRowData(workbook.Worksheets[0], style, dtBody.Rows[i], eParams);
            }
        }

        private void FillRowData(Worksheet sheet, Style style, DataRow row, ExporterParams eParams)
        {
            //上一次当前行索引
            var oldCurrentRowIndex = CurrentRowIndex;

            //不合并列索引
            var nonMergeColumnIndex = GetColumnIndex(eParams.NonMergeColumnCode, eParams.ExeclDataHeader);

            //图片列索引
            var imageColumnIndex = GetColumnIndex(eParams.ImageColumnCode, eParams.ExeclDataHeader);

            //合并行的数据
            var mergeRowArr = row[eParams.NonMergeColumnCode] == DBNull.Value ? new string[0] : row[eParams.NonMergeColumnCode].ToString().Split(',');

            //重新设置当前行索引
            CurrentRowIndex = mergeRowArr.Length == 0 ? CurrentRowIndex + 1 : CurrentRowIndex + mergeRowArr.Length;

            for (var j = 0; j < eParams.ExeclDataHeader.Count; j++)
            {
                var head = eParams.ExeclDataHeader[j];

                var obj = row[head.ColumnCode] == DBNull.Value ? "" : row[head.ColumnCode];

                //需要合并
                if (mergeRowArr.Length >= 1)
                {
                    if (j != nonMergeColumnIndex)
                    {
                        //合并
                        sheet.Cells.Merge(oldCurrentRowIndex, j, mergeRowArr.Length, 1);

                        if (j == imageColumnIndex)
                        {
                            if (!string.IsNullOrWhiteSpace(obj.ToString()))
                            {
                                var ms = GetMemoryStream(obj.ToString());
                                if (ms != null)
                                {
                                    sheet.Cells.SetRowHeight(oldCurrentRowIndex, 45);
                                    int index = sheet.Pictures.Add(oldCurrentRowIndex, j, ms, 100, 100);
                                    SetImagePosition(sheet, index);
                                }
                            }
                            SetCellStyle(sheet, style, oldCurrentRowIndex, j);
                        }
                        else
                        {
                            //赋值
                            sheet.Cells[oldCurrentRowIndex, j].PutValue(obj);

                            for (var k = 0; k < mergeRowArr.Length; k++)
                            {
                                SetCellStyle(sheet, style, oldCurrentRowIndex + k, j);
                            }
                        }
                    }
                    else
                    {
                        for (var k = 0; k < mergeRowArr.Length; k++)
                        {
                            sheet.Cells[oldCurrentRowIndex + k, j].PutValue(mergeRowArr[k]);
                            SetCellStyle(sheet, style, oldCurrentRowIndex + k, j);
                        }
                    }
                }
                else
                {
                    if (j == imageColumnIndex)
                    {
                        var ms = GetMemoryStream(obj.ToString());
                        if (ms != null)
                        {
                            sheet.Cells.SetRowHeight(oldCurrentRowIndex, 45);
                            int index = sheet.Pictures.Add(oldCurrentRowIndex, j, ms);
                            SetImagePosition(sheet, index);
                        }

                    }
                    else
                    {
                        sheet.Cells[oldCurrentRowIndex, j].PutValue(obj);
                    }

                    SetCellStyle(sheet, style, oldCurrentRowIndex, j);
                }
            }
        }

        private void SetCellStyle(Worksheet sheet, Style style, int k, int j)
        {
            sheet.Cells[k, j].SetStyle(style);
        }

        private void SetImagePosition(Worksheet sheet, int index)
        {
            Aspose.Cells.Drawing.Picture pic = sheet.Pictures[index];
            pic.Left = 6;
            pic.Top = 2;
        }

        private void DefineStyle(Workbook workbook)
        {
            Style style;
            int styleIndex = workbook.Styles.Add();
            style = workbook.Styles[styleIndex];
            style.Font.Size = 25;
            style.Font.IsBold = true;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Borders[BorderType.LeftBorder].Color = Color.Black;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].Color = Color.Black;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].Color = Color.Black;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].Color = Color.Black;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Name = "Font25Center";

            styleIndex = workbook.Styles.Add();
            style = workbook.Styles[styleIndex];
            style.Font.IsBold = true;
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.Borders[BorderType.LeftBorder].Color = Color.Black;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].Color = Color.Black;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].Color = Color.Black;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].Color = Color.Black;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Name = "FontTitleCenter";

            styleIndex = workbook.Styles.Add();
            style = workbook.Styles[styleIndex];
            style.HorizontalAlignment = TextAlignmentType.Center;
            style.IsTextWrapped = true;
            style.Borders[BorderType.LeftBorder].Color = Color.Black;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].Color = Color.Black;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].Color = Color.Black;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].Color = Color.Black;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Name = "FontCenter";

        }

        #region 辅助方法

        private int GetColumnIndex(string columnCode, IList<DataHeader> ExeclDataHeader)
        {
            var item = ExeclDataHeader.FirstOrDefault(w => w.ColumnCode == columnCode);

            return ExeclDataHeader.IndexOf(item);
        }

        private MemoryStream GetMemoryStream(string url)
        {
            try
            {
                WebClient wc = new WebClient();
                byte[] bytes = wc.DownloadData(url);
                return new MemoryStream(bytes);
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region 私有属性

        private int CurrentRowIndex { set; get; } 

        #endregion
    }

 　　　　调用代码如下：

　　　　　　var data = GetExportProductInfo(querymodel).Data; //这里是datatable
            
            if (data.Rows.Count == 0)
                return null;

            var exporterParams = new ExporterParams();
            exporterParams.ExeclDataHeader = new List<DataHeader>() { 
                new DataHeader { DisplayName = "样版编号", ColumnCode = "SampleCode", ColumnWidth = 20 },
                new DataHeader { DisplayName = "图片", ColumnCode = "SmallPicStyle",  ColumnWidth = 8 },
                new DataHeader { DisplayName = "颜色", ColumnCode = "ColorName", ColumnWidth = 15 },
            };

            exporterParams.DocumentFileName = filePath;
            exporterParams.ExeclDataBody = data;
            exporterParams.NonMergeColumnCode = "ColorName";
            exporterParams.ImageColumnCode = "SmallPicStyle";
            exporterParams.ExeclTitle = "产品资料列表";

            IExeclDataExporter exporter = new ExeclDataExporter();
            exporter.DataExport(exporterParams);

  　　　　你们看了上面的代码是不是觉得很简单啊，呵呵，个人觉得吧，在合并单元格的计算逻辑需要注意一下，其他的都是搬砖的活，如果有什么疑问可以给我留言，写的不是很好请勿见怪。