This project has moved. For the latest updates, please go here.

Merging multiple worksheets from external documents failing

Oct 10, 2014 at 5:33 PM
Instead of creating sheets from scratch, I had my client create a template which I am loading in and pulling the worksheet across into a new merged sheet (created on the server.)

If I pull one worksheet in, the operation works. If I attempt to pull in both worksheets, the operation fails with:

Index was out of range. Must be non-negative and less than the size of the collection.

Here's what the code looks like:
  internal void BDBB(ExcelPackage BDWS)
  {
    string ExcelFile = Server.MapPath("~/lib/template/bdbb.xlsx");
    ExcelPackage ep = new ExcelPackage(new FileInfo(ExcelFile));
    ep.Load(new FileStream(ExcelFile, FileMode.Open));
    ExcelWorksheet single = ep.Workbook.Worksheets["Single"];
    //ExcelWorksheet multiple = ep.Workbook.Worksheets["Multiple"];
    BDWS.Workbook.Worksheets.Add("Single 2014", single);
    //BDWS.Workbook.Worksheets.Add("Multiple 2014", multiple);
  }
That works, however, if I uncomment out the addition of the worksheet "Multiple 2014" then the generation fails with the above message. Before the crash I can trace out the Workbook has 3 worksheets in it but an attempt to do this fails.
Response.BinaryWrite(ExcelDocument.GetAsByteArray());
When the page inits, I create, manually, the first cover page and then add this sheet to it. Additionally, some elements on the page are not copied over correctly with the copied version having visible XML instead of text.
Oct 10, 2014 at 5:46 PM
New twist, if I remove the addition of the worksheet "Single" and try just to add "Multiple" which is just the second worksheet with formulas, it fails with the same error so it's something to do with that worksheet it seems.
Oct 10, 2014 at 6:16 PM
Follow up twist - I can add the good working sheet 4 times in a row but this one other sheet I can't add. Is there a way to see if a page add had errors?