Aspose.Cells.Workbook批量生成复杂多页签EXCEL文档并导出

以前没有做过这个东西，最近工作遇到了，把结果记录一下，以后或许有用。

前端

<button id="btn_ExportItemToExcel" type="button" style="min-width: 60px;">导出生产料号</button>
<form id="formExport" action="/APSManage/OrderSchedule/ExportBOMServlet" method="post" style="float:left;">
     <input id="ItemNos" name="ItemNos" type="hidden" value="" />
</form>

//暂时没找到批量提交form的方法，这种写法只能提交选中行的最后一个
//PS：导出EXCEL只能form提交，用ajax的方式不行，ajax只能返回数据流，还需要在前端做处理才行
//    form提交会直接浏览器下载文件
/*
$("#btn_ExportItemToExcel").click(function () {
                var selectedRows = _oSpeadJS.fnGetSelectedRows();
                var data = _oSpeadJS.data();
                var itemNos = [];
                $(selectedRows).each(function (idx, rowIndex) {
                    var item = data[rowIndex];
                    if (item.WIP_ITEM_CODE != "") {
                        //itemNos.push(item.WIP_ITEM_CODE);
                        //$("#ItemNos").val(JSON.stringify(itemNos));
                        $("#ItemNos").val(item.WIP_ITEM_CODE);
                        $("#formExport").trigger("submit");
                        setTimeout(function () {
                            layer.close(index);
                        }, 2000);
                    }
                });
            });
*/
//找到批量导出的方法了，将Execl文件生成到一个临时目录，然后生成压缩包导出
 $("#btn_ExportItemToExcel").kendoButton({
                spriteCssClass: "k-icon icon-download",
                click: function () {
                    var index = layer.msg("正在导出Excel...");
                    $(this).blur();

                    var selectedRows = _oSpeadJS.fnGetSelectedRows();
                    var data = _oSpeadJS.data();
                    var itemNos = [];
                    $(selectedRows).each(function (idx, rowIndex) {
                        var item = data[rowIndex];
                        if (item.WIP_ITEM_CODE != "") {
                            itemNos.push(item.WIP_ITEM_CODE);

                            //$("#ItemNos").val(item.WIP_ITEM_CODE);
                        }
                    });
                    if (itemNos.length > 0) {
                        $("#ItemNos").val(JSON.stringify(itemNos));
                        $("#formExport").trigger("submit");
                        setTimeout(function () {
                            layer.close(index);
                        }, 2000);
                    }
                }
            });

后端

[HttpPost]
 public void ExportBOMServlet(ExportDto model)
        {
            MemoryStream ms = new MemoryStream();
            try
            {
                if (model == null || string.IsNullOrEmpty(model.ItemNos))
                {
                    Response.Write("<script>window.parent.failTips('无数据!');location.href='/APSManage/OrderSchedule/Index';</script>");
                    Response.End();
                    return;
                }

                string directoryPath = Server.MapPath("~/FileUpload/OrderBom");
                // 确保目录存在
                if (!Directory.Exists(directoryPath))
                {
                    Directory.CreateDirectory(directoryPath);
                }
                List<string> fileList = new List<string>();
                var ItemNos = JsonConvert.DeserializeObject<List<string>>(model.ItemNos);
                foreach (var item in ItemNos)
                {
                    //获取BOM信息
                    var data = GetBomInfo(item);
                    if (data != null && data.code == "200")
                    {
                        string fileName = $"{item}.xls";
                        string filePath = Path.Combine(directoryPath, fileName);

                        //获取变更记录信息
                        var changeData = GetChangeHistoryInfo(item);
                        var softWare = GetSoftWareInfo(item);
                        Workbook wb = ToExcel(data.result, changeData, softWare);
                        wb.Save(filePath);
                        fileList.Add(filePath);
                        //ms = wb.SaveToStream();
                        //var attachment = string.Format("attachment;filename={0}.xls", model.ItemNos);
                        //Response.AppendHeader("Content-Disposition", attachment);
                        //Response.ContentType = "application/vnd.ms-excel";
                        //Response.ContentEncoding = Encoding.UTF8;
                        //wb.Save(Response.OutputStream, Aspose.Cells.SaveFormat.Excel97To2003);
                        //Response.Flush();
                        //Response.Clear();
                        //Response.End();
                    }
                }
               
                if (fileList.Count > 0)
                {
                    var zipName = "OrderBom.zip";
                    var fullPath = Path.Combine("FileUpload", "OrderBom", zipName);
                    var zipFileFullPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fullPath);
                    var size = ZipHelper.CreateFile(zipFileFullPath, fileList);
                    if (size > 0)
                    {
                        foreach (var item in fileList)
                        {
                            System.IO.File.Delete(item);
                        }
                        FileStream stream = new FileInfo(zipFileFullPath).OpenRead();
                        byte[] fileB = new byte[stream.Length];
                        stream.Read(fileB, 0, fileB.Length);
                        stream.Close();
                        System.IO.File.Delete(zipFileFullPath);
                        Response.AddHeader("Content-Disposition", "attachment;filename=" + WebHelper.GetFileName(zipName));
                        Response.BinaryWrite(fileB);
                        Response.End();
                    }
                }
            }
            catch (Exception exp)
            {
                Response.Write("<script>alert('导出失败，" + exp.Message + "');location.href='/APSManage/OrderSchedule/Index'; </script>");
                Response.End();
                return;
            }
        }


       
        private Style GetStyle(Workbook workbook, int size, bool isBold, bool isTextWrapped, TextAlignmentType type)
        {
            Style style = workbook.CreateStyle();
            style.HorizontalAlignment = type;
            style.Font.Name = "宋体";
            style.Font.Size = size;
            style.Font.IsBold = isBold;
            style.IsTextWrapped = isTextWrapped;

            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

            return style;
        }

        private Workbook ToExcel(ResultDto data, ChangeHistoryDto historyDto, SoftWareDto softWareDto)
        {
            Workbook workbook = new Workbook();
            
            // 粗体12左对齐
            Style style_B_L = GetStyle(workbook, 12, true, false, TextAlignmentType.Left);
            // 粗体12右对齐
            Style style_B_R = GetStyle(workbook, 12, true, false, TextAlignmentType.Right);
            // 粗体12居中对齐
            Style style_B_C = GetStyle(workbook, 12, true, false, TextAlignmentType.Center);
            //细体10左对齐
            Style style_L = GetStyle(workbook, 10, false, false, TextAlignmentType.Left);
            //细体10右对齐
            Style style_R = GetStyle(workbook, 10, false, true, TextAlignmentType.Right);
            //细体10居中对齐
            Style style_C = GetStyle(workbook, 10, false, false, TextAlignmentType.Center);

            #region bom_sheet
            var bom_sheet = workbook.Worksheets[0];
            bom_sheet.Name = "bom";
            Cells cells = bom_sheet.Cells;
            #region 设置行高列宽
            cells.SetRowHeight(0, 35);
            cells.SetRowHeight(1, 25);
            cells.SetRowHeight(2, 25);
            cells.SetRowHeight(3, 25);
            cells.SetRowHeight(4, 25);
            cells.SetRowHeight(5, 35);


            cells.SetColumnWidth(0, 7);
            cells.SetColumnWidth(1, 7);
            cells.SetColumnWidth(2, 10);
            cells.SetColumnWidth(3, 12);
            cells.SetColumnWidth(4, 20);
            cells.SetColumnWidth(5, 45);
            cells.SetColumnWidth(6, 5);
            cells.SetColumnWidth(7, 30);
            cells.SetColumnWidth(8, 14);
            cells.SetColumnWidth(9, 15);
            cells.SetColumnWidth(10, 10);
            cells.SetColumnWidth(11, 15);
            #endregion

            #region 内容填充
            // 合并单元格，参数为开始行号，开始列号，合并几行，合并几列
            //Merge(int firstRow, int firstColumn, int totalRows, int totalColumns);
            #region 第一行
            //拟制：
            cells.Merge(0, 0, 1, 5);
            cells[0, 0].PutValue("拟制：" + data.createUser);

            //审核：
            cells[0, 5].PutValue("审核：" + data.auditor);

            //更新日期：
            cells.Merge(0, 6, 1, 2);
            cells[0, 6].PutValue("更新日期：" + data.revReleaseDate);

            //版本：
            cells.Merge(0, 8, 1, 4);
            cells[0, 8].PutValue("版本：" + data.rev);
            Range range = bom_sheet.Cells.CreateRange(0, 0, 1, 12);
            range.ApplyStyle(style_B_L, new StyleFlag() { All = true });
            #endregion

            #region 第二行
            //内部型号：
            cells.Merge(1, 0, 1, 3);
            cells[1, 0].PutValue("内部型号：");
            cells.Merge(1, 3, 1, 2);
            cells[1, 3].PutValue(data.productType);

            //规格型号：
            cells[1, 5].PutValue("规格型号：");
            cells.Merge(1, 6, 1, 2);
            cells[1, 6].PutValue(data.spec);

            //产品属性：
            cells[1, 8].PutValue("产品属性：");
            cells.Merge(1, 9, 1, 3);
            cells[1, 9].PutValue(data.productAttr);
            #endregion

            #region 第三行
            //第三行
            //中文品名：
            cells.Merge(2, 0, 1, 3);
            cells[2, 0].PutValue("中文品名：");
            cells.Merge(2, 3, 1, 2);
            cells[2, 3].PutValue(data.chineseName);

            //英文品名：
            cells[2, 5].PutValue("英文品名：");
            cells.Merge(2, 6, 1, 6);
            cells[2, 6].PutValue(data.englishName);
            #endregion

            #region 第四行
            //第四行
            //EAN码：
            cells.Merge(3, 0, 1, 3);
            cells[3, 0].PutValue("EAN码：");
            cells.Merge(3, 3, 1, 2);
            cells[3, 3].PutValue(data.ean);

            //UPC码：
            cells[3, 5].PutValue("UPC码：");
            cells.Merge(3, 6, 1, 6);
            cells[3, 6].PutValue(data.upc);
            #endregion

            #region 第五行
            //第五行
            //项目型标准质保期(月)：
            cells.Merge(4, 0, 1, 3);
            cells[4, 0].PutValue("项目型标准质保期(月)：");
            cells.Merge(4, 3, 1, 2);
            cells[4, 3].PutValue(data.p2Num01);

            //分销型标准质保期(月)：
            cells[4, 5].PutValue("分销型标准质保期(月)：");
            cells.Merge(4, 6, 1, 2);
            cells[4, 6].PutValue(data.p2Num02);

            //几个一大包：
            cells[4, 8].PutValue("几个一大包：");
            cells.Merge(4, 9, 1, 3);
            cells[4, 9].PutValue(data.p3Num06);
            Range range2 = bom_sheet.Cells.CreateRange(1, 0, 4, 12);
            range2.ApplyStyle(style_B_R, new StyleFlag() { All = true });

            #endregion

            #region 第六行  明细表头
            //第六行  明细表头
            cells[5, 0].PutValue("级别");
            cells[5, 1].PutValue("行号");
            cells[5, 2].PutValue("标记");
            cells[5, 3].PutValue("主料/替代料");
            cells[5, 4].PutValue("器件编号");
            cells[5, 5].PutValue("器件名称及规格");
            cells[5, 6].PutValue("数量");
            cells[5, 7].PutValue("位置及说明");
            cells[5, 8].PutValue("备注");
            cells[5, 9].PutValue("烧录方式");
            cells[5, 10].PutValue("焊接方式");
            cells[5, 11].PutValue("焊接PIN数量");
            Range range3 = bom_sheet.Cells.CreateRange(5, 0, 1, 12);
            range3.ApplyStyle(style_B_C, new StyleFlag() { All = true });
            #endregion

            //填充BOM明细
            if (data.itemlist.Count > 0)
            {
                for (int i = 0; i < data.itemlist.Count; i++)
                {
                    var item = data.itemlist[i];
                    cells[6 + i, 0].PutValue(item.level);
                    cells[6 + i, 0].SetStyle(style_L);

                    cells[6 + i, 1].PutValue(item.findNumber);
                    cells[6 + i, 1].SetStyle(style_R);

                    cells[6 + i, 2].PutValue(item.mark);
                    cells[6 + i, 2].SetStyle(style_L);

                    cells[6 + i, 3].PutValue(item.mainReplaceItem);
                    cells[6 + i, 3].SetStyle(style_C);

                    cells[6 + i, 4].PutValue(item.itemNumber);
                    cells[6 + i, 4].SetStyle(style_L);

                    cells[6 + i, 5].PutValue(item.itemName);
                    cells[6 + i, 5].SetStyle(style_L);

                    cells[6 + i, 6].PutValue(item.qty);
                    cells[6 + i, 6].SetStyle(style_R);

                    cells[6 + i, 7].PutValue(item.refDes);
                    cells[6 + i, 7].SetStyle(style_L);

                    cells[6 + i, 8].PutValue(item.notes);
                    cells[6 + i, 8].SetStyle(style_L);

                    cells[6 + i, 9].PutValue(item.burn);
                    cells[6 + i, 9].SetStyle(style_L);

                    cells[6 + i, 10].PutValue(item.weldType);
                    cells[6 + i, 10].SetStyle(style_L);

                    cells[6 + i, 11].PutValue(item.weldPINNumber);
                    cells[6 + i, 11].SetStyle(style_R);
                }
            }
            #endregion

            #endregion

            #region change history
            workbook.Worksheets.Add("change history");
            var history_sheet = workbook.Worksheets["change history"];
            Cells history_cells = history_sheet.Cells;

            #region 设置行高列宽
            history_cells.SetRowHeight(0, 35);
            history_cells.SetRowHeight(1, 30);


            history_cells.SetColumnWidth(0, 7);
            history_cells.SetColumnWidth(1, 12);
            history_cells.SetColumnWidth(2, 40);
            history_cells.SetColumnWidth(3, 20);
            history_cells.SetColumnWidth(4, 20);
            history_cells.SetColumnWidth(5, 15);
            history_cells.SetColumnWidth(6, 15);
            history_cells.SetColumnWidth(7, 15);
            history_cells.SetColumnWidth(8, 15);
            history_cells.SetColumnWidth(9, 15);
            #endregion

            #region 内容填充
            // 合并单元格，参数为开始行号，开始列号，合并几行，合并几列
            //Merge(int firstRow, int firstColumn, int totalRows, int totalColumns);
            #region 第一行
            //拟制：
            history_cells.Merge(0, 0, 1, 10);
            history_cells[0, 0].PutValue("变更历史记录");

            Range range_history = history_sheet.Cells.CreateRange(0, 0, 1, 10);
            range_history.ApplyStyle(style_B_C, new StyleFlag() { All = true });
            #endregion

            #region 第二行  明细表头
            //第二行  明细表头
            history_cells[1, 0].PutValue("版本");
            history_cells[1, 1].PutValue("变更单编号");
            history_cells[1, 2].PutValue("变更单描述");
            history_cells[1, 3].PutValue("发放日期");
            history_cells[1, 4].PutValue("生效日期");
            history_cells[1, 5].PutValue("生命周期阶段");
            history_cells[1, 6].PutValue("变更原因详情");
            history_cells[1, 7].PutValue("变更前说明");
            history_cells[1, 8].PutValue("变更后说明");
            history_cells[1, 9].PutValue("变更切换方式");
            Range range_history2 = history_sheet.Cells.CreateRange(1, 0, 1, 10);
            range_history2.ApplyStyle(style_B_L, new StyleFlag() { All = true });
            #endregion

            //填充明细
            if (historyDto.changeHistorys.Count > 0)
            {
                for (int i = 0; i < historyDto.changeHistorys.Count; i++)
                {
                    var item = historyDto.changeHistorys[i];
                    history_cells[2 + i, 0].PutValue(item.rev);
                    history_cells[2 + i, 0].SetStyle(style_L);

                    history_cells[2 + i, 1].PutValue(item.changeNumber);
                    history_cells[2 + i, 1].SetStyle(style_L);

                    history_cells[2 + i, 2].PutValue(item.changeDescription);
                    history_cells[2 + i, 2].SetStyle(style_L);

                    history_cells[2 + i, 3].PutValue(item.relDate);
                    history_cells[2 + i, 3].SetStyle(style_L);

                    history_cells[2 + i, 4].PutValue(item.effectiveDate);
                    history_cells[2 + i, 4].SetStyle(style_L);

                    history_cells[2 + i, 5].PutValue(item.lifecyclePhase);
                    history_cells[2 + i, 5].SetStyle(style_L);

                    history_cells[2 + i, 6].PutValue(item.reasonDetail);
                    history_cells[2 + i, 6].SetStyle(style_L);

                    history_cells[2 + i, 7].PutValue(item.beforeChangeNote);
                    history_cells[2 + i, 7].SetStyle(style_L);

                    history_cells[2 + i, 8].PutValue(item.afterChangeNote);
                    history_cells[2 + i, 8].SetStyle(style_L);

                    history_cells[2 + i, 9].PutValue(item.switchType);
                    history_cells[2 + i, 9].SetStyle(style_L);
                }
            }
            #endregion

            #endregion

            #region software
            workbook.Worksheets.Add("software");
            var soft_sheet = workbook.Worksheets["software"];
            Cells soft_cells = soft_sheet.Cells;

            #region 设置行高列宽
            soft_cells.SetRowHeight(0, 35);

            soft_cells.SetColumnWidth(0, 35);
            soft_cells.SetColumnWidth(1, 130);
            #endregion

            #region 内容填充
            // 合并单元格，参数为开始行号，开始列号，合并几行，合并几列
            //Merge(int firstRow, int firstColumn, int totalRows, int totalColumns);
            #region 第一行
            //拟制：
            soft_cells.Merge(0, 0, 1, 2);
            soft_cells[0, 0].PutValue("软件信息");

            Range range_soft = soft_sheet.Cells.CreateRange(0, 0, 1, 2);
            range_soft.ApplyStyle(style_B_C, new StyleFlag() { All = true });
            #endregion

            #region 第二行  明细表头
            //第二行  明细表头
            soft_cells[1, 0].PutValue("程序编号");
            soft_cells[1, 1].PutValue("软件信息");
            Range range_soft2 = soft_sheet.Cells.CreateRange(1, 0, 1, 2);
            range_soft2.ApplyStyle(style_B_C, new StyleFlag() { All = true });
            #endregion

            int cellNum = 1;
            //填充明细
            if (softWareDto.softwareInfos.Count > 0)
            {
                for (int i = 0; i < softWareDto.softwareInfos.Count; i++)
                {
                    var item = softWareDto.softwareInfos[i];
                    soft_cells[2 + i, 0].PutValue(item.softwareNumber);
                    soft_cells[2 + i, 0].SetStyle(style_C);

                    soft_cells[2 + i, 1].PutValue(item.softwareDescription);
                    soft_cells[2 + i, 1].SetStyle(style_L);

                    cellNum ++;
                }
            }
            #endregion

            #region 软件信息说明	
            cellNum += 2;
            soft_cells.Merge(cellNum, 0, 1, 2);
            soft_cells[cellNum, 0].PutValue("软件信息说明");

            Range range_soft3 = soft_sheet.Cells.CreateRange(cellNum, 0, 1, 2);
            range_soft3.ApplyStyle(style_B_C, new StyleFlag() { All = true });

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("程序说明");
            soft_cells[cellNum, 1].PutValue(softWareDto.softwareExplain);
            soft_cells[cellNum, 1].SetStyle(style_L);

            Range range_soft3_2 = soft_sheet.Cells.CreateRange(cellNum, 0, 3, 1);
            range_soft3_2.ApplyStyle(style_B_C, new StyleFlag() { All = true });

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("web显示型号");
            soft_cells[cellNum, 1].PutValue(softWareDto.webModel);
            soft_cells[cellNum, 1].SetStyle(style_L);

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("FPGA版本");
            soft_cells[cellNum, 1].PutValue(softWareDto.fpgaVer);
            soft_cells[cellNum, 1].SetStyle(style_L);

            #endregion 

            #region 参数信息	
            cellNum += 2;
            soft_cells.Merge(cellNum, 0, 1, 2);
            soft_cells[cellNum, 0].PutValue("参数信息");

            Range range_soft4 = soft_sheet.Cells.CreateRange(cellNum, 0, 1, 2);
            range_soft4.ApplyStyle(style_B_C, new StyleFlag() { All = true });

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("BOOT");
            soft_cells[cellNum, 1].PutValue(softWareDto.boot);
            soft_cells[cellNum, 1].SetStyle(style_L);

            Range range_soft4_2 = soft_sheet.Cells.CreateRange(cellNum, 0, 3, 1);
            range_soft4_2.ApplyStyle(style_B_C, new StyleFlag() { All = true });

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("平台信息");
            soft_cells[cellNum, 1].PutValue(softWareDto.platformInfo);
            soft_cells[cellNum, 1].SetStyle(style_L);

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("MAC数量");
            soft_cells[cellNum, 1].PutValue(softWareDto.macQty);
            soft_cells[cellNum, 1].SetStyle(style_L);

            #endregion 

            #region 光盘信息	
            cellNum += 2;
            soft_cells.Merge(cellNum, 0, 1, 2);
            soft_cells[cellNum, 0].PutValue("光盘信息");
            Range range_soft5 = soft_sheet.Cells.CreateRange(cellNum, 0, 1, 2);
            range_soft5.ApplyStyle(style_B_C, new StyleFlag() { All = true });

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue("光盘编号");
            soft_cells[cellNum, 1].PutValue("光盘名称");
            Range range_soft6 = soft_sheet.Cells.CreateRange(cellNum, 0, 1, 2);
            range_soft6.ApplyStyle(style_B_C, new StyleFlag() { All = true });

            cellNum += 1;
            soft_cells[cellNum, 0].PutValue(softWareDto.discInfos.number);
            soft_cells[cellNum, 0].SetStyle(style_C);

            soft_cells[cellNum, 1].PutValue(softWareDto.discInfos.name);
            soft_cells[cellNum, 1].SetStyle(style_L);
            #endregion 

            #endregion

            return workbook;
        }

最终呈现效果做比较