SharedFormulaId 0 not found

[johnmiller86]

Good morning,

We recently upgraded from version 6.0.8 to version 6.2.2 to resolve an issue clearing out what-if analysis data table formulas.

Release notes 6.1.0
Preserve the 'Table' formula properties (Created via the What-If Analysis-Data Table).

I'm unable to determine if this change has resolved our issue as I am not able to save the files in any version 6.1.0 or higher. There is an InvalidDataException occurring on a different sheet that SharedFormulaId 0 is not found. If I clear formulas on this sheet, I receive the same error on other sheets. Any advice on this one?

System.InvalidOperationException: Error saving file C:\DIRECTORY\FILENAME.xlsm\r\n ---> System.IO.InvalidDataException: SharedFormulaId 0 not found on Worksheet Variables cell AB7, SharedFormulas Count 566\r\n at OfficeOpenXml.ExcelWorksheet.UpdateRowCellData(StreamWriter sw, String prefix)\r\n at OfficeOpenXml.ExcelWorksheet.SaveXml(Stream stream)\r\n at OfficeOpenXml.Packaging.ZipPackagePart.WriteZip(ZipOutputStream os)\r\n at OfficeOpenXml.Packaging.ZipPackage.Save(Stream stream)\r\n at OfficeOpenXml.ExcelPackage.SaveAsync(CancellationToken cancellationToken)\r\n --- End of inner exception stack trace ---\r\n at OfficeOpenXml.ExcelPackage.SaveAsync(CancellationToken cancellationToken)\r\n at COMPANY_NAME.WebServices.Internal.Services.Rater.Excel.ExcelManager.GenerateRaterWorkbook(RaterGroup group) in C:\Git\COMPANY_NAME.WebServices.Internal\COMPANY_NAME.WebServices.Internal.Services.Rater\Excel\ExcelManager.cs:line 75

[OssianEPPlus]

Hi! It's hard for us to determine the source of an error like this without the workbook in question or a reproducible code sample. Would it be possible for you to provide us with both or either?

[johnmiller86]

Just received approval to share the template. I wrote a quick console app to reproduce the issue without inserting the PHI data we normally would:

ExcelPackage.LicenseContext = LicenseContext.Commercial;

using var excelPack = new ExcelPackage(new FileInfo($"C:\\Users\\jmiller\\Desktop\\Sample\\Outputs\\{Guid.NewGuid()}.xlsm"), new FileInfo(@"C:\Users\jmiller\Desktop\Sample\Templates\UW Renewal Rater Workbook Template.xlsm"));

// Removed insertion of PHI data, just re-saving the template for sample purposes

// Workaround - Issue with "Inputs" tab - Validation of T60:T64 failed: Formula2 must be set if operator is 'between' or 'notBetween' when cells are not using between or notBetween
var otherInputTab = excelPack.Workbook.Worksheets.FirstOrDefault(ws => ws.Name.Equals("Inputs"));
if (otherInputTab != null)
{
    otherInputTab.DataValidations.InternalValidationEnabled = false;
}

// Saving
await excelPack.SaveAsync();

@OssianEPPlus is there an email address I can send the template file to? Github only supports xlsx, not xlsm file types.

Update:
I was able to resave as xlsx and still reproduce the error. Attaching the template.

[UW Renewal Rater Workbook Template.xlsx]

Update:
Removed Template from Public Access

[JanKallman]

This is a bug. We will provide a fix shortly.

[OssianEPPlus]

We should have resolved this bug now! Please test it via our Develop NuGet Feed to confirm.

[johnmiller86]

This issue has been resolved, I am able to save again! The Table formula changes introduced in 6.1.0 have also resolved our original issue. Do we know when this will be released to NuGet so I don't have to add the development package source to our DevOps server?

Thanks so much!

John

[JanKallman]

We aim for a new release today.

[johnmiller86]

Fantastic, much appreciated!!

[JanKallman]

Fixed in EPPlus 6.2.3

[johnmiller86]

While this has resolved our issue with clearing the formulas, I do also have to read the files back in to generate an aggregated summary workbook. Is there a way to calculate the Table functions to retrieve their values, or was the change in 6.1.0 only to retain the function properties?

[OssianEPPlus]

Worksheet.Calculate or Workbook.Calculate might do the trick. See our wiki

[johnmiller86]

I have tried that. Unfortunately, it seems that any cells depending on a Table function result in calculation errors once loading the Workbook:

[JanKallman]

Make sure you use the correct reference to the table parts as the Excel GUI and the OOXML format is different. See https://github.com/EPPlusSoftware/EPPlus/wiki/Referencing-tables-in-formulas
If this is not the issue, please attach a workbook describing your issue.

[johnmiller86]

Thanks for the info, I'll give this a shot this afternoon and see if I can resolve it. These templates were created years ago by our UW department and we're now automating their process for them. If the wiki doesn't resolve it we can track under a new issue as this has been resolved, thanks again for your help with this one.

[johnmiller86]

Unfortunately, it seems there is an issue with getting data from the what-if-analysis data tables, which were leading to calculation errors in other areas of our workbook that presented themselves as seen in the previous screenshot. I have opened #868 with more details and a basic sample.