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);
//}
#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);
}
#region 尾部
#region 删除模板sheet
//wk.Worksheets.RemoveAt(wk.Worksheets["表2"].Index);
//wk.Worksheets.RemoveAt(wk.Worksheets["表3"].Index);
designer.Process();
wk.CalculateFormula();
#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;
#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());
}
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);
}
到此这篇关于C# 使用Aspose.Cells 导出Excel的文章就介绍到这了,更多相关C# 导出Excel内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
相关推荐
-
C#使用Aspose.Cells导出excel
C# winform导出excel可以使用 Microsoft.Office.Interop.Excel.dll或者Aspose.Cells.dll以及其他方法.Microsoft.Office.Interop.Excel.dll导出速度慢,不适用于数据量大情况.Aspose.Cells.dll到处速度很快.由于Aspose.Cells.dll本身收费,所以需要加载破解证书. Aspose.Cells简介:Aspose.Cells是一款功能强大的Excel文档处理和转换控件,开发人员和客户电脑无
-
C#语言MVC框架Aspose.Cells控件导出Excel表数据
本文实例为大家分享了Aspose.Cells控件导出Excel表数据的具体代码,供大家参考,具体内容如下 控件bin文件下载地址 @{ ViewBag.Title = "xx"; } <script type="text/javascript" language="javascript"> function getparam() { var param = {}; param.sear = $("#sear").t
-
C# 使用Aspose.Cells 导出Excel的步骤及问题记录
今天在工作中碰到同事用了一种新型的方式导入excel,在此做个学习记录. Aspose.Cells简介:Aspose.Cells是一款功能强大的Excel文档处理和转换控件,开发人员和客户电脑无需安装Microsoft Excel也能在应用程序中实现类似Excel的强大数据管理功能,支持所有Excel格式类型的操作,在没有Microsoft Excel的环境下,用户也可为其应用程序嵌入类似Excel的强大数据管理功能. 插件:Aspose.Cells 第一步:准备好导出的模板,例子: C#代码:
-
利用Aspose.Cells和Excel模板导出统计数据
在项目中要进行导出Excel报表给客户,里面有统计,就心思用Aspose.Cells 配合实体类数据直接导出.结果呢比较麻烦.然后就检索找到利用Excel模板配合实体类数据导出. 总的来说一般的报表生成,基本上是基于以下几种方式:一种是基于微软Excel内置的引擎来实现:一种是构造HTML格式的Excle报表:一种是基于控件的方式来处理,基于控件有很多种方式,个人认为比较有名的是Aspose.Cell和NPOI,下面用到的是Aspose.Cell 效果图: Excel模板及相关变量 报表1模板如
-
Aspose.Cells组件导出excel文件
本文实例为大家分享了Aspose.Cells导出excel文件的方法,供大家参考,具体内容如下 /// <summary> /// 导出数据到本地 /// </summary> /// <param name="dt">要导出的数据</param> /// <param name="tableName">导出名称</param> /// <param name="tableTit
-
C#使用Aspose.Cells控件读取Excel
Aspose是一个很强大的控件,可以用来操作word,excel,ppt等文件,用这个控件来导入.导出数据非常方便.其中Aspose.Cells就是用来操作Excel的,功能有很多.我所用的是最基本的功能,读取Excel的数据并导入到Dataset或数据库中.读取Excel表格数据的代码如下: 首先要引入命名空间:using Aspose.Cells; 复制代码 代码如下: Workbook workbook = new Workbook(); workbook.Open("C:\\test.x
-
使用Aspose.Cells实现导入导出
本文实例为大家分享了Aspose.Cells实现导入导出的具体代码,供大家参考,具体内容如下 这是自己整理的导入导出类,里面有注释. using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using Aspose.Cells; namespace L
-
利用Aspose.Cells实现万能导出功能
最近做了个项目,客户对导出excel功能情有独钟,几乎要求每一个列表数据都支持导出excel功能,为了避免代码重复,万能粉嫩的小码农开发了万能导出QAQ. 导出Excel无非就是取出数据,然后利用Aspose.Cells插件填充到Excel文件中,DataTable类型的数据是最适合填充Excel不过了.唯一的问题就是DataTable数据的列头一般是英文,突然就想出了利用SQL Server每一列的说明来替换掉英文列头的方法,我太TM机智了. /// <summary> /// 导出 ///
-
Aspose.Cells 读取受保护有密码的Excel文件
最近遇到一个需求,要能够读取受密码保护的Excel内容,之前都是直接读取Excel中的数据,不需要做任何其他的处理. 当Excel双击的时候,需要输入密码,在使用Aspose.Cells 组件读取的时候就会报错 Workbook book = new Workbook(fullFilename, new LoadOptions() { Password="111111" }); Worksheet sheet = book.Worksheets[0]; Cells cells = sh
-
使用Aspose.Cells组件生成Excel文件实例
生成带表头的Excel文件,格式如下显示. 当然更复杂的一些也可以通过 合并单元格的方法 public void Merge(int firstRow, int firstColumn, int totalRows, int totalColumns)来实现. 实现方式: 1. 首先,需要添加对"Aspose.Cells.dll"的引用. 2. 实现代码如下: 复制代码 代码如下: //新建工作簿 Workbook workbook = new Workbook