C#导出Excel使Aspose.Cells

 今天在工作中碰到同事用了一种新型的方式导入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


          /*

　　　　　　//修改单元格样式

          //橘色背景单元格样式
          Style orangeStyle = wb.CreateStyle(); ;
          orangeStyle.Pattern = BackgroundType.Solid;
          orangeStyle.ForegroundColor = Color.Orange;
          orangeStyle.Font.Color = Color.Black;
          orangeStyle.Font.Size = 10;
          orangeStyle.IsTextWrapped = true;//单元格内容自动换行
          orangeStyle.VerticalAlignment = TextAlignmentType.Center;//文字居中
          orangeStyle.Font.Name = "宋体";//文字字体
          orangeStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
          orangeStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
          orangeStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
          orangeStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
          foreach (var item in orangeBackgroundList)
          {
          Aspose.Cells.Range orangeW = wb.Worksheets[0].Cells.CreateRange(item.ID, item.Count, 1, 1);
          orangeW.ApplyStyle(orangeStyle, new StyleFlag() { All = true });
          }

          //隐藏列
          Cells cells = wb.Worksheets[0].Cells;
          cells.HideColumns(7, 3);

           */


         
            #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);
        }

[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);
}

 

标签: Aspose 导出Excle,  Excel