使用EPPlus 4.0生成Excel时Total_Monthly_Amount列值为0的问题

我看到你在用EPPlus 4.0生成Excel时，遇到了Total_Monthly_Amount列公式显示0但预期是分组汇总值的问题。咱们来一步步排查并修复这个问题：

问题根源分析 #

从你的代码和描述来看，主要有几个关键点导致公式无法正确计算：

• 单元格引用逻辑错误：cntrSameEmployeeId的计数逻辑混乱，导致生成的currentCell和previousCell引用了错误的行，公式自然无法匹配到正确的员工ID进行汇总。
• 文本类型ID的公式处理缺失：如果你的EmployeeId是文本类型，Excel公式里直接写B2会被当成数值比较，需要用引号包裹才能正确匹配文本。
• 计算时机和模式的细节：虽然设置了Automatic计算模式，但EPPlus有时需要手动触发完整计算，且要确保在所有单元格内容设置完成后执行。

修复后的代码 #

下面是调整后的代码，我简化了分组逻辑，修正了公式生成和计数问题：

using (ExcelPackage excel = new ExcelPackage()) 
{
    ExcelWorksheet workSheet = excel.Workbook.Worksheets.Add("Sheet1");
    excel.Workbook.CalcMode = ExcelCalcMode.Automatic;
    workSheet.TabColor = System.Drawing.Color.Black;
    workSheet.DefaultRowHeight = 12;
    
    // 设置表头样式
    workSheet.Row(1).Height = 20;
    workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    workSheet.Row(1).Style.Font.Bold = true;
    
    DataTable dt = ds.Tables[0];
    dt.Columns.RemoveAt(0); // 移除第一列
    
    // 写入原有列表头
    for (int i = 1; i <= dt.Columns.Count; i++) 
    {
        workSheet.Cells[1, i].Value = dt.Columns[i - 1].ColumnName;
    }
    
    // 添加汇总列表头
    int totalColumnIndex = dt.Columns.Count + 1;
    workSheet.Cells[1, totalColumnIndex].Value = "Total_Monthly_Amount";
    
    // 跟踪当前员工ID，用于分组判断
    string currentEmpId = null;
    
    // 写入数据行并设置汇总公式
    for (int j = 0; j < dt.Rows.Count; j++) 
    {
        int currentRow = j + 2; // 数据从第2行开始（表头是第1行）
        string empId = Convert.ToString(dt.Rows[j]["EmployeeId"]); // 这里请根据实际列名调整
        
        // 写入当前行数据
        for (int k = 0; k < dt.Columns.Count; k++) 
        {
            workSheet.Cells[currentRow, k + 1].Value = dt.Rows[j].ItemArray[k];
        }
        
        // 仅当员工ID变化时设置汇总公式
        if (!empId.Equals(currentEmpId)) 
        {
            // 修正公式：给文本类型的ID添加引号，确保Excel正确识别
            string formula = $"IF(B{currentRow}=B{currentRow - 1},\"\",SUMIF(B:B,\"{empId}\",I:I))";
            workSheet.Cells[currentRow, totalColumnIndex].Formula = formula;
            
            // 更新当前员工ID
            currentEmpId = empId;
        }
        else 
        {
            // 同一员工的其他行留空
            workSheet.Cells[currentRow, totalColumnIndex].Value = "";
        }
    }
    
    // 自动适配列宽
    workSheet.Cells.AutoFitColumns();
    
    // 所有内容设置完成后，触发Excel计算
    excel.Workbook.Calculate();
    
    // 保存文件
    FileInfo fi = new FileInfo(path1 + @"\FormulaExample.xlsx");
    excel.SaveAs(fi);
}

关键改动说明 #

• 简化分组逻辑：直接跟踪当前员工ID，只有当ID变化时才设置汇总公式，避免了复杂且易出错的计数变量。
• 修正公式中的文本引用：用\"{empId}\"把员工ID包裹起来，确保Excel将其作为文本进行匹配，解决了文本类型ID无法正确汇总的问题。
• 调整计算时机：excel.Workbook.Calculate()放在所有数据和公式设置完成后执行，确保所有公式都能被正确计算。
• 明确列引用：假设EmployeeId在B列，Amount在I列，你可以根据实际的列位置调整公式中的列标识。

这样修改后，生成的Excel文件中Total_Monthly_Amount列应该会正确显示每个员工的汇总金额，而不是0了。

内容的提问来源于stack exchange，提问作者Vaibhav Deshmukh