After adding rows to ExcelTable, the filled column formula loses the properties of the array formula

[minren118]

testExcelTable.xlsx

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

7.4

Spreadsheet application

Excel365

Description

I used the following code to add a new line after opening Excel, but the array formula lost its function and became a formula with an @ sign in front of it

            using (var package = new ExcelPackage("testExcelTable.xlsx"))
            {
                var worksheet = package.Workbook.Worksheets["Sheet1"];  
                var excelTable = worksheet.Tables[0];
                excelTable.AddRow(10);

                for (int i = 0; i < 10; i++)
                {
                    for (int j = 0; j < 5; j++)
                    {
                        worksheet.Cells[i + 2, j + 1].Value = i + j;
                    }

                }
                package.SaveAs("C:\\Users\\19026\\Desktop\\testResult.xlsx");
            }

it need to refill the value of the cell depending on whether the calculatedColumnFormula is an array, take the label of the array with it

[OssianEPPlus]

This is indeed a bug. Looks like Epplus does not extend the array formula correctly when the rows are added. Interestingly adding a row in excel also does not extend it correctly but in a different way (The braces are not added)

The '@' is likely added because it assumes a different kind of formula where the '@' is always the first token e.g. =@D2 We're working on a fix

[OssianEPPlus]

Epplus does not support array formulas in tables fully. We are considering adding it as a feature/bugfix but it requires considerable overhaul/overhead to ensure we do it right.

Meanwhile, as you've already discovered in #1635 there is a workaround. As long as your file already contains the CalculatedColumnFormula with array="1"

You can apply the workaround like this:

static void SetArrayFormulas(ExcelTable table, int columnNr)
{
    var formula = table.Columns[columnNr].CalculatedColumnFormula;
    var col = table.Range.TakeSingleColumn(columnNr).SkipRows(1);

    foreach (var row in col)
    {
        row.CreateArrayFormula(formula);
    }
}

using (var package = new ExcelPackage("testExcelTable.xlsx"))
{
        var worksheet = package.Workbook.Worksheets["Sheet1"];
        var excelTable = worksheet.Tables[0];
        excelTable.AddRow(10);

        for (int i = 0; i < 10; i++)
        {
            for (int j = 0; j < 5; j++)
            {
                worksheet.Cells[i + 2, j + 1].Value = i + j;
            }
        }

        SetArrayFormulas(excelTable, 6);
        SetArrayFormulas(excelTable, 7);

       package.SaveAs("C:\\Users\\19026\\Desktop\\testResult.xlsx");
}

[minren118]

Yes, I am also now manually adding all the cells in the current column myself, creating an array formula instead of the one EPPLUS automatically created for me. it's really a problem, the formula returns only one cell, how to tell if this formula is an array formula is a big problem.

However, I think there is one small improvement that can be made. If the file already marks the CalculatedColumnFormula definition as an array formula, consider using CreateArrayFormula to generate the formula content of the cell when EPPLUS's formula is filled. It's not a normal formula.

In my scenario, in fact, I created my template file in the Excel environment, and used the function of importing and exporting data on EPPLUS. When importing data, I adjusted the lines of ExcelTable according to the size of the data, and the formula automatically helped me fill it with the FillDown effect similar to VBA.

[OssianEPPlus]

This should be resolved as of 7.5 see #1649