C#?使用Aspose.Cells?導(dǎo)出Excel的步驟及問題記錄
更新時間：2022年01月27日 08:38:26 作者：我本梁人
Aspose.Cells是一款功能強(qiáng)大的Excel文檔處理和轉(zhuǎn)換控件，開發(fā)人員和客戶電腦無需安裝Microsoft Excel也能在應(yīng)用程序中實(shí)現(xiàn)類似Excel的強(qiáng)大數(shù)據(jù)管理功能，對C#?使用Aspose.Cells?導(dǎo)出Excel的步驟及問題記錄感興趣的朋友一起看看吧
今天在工作中碰到同事用了一種新型的方式導(dǎo)入excel，在此做個學(xué)習(xí)記錄。
Aspose.Cells簡介：Aspose.Cells是一款功能強(qiáng)大的Excel文檔處理和轉(zhuǎn)換控件，開發(fā)人員和客戶電腦無需安裝Microsoft Excel也能在應(yīng)用程序中實(shí)現(xiàn)類似Excel的強(qiáng)大數(shù)據(jù)管理功能，支持所有Excel格式類型的操作，在沒有Microsoft Excel的環(huán)境下，用戶也可為其應(yīng)用程序嵌入類似Excel的強(qiáng)大數(shù)據(jù)管理功能。
插件：Aspose.Cells
第一步:準(zhǔn)備好導(dǎo)出的模板，例子：
C#代碼：
#region 驗(yàn)證數(shù)據(jù)
if (model == null)
{
throw new FriendlyException("無該月結(jié)單！");
}
var templatePath = @"Template\trade\Cecsc2bHN\分包分供月度結(jié)算審批表.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 數(shù)據(jù)重構(gòu)造
// 工程形象進(jìn)度
#region 構(gòu)造頭部信息
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,
//形象進(jìn)度描述
List<TradeSettleReportProjectProgressItemModel> ProjectProgressList = new List<TradeSettleReportProjectProgressItemModel>();
for (int i = 0; i < 3; i++)
TradeSettleReportProjectProgressItemModel Progressmodel = new TradeSettleReportProjectProgressItemModel()
{
ConstractArea = "測試水水水水水水" + i,
Remark = "測試模擬的備注" + i,
ProgressDesc = "測試撒子怕發(fā)送所屬" + i,
ConstractSite = "場地" + i
};
ProjectProgressList.Add(Progressmodel);
#region 工程名稱
//綁定數(shù)據(jù)到excel中
//designer.SetDataSource("綁定的到excle的變量名"，"對應(yīng)的值")
designer.SetDataSource("ProgressItem", ProjectProgressList); //list可以循環(huán)綁定 綁定的例子在下面。
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 重新計(jì)算行高
var startRow = 9;
var endRow = startRow;
#region 導(dǎo)出文件
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());此種當(dāng)時的重點(diǎn)是Excel模板的數(shù)據(jù)綁定：
第一種：單個值的綁定
第二種：列表值的循環(huán)綁定
完整代碼
Service層：
public KeyValuePair<string, byte[]> ExportSettle(TradeSettleReportModel model)
{
#region 驗(yàn)證數(shù)據(jù)
if (model == null)
{
throw new FriendlyException("無該月結(jié)單！");
}
var templatePath = @"Template\trade\Cecsc2bHN\分包分供月度結(jié)算審批表.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 數(shù)據(jù)重構(gòu)造
// 工程形象進(jìn)度
#region 構(gòu)造頭部信息
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);
//形象進(jìn)度描述
List<TradeSettleReportProjectProgressItemModel> ProjectProgressList = new List<TradeSettleReportProjectProgressItemModel>();
for (int i = 0; i < 3; i++)
TradeSettleReportProjectProgressItemModel Progressmodel = new TradeSettleReportProjectProgressItemModel()
ConstractArea = "測試水水水水水水" + i,
Remark = "測試模擬的備注" + i,
ProgressDesc = "測試撒子怕發(fā)送所屬" + 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 預(yù)算編號
//var _dic = this.GetLaborProSettleInfo(exportDataSource);
//foreach (var item in _dic)
//{
// designer.SetDataSource(item.Key, item.Value);
//}
#region 寫入數(shù)據(jù)
//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 重新計(jì)算行高
var startRow = 9;
var endRow = startRow;
#region 導(dǎo)出文件
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);
}到此這篇關(guān)于C# 使用Aspose.Cells 導(dǎo)出Excel的文章就介紹到這了,更多相關(guān)C# 導(dǎo)出Excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
ASP.NET連接數(shù)據(jù)庫的技術(shù)叫ADO.NET，它是用來向數(shù)據(jù)庫提交sql語句的一堆類。這里連接的是Sql Server 2008數(shù)據(jù)庫，其他數(shù)據(jù)庫用法差不多，就是調(diào)用的類名不一樣2015-11-11
C#并發(fā)實(shí)戰(zhàn)記錄之Parallel.ForEach使用
這篇文章主要給大家介紹了關(guān)于C#并發(fā)實(shí)戰(zhàn)記錄之Parallel.ForEach使用的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用C#具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-08-08
這篇文章主要給大家介紹了關(guān)于C# using語法糖的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-01-01
這篇文章主要介紹了C#與PLC通訊的實(shí)現(xiàn)代碼，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
這篇文章主要介紹了WPF中對象資源的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用WPF，感興趣的朋友可以了解下2021-04-04
這篇文章主要介紹了C#實(shí)現(xiàn)一個簡單的定時小程序代碼，實(shí)現(xiàn)過程很簡單，需要的朋友可以參考下2015-09-09
本篇文章是對c#中滾動字幕動畫窗體的實(shí)現(xiàn)方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
基于C#實(shí)現(xiàn)WinForm開發(fā)操作系統(tǒng)的文件管理系統(tǒng)代碼
基于C#的WinForm應(yīng)用程序來模擬操作系統(tǒng)文件管理系統(tǒng),可以幫助用戶在Windows環(huán)境下進(jìn)行文件的創(chuàng)建、移動、刪除與搜索等操作,這種模擬工具有助于學(xué)習(xí)文件系統(tǒng)的工作原理以及測試和開發(fā)其他軟件項(xiàng)目2024-12-12
在C#中,接口（interface）是一種定義類必須實(shí)現(xiàn)的方法和屬性的抽象類型,除了方法和屬性,接口還可以包含事件,實(shí)現(xiàn)接口事件可以幫助我們設(shè)計(jì)更加靈活和解耦的系統(tǒng),本文將詳細(xì)探討如何在C#中實(shí)現(xiàn)接口事件,并通過具體示例說明其應(yīng)用,需要的朋友可以參考下2024-08-08
講解C#設(shè)計(jì)模式編程中享元模式的運(yùn)用
這篇文章主要介紹了C#設(shè)計(jì)模式編程中享元模式的運(yùn)用,享元模式主張限制對象的數(shù)量來優(yōu)化內(nèi)存使用,需要的朋友可以參考下2016-02-02