Excel, EPPlus, Formulas not properly evaluated

Question

I am trying to insert a formula into an excel column using EPPlus and evaluate it in Excel. To be clear: no, I don't need the result at the runtime of the program.

This is my code:

 using (ExcelRange range = worksheet.Cells[1, 1, rowCounter - 1, worksheet.Dimension.End.Column])
 {
     ExcelTable table = worksheet.Tables.Add(range, $"someName");
     table.ShowHeader = true;

     // insert calculated column
     ExcelTableColumn column = table.Columns[0];
     column.Name = "Remaining Runtime";
     column.CalculatedColumnFormula = "=DAYS([DateOfRepayment],TODAY())";
 }

The range includes a column with the header DateOfRepayment, the formula works if I select a field and then deselect it or when I just select the formula in the cell in Excel and hit enter. Before these steps, I have an Invalid Name Error referencing the [DateOfRepayment]. However, using just =[DateOfRepayment] as the formula works fine.

How can I insert this formula in EPPlus so that it will work in Excel?

(I am using EPPlus version 4.5.1)

What is [DateOfRepayment]? Is it a named range or some column from the EPPlus? — Vityata
@Vityata DateOfRepayment is a column in table. It is created by just filling cells with values and after that creating the table as you see in the code. — Jerome Reinländer

Sanjay Sanjay · Accepted Answer · 2018-06-07T06:28:06

At the moment of this answer, the formula you are referring "=DAYS()" is not supported by EPPlus. I know you are not looking for the result at the runtime of the program. But you can either replace that formula by the one that is supported by EPPlus or generate result at runtime.

https://github.com/JanKallman/EPPlus/wiki/Supported-Functions

Excel, EPPlus, Formulas not properly evaluated

1 Answers