Column doesn't execute formula Excel/C#/EPPlus

Question

I have this excel sheet I produce right out of a C#.NET application with EPPlus and I have a formula that won't execute unless I click manually on the cell and press enter.

This is my current output (First value being F column and second being G) :

02:10:58    04:30:00    =F1/G1
01:50:52    04:06:00    =F2/G2

This is what it should look like:

02:10:58    04:30:00    0,485061728
01:50:52    04:06:00    0,450677507

The values on C# side are initialized as strings and that's probably what's causing the error :

string performance = "=F" + (list.Count + 2) + "/G" + (list.Count + 2)

Is there a workaround or another way to initialized my column in order to show directly the value to user?

Thanks!

EDIT

Code that creates the excel file

ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Informations_OF");
worksheet.Cells["A1"].LoadFromCollection(list.ToListExport(), true);
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

You are pasting the formula in as text or you are in 'Show Formulas' view mode. In the case of the former, set the column to General format and run a quick Text-to-Columns, Fixed width through it; for the latter, choose the correct view mode. — user4039065
I can confirm I am not in "show formulas" mode, and yes the problem is that I am pasting the formula in as text but I can't manage to make it calculate all at once — J. Lavoie
@PanagiotisKanavos Updated the question but yeah that's the problem i'm running in... I don't know how to tell EPPlus that this column is formula type! I'm only passing my item list and the formula in it is a string treated as text by excel — J. Lavoie

Pete Pete · Accepted Answer · 2017-06-21T13:38:05

It looks like you're probably setting the Value property of the cell in your C# code. Try setting the Formula property instead. Oh, and drop the leading "=" in your formula definition - the documentation for EPPlus states that you shouldn't put it in there.

Updated with code sample

var path = @"C:\Temp\EPPlus Demo.xlsx";

var fi = new FileInfo(path);
if (fi.Exists)
    fi.Delete();

using (var pck = new ExcelPackage())
{
    var wb = pck.Workbook;
    var ws = wb.Worksheets.Add("Demo");
    ws.Cells["A1"].LoadFromCollection(list.ToListExport(), true);
    for (var r = 2; r <= ws.Dimension.Rows; r++)
    {
        ws.Cells[r, 8].Formula = $"F{r}/G{r}";
        // Or alternatively, using R1C1 format
        ws.Cells[r, 8].FormulaR1C1 = "RC[-2]/RC[-1]";
    }
    pck.SaveAs(fi);
}

Column doesn't execute formula Excel/C#/EPPlus

1 Answers