defined name error cases

[hershaw]

EPPlus usage

Commercial use (I have a commercial license)

Environment

macOS 14.5 (Sonoma) on Apple M3 Pro, .NET 8.0 console app (dotnet run)

Epplus version

8.4.0

Spreadsheet application

excel

Description

Reproduce with the following:

using System;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.FormulaParsing;

ExcelPackage.License.SetNonCommercialPersonal("epplus-nullref-repro");

var cases = new (string Name, string Description, Action<ExcelWorkbook, ExcelWorksheet> Setup)[]
{
    (
        "SciArrayFormula",
        "Inline array literal containing scientific-notation constants stored in Formula",
        (workbook, sheet) =>
        {
            workbook.Names.AddFormula(
                "SciArrayFormula",
                "{4.02506300418233E-305,3.33761291040418E-308}");
            sheet.Cells["B1"].Formula = "SciArrayFormula";
        }
    ),
    (
        "UndefinedUdfName",
        "Workbook-level name that references an undefined UDF (Main.SAPF4Help)",
        (workbook, sheet) =>
        {
            workbook.Names.AddFormula("SAPFuncF4Help", "Main.SAPF4Help()");
        }
    ),
    (
        "CubeSetName",
        "Workbook-level name that uses CUBESET against ThisWorkbookDataModel",
        (workbook, sheet) =>
        {
            workbook.Names.AddFormula(
                "Slicer_PC_P210",
                "CUBESET(\"ThisWorkbookDataModel\",\"[DIM_PC].[PC_P2].&[RS]\",\"Slicer\")");
        }
    )
};

foreach (var (name, description, setup) in cases)
{
    var tempFile = Path.Combine(Path.GetTempPath(), $"{name}_{Guid.NewGuid():N}.xlsx");

    try
    {
        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");
            worksheet.Cells["A1"].Value = 1;
            setup(package.Workbook, worksheet);
            package.SaveAs(new FileInfo(tempFile));
        }

        using var reopened = new ExcelPackage(new FileInfo(tempFile));

        Console.WriteLine($"Case: {name}");
        Console.WriteLine($"  Description: {description}");

        try
        {
            reopened.Workbook.Calculate(new ExcelCalculationOption { AllowCircularReferences = true });
            Console.WriteLine("  Result: calculation succeeded (unexpected)\n");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"  Result: {ex.GetType().Name} - {ex.Message}\n");
        }
    }
    finally
    {
        if (File.Exists(tempFile))
        {
            File.Delete(tempFile);
        }
    }
}

[JanKallman]

Thanks for reporting these issues. We will provide fixes shortly.

[JanKallman]

Fixed in EPPlus 8.4.1