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

  • 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;
    } 


    Monday, December 31, 2018 6:16 PM

Answers

  • Hi Sudip_inn,

    Thank you for posting here.

    If you want to use Sum in EPPlus, you could try the code below.

     
    FileInfo existingFile = new FileInfo("1.xlsx");
                using (var package = new ExcelPackage(existingFile))
                {
                    ExcelWorkbook workBook = package.Workbook;
                    var currentWorksheet = workBook.Worksheets.First();
                    currentWorksheet.Workbook.CalcMode = ExcelCalcMode.Automatic;
                    currentWorksheet.Cells["C3"].Formula = "=SUM(C1:C2)";
                    currentWorksheet.Cells["C1"].Value = 10;
                    currentWorksheet.Cells["C2"].Value = 30;
                    package.Save();
    
    
                }

    My original excel file.

    My Result.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, January 2, 2019 6:07 AM
    • Marked as answer by Sudip_inn Tuesday, January 8, 2019 2:15 PM
    Tuesday, January 1, 2019 6:00 AM
    Moderator

All replies

  • Have you tried attaching a formula logger to see what's happening?

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

    var excelFile = new FileInfo(@"c:\myExcelFile.xlsx");
    using (var package = new ExcelPackage(excelFile))
     {
             // Output from the logger will be written to the following file
             var logfile = new FileInfo(@"c:\logfile.txt");
             // Attach the logger before the calculation is performed.
             package.Workbook.FormulaParserManager.AttachLogger(logfile);
             // Calculate - can also be executed on sheet- or range level.
             package.Workbook.Calculate();
             // The following method removes any logger attached to the workbook.
             package.Workbook.FormulaParserManager.DetachLogger();
    }


    var blog = "jessehouwing.net";

    Monday, December 31, 2018 7:21 PM
  • After loading data from DataTable, have you tried something like this: ws.Cells[“A3”].FormulaR1C1 = “=SUM(A1,A2)”; ws.Calculate()?

    Monday, December 31, 2018 8:02 PM
  • Hi Sudip_inn,

    Thank you for posting here.

    If you want to use Sum in EPPlus, you could try the code below.

     
    FileInfo existingFile = new FileInfo("1.xlsx");
                using (var package = new ExcelPackage(existingFile))
                {
                    ExcelWorkbook workBook = package.Workbook;
                    var currentWorksheet = workBook.Worksheets.First();
                    currentWorksheet.Workbook.CalcMode = ExcelCalcMode.Automatic;
                    currentWorksheet.Cells["C3"].Formula = "=SUM(C1:C2)";
                    currentWorksheet.Cells["C1"].Value = 10;
                    currentWorksheet.Cells["C2"].Value = 30;
                    package.Save();
    
    
                }

    My original excel file.

    My Result.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, January 2, 2019 6:07 AM
    • Marked as answer by Sudip_inn Tuesday, January 8, 2019 2:15 PM
    Tuesday, January 1, 2019 6:00 AM
    Moderator
  • Hi Sudip_inn,

    Is there any update? do you resolve the issue? if the issue still exists, please feel free let us know.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 8, 2019 2:41 AM
    Moderator
  • i resolve it. in execel we write formula like =SUM(A1:A2) but in case of EPPlus we can not use = sign before formula. my issue resolved. thanks for help.
    Tuesday, January 8, 2019 2:16 PM