Concurrent Reads and Write to an Excel file

Question

Is it possible to have only one process do WRITE and many doing a READ operation on an excel file? I am using ExcelPackage(EPPlus) for this.

To demo, I wrote two console app one to write iteratively and another to read. Running them concurrently will cause a failure on either side.

WRITE

// simply write to a column
var fileLocation = "D:\\Book.xlsx";
FileInfo fi = new FileInfo(fileLocation);

int i = 1;
while (1 == 1)  //ALERT: an infinite loop!
{
    using (ExcelPackage excelPackage = new ExcelPackage(fi))
    {
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["Sheet1"];
        var row = worksheet.Row(2);

        worksheet.Cells[$"A{i}"].Value = "Test " + i.ToString();
        excelPackage.Save();
        i++;
    }
}

READ

//simply populate a list reading excel
var fileLocation = "D:\\Book.xlsx";
FileInfo fi = new FileInfo(fileLocation);
List<string> list = new List<string>();
ExcelWorksheet worksheet = null;
int i = 1;
while (1 == 1)  //ALERT: an infinite loop!
{
    using (ExcelPackage excelPackage = new ExcelPackage(fi))
    {
        worksheet = excelPackage.Workbook.Worksheets["Sheet1"];
        if (worksheet.Cells[i, 1].Value != null)
        {
            list.Add(worksheet.Cells[i, 1].Value.ToString());
        }
    }

    list.Clear();
}

Answer 1

I slightly changed my code by making the WRITE program lock the file before writing and READ to be resilient when a failure happens:

WRITE: Use a FileStream and lock it before attempting to write. This will prevent the WRITE from failing

READ: added a retry mechanism implementing a try/catch block

Modified code:

WRITE

// simply write to a column
var fileLocation = "D:\\Book.xlsx";
FileInfo fi = new FileInfo(fileLocation);

int i = 1;
while (1 == 1)  //ALERT: an infinite loop!
{

    using (ExcelPackage excelPackage = new ExcelPackage(fi))
    {
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["Sheet1"];
        worksheet.Cells[$"A{i}"].Value = "Test " + i.ToString();

        using (var fs = new FileStream(fileLocation, FileMode.Open, FileAccess.ReadWrite, FileShare.Read))
        {
            fs.Lock(0, fs.Length);
            excelPackage.SaveAs(fs);
            try
            {
                fs.Unlock(0, fs.Length); // this raises an exception if fs unlocked already by itself
            }
            catch (IOException ex) when (ex.Message.ToLower().StartsWith("the segment is already unlocked.",
                StringComparison.InvariantCultureIgnoreCase))
            {
                // NOP; just ignore if already unlocked
            }
        }
        i++;
    }
}

READ

//simply populate a list reading excel
var fileLocation = "D:\\Book.xlsx";
FileInfo fi = new FileInfo(fileLocation);
List<string> list = new List<string>();
ExcelWorksheet worksheet = null;
int i = 1;
while (1 == 1)  //ALERT: an infinite loop!
{
    try
    {
        using (ExcelPackage excelPackage = new ExcelPackage(fi))
        {
            worksheet = excelPackage.Workbook.Worksheets["Sheet1"];
            if (worksheet.Cells[i, 1].Value != null)
            {
                list.Add(worksheet.Cells[i, 1].Value.ToString());
            }

            Console.WriteLine(worksheet.Dimension.Rows.ToString()); // just prove that it read
        }
    }

    catch (Exception ex) when (
        ex is IOException &&
        ex.Message.StartsWith("The process cannot access the file because another process has locked a portion of the file.", StringComparison.InvariantCultureIgnoreCase))
    {
        Console.WriteLine($"Attempt: {i}");
    }

    list.Clear();
}

in the actual app's code I set the limit of READ's WHILE to 3 to retry twice if first read attempt failed. That proves to be more than enough in my case (as the WRITEs are short; append a row at a time) and the app is running well over a month.

Answer 2

You can not read and write same file concurrently as its get locked when you are reading it. You can read the entire file into memory and then you can process it and after processing you can write it back.