Search code examples
c#excelexcel-formulaepplus

Excel removes formula added with EPPlus

I have a spreadsheet, created using the EPPlus library, with Unit (C), Qty (D), Rate (E) and Total (F) columns. The formula to determine the total for a row is a bit complex, as I am trying to avoid #Value errors for blank cells etc. Hence all the ISNUMBER function calls in the formula.

The formula is:

=SWITCH(C3; ISBLANK(D3); ""; "Percentage"; IF(ISNUMBER(D3); D3; 0) * IF(ISNUMBER(E3); E3; 0) / 100; IF(ISNUMBER(D3); D3; 0) * IF(ISNUMBER(E3); E3; 0))

When I paste this into the spreadsheet, in cell F3 (Total), and drag it to all other rows in the spreadsheet, it works fine, giving correct Totals on all rows. Yet when I try and add the formula while creating the spreadsheet, as follows:

for (var r = startAt + 2; r < endAt; r++)
{
    var amountFormula =
        $"=SWITCH(C{r}; ISBLANK(D{r}); \"\"; \"Percentage\"; IF(ISNUMBER(D{r}), D{r}, 0) * IF(ISNUMBER(E{r}), E{r}, 0) / 100; IF(ISNUMBER(D{r}), D{r}, 0) * IF(ISNUMBER(E{r}), E{r}, 0))";
    ws.Cells[$"F{r}"].Formula = amountFormula;
}

I do the loop for every set of rows where the formula is used, as it is not used in every row, e.g. headings, group totals etc.

When I try and open the spreadsheet in Excel, it says there is problem content that it will remove, and if I say yes, it removes any trace of the formula, and if I say no, it doesn't open the spreadhseet. I feel there is some sort of character encoding problem or something like that when I try and add the formula through code. How else will the formula work when pasted, yet fail when added via code?

Solution

  • I had to replace the semicolons with commas. I assume thats a region thing for you but EPPlus doesnt support them:

    https://github.com/JanKallman/EPPlus/wiki/Formula-Calculation

    Also had to specify that the SWITCH function was the native one in Excel. Otherwise, Excel will assume it is user defined and give a $NAME error in the cell:

    [TestMethod]
public void IsNumber_Formula_Test()
{
    //https://stackoverflow.com/questions/58482284/excel-removes-formula-added-with-epplus

    //Throw in some data
    var dataTable = new DataTable("tblData");
    dataTable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof(int)),
        new DataColumn("Col2", typeof(int)),
        new DataColumn("Col3", typeof(int))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = dataTable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i * 100;
        dataTable.Rows.Add(row);
    }

    var fi = new FileInfo(@"c:\temp\IsNumber_Formula_Test.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var package = new ExcelPackage(fi))
    {
        var workbook = package.Workbook;
        var ws = workbook.Worksheets.Add("Sheet1");
        var cells = ws.Cells;
        cells["C1"].LoadFromDataTable(dataTable, true);

        var startAt = 0;
        var endAt = dataTable.Rows.Count + 2;

        for (var r = startAt + 2; r < endAt; r++)
        {
            //Had to use commas and specify the function domain
            //var amountFormula = $"=SWITCH(C{r}; ISBLANK(D{r}); \"\"; \"Percentage\"; IF(ISNUMBER(D{r}), D{r}, 0) * IF(ISNUMBER(E{r}), E{r}, 0) / 100; IF(ISNUMBER(D{r}), D{r}, 0) * IF(ISNUMBER(E{r}), E{r}, 0))";
            var amountFormula = $"=_xlfn.SWITCH(C{r}, ISBLANK(D{r}), \"\", \"Percentage\", IF(ISNUMBER(D{r}), D{r}, 0) * IF(ISNUMBER(E{r}), E{r}, 0) / 100, IF(ISNUMBER(D{r}), D{r}, 0) * IF(ISNUMBER(E{r}), E{r}, 0))";
            ws.Cells[$"F{r}"].Formula = amountFormula;
        }

        package.Save();

    }
}

    Gives this:

    enter image description here