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

Can't re-call package.save()

Jan 2, 2014 at 8:25 PM
Edited Jan 2, 2014 at 9:24 PM
Hi,

I've seen similar threads, but no answers to my issue.

I have an application that gets information from each PDF File in a given folder.
For each PDF File, there are 1 or more rows of information which need to be stored in a common Excel file.

I have written a function that is called for each PDF file. It does the following:
  1. Opens excel file using EPPlus
  2. Gets the next row # (col a value) to be written to by examining contents of col a.
  3. writes row(s) into the excel file based on data collected from the PDF File
  4. Saves the package
Problem:

I cannot seem to run the function twice, it fails on package.save() on the second call.
  • My function processes first file processes perfectly.
  • My function throws an exception on the second file when package.save() is called.
  • {"Index was outside the bounds of the array."}
    at OfficeOpenXml.Utils.CompoundDocument.GetChunk(Byte[] compBuffer, Int32& pos)
    at OfficeOpenXml.Utils.CompoundDocument.DecompressPart(Byte[] part, Int32 startPos)
    at OfficeOpenXml.VBA.ExcelVbaProject.ReadModules()
    at OfficeOpenXml.VBA.ExcelVbaProject.GetProject()
    at OfficeOpenXml.VBA.ExcelVbaProject..ctor(ExcelWorkbook wb)
    at OfficeOpenXml.ExcelWorkbook.get_VbaProject()
    at OfficeOpenXml.ExcelWorkbook.Save()
    at OfficeOpenXml.ExcelPackage.Save()
  • If I open the excel file in Windows (using Excel) and press the Save button, then close it, I can
    rerun my function, and it works.
  • no other code is opening the excel file in any way, nor is the excel file open in Windows when error occurs.
<begin code snip>
    private void exportToExcel()
    {
             FileInfo myExcelOutputFile = new FileInfo(myFile);
            ExcelPackage package = new ExcelPackage(myExcelOutputFile);

            //Open worksheet 1 - "main"
            ExcelWorksheet worksheet = package.Workbook.Worksheets["Main"];

            // do work
            int lvIndx = 0, row = 0, count = 0, nextRow = 0;
//Select all cells in column a that have a data type double value (as opposed to blank)
var query1 = (from cell in worksheet.Cells["a:a"] where cell.Value is double select cell);

// find last row with a valid datatype double value
foreach (var cell in query1) { count++; }

// offset for initial header row
            nextRow = count + 2;


            // add data from myitems to the excel sheet, 1 row at a time
    for (; lvIndx < myPDFItems.Count; lvIndx++)
                {
                    row = lvIndx + nextRow; // calc the next row
                    worksheet.InsertRow(row, 1);
                    worksheet.Cells[row, 1].Value = Convert.ToDouble(myNextPDFID); 
                    worksheet.Cells[row, 2].Value = "info"; 
                    worksheet.Cells[row, 3].Value = myNextPDFBkmrkdesc;                                    
                }

           // save our new workbook and we are done!

           try
           {
            //fails for second file!
    package.Save();

           }

           catch (Exception eppMsg)
           {

             MessageBox.Show(eppMsg.Message);
           }

        }
    }
</end code snip>

I've tried:
package.SaveAs(differentExcelFileName); // same error
package.Dispose(); // same error
package = null; // same error

I don't know what " index would be out of bounds" but thought maybe my code was doing it, so I tried a simple function where i just open the excel file, then call package.save(), without doing any processing whatsoever, and I still get the error. see test code below:

<begin test code snip>
    private void exportToExcel()
    {
            FileInfo myExcelOutputFile = new FileInfo(myFile);
            ExcelPackage package = new ExcelPackage(myExcelOutputFile);

            //Open worksheet 1 - "main"
            ExcelWorksheet worksheet = package.Workbook.Worksheets["Main"];

           try
           {
            //fails for second call!
    package.Save();

           }

           catch (Exception eppMsg)
           {

             MessageBox.Show(eppMsg.Message);
           }    
    }
</end code snip>


Any help would be appreciated!
Feb 20, 2014 at 9:07 PM
Maybe there is a problem with calling the Dispose directly. Have you tried:


// the using statement automatically calls Dispose() which closes the package.
using (ExcelPackage package = new ExcelPackage(newFile))
{

}