C# 使用Aspose.Cells 导出Excel的步骤及问题记录-C#/.NET开发

Aspose.Cells是一款功能强大的Excel文档处理和转换控件，开发人员和客户电脑无需安装MicrosoftExcel也能在应用程序中实现类似Excel的强大数据管理功能，对C# 使用Aspose.Cells 导出Excel的步骤及问题记录感兴趣的朋友一起看看吧

今天在工作中碰到同事用了一种新型的方式导入excel，在此做个学习记录。

Aspose.Cells简介：Aspose.Cells是一款功能强大的Excel文档处理和转换控件，开发人员和客户电脑无需安装Microsoft Excel也能在应用程序中实现类似Excel的强大数据管理功能，支持所有Excel格式类型的操作，在没有Microsoft 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);
            #region 数据重构造
            // 工程形象进度
            #region 构造头部信息
            var topInfo = new Dictionary<string, object>
                { "Title", bigTitle },
                { "ProjectName", model.ProjectName },
                { "Code", barCodeStr }
            };
            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);
            #region 尾部
            #endregion 
            designer.Process();
            wk.CalculateFormula(); 
            #region 重新计算行高
            var startRow = 9;
            var endRow = startRow;
            #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());

此种当时的重点是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);
            #region 数据重构造
            // 工程形象进度
            #region 构造头部信息
            var topInfo = new Dictionary<string, object>
                { "Title", bigTitle },
                { "ProjectName", model.ProjectName },
                { "Code", barCodeStr }
            };
            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);
            #region 预算编号
            //var _dic = this.GetLaborProSettleInfo(exportDataSource);
            //foreach (var item in _dic)
            //{
            //    designer.SetDataSource(item.Key, item.Value);
            /

基础教程推荐