"Worksheet position out of range" error when calculating a worksheet after deleting some sheets with a IsWorksheets1Based flag

[hubaksis]

EPPlus usage

Still checking the library before the purchase. Not yet.

Environment

Windows

Epplus version

7.5.0

Spreadsheet application

Excel

Description

TL;DR: see this comment for summary and how to reproduce the issue, see this comment for the bad and the good file differences.

Hi, I am encountering the same error: "Worksheet position out of range" when calculating all formulas in the workbook.

I am trying to create a bare minimum example. I will update the code if/when I succeed. For some reason Unit test works fine with similar code and similar file.

If I run almost the same code locally on IIS, or at the staging environment, or from the integration test (that is executed from a different project) then I receive an error. Not yet sure why the difference. Debugger shows EPPlus version library 7.5.0.

The idea of the method is to clone an excel 'template' sheet, paste some data, delete template sheet, calculate formulas, save the result. Some formulas that are present in the template file: G18: =SUM(C18:F18) L18: =IF(ISERROR(I18/E18)=TRUE,"",(I18/E18)) M18: =IF(ISERROR(J18/G18)=TRUE,"",(J18/G18)) G18: =SUM(C19:F19) Attached formula logger shows that M18 is calculated correctly, however no info on G18 - I guess the error happens here.

What I found out for now: The same Unit Test in an empty project works fine. The issue is in the integration test. It appears that deleting a sheet interferes with subsequent formula calculations The error occurs during the second formula calculation attempt, resulting in the following exception System.IndexOutOfRangeException: 'Worksheet position out of range.' in the following piece of code:

wb.Workbook.Calculate();
wb.Workbook.Worksheets.Delete("one");
wb.Workbook.Worksheets.Delete("two");
wb.Workbook.Worksheets.Delete("three");
wb.Workbook.Calculate();

Stracktrace:

 System.IndexOutOfRangeException
  HResult=0x80131508
  Message=Worksheet position out of range.
  Source=EPPlus
  StackTrace:
   at OfficeOpenXml.ExcelWorksheets.get_Item(Int32 PositionID)
   at OfficeOpenXml.FormulaParsing.RpnFormulaExecution.SetAndReturnValueError(RpnOptimizedDependencyChain depChain, Exception ex, RpnFormula f)
   at OfficeOpenXml.FormulaParsing.RpnFormulaExecution.ExecuteChain(RpnOptimizedDependencyChain depChain, ExcelRangeBase range, ExcelCalculationOption options, Boolean writeToCell)
   at OfficeOpenXml.FormulaParsing.RpnFormulaExecution.Execute(ExcelWorksheet ws, ExcelCalculationOption options)  

Hope this info help you to find something. Otherwise I will keep digging tomorrow.

Saving file before the second calculation, opening the file and running the calculation again does not cause the error.

...
wb.Workbook.Worksheets.Delete("three");
wb.SaveAs("...broken.xlsx");
wb.Workbook.Calculate(); -- error here

Then run the code that will load the 'broken' file, and calculation does not cause an error.

[JanKallman]

It's not clear to me what happens here. We will need some code (and a workbook if needed), to be able to reproduce this.

[hubaksis]

Files: 1 v20 - 1.xlsx 1 v21.xlsx

The miminum code that reproduces the error:

var bytes = File.ReadAllBytes("D:\\temp\\1 v20 - 1.xlsx"); //bad file
//var bytes = File.ReadAllBytes("D:\\temp\\1 v21.xlsx");  //good file
using (var ms = new MemoryStream(bytes))
{
    using (var wb = new ExcelPackage())
    {
        wb.Load(ms);
        wb.Workbook.Worksheets.Copy("template", "Test-Copy");
        wb.Workbook.Calculate();

        wb.Workbook.Worksheets.Delete("template");
        //wb.SaveAs("D:\\temp\\template-test.xlsx");

        wb.Workbook.Calculate(); //exception here
    }
}

The file 1 v20 - 1 is a trimmed version of my original file. 1 v21 - is a file with the same content generate by EPPlus.

1 v20 - 1 causes the error. 1 v21 does not cause the error.

I've compared both files internally, and found three differences

1. core.xml - different modified time.
2. workbook.xml - different GUIDs.
3. sheet1.xml: A good file has:

   <row r="6" spans="7:7" x14ac:dyDescent="0.4">
   <c r="G6">
       <f>SUM(C6:F6)</f>
       <v>0</v>
   </c>
   </row>

   a bad file has:

   <row r="6" spans="7:7" x14ac:dyDescent="0.4">
   <c r="G6">
       <f t="shared" ref="G6" si="0">SUM(C6:F6)</f>
       <v>0</v>
   </c>
   </row>

   You can see the difference in f tag with additional attributes t="shared" ref="G6" si="0". Somehow these breaks the workbook after cloning the sheet.

I am not sure what these attributes are for, however they cause the issue. I've copied these missing attributes to the 'good' file (manually), repacked it back and the code above stopped working.

Hope this help.

[hubaksis]

And I found the difference between projects.

To reproduce the issue set IsWorksheets1Based to true.

I set it in app.config: <add key="EPPlus:ExcelPackage.Compatibility.IsWorksheets1Based" value="true" />

If this option is not set, the exception is not thrown.

Finally, the summary:

1. Take two files from the post above.
2. Set IsWorksheets1Based to true
3. Run the code below.
4. Outcome: Test fails.
5. Expected result: test does not fail.

[Test]
public void Test2()
{    
    var bytesGoodFile = File.ReadAllBytes("D:\\temp\\1 v21.xlsx");  //good file
    using (var ms = new MemoryStream(bytesGoodFile))
    {
        using (var wb = new ExcelPackage(ms))
        {
            wb.Workbook.Worksheets.Copy("template", "Test-Copy");
            wb.Workbook.Calculate();    
            wb.Workbook.Worksheets.Delete("template");

            wb.Workbook.Calculate();
        }
    }

    var bytesBadFile = File.ReadAllBytes("D:\\temp\\1 v20 - 1.xlsx"); //bad file    
    using (var ms = new MemoryStream(bytesBadFile))
    {
        using (var wb = new ExcelPackage(ms))
        {
            wb.Workbook.Worksheets.Copy("template", "Test-Copy");
            wb.Workbook.Calculate();    
            wb.Workbook.Worksheets.Delete("template");

            Assert.DoesNotThrow(() => wb.Workbook.Calculate());
        }
    }
}

[JanKallman]

This is a bug. I have provided a fix.

[JanKallman]

Fixed in EPPlus 7.5.1