epplus dispose don't work

Question

I open with epplus an excel file.

After reading some data, I would like to close the package:

pck.Stream.Close()
pck.Dispose()

Unfortunatelly the excel file is still blocked. I need to close the whole application to get the excel file unlocked.

I have googled, but found nothing useful except the above.

Answer 1

How are you opening the file? The following creates, saves, reopens, prints, and finally deletes all withing the same thread without issue. I can even set a breakpoint anywhere and delete the file. Reading the file this way should not lock the file since it is pulled into memory:

[TestMethod]
public void OpenReopenPrintDeleteTest()
{
    //Create some data
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        workbook.Worksheets.Add("newsheet"); 
        package.Save();
    }

    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.First();

        //The data
        worksheet.Cells["A1"].Value = "Col1";
        worksheet.Cells["A2"].Value = "sdf";
        worksheet.Cells["A3"].Value = "ghgh";
        worksheet.Cells["B1"].Value = "Col2";
        worksheet.Cells["B2"].Value = "Group B";
        worksheet.Cells["B3"].Value = "Group A";
        worksheet.Cells["C1"].Value = "Col3";
        worksheet.Cells["C2"].Value = 634.5;
        worksheet.Cells["C3"].Value = 274.5;
        worksheet.Cells["D1"].Value = "Col4";
        worksheet.Cells["D2"].Value = 996440;
        worksheet.Cells["D3"].Value = 185780;

        package.Save();
    }

    //Reopen the file
    using (var package = new ExcelPackage(existingFile))
    {
        var workBook = package.Workbook;
        if (workBook != null)
        {
            if (workBook.Worksheets.Count > 0)
            {
                var currentWorksheet = workBook.Worksheets.First();
                var lastrow = currentWorksheet.Dimension.End.Row;
                var lastcol = currentWorksheet.Dimension.End.Column;
                for (var i = 1; i <= lastrow; i++)
                    for (var j = 1; j <= lastcol; j++)
                        Console.WriteLine(currentWorksheet.Cells[i, j].Value);
            }
        }
    }

    //Delete the file
    existingFile.Delete();
}

Answer 2

I was having the same problem... But I found the solution:

During the "SaveAs" method, I was creating a FileStream that was not being disposed.

Before:

        ExcelPackage excel_package = new ExcelPackage(new MemoryStream());

        //...
        //Do something here
        //...

        excel_package.SaveAs(new FileStream("filename.xlsx", FileMode.Create));
        excel_package.Dispose();

After:

        ExcelPackage excel_package = new ExcelPackage(new MemoryStream());

        //...
        //Do something here
        //...

        var file_stream = new FileStream("filename.xlsx", FileMode.Create);
        excel_package.SaveAs(file_stream);
        file_stream.Dispose();
        excel_package.Dispose();

Note that the Memory Stream opened during the ExcelPackage declaration was not explicitly disposed, because the last command "excel_package.Dispose()" already does this internally.

Hope it help.

c# excel epplus