C# 使用Aspose.Cells 导出Excel
2022/1/26 20:08:27
本文主要是介绍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 }View Code
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); }View Code
这篇关于C# 使用Aspose.Cells 导出Excel的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2022-03-01沐雪多租宝商城源码从.NetCore3.1升级到.Net6的步骤
- 2024-12-06使用Microsoft.Extensions.AI在.NET中生成嵌入向量
- 2024-11-18微软研究:RAG系统的四个层次提升理解与回答能力
- 2024-11-15C#中怎么从PEM格式的证书中提取公钥?-icode9专业技术文章分享
- 2024-11-14云架构设计——如何用diagrams.net绘制专业的AWS架构图?
- 2024-05-08首个适配Visual Studio平台的国产智能编程助手CodeGeeX正式上线!C#程序员必备效率神器!
- 2024-03-30C#设计模式之十六迭代器模式(Iterator Pattern)【行为型】
- 2024-03-29c# datetime tryparse
- 2024-02-21list find index c#
- 2024-01-24convert toint32 c#