EPPlus公式在不调用Calculate的情况下不起作用 [英] EPPlus formula not working without calling Calculate

问题描述

我尝试在excel中使用两个功能

那么，我在这里做什么错了?

解决方案

在公式中使用逗号代替分号:

  ws.Cells ["A4"].Formula ="INDEX($ C $ 1:$ D $ 24,4,2)";ws.Cells ["A6"].Formula ="INDEX(C1:D24,6,2)";ws.Cells ["B26"].Formula ="MATCH($ B $ 12，$ C $ 1:$ D $ 1,0)"; 

我猜想当您直接在Excel中输入分号时，MS会即时修复公式.实际的XML可能需要逗号，但是我不确定.

I've tried to use two functions in excel INDEX and MATCH and according to EPPlus codeplex, these should be supported. Everytime I open the file, there's a warning message saying that something is broken and asks if I want to repare the document, and when it opens, all formulas are gone. When repairing the document a dialog is shown saying that the formula has been removed from /xl/worksheets/sheet1.xml-del.

I can put the formulas manually into the spreedsheet and they work, but the only way to get any data to show in the generated xlsx file is to call Calculate() on the worksheet. This will output the data correctly, but what I need is to have the formulas inside the document.

Here's a small example of formulas I've tried

private byte[] TestingFormula()
{
    using (ExcelPackage pck = new ExcelPackage(_helper.GetTemplateStream("TEST.xlsx")))
    {
        var ws = pck.Workbook.Worksheets[1];

        ws.Cells["A4"].Formula = "INDEX($C$1:$D$24;4;2)";
        ws.Cells["A6"].Formula = "INDEX(C1:D24;6;2)";

        ws.Cells["B26"].Formula = "MATCH($B$12;$C$1:$D$1;0)";

        // sum works as expected
        //ws.Cells["D25"].Formula = "SUM(D2:D24)";

        //pck.Workbook.Calculate();
        //pck.Workbook.CalcMode = ExcelCalcMode.Automatic;
        return pck.GetAsByteArray();
    }
}

The TEST.xlsx is just a template that looks like this

So, what am I doing wrong here?

解决方案

Use commas instead of semicolons in your formulas:

ws.Cells["A4"].Formula = "INDEX($C$1:$D$24,4,2)";
ws.Cells["A6"].Formula = "INDEX(C1:D24,6,2)";

ws.Cells["B26"].Formula = "MATCH($B$12,$C$1:$D$1,0)";

I'm guessing when you enter semicolons directly in Excel, MS fixes the formula on the fly. The actual XML probably requires commas, but I'm not sure.

这篇关于EPPlus公式在不调用Calculate的情况下不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！