EPPlus Dispose Doesn't Work

Question

I want to create workbook and then write data using EPPlus. When I create new workbook, it can create successfully. But when I want to write some data to that worksheet, it failed and error says

The process cannot access the file 'filename' because it is being used by another process.

I have disposed previous ExcelPackage but the error still show when I write data.

//Create new Workbook
private void PengisianBaruBW_DoWork(object sender, DoWorkEventArgs e)
{
    this.Invoke(new MethodInvoker(delegate
    {
        SetPengisianBtn.Enabled = false;
    }));

    FileInfo filePath = new FileInfo("D:\\Data Pengisian SLA Surabaya\\" + day + "_" + date + ".xlsx");

    if (File.Exists(filePath.ToString()))
    {
        File.Delete(filePath.ToString());
    }

    using (ExcelPackage pck = new ExcelPackage(filePath))
    {
        var schedule = pck.Workbook.Worksheets.Add("Schedule");
        var cart = pck.Workbook.Worksheets.Add("Cartridge");
        var unsche = pck.Workbook.Worksheets.Add("Unschedule");
        var rekap = pck.Workbook.Worksheets.Add("Rekap");

        //My Code here

        pck.SaveAs(filePath);
        pck.Dispose(); //I have disposed ExcelPakcage here

    }
}


//Write Data to Excel File
private void PrintScheduleBtn_Click(object sender, EventArgs e)
{
    if (StaffATB.Text != "" && HelperTeamATB.Text != "" && StaffBTB.Text != "" && HelperTeamBTB.Text != "" && StaffCTB.Text != "" && HelperTeamCTB.Text != "" && StaffDTB.Text != "" && HelperTeamDTB.Text != "")
    {
        DialogResult dialogResult = MessageBox.Show("Apakah Anda yakin ingin menyimpan jadwal pengisian ?", "", MessageBoxButtons.YesNo);


        if (dialogResult == DialogResult.Yes)
        {
            FileInfo file = new FileInfo("D:\\Data Pengisian SLA Surabaya\\" + day + "_" + date + ".xlsx");

            using (ExcelPackage pck = new ExcelPackage(file)) //error here
            {
                var rekap = pck.Workbook.Worksheets["Rekap"];
                var data = pck.Workbook.Worksheets["Data"];

                //my code to write data here

                pck.SaveAs(file);
                pck.Dispose();

            }
        }
    }
    else
    {
        MessageBox.Show("Silakan isi PIC terlebih dahulu !");
    }
}

---

I have added this code to check whether my excel file is active or not. But the error still exsit. I set breakpoint and I see that stream value is null that indicate that my excel file is close. But why the error still exists ? Can anyone help me ?

string file = "D:\\Data Pengisian SLA Surabaya\\" + day + "_" + date + ".xlsx";
                var path = Path.Combine(Path.GetTempPath(), "D:\\Data Pengisian SLA Surabaya\\" + day + "_" + date + ".xlsx");
                var tempfile = new FileInfo(path);

                FileStream stream = null;

                try
                {
                    stream = tempfile.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
                }
                catch (IOException)
                {

                }
                finally
                {
                    if (stream != null)
                        stream.Close();
                }

Answer 1

I simplified your snippet for testing. It all worked as it should. Are you sure there is no other cause of the file access problem, like a virus scanner, backup program etc. since you also have another question with the same basic problem.

Take a look at the snippet below, try it and see if this one works. If not the problem is not in the code.

FileInfo filePath = new FileInfo("ExcelDemo.xlsx");

if (File.Exists(filePath.ToString()))
{
    File.Delete(filePath.ToString());
}

using (ExcelPackage pck = new ExcelPackage(filePath))
{
    var schedule = pck.Workbook.Worksheets.Add("Schedule");
    var cart = pck.Workbook.Worksheets.Add("Cartridge");
    var unsche = pck.Workbook.Worksheets.Add("Unschedule");
    var rekap = pck.Workbook.Worksheets.Add("Rekap");

    pck.SaveAs(filePath);
}

using (ExcelPackage pck = new ExcelPackage(filePath))
{
    var rekap = pck.Workbook.Worksheets["Rekap"];
    var schedule = pck.Workbook.Worksheets["Schedule"];

    rekap.Cells[4, 1].Value = "Added data";
    schedule.Cells[4, 1].Value = "Added data";

    pck.SaveAs(filePath);
}

Answer 2

As already stated, the basic code should work just fine. However, looking at your code, I sense that you are using some kind of BackgroundWorker (PengisianBaruBW_DoWork name suggests this).

If so, you might run into accessing the same file from another thread (PengisianBaruBW_DoWork executes in parallel with PrintScheduleBtn_Click).

To help you more, you should add where (what line) do you receive this error and the call stack.

[Edit] Based on additional comments, I think of one of these scenarios:

1) PengisianBaruBW_DoWork gets called many times and sometimes it happens to do work with the file, while PrintScheduleBtn_Click is trying to do work with the same file

2) An unhandled exception in _DoWork might get swallowed and leave the file opened (highly improbable since you have a disposable context).

Either way, put a breakpoint at the start of your _DoWork and one at beginning of PrintScheduleBtn_Click and use step over (F10).

Answer 3

I know this an old post, but it never got solved. I ran into the same problem, but i think i found the solution for it (at least it unlocked my excel file):

        excelPackage.Dispose();
        excelPackage = null;
        GC.Collect();