C# NOPI导出Excel 设置计算公式，多行表头

说明

现需要导出Excel，统计每个人的工作量，计算方式为：项目分值*项目数量

效果

c#代码

//创建工作簿
MemoryStream MStream = new MemoryStream();
 XSSFWorkbook WBook = new XSSFWorkbook();
//创建sheet
ISheet sheet = WBook.CreateSheet(DateTime.Now.ToString("yyyy-MM"));
//创建行 --表头
IRow headItemRow = sheet.CreateRow(0);
IRow headValueRow = sheet.CreateRow(1);

//定义2个数组，存放分值和数量的坐标,用于excel公式计算 C2*C3+D2*D3+E2*E3
ArrayList itemXB = new ArrayList(); //存放项目数量的坐标  C2 D2 E2 F2 G2....
ArrayList valueXB = new ArrayList(); //存放项目分值的坐标 C3 D3 E3 F3 G3....
// 测试项目用固定的数据 后续改为动态获取
string[] headerItem = new string[] { "日期","姓名","CCCA", "PH值", "检测", "百度", "含量", "电导", "含体", "可含量", "耐化指数（C）", "耐电数（I）", "电化数（P）", "耐试验", "有机剂", "黏度", "熔点", "粘度","总计","备注" }; 
// 创建第一行的单元格，并赋值
for (int i = 0; i < headerItem.Length; i++)
{
    headItemRow.CreateCell(i).SetCellValue(headerItem[i]);               
}

// 测试分值用固定的数据 后续改为动态获取
string[] headerValue = new string[] { DateTime.Now.Year+"年", "分值", "5", "", "", "10", "10", "", "3", "", "", "", "", "", "", "", "8", "30","","" };
// 创建第二行的单元格，并赋值
for (int i = 0; i < headerValue.Length; i++)
{
   headValueRow.CreateCell(i).SetCellValue(headerValue[i]); 
        // 获取每一个分值的坐标，存入之前的数组中         
        if (i >= 2 && i< headerValue.Length -2)
        {
           var cr = new CellReference(1, i);
           var xb = cr.CellRefParts[2] + cr.CellRefParts[1];
           valueXB.Add(xb);
         }               
}

// 创建第三行数据 后续改为动态获取
IRow testValueRow = sheet.CreateRow(2);
string[] testValue = new string[] {"2023-04-12", "张三", "1", "2", "3", "4", "5", "", "3", "", "8", "10", "20", "", "", "", "5", "20", "", "" };
// 创建第二行的单元格，并赋值           
for (int i = 0; i < testValue.Length; i++)
{
    testValueRow.CreateCell(i).SetCellValue(testValue[i]);
    // 获取每一个分值的坐标，存入之前的数组中   
     if (i >= 2 && i < testValue.Length - 2)
     {
         var cr = new CellReference(2, i);
         var xb = cr.CellRefParts[2] + cr.CellRefParts[1];
         itemXB.Add(xb);
      }
}
//Excel计算公式 Sumcell是存放计算公式的单元格 
var formulaString = "";
ICell Sumcell = testValueRow.GetCell(headerItem.Length-2);
for (int i = 0; i < valueXB.Count; i++)
{
    //拼接公式格式 C2*C3+D2*D3+E2*E3.....
    formulaString += valueXB[i] + "*" +itemXB[i]+"+";
}
formulaString=formulaString.Substring(0, formulaString.Length - 1);
//为单元格添加计算公式
Sumcell.SetCellFormula(formulaString);

//Excel默认高度
sheet.DefaultRowHeight = 30 * 20;
// 设置单元格 字体 样式
ICellStyle style = WBook.CreateCellStyle();
style.WrapText = true;           
IFont font = WBook.CreateFont();
font.FontName = "等线";
font.FontHeightInPoints = 11;
font.IsBold = true;
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);

for (int i = 0; i < headerItem.Length; i++)
{
     sheet.AutoSizeColumn(i, true); // 自动换行
     headItemRow.GetCell(i).CellStyle = style;
     headValueRow.GetCell(i).CellStyle = style;
}

// sheet.ProtectSheet("password"); //密码
WBook.Write(MStream);
byte[] FBytes = MStream.ToArray();
HttpContext.Response.Headers.Add("Content-disposition", "attachment");
HttpContext.Response.Headers.Add("filename", $"{ HttpUtility.UrlEncode($"工作记录表.xlsx") }");
HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Response.BinaryWrite(FBytes);
HttpContext.Response.Flush();
HttpContext.Response.Close();