Unable to Retrieve Value from What-If-Analysis Data Table

[johnmiller86]

Good afternoon,

After upgrading to version 6.1.0+, I am able to successfully save an Excel file without wiping out the what-if-analysis data table formulas. However, I am not able to retrieve the values from those cells via EPPlus in order to generate a summary workbook from multiple excel files. I have reproduced this with a single input file (probably not the best example of a what-if-analysis data table, but it does illustrate the issue):

Below is the code used to replicate the issue:

using OfficeOpenXml;
using System.IO;

var NewFileGuid = Guid.NewGuid();

Init();
await GenerateDataFile(NewFileGuid);
await GenerateSummary(NewFileGuid);

static void Init()
{
    ExcelPackage.LicenseContext = LicenseContext.Commercial;
}

async static Task GenerateDataFile(Guid fileGuid)
{

    // Instantiate ExcelPackage with Save and Template Paths
    using var excelPack = new ExcelPackage(new FileInfo($"{Constants.FilePaths.BaseFilepath}\\Outputs\\Sample_Data_Table_{fileGuid}.xlsx"), new FileInfo($"{Constants.FilePaths.BaseFilepath}\\Templates\\Sample_Data_Table_Template.xlsx"));

    // Getting Worksheet
    var worksheet = excelPack.Workbook.Worksheets[0];

    // Setting Input Cell
    worksheet.SetValueAtCellAddress(Constants.DataFileConstants.BasePriceCellAddress, 250000m);

    // Saving
    await excelPack.SaveAsync();
}

async static Task GenerateSummary(Guid fileGuid)
{
    // Retrieving Data
    var data = GetData(fileGuid);

    // Instantiate ExcelPackage with Save and Template Paths
    using var excelPack = new ExcelPackage(new FileInfo($"{Constants.FilePaths.BaseFilepath}\\Outputs\\Sample_Summary_{Guid.NewGuid()}.xlsx"), new FileInfo($"{Constants.FilePaths.BaseFilepath}\\Templates\\Sample_Summary_Template.xlsx"));

    // Getting Worksheet
    var worksheet = excelPack.Workbook.Worksheets[0];

    // Setting Input Cells
    worksheet.SetValueAtCellAddress(Constants.SummaryConstants.PremiumACellAddress, data.Item1);
    worksheet.SetValueAtCellAddress(Constants.SummaryConstants.PremiumBCellAddress, data.Item2);
    worksheet.SetValueAtCellAddress(Constants.SummaryConstants.PremiumCCellAddress, data.Item3);

    // Saving
    await excelPack.SaveAsync();
}

static Tuple<object?, object?, object?> GetData(Guid fileGuid)
{
    // Instantiate ExcelPackage with Filepath
    using var excelPack = new ExcelPackage($"{Constants.FilePaths.BaseFilepath}\\Outputs\\Sample_Data_Table_{fileGuid}.xlsx");

    // Getting Worksheet
    var worksheet = excelPack.Workbook.Worksheets[0];

    // Calculate the Workbook
    excelPack.Workbook.Calculate();
    
    // Return Data
    return new Tuple<object?, object?, object?>(
        worksheet.GetDecimalValueFromCellAddress(Constants.DataFileConstants.PremiumACellAddress),
        worksheet.GetDecimalValueFromCellAddress(Constants.DataFileConstants.PremiumBCellAddress),
        worksheet.GetDecimalValueFromCellAddress(Constants.DataFileConstants.PremiumCCellAddress)
    );
}

public static class SpreadsheetExtensions
{
    public static object? GetDecimalValueFromCellAddress(this ExcelWorksheet worksheet, string FieldAddress)
    {
        try
        {
            return Convert.ToDecimal(worksheet.Cells[FieldAddress].Value);
        }
        catch (Exception)
        {
            // Break Here
            return worksheet.Cells[FieldAddress].Text;
        }
    }

    public static void SetValueAtCellAddress(this ExcelWorksheet worksheet, string FieldAddress, object? value, string? format = null)
    {
        worksheet.Cells[FieldAddress].Value = value;
        if (!string.IsNullOrEmpty(format))
        {
            worksheet.Cells[FieldAddress].Style.Numberformat.Format = format.Equals("CURRENCY") ? "[$$-en-US] #,0.00" : "0.00%";
        }
    }
}

public struct Constants
{
    public struct FilePaths
    {
        public const string BaseFilepath = @"C:\Users\jmiller\Desktop\Sample\";
    }

    public struct DataFileConstants
    {
        public const string BasePriceCellAddress = "B2";
        public const string PremiumACellAddress = "B6";
        public const string PremiumBCellAddress = "B7";
        public const string PremiumCCellAddress = "B8";
    }

    public struct SummaryConstants
    {
        public const string PremiumACellAddress = "A2";
        public const string PremiumBCellAddress = "B2";
        public const string PremiumCCellAddress = "C2";
    }
}

As well as the template files used:

Sample.zip

Any advice on this item? I'd like to avoid Interop if at all possible, when reading in the calculated workbook values.

Thanks,

John

[JanKallman]

EPPlus does not support calculating the TABLE() function. You will have to rely on Excel to get it calculated.
Supported function can be found here: https://github.com/EPPlusSoftware/EPPlus/wiki/Supported-Functions
We are currently working on implementing Array formulas in the coming the EPPlus 7, so we will look at adding it in a coming version.

[johnmiller86]

Yeah, that's what I was trying to ask on #863 if the changes in 6.1.0 were aiming to support this function or just retain the function properties. Please feel free to close this as a non-issue as this would be an enhancement as you mentioned.

Best,

John