CODEWITHSUNDEEP
c#excelepplus
J. Lavoie
J. Lavoie

Reputation: 334

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

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();

Upvotes: 0

Views: 1819

Answers (1)

Pete
Pete

Reputation: 1867

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

Upvotes: 2

Related Questions