并发读写Excel文件

Question

是否可能只有一个进程对Excel文件执行写操作，而许多进程执行读操作？我正在使用ExcelPackage（EPPlus）进行此操作。

为了演示，我编写了两个控制台应用程序，一个用于迭代写入，另一个用于读取。同时运行它们会导致任一方失败。

写入

// 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++;
    }
}

阅读

//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();
}

- Tony Thomas

---

你期望发生什么？文件在读取或写入时被锁定，但完成后会被释放。所以我不明白你的问题。你想要实现什么？ - VDWWD

不指望有什么魔法。但如果我们能够像这里讨论的那样找到一个解决方法：https://dev59.com/nlfUa4cB1Zd3GeqPMuwX（我承认这是不同的技术），那将会很有帮助。 - Tony Thomas

Answer 1

我稍微修改了我的代码，通过让写入程序在写入之前锁定文件，并使读取程序在发生故障时具有弹性：

写入：使用FileStream并在尝试写入之前对其进行锁定。这将防止写入失败。

读取：添加了重试机制，实现了try/catch块。

修改后的代码：

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++;
    }
}

阅读

//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();
}

在实际应用程序的代码中，我设置了READ的WHILE限制为3，以便在第一次读取失败时重试两次。在我的情况下，这被证明是足够的（因为WRITE很短；每次只追加一行），并且该应用已经运行了一个多月，并且运行良好。

- Tony Thomas

Answer 2

当您正在读取文件时，无法同时读写同一文件，因为它会被锁定。您可以将整个文件读入内存，然后处理它，在处理完成后再将其写回。

- Gaurav