CompoundDocumentFile throws System.ArgumentOutOfRangeException: Index was out of range.

[gengle]

Encountered on 2d08514

The following scenario will generate ArgumentOutOfRangeException exception

FileInfo TempFile(string name)
{
  var baseFolder = Path.Combine(Path.Combine(Path.GetTempPath(), Path.GetRandomFileName()));
  Directory.CreateDirectory(baseFolder);
  return new FileInfo(Path.Combine(baseFolder, name));
}
   
[TestMethod]
public void MustNotThrowWhenWhenHaving65Sheets()
{
   var src = TempFile("report.xlsm");
   var package = new ExcelPackage(src);
   var sheets = Enumerable.Range(1, 65)
       .Select(x => $"Sheet{x}");
   foreach (var sheet in sheets)
       package.Workbook.Worksheets.Add(sheet);

   package.Workbook.CreateVBAProject();
   package.Workbook.VbaProject.Modules.AddModule("Module1").Code
       = "\r\nPublic Sub SayHello()\r\nMsgBox(\"Hello\")\r\nEnd Sub\r\n";

   package.Save();
}

Here is the full exception

Test method EPPlusTest.VBA.MustNotThrowWhenWhenHaving65Sheets threw exception: 
System.InvalidOperationException: Error saving file C:\Users\gengle\AppData\Local\Temp\jkbrtwwt.ikk\report.xlsm ---> System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
    at System.ThrowHelper.ThrowArgumentOutOfRange_IndexException()
   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.SetSiblings(List`1 l, List`1 children, Int32 pos, Int32 div) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 486
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.SetSiblings(List`1 l, List`1 children, Int32 pos, Int32 div) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 494
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.SetSiblings(List`1 l, List`1 children, Int32 pos, Int32 div) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 494
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.SetSiblings(List`1 l, List`1 children, Int32 pos, Int32 div) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 494
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.SetSiblings(List`1 l, List`1 children, Int32 pos, Int32 div) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 494
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.AddChildren(CompoundDocumentItem item, List`1 l) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 464
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.AddChildren(CompoundDocumentItem item, List`1 l) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 469
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.FlattenDirs() in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 440
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocumentFile.Write(MemoryStream ms) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocumentFile.cs:line 425
   at OfficeOpenXml.Utils.CompundDocument.CompoundDocument.Save(MemoryStream ms) in C:\Users\gengle\source\EPPlus\EPPlus\Utils\CompundDocument\CompoundDocument.cs:line 120
   at OfficeOpenXml.VBA.ExcelVbaProject.Save() in C:\Users\gengle\source\EPPlus\EPPlus\VBA\ExcelVBAProject.cs:line 602
   at OfficeOpenXml.ExcelWorkbook.Save() in C:\Users\gengle\source\EPPlus\EPPlus\ExcelWorkbook.cs:line 838
   at OfficeOpenXml.ExcelPackage.Save() in C:\Users\gengle\source\EPPlus\EPPlus\ExcelPackage.cs:line 831
--- End of inner exception stack trace ---
    at OfficeOpenXml.ExcelPackage.Save() in C:\Users\gengle\source\EPPlus\EPPlus\ExcelPackage.cs:line 899
   at EPPlusTest.VBA.MustNotThrowWhenWhenHaving65Sheets() in C:\Users\gengle\source\EPPlus\EPPlusTest\VBA.cs:line 95

What's known

1. Error appears to be related to number of worksheets -- 65 and 100 fail
2. Appears to be how the BTree index is handled with the mcdf implimentation

• Current CompoundFile Implementation is light on testing and I have overall concerns on it's implementation. Should we consider using an alternative, such as https://github.com/CodeCavePro/OpenMCDF

[gengle]

I can submit a PR using OpenMCDF implementation -- wanted to ask first to see if anyone has objections on introducing a dependency.

[roelvandersteen]

I find the current implementation very unreliable, so I am in favor of VBA Project support via a different package. OpenMCDF is the logical candidate.

[gengle]

I can have a PR ready with OpenMCDF by weeks end.

[JanKallman]

It seems the sorting gets messed-up when setting the left and right siblings in the tree.
I'll have a look at it and supply a fix.
I prefer not to rely on third party packages unless it's nessesary, but we'll see where we end up.

[gengle]

I can say that using OpenMCDF does fix my two issues. However, I quickly recompiled and excluded encryption.

If we create contract tests against OpenMCDF that might aide in our own implementation.

I'm here to help best I can.

[JanKallman]

Sounds good. I appreciate your help testing this.
I hope I can commit something later this week.

[JanKallman]

Ok, just checked in a fix for this. Please try it out and give me feedback if it works or not. Sorting was wrong(left-right siblings) and I rewrote the allocation. I will improve this with some more tests and if it works for you I will create an rc2 before the the final release. Thanks

[gengle]

Sounds good. I'll give it a spin; at a minimum I'd think the tests I wrote earlier would pass. I have a few more scenarios I can test too.

[gengle]

I'm reviewing this now; should have an update soon.

[gengle]

@JanKallman is there any particular reason these tests are ignored and reference a static path? I can make them dynamic and repeatable.

[JanKallman]

Feel free to do so. I usually ignore tests that read xl files, as I don't want include a lot of binary files in the repository. If you want to clean it up, it's most welcome.