EPPlus: Is Auto Save Supported?

I have already accepted Jan 'splite' Kondelík's solution as a solution as it provides the information needed to solve the problem.

However, I am posting my own sample to help other users find the solution Ian suggested faster.

Basically, when reopening the file, you need to pass the file name in the ExcelPackage () constructor. Alternatively, you can get a reference to the sheet you want by passing either the name of the existing sheet or the index as shown below

package.Workbook.Worksheets["PerformanceTest"] 

      

        
        
        
      

    

OR

package.Workbook.Worksheets[1] //1 to n, 1 being base

      

        
        
        
      

    

I am posting a sample that helps demonstrate how to implement the AutoSave logic. Hope this helps someone else too.

I created a C # console application to demonstrate this.

Sample code

Program.cs

using System;
using System.IO;

namespace EPPlus_AutoSave_Excel_Export
{
    class Program
    {
        static void Main(string[] args)
        {
            DirectoryInfo outputDir = new DirectoryInfo(@"c:\temp\Auto-Save");

            if (!outputDir.Exists) 
                throw new Exception("outputDir does not exist!");

            Sample_Auto_Save obj = new Sample_Auto_Save(outputDir, 50000);
            obj.ExportWithAutoSave();
        }
    }
}

      

        
        
        
      

    

Sample_Auto_Save.cs

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.IO;
using System.Drawing;

namespace EPPlus_AutoSave_Excel_Export
{
    class Sample_Auto_Save
    {
        int Rows;
        int nRowsWritten = 0;
        bool bAppendData = false;
        ExcelWorksheet ws;
        DirectoryInfo outputDir;


        public Sample_Auto_Save(DirectoryInfo outputDir, int Rows)
        {
            this.Rows = Rows;
            this.outputDir = outputDir;
        }

        public void ExportWithAutoSave()
        {

            FileInfo newFile = new FileInfo(outputDir.FullName + @"\Auto_Save_Export.xlsx");
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                newFile = new FileInfo(outputDir.FullName + @"\Auto_Save_Export.xlsx");
            }

            Console.WriteLine("{0:HH.mm.ss}\tStarting...", DateTime.Now);
            while (nRowsWritten < Rows)
            {
                if (bAppendData == false)
                {
                    using (ExcelPackage package = new ExcelPackage())
                    {

                        //Load the sheet with one string column, one date column and a few random numbers
                        ws = package.Workbook.Worksheets.Add("Performance Test");
                        InsertRows();
                        bAppendData = true;
                        AutoSave(newFile, package);
                    }
                }
                else
                {
                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {
                        Console.WriteLine("{0:HH.mm.ss}\tOpening existing file again!...", DateTime.Now);
                        ws = package.Workbook.Worksheets["Performance Test"];

                        InsertRows();

                        AutoSave(newFile, package);
                    }
                }       
            }
            Console.WriteLine("{0:HH.mm.ss}\tDone!!", DateTime.Now);           
        }

        private void AutoSave(FileInfo newFile, ExcelPackage package)
        {
            ws.Select("C2");
            Console.WriteLine("{0:HH.mm.ss}\tAuto-Saving...", DateTime.Now);
            package.Compression = CompressionLevel.BestSpeed;
            package.SaveAs(newFile);
            bAppendData = true;
        }

        private void InsertRows()
        {

            //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();
            int startRow;
            if (ws.Dimension == null)
                startRow = 0;
            else
                startRow = ws.Dimension.End.Row;
            for (int row = startRow + 1; row <= Rows; row++, nRowsWritten++)
            {
                ws.SetValue(row, 1, row);                               //The SetValue method is a little bit faster than using the Value property
                ws.SetValue(row, 2, string.Format("Row {0}", row));
                ws.SetValue(row, 3, DateTime.Now.ToShortTimeString());
                ws.SetValue(row, 4, rnd.NextDouble() * 10000);


                if (row % (Rows/5) == 0)
                {
                    Console.WriteLine("{0:HH.mm.ss}\tWritten {1} records!...", DateTime.Now, row);
                    nRowsWritten++;
                    return;
                }
            }
        }
    }
}

      

        
        
        
      

    

UPDATE: It looks like the overhead of automatically saving a large file is extremely high. After discussing with Jan and Patrick, I realized that it would be best to write all my data into a separate data file and finally export Excel to use EPPlus at the end of my test. This will be the fastest and easiest way!

Alternatively, if it is not necessary for ALL data to be present in one file, consider splitting your data into multiple excel files. for example, you could store 10K records per file, and that way you keep speed, low memory requirements, and data security all at the same time.