EPPlus: Auto-save feature supported?

Question

I'm planning to use the EPPlus (4.0.4 version) library to export my live data to an excel sheet using C# .NET in my WPF application. My question is however specific to the EPPLus library.

I've gone through the EPPlus samples () wherein you can use SetValue to write out the values to the worksheet.

The problem is that since my application needs to write live data over a span of potentially several days(!), I want to ensure that my data is saved every once in a while to avoid loss of data in the case of an application/system crash.

Normally, I'd expect there to be a auto-save function wherein one can save the current state, and then proceed as normal with appending new records.

However, EPPlus doesn't seem to have this(?)... How would I go about achieving this?

Example source code:

using (ExcelPackage package = new ExcelPackage())
{
      //Load the sheet with one string column, one date column and a few random numbers.
    var ws = package.Workbook.Worksheets.Add("Performance Test");

    //Format all cells
    ExcelRange cols = ws.Cells["A:XFD"];
    cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
    cols.Style.Fill.BackgroundColor.SetColor(Color.LightGray);

    var rnd = new Random();                
    for (int row = 1; row <= Rows; row++)
    {
        ws.SetValue(row, 1, row);
        ws.SetValue(row, 2, string.Format("Row {0}", row));
        ws.SetValue(row, 3, DateTime.Today.AddDays(row));
        ws.SetValue(row, 4, rnd.NextDouble() * 10000);
        ws.SetValue(row, 5, rnd.NextDouble() * 100);
        ws.SetValue(row, 6, rnd.NextDouble() * 10);
        ws.SetValue(row, 7, rnd.NextDouble() * 78);
        ws.SetValue(row, 8, rnd.NextDouble() * 5300);
        ws.SetValue(row, 9, rnd.NextDouble() * 1250);
        ws.SetValue(row, 10, rnd.NextDouble() * 670);

        if (row % 10000 == 0)
        {
            Console.WriteLine("{0:HH.mm.ss}\tWriting row {1}...", DateTime.Now, row);

            //I need a way to save the existing data say every 10K records or so.

        }
    }             

    ws.Select("C2");
    Console.WriteLine("{0:HH.mm.ss}\tSaving...", DateTime.Now);
    package.Compression = CompressionLevel.BestSpeed;
    package.SaveAs(newFile); //this seems to be done only at the end of processing!
}

Answer 1

Why not to save after every batch and open existing xls & do updates & save again?

Just like

ExcelPackage package = new ExcelPackage(newFile);

(*plese note here that newFile is only variable name, in this context, it is more like existingFile)

you will NOT lost what you already have in your sheets, you just need to find last row to continue where you end last time:

ws.Dimension.End.Row;

As bonus, you will not block user access to file (your app right now keep write permission and block file) to save his own changes (lets say, after he add that 'free and very cool graph he want from your data' :) )