网站优化课程_汕头seo按天付费_百度手机助手下载安装最新版_广州网站建设方案优化

背景：需求需要读取数据导出成Excel文件，并且其中有一列需要赋值为公式，用于用户自己修改数据自动计算
导出Excel，我用到开源包MiniExcel
Gitee地址MiniExcel源码介绍，功能说明
Nuget安装 搜索MiniExcel

导出代码如下：
 

//多个Sheet
var sheets = new Dictionary<string, object>();
//保存文件位置
string filePath = Path.Combine(Directory.GetCurrentDirectory(), $"ExportTemplate\\{DateTime.Now.ToString("yyyyMMddHHmmss")}_StoreInventoryDt.xlsx");
{    
List<dynamic> value = new List<dynamic>();int index = 2;foreach (var itemDetail in storeInventoryExcelDto.ExportList){itemDetail.saleInventory = itemDetail.startInventory + itemDetail.barInventory + itemDetail.todayPickUp - itemDetail.saleCount;// 创建计算公式，例如：B2 + C2//string formula = $"={MiniExcel.GetColumns(columnIndex)}2 + {MiniExcel.GetColumns(columnIndex + 1)}2";value.Add(new{品名 = itemDetail.goodsName,品类 = itemDetail.goodsType,期初数 = itemDetail.startInventory,吧台入库 = itemDetail.barInventory,本日拾遗 = itemDetail.todayPickUp,售卖 = itemDetail.saleCount,置换 = itemDetail.changeCount,销售库存 = itemDetail.saleInventory,寄存 = itemDetail.inStorageCount,取出 = itemDetail.outStorageCount,总库存 = itemDetail.saleInventory + itemDetail.inStorageCount - itemDetail.outStorageCount,// itemDetail.totalInventory,本日实盘 = itemDetail.todayInventory,盘点差异 = itemDetail.inventoryDif,//$"=SUM(K{index},-L{index})", //备注 = "",});index++;}sheets.Add(storeInventoryExcelDto.InvDate.ToString("MM月dd日"), value.ToArray());
}
MiniExcel.SaveAs(filePath, sheets);

导出结果如下：

找了很久MiniExcel没有设置公式的方法，所以使用了ClosedXml
ClosedXml GitHub源码地址 ClosedXml源码和说明
说明文档-英文版

就是重新读取刚刚保存的excel文件，设置M列=K列-L列，M=K-L
代码如下：

//读取当前excel文件的sheet 
var sheetNames = MiniExcel.GetSheetNames(filePath);// 打开现有的Excel文件using (var workbook = new XLWorkbook(filePath)){foreach (var sheet in sheetNames){// 获取工作表var worksheet = workbook.Worksheet(sheet);//获取数据行数int rowCount = worksheet.RowsUsed().Count();for (int i = 2; i <= rowCount; i++){// 设置特定单元格的公式worksheet.Cell($"M{i}").FormulaA1 = $"=K{i}-L{i}"; // 使用K列引用样式设置公式// 或者使用行和列的索引//worksheet.Cell(1, 1).FormulaA1 = "=B1*C1"; // 第1行，第1列的单元格}// 保存更改workbook.Save();}}

就完成excel文件中指定某列的计算公式

整体代码：

    var sheets = new Dictionary<string, object>();
string filePath = Path.Combine(Directory.GetCurrentDirectory(), $"ExportTemplate\\{DateTime.Now.ToString("yyyyMMddHHmmss")}_StoreInventoryDt.xlsx");List<dynamic> value = new List<dynamic>();//storeInventoryExcelDto数据源foreach (var itemDetail in storeInventoryExcelDto.ExportList){itemDetail.saleInventory = itemDetail.startInventory + itemDetail.barInventory + itemDetail.todayPickUp - itemDetail.saleCount;// 创建计算公式，例如：B2 + C2//string formula = $"={MiniExcel.GetColumns(columnIndex)}2 + {MiniExcel.GetColumns(columnIndex + 1)}2";value.Add(new{品名 = itemDetail.goodsName,品类 = itemDetail.goodsType,期初数 = itemDetail.startInventory,吧台入库 = itemDetail.barInventory,本日拾遗 = itemDetail.todayPickUp,售卖 = itemDetail.saleCount,置换 = itemDetail.changeCount,销售库存 = itemDetail.saleInventory,寄存 = itemDetail.inStorageCount,取出 = itemDetail.outStorageCount,总库存 = itemDetail.saleInventory + itemDetail.inStorageCount - itemDetail.outStorageCount,// itemDetail.totalInventory,本日实盘 = itemDetail.todayInventory,盘点差异 = itemDetail.inventoryDif,//$"=SUM(K{index},-L{index})", //备注 = "",});}sheets.Add(storeInventoryExcelDto.InvDate.ToString("MM月dd日"), value.ToArray());MiniExcel.SaveAs(filePath, sheets);
var sheetNames = MiniExcel.GetSheetNames(filePath);
// 打开现有的Excel文件
using (var workbook = new XLWorkbook(filePath))
{foreach (var sheet in sheetNames){// 获取工作表var worksheet = workbook.Worksheet(sheet);int rowCount = worksheet.RowsUsed().Count();for (int i = 2; i <= rowCount; i++){// 设置特定单元格的公式worksheet.Cell($"M{i}").FormulaA1 = $"=K{i}-L{i}"; // 使用A1引用样式设置公式// 或者使用行和列的索引//worksheet.Cell(1, 1).FormulaA1 = "=B1*C1"; // 第1行，第1列的单元格}// 保存更改workbook.Save();}
}