C# EPPlus not evaluating formula SUM(A3:B3)

Question

I have just started working with EPPlus. i have data table which has some numeric data and formula. when i load data table by EPPlus and save to excel then formula not evaluated i found when i open the same excel file. formula lies in excel cell as string like SUM(A3:B3)

To evaluate formulate i have tried many options of EPPLUS and those are listed here

pack.Workbook.Worksheets["Test"].Calculate();
pack.Workbook.Worksheets["Test"].Cells["A3"].Calculate();
pack.Workbook.Worksheets["Test"].Cells["B3"].Calculate();
ws.Calculate();

Here i am referring my full sample code where formula not working. please have a look and tell me what i need to add in my code to evaluate formula.

private void button1_Click(object sender, EventArgs e)
{
    DataTable dt = GetDataTable();
    string path = @"d:\EPPLUS_DT_Excel.xlsx";
    Stream stream = File.Create(path);

    using (ExcelPackage pack = new ExcelPackage())
    {
        ExcelWorksheet ws = pack.Workbook.Worksheets.Add("Test");
        ws.Cells["A1"].LoadFromDataTable(dt, false);
        //pack.Workbook.Worksheets["Test"].Calculate();
        //pack.Workbook.Worksheets["Test"].Cells["A3"].Calculate();
        //pack.Workbook.Worksheets["Test"].Cells["B3"].Calculate();
        ws.Calculate();
        pack.SaveAs(stream);
        stream.Close();
        MessageBox.Show("Done");
    }
}

public DataTable GetDataTable()
{
    string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";

    int startsum = 0;
    int currow = 0;
    bool firstTimeSum = true;

    int NumRows = 3;
    int NumColumns = 2;

    DataTable dt = new DataTable();

    for (int col = 0; col < NumColumns; col++)
    {
        strColName = GenerateColumnText(col);
        DataColumn datacol = new DataColumn(strColName, typeof(object));
        dt.Columns.Add(datacol);
    }


    for (int row = 0; row < NumRows; row++)
    {
        dt.Rows.Add();

        for (int col = 0; col < NumColumns; col++)
        {
            if (row < 2)
            {
                dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));
            }
            else
            {
                if (firstTimeSum)
                {
                    if (row - currow == 2)
                    {
                        currow = row;
                        startsum = 0;
                        firstTimeSum = false;
                    }
                    else
                    {
                        startsum = 1;
                    }
                }
                else
                {
                    if (row - currow == 3)
                    {
                        currow = row;
                        startsum = 0;
                    }
                }


                if (startsum == 0)
                {
                    strColName = GenerateColumnText(col);
                    strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
                    strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
                    strSum = string.Format("+SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
                    dt.Rows[row][col] = strSum;
                }
                else
                {
                    dt.Rows[row][col] = Convert.ToInt32(new Random().Next(1, NumRows));
                }
            }

        }

        startsum = 1;
    }
    return dt;
}

private string GenerateColumnText(int num)
{
    string str = "";
    char achar;
    int mod;
    while (true)
    {
        mod = (num % 26) + 65;
        num = (int)(num / 26);
        achar = (char)mod;
        str = achar + str;
        if (num > 0) num--;
        else if (num == 0) break;
    }
    return str;
}

if you see "SUM(A3:B3)" in a cell you are either missing the "=" sign or the cell is formatted as Text. Check your cell formatting. — Sorceri, Dec 31 '18 at 18:55
it is formula convention for EPPlus that we should not provide = sign. that is the reason i remove = sign from sum. see this https://stackoverflow.com/a/45138440/10839668 — T_Zacks, Dec 31 '18 at 19:26
The basic how-to page [is here](https://github.com/JanKallman/EPPlus/wiki/Formula-Calculation). So don't use "+SUM()", just "SUM". — Hans Passant, Dec 31 '18 at 22:17

score 2 · Answer 1 · answered Dec 31 '18 at 20:27

When adding a formula to a cell you use the Formula property. When you load a range from a DataTable using LoadFromDataTable it has no way of knowing that some of the values are meant to be interpreted as formulas.

You can use LoadDataTable to populate the cells on which the formulas will operate, but for the formulas you'll need to set the Formula property.

C# EPPlus not evaluating formula SUM(A3:B3)

1 Answers1