Excel table created by EPPlus doesn't propagate formula to new rows

Question

I'm using EPPlus 4.5.3.1 to create an Excel workbook. It contains a single worksheet with a single Excel table containing data.

The requirement is for users to be able to add new rows to the table (which in Excel is done either by right-clicking on a row and choosing Insert > New row, or hitting the tab key from the bottom-right cell of the table).

I can't seem to get my EPPlus-generated workbook to propagate a formula that I've got in a column, to new rows generated in the table in Excel. If I manually create an identical workbook, the formula is propagated.

I've tried assigning the formula to the column's range in a couple of ways, as per the below sample.

Here is a minimal example (console app). This creates the workbook. When opened in Excel, the formula in the rightmost column doesn't propagate to new rows when a user creates new rows in the table.

My question is, is this a limitation of EPPlus, or have I missed something that will exhibit the behaviour where formulas in a table column are propagated to new rows created in that table?

using System.Collections.Generic;
using System.IO;
using System.Linq;
using OfficeOpenXml;
using OfficeOpenXml.Table;

namespace EPPlusTests
{
    class Program
    {
        static void Main(string[] args)
        {
            List<ExampleData> exampleDatas = new List<ExampleData>();
            exampleDatas.Add(new ExampleData() { PersonName = "Tom", PersonAge = 44 });
            exampleDatas.Add(new ExampleData() { PersonName = "Richard", PersonAge = 37 });
            exampleDatas.Add(new ExampleData() { PersonName = "Harry", PersonAge = 41 });

            ExcelPackage package = new ExcelPackage();
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Testing");
            worksheet.Cells.LoadFromCollection(exampleDatas, true, TableStyles.Dark1);

            var col = worksheet.Cells.First(x => x.Value.ToString() == "TheFormula").Start.Column;
            //worksheet.Cells[2, col, worksheet.Dimension.End.Row, col].FormulaR1C1 = "2*RC[-1]"; // nope
            worksheet.Cells["C2:C4"].FormulaR1C1 = "2*RC[-1]"; // nope

            package.SaveAs(new FileInfo("C:\\Temp\\EPPlusTestFormula.xlsx"));
        }
    }

    class ExampleData
    {
        public string PersonName { get; set; }
        public int PersonAge { get; set; }
        public string TheFormula { get; set; }
        public ExampleData() { }
    }
}

It appears that what the EPPlus-generated document lacks is the <calculatedColumnFormula>...</calculatedColumnFormula> element in the package at {package}\xl\tables\{tableName}.xml — sasfrog

sasfrog sasfrog · Accepted Answer · 2019-05-01T03:29:55

OK it turned out to be very easy. The resolution is rather than addressing a range of cells, to instead address a column object within a table, and use the .CalculatedColumnFormula to assign a formula. I don't know if you can use R1C1 for this, you may need to use the table-specific formula syntax.

Here's what works for the above example:

ExcelTable table = worksheet.Tables[0];
table.Columns[2].CalculatedColumnFormula = "2*Table1[[#This Row],[PersonAge]]";

Probably some more concerted Googling would have got me there initially. I found the answer in the code here.

Excel table created by EPPlus doesn't propagate formula to new rows

1 Answers