C# 使用Aspose.Cells 匯出Excel

 今天在工作中碰到同事用了一種新型的方式匯入excel，在此做個學習記錄。

外掛：Aspose.Cells 

 

第一步:準備好匯出的模板，例子：

 

C#程式碼：

#region 驗證資料

            if (model == null)
            {
                throw new FriendlyException("無該月結單！");
            }
            var templatePath = @"Template\trade\Cecsc2bHN\分包分供月度結算審批表.xlsx"; ;
            var resFileName = string.Empty;
            var bigTitle = string.Empty;
            var barCodeStr = string.Empty;


            if (string.IsNullOrEmpty(templatePath) || !File.Exists(templatePath))
            {
                throw new FriendlyException("未找到模板檔案！");
            }

            #endregion

            #region 初始化模板檔案

            var wk = new Workbook(templatePath);
            var designer = new WorkbookDesigner(wk);

            #endregion

            #region 資料重構造

            // 工程形象進度

            #endregion

            #region 構造頭部資訊
            var topInfo = new Dictionary<string, object>
            {
                { "Title", bigTitle },
                { "ProjectName", model.ProjectName },
                { "Code", barCodeStr }
            };
            #endregion

            MonthSettlementInfo monthInfo = new MonthSettlementInfo()
            {
                CurrentInContractSettlementAmount = 56.32M,
                TerminalInContractSettlementAmount = 123.32M,
                CurrentOutContractSettlementAmount = 6.32M,
                TerminalOutContractSettlementAmount = 5.32M,
                CurrentDeductionSettlementAmount = 12.32M,
                TerminalDeductionSettlementAmount = 26.32M,


                CurrentInContractSafeAmount = 2.32M,
                TerminalInContractSafeAmount = 1.32M,
                CurrentOutContractSafeAmount = 6.32M,
                TerminalOutContractSafeAmount = 8.32M,
                CurrentDeductionSafeAmount = 4.32M,
                TerminalDeductionSafeAmount = 3.32M, 
            };

 

            //形象進度描述
            List<TradeSettleReportProjectProgressItemModel> ProjectProgressList = new List<TradeSettleReportProjectProgressItemModel>();

            for (int i = 0; i < 3; i++)
            {
                TradeSettleReportProjectProgressItemModel Progressmodel = new TradeSettleReportProjectProgressItemModel()
                {
                    ConstractArea = "測試水水水水水水" + i,
                    Remark = "測試模擬的備註" + i,
                    ProgressDesc = "測試撒子怕傳送所屬" + i,
                    ConstractSite = "場地" + i
                };
                ProjectProgressList.Add(Progressmodel);
            }

            #region 工程名稱
  
            //繫結資料到excel中
            //designer.SetDataSource("繫結的到excle的變數名"，"對應的值")
            designer.SetDataSource("ProgressItem", ProjectProgressList);    //list可以迴圈繫結  繫結的例子在下面。
            designer.SetDataSource("ProjectName", model.ProjectName);
            designer.SetDataSource("ContractName", model.ContractName);
            designer.SetDataSource("ContractCode", model.ContractCode);
            designer.SetDataSource("MonthSettlementNo", model.MonthSettlementNo);
            designer.SetDataSource("ContractingUnit", model.ContractingUnit);
            designer.SetDataSource("SettlementCode", model.SettlementCode);
            designer.SetDataSource("EnterpriseName", model.EnterpriseName);
            designer.SetDataSource("ThirdPartyName", model.ThirdPartyName);
            designer.SetDataSource("SettlementMonth", model.SettlementMonth);
            designer.SetDataSource("TotalContractAmount", model.TotalContractAmount);
            designer.SetDataSource("SettlementAmountCapitalize", model.SettlementAmountCapitalize);
            #endregion
 
 

            #endregion


            #region 尾部

            #endregion 
            designer.Process();
            wk.CalculateFormula(); 
 

            #region 重新計算行高
            var startRow = 9;
            var endRow = startRow;

            #endregion

            #region 匯出檔案

            var fileName = $"{model.SettlementMonth.ToString("yyyy年MM月")}{resFileName}.xlsx";
            using (var file = new MemoryStream())
            {
                wk.Save(file, SaveFormat.Xlsx);
                wk.Dispose();
                return new KeyValuePair<string, byte[]>(fileName, file.ToArray());
            }
            #endregion

 

此種當時的重點是Excel模板的資料繫結：

第一種：單個值的繫結

 

 

第二種：列表值的迴圈繫結

 

 

 完整程式碼

   Service層：

 

public KeyValuePair<string, byte[]> ExportSettle(TradeSettleReportModel model)
        {
            #region 驗證資料

            if (model == null)
            {
                throw new FriendlyException("無該月結單！");
            }
            var templatePath = @"Template\trade\Cecsc2bHN\分包分供月度結算審批表.xlsx"; ;
            var resFileName = string.Empty;
            var bigTitle = string.Empty;
            var barCodeStr = string.Empty;


            if (string.IsNullOrEmpty(templatePath) || !File.Exists(templatePath))
            {
                throw new FriendlyException("未找到模板檔案！");
            }

            #endregion

            #region 初始化模板檔案

            var wk = new Workbook(templatePath);
            var designer = new WorkbookDesigner(wk);

            #endregion

            #region 資料重構造

            // 工程形象進度

            #endregion

            #region 構造頭部資訊
            var topInfo = new Dictionary<string, object>
            {
                { "Title", bigTitle },
                { "ProjectName", model.ProjectName },
                { "Code", barCodeStr }
            };
            #endregion

            MonthSettlementInfo monthInfo = new MonthSettlementInfo()
            {
                CurrentInContractSettlementAmount = 56.32M,
                TerminalInContractSettlementAmount = 123.32M,
                CurrentOutContractSettlementAmount = 6.32M,
                TerminalOutContractSettlementAmount = 5.32M,
                CurrentDeductionSettlementAmount = 12.32M,
                TerminalDeductionSettlementAmount = 26.32M,


                CurrentInContractSafeAmount = 2.32M,
                TerminalInContractSafeAmount = 1.32M,
                CurrentOutContractSafeAmount = 6.32M,
                TerminalOutContractSafeAmount = 8.32M,
                CurrentDeductionSafeAmount = 4.32M,
                TerminalDeductionSafeAmount = 3.32M, 
            };



            //審批記錄
            List<TradeSettleReportApprovalCommentsModel> approveModelList = new List<TradeSettleReportApprovalCommentsModel>();

            for (int i = 0; i < 2; i++)
            {

                TradeSettleReportApprovalCommentsModel approveModel = new TradeSettleReportApprovalCommentsModel()
                {
                    Approver = "admin" + i,
                    Comments = "審批通過" + i,
                    NodeName = "測試模板" + i,
                    Seq = i
                };
                approveModelList.Add(approveModel);
            }

            //形象進度描述
            List<TradeSettleReportProjectProgressItemModel> ProjectProgressList = new List<TradeSettleReportProjectProgressItemModel>();

            for (int i = 0; i < 3; i++)
            {
                TradeSettleReportProjectProgressItemModel Progressmodel = new TradeSettleReportProjectProgressItemModel()
                {
                    ConstractArea = "測試水水水水水水" + i,
                    Remark = "測試模擬的備註" + i,
                    ProgressDesc = "測試撒子怕傳送所屬" + i,
                    ConstractSite = "場地" + i
                };
                ProjectProgressList.Add(Progressmodel);
            }

            #region 工程名稱

            designer.SetDataSource("CurrentInContractSettlementAmount", monthInfo.CurrentInContractSettlementAmount);
            designer.SetDataSource("TerminalInContractSettlementAmount", monthInfo.TerminalInContractSettlementAmount);
            designer.SetDataSource("CurrentOutContractSettlementAmount", monthInfo.CurrentOutContractSettlementAmount);
            designer.SetDataSource("TerminalOutContractSettlementAmount", monthInfo.TerminalOutContractSettlementAmount);
            designer.SetDataSource("CurrentDeductionSettlementAmount", monthInfo.CurrentDeductionSettlementAmount);
            designer.SetDataSource("TerminalDeductionSettlementAmount", monthInfo.TerminalDeductionSettlementAmount);
            designer.SetDataSource("CurrentInContractSafeAmount", monthInfo.CurrentInContractSafeAmount);
            designer.SetDataSource("TerminalInContractSafeAmount", monthInfo.TerminalInContractSafeAmount);
            designer.SetDataSource("CurrentOutContractSafeAmount", monthInfo.CurrentOutContractSafeAmount);
            designer.SetDataSource("TerminalOutContractSafeAmount", monthInfo.TerminalOutContractSafeAmount);
            designer.SetDataSource("CurrentDeductionSafeAmount", monthInfo.CurrentDeductionSafeAmount);
            designer.SetDataSource("TerminalDeductionSafeAmount", monthInfo.TerminalDeductionSafeAmount);


            designer.SetDataSource("DetailItem", approveModelList);
            designer.SetDataSource("ProgressItem", ProjectProgressList);
            designer.SetDataSource("ProjectName", model.ProjectName);
            designer.SetDataSource("ContractName", model.ContractName);
            designer.SetDataSource("ContractCode", model.ContractCode);
            designer.SetDataSource("MonthSettlementNo", model.MonthSettlementNo);
            designer.SetDataSource("ContractingUnit", model.ContractingUnit);
            designer.SetDataSource("SettlementCode", model.SettlementCode);
            designer.SetDataSource("EnterpriseName", model.EnterpriseName);
            designer.SetDataSource("ThirdPartyName", model.ThirdPartyName);
            designer.SetDataSource("SettlementMonth", model.SettlementMonth);
            designer.SetDataSource("TotalContractAmount", model.TotalContractAmount);
            designer.SetDataSource("SettlementAmountCapitalize", model.SettlementAmountCapitalize);
            #endregion

            #region 預算編號
            //var _dic = this.GetLaborProSettleInfo(exportDataSource);
            //foreach (var item in _dic)
            //{
            //    designer.SetDataSource(item.Key, item.Value);
            //}

            #endregion

            #region 寫入資料
            //foreach (var keyValuePair in topInfo)
            //{
            //    designer.SetDataSource(keyValuePair.Key, keyValuePair.Value);
            //}
            //designer.SetDataSource("ProjectProgress", exportDataSource.ProjectProgressItems);
            //designer.SetDataSource("MonthEstimate", exportDataSource.MonthEstimateOutputs);
            #region 表1
            //
            Dictionary<string, decimal> dic = new Dictionary<string, decimal>();

            if (dic != null)
            {
                foreach (var item in dic)
                {
                    designer.SetDataSource(item.Key, item.Value);
                }
            }

            #endregion

            #endregion


            #region 尾部

            #endregion
            #region 刪除模板sheet
            //wk.Worksheets.RemoveAt(wk.Worksheets["表2"].Index);
            //wk.Worksheets.RemoveAt(wk.Worksheets["表3"].Index);
            designer.Process();
            wk.CalculateFormula();
            #endregion

            #region 插入條形碼

            //var barCode = BarCodeGenerator.GetBarCodePNG(barCodeStr);
            //var barCode = BarCodeGenerator.WriteQRCodeImg(barCodeStr);
            //wk.Worksheets[0].Pictures.Add(0, 0, 1, 1, new MemoryStream(barCode));
            //wk.Worksheets[0].Pictures[0].Width = 60;
            //wk.Worksheets[0].Pictures[0].Width = 60;
            #endregion

            #region 重新計算行高
            var startRow = 9;
            var endRow = startRow;

            #endregion

            #region 匯出檔案

            var fileName = $"{model.SettlementMonth.ToString("yyyy年MM月")}{resFileName}.xlsx";
            using (var file = new MemoryStream())
            {
                wk.Save(file, SaveFormat.Xlsx);
                wk.Dispose();
                return new KeyValuePair<string, byte[]>(fileName, file.ToArray());
            }
            #endregion
        }

Interface層：

KeyValuePair<string, byte[]> ExportSettle(TradeSettleReportModel model);

Controller層：

        [AllowAnonymous]
        [HttpPost("Export")]
        public async Task<IActionResult> Export()
        {
            TradeSettleReportModel model = new TradeSettleReportModel()
            {
                ProjectName = "測刷",
                ContractName = "測試合同名稱",
                ContractCode = "0010101012E",
                MonthSettlementNo = "0010101012E",
                ContractingUnit = "重慶市",
                SettlementCode = "EW2Z4523",
                EnterpriseName = "WZQ我在錢錢錢",
                ThirdPartyName = "第三方地中四年",
                SettlementMonth = DateTime.Now,
                SettlementAmountCapitalize = "壹拾貳萬叄仟壹佰貳拾叄元貳角伍分",

                


            };
            var result = await Task.Run(() => tradeSettleCommonService.ExportSettle(model));
            return File(result.Value, "application/octet-stream", result.Key);
        }