EPPlus not saving changes to worksheet

Question

I have a batch job that loops through a spreadsheet processing each record row by row. Depending on the results, I want to leave a comment in the last column of each record.

I can tell, by monitoring the value in this cell while the program's running, that the appropriate changes are going where they need to. However, after running the .Save() function I open the spreadsheet and each column looks the same as it did before the program was run.

It seems like they built EPPlus to be pretty straightforward with regard to saving your changes, so I'm not sure what I'm doing wrong.

Here is how I open the spreadsheet

if (File.Exists(path))
{
    try
    {
        //Connect to spreadsheet
        FileStream _stream = File.Open(path, FileMode.Open, FileAccess.ReadWrite);
        FileInfo file = new FileInfo(path);

    }
    catch (IOException)
    {
        SendEmail.OkMail("Spreadsheet is currently in use. Please close and try again.");

        //Exit program
        Environment.Exit(0);
    }

    //Pull data into EPPlus object
    _package = new ExcelPackage();
    _package.Load(_stream);
    _sheet = _package.Workbook.Worksheets.First(); 
}
else
{
    //Notify user and exit program
    SendEmail.OkMail("Cannot find file in requested directory. Please check and try again.");

    //Exit program
    Environment.Exit(0);
}

This code makes the appropriate changes. While stepping through this part of the code, I can see that the right value is being stored in the correct cell of the ExcelWorkSheet object named _sheet

//Figure out what type of update occurred
if (ignore == (_noUpdates - 1))
{
    _sheet.Cells[row, col + 11].Value = "Ignore";
}
else if (add == (_adds - 1))
{
    _sheet.Cells[row, col + 11].Value = "Valid Add";
}
else if (update == (_updates - 1))
{
    _sheet.Cells[row, col + 11].Value = "Valid Update";
}
else if (autoterm == (_autotermed - 1))
{
    _sheet.Cells[row, col + 11].Value = "Valid Autoterm";
} 

Once I'm done looping through the spreadsheet, I make this call to the .Save() function. Nothing happening here though. Changes are not being saved.

//Save changes to spreadsheet.
_package.Save();

Answer 1

The Load(Stream) method doesn't store the path of the file, and doesn't store the passed-in stream to re-use when saving. When you call the Save() method, the package doesn't know where to save the file, and simply closes the package.

(This seems like a bad design to me; it should probably throw an exception in this case.)

Try changing your code to use the constructor which takes a FileInfo argument:

FileInfo file = new FileInfo(path);
if (file.Exists)
{
    try
    {
        _package = new ExcelPackage(file);
        _sheet = _package.Workbook.Worksheets.First();
    }
    catch (IOException)
    {
        SendEmail.OkMail("Spreadsheet is currently in use. Please close and try again.");

        //Exit program
        Environment.Exit(0);
    }
}
else
{
    //Notify user and exit program
    SendEmail.OkMail("Cannot find file in requested directory. Please check and try again.");

    //Exit program
    Environment.Exit(0);
}