c# - 并发读取和写入 Excel 文件
问题描述
是否有可能只有一个进程执行 WRITE 而许多进程对 excel 文件执行 READ 操作?我为此使用 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();
}
解决方案
我稍微改变了我的代码,让 WRITE 程序在写入之前锁定文件,并在发生故障时使 READ 具有弹性:
WRITE:在尝试写入之前使用 FileStream 并锁定它。这将防止 WRITE 失败
阅读:添加了实现 try/catch 块的重试机制
修改后的代码:
写
// 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 很短;一次追加一行)并且应用程序运行了一个多月。
推荐阅读
- calendar - autosys 为第七个业务日期的月度工作
- python - Scrapy:如何判断 robots.txt 是否存在
- javascript - 通过单击按钮将模型中的数据加载到 HTML 文件中
- python - IndexError:Python中的列表分配索引超出范围
- oracle - select into causes error ORA-00933: SQL command not properly ended
- snowflake-cloud-data-platform - 在表格中输入数据时运行小脚本
- php - socket_read(): 无法从套接字读取 [10060]
- javascript - 没有类的表单组件内的主要反应数据表
- networking - Raspberry Pi 4 - IP 可以通过 DHCP 获得,但未分配给 dev eth0
- python - 使用 NamedTemporaryFile 上的 ffprobe 拒绝 Python 权限