Formulas are not evaluated to Array Formulas appropriately

[OssianEPPlus]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.4

Spreadsheet application

Excel

Description

Our formula handling does not currently consider e.g. SUM(A2:B2 * 1) an array formula. Despite Excel considering it an array formula. This leads to various bugs and issues.

As an example, consider the following test case:

[TestMethod]
public void FormulaNotConsideredArrayFormula()
{
    using (var package = OpenPackage("FaultyNonArrayFormula.xlsx", true))
    {
        var wb = package.Workbook;
        var sheet = wb.Worksheets.Add("newWorksheet");

        sheet.Cells["D2"].Formula = "SUM(A2:B2 * 1)";

        SaveAndCleanup(package);
    }
}

This probably SHOULD result in something like:

But instead becomes: Note the @ sign.

And in XML. Faulty XML:

Correct XML:

Known Workarounds:

In most cases this can be avoided by specifying that the formula is an array formula in Epplus using the CreateArrayFormula method. So instead of: sheet.Cells["D2"].Formula = "SUM(A2:B2 * 1)"; You can do: sheet.Cells["D2"].CreateArrayFormula("SUM(A2:B2 * 1)", true);

However, it feels a bit clumsy/more difficult to use.

The bug with Tables

In addition this bug becomes especially bothersome when dealing with ExcelTables as Inserting a row does not extend the formulae correctly. Consider the following test:

[TestMethod]
public void DynamicArrayTable()
{
    using (var package = OpenPackage("DynamicArrayTable.xlsx", true))
    {
        var wb = package.Workbook;
        var sheet = wb.Worksheets.Add("newWorksheet");

        var excelTable = sheet.Tables.Add(sheet.Cells["A1:D2"], "TableTest");

        excelTable.Columns[3].CalculatedColumnFormula = "SUM((TableTest[[#This Row],[Column1]:[Column2]]<>0)*1)>0";
        excelTable.InsertRow(1);

        SaveAndCleanup(package);
    }
}

Which results in the following:

Epplus should be able to identify it as an array formula based only on the SUM(TableTest[[#This Row],[Column1]:[Column2]]<>0) part of the formula as it's just a slightly more complex version of the SUM(A2:B2 * 1) case,

In addition we do not support the array (or any other) attribute for CalculatedColumnFormula which is a bug in itself. See Table1.xml for example:

It does not seem possible for an end-user to create the attribute today other than to read in a table column that already has it set.

[OssianEPPlus]

This bug is what causes amongst others the issues of #1635 , #1638

[minren118]

Perhaps in EPPLUS, when reading Excel files, you need to pay more attention to whether there is a word in the cell to identify the array formula, if there is this identifier, Excel will not use the implicit conversion to a single cell operation when opened, add @ symbol.

Even if I use the copy method, EPPLUS has not taken into account the case of an array formula when dealing with a cell formula.

 var sht= package.Workbook.Worksheets["Sheet1"];
  sht.Cells["H5"].Copy(sht.Cells["H6"]);