delete rows cause System.IO.InvalidDataException: SharedFormulaId 3 not found

[eurohung]

EPPlus usage

Personal use

Environment

Windows

Epplus version

7.1.3

Spreadsheet application

Excel

Description

my program load a template file, fill data , where days of this month is not 31, delete some rows. when call Package.SaveAs(..), I got exception like follows: System.IO.InvalidDataException: SharedFormulaId 3 not found on Worksheet 彩色 cell Z36, SharedFormulas Count 7 於 OfficeOpenXml.Core.Worksheet.XmlWriter.WorksheetXmlWriter.UpdateRowCellData(StreamWriter sw, String prefix) 於 OfficeOpenXml.Core.Worksheet.XmlWriter.WorksheetXmlWriter.WriteNodes(StreamWriter sw, String xml, Int32& startOfNode, Int32& endOfNode) 於 OfficeOpenXml.ExcelWorksheet.SaveXml(Stream stream) 於 OfficeOpenXml.ExcelWorksheet.SaveHandler(ZipOutputStream stream, CompressionLevel compressionLevel, String fileName) 於 OfficeOpenXml.Packaging.ZipPackagePart.WriteZip(ZipOutputStream os) 於 OfficeOpenXml.Packaging.ZipPackage.Save(Stream stream) 於 OfficeOpenXml.ExcelPackage.Save() 於 OfficeOpenXml.ExcelPackage.SaveAs(Stream OutputStream)

[OssianEPPlus]

Unfortunately we cannot reproduce or figure out what is wrong from this information alone. We would require the workbook and a reproducible code sample that causes the error.

[eurohung]

On Wed, Jun 5, 2024 at 7:01 PM OssianEPPlus @.***> wrote:

Unfortunately we cannot reproduce or figure out what is wrong from this information alone. We would require the workbook and a reproducible code sample that causes the error.

— Reply to this email directly, view it on GitHub https://github.com/EPPlusSoftware/EPPlus/issues/1474#issuecomment-2149515231, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABP5D6QMIG2KMTW7ZQ4CGELZF3V2DAVCNFSM6AAAAABI2NHHNSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNBZGUYTKMRTGE . You are receiving this because you authored the thread.Message ID: @.***>

using Microsoft.VisualStudio.TestTools.UnitTesting; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using OfficeOpenXml; using OfficeOpenXml.FormulaParsing.Excel.Functions.MathFunctions; using OfficeOpenXml.FormulaParsing; using System.IO; using OfficeOpenXml.FormulaParsing.Excel.Functions.Information; using OfficeOpenXml.FormulaParsing.LexicalAnalysis;

namespace EPPlusTest.Issues { [TestClass] public class ShareFormulaError : TestBase { [ClassInitialize] public static void Init(TestContext context) { } [ClassCleanup] public static void Cleanup() { } [TestInitialize] public void Initialize() { }

    [TestMethod]
    public void ShareFormulaIDNotFoundError()
    {
        var filename = "ShareFormulaError.xlsx";
        using (var p = OpenPackage(filename))
        {
            var ws = p.Workbook.Worksheets.First();
            ws.DeleteRow(36, 1);

            try
            {
                p.SaveAs("share_formula_error_test.xlsx");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace);
                //Assert.Fail("Expected no exception, but got: " + ex.Message);
            }
        }
    }
}

}

[JanKallman]

To reproduce this we will require the workbook with the formula you are using as well.

[eurohung]

ShareFormulaError.xlsx

[JanKallman]

Thanks, I can reproduce your issue with this workbook. I'll try to provide a fix.

[JanKallman]

This issue occurred if you have a shared formula where the first cell has been deleted. I'll provide a fix shortly.