Saving an ExcelPackage with exclusive lock of excel, giving error after opening excel file - C# - EPPlus

Question

My objective is to write some data into an excel.

Here i am opening a file with file stream by exclusive lock (FileMode.Open, FileShare.Read etc., I need to lock the file to restrict others writing into excel while i am processing.) then writing some content into it and finally close the stream, so that other threads can write into this file. I am using EPPlus(5.7.4) version.

The code i am using here is :

public void WriteToExcel()
{
     using (var stream = new FileStream(path, FileMode.Open, FileAccess.ReadWrite, FileShare.Read))
     using (var excelPackage = new ExcelPackage(stream))
     {
         DoSomething(excelPackage);
         excelPackage.SaveAs(stream);
         stream.Close();
     }
}

public void DoSomething(ExcelPackage excelPackage)
{
     var cell = excelPackage.Workbook.Worksheets[0].Cells[2, 3];
     cell.Value = "some value";
}

I put a break point in using statement and opened excel in the middle of execution and it showing a message saying like below which is correct.

Image, after locking excel file

But once i finish with execution when i try to open excel file it showing below error message.

We found a problem with some content in Sample.xlsx. Do you want us to try to recover as much as we can? if you trust the source of this book, Click Yes

Image, getting error message

I tried in different ways but none worked for me, as same error message is displaying. Can someone help me resolving this issue.

user_7107097 · Answer

The problem is that you're reading from and rewriting to the same file stream simultaneously.

You can test this by changing excelPackage.SaveAs(new FileInfo("Book2.xlsx")); and create a new file - your file will be created without any issues.

You could open your original document, write the changes to a new file, then delete the original file and rename the new file back to the original name:

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var stream = new FileStream("Book1.xlsx", FileMode.Open, FileAccess.ReadWrite, FileShare.Read))
using (var excelPackage = new ExcelPackage(stream))
{
    DoSomething(excelPackage);
    excelPackage.SaveAs(new FileInfo("Book2.xlsx"));
}

File.Delete("Book1.xlsx");
File.Move("Book2.xlsx", "Book1.xlsx");

The caveat with this is that if you have multiple things trying to access that file, then they might throw FileNotFound exceptions if they happen to try to open Book1.xlsx after it's delete and before Book2.xlsx is renamed.

That said, if you're dealing with that level of parallelism then you shouldn't be using a Excel file.

Side note: You don't need stream.Close(); as the using block automatically closes the stream.

user_7687666 · Answer

Below code useful to me, you can refer it.

 public void WriteToExcel()
 {
     string path = @"C:\Use**op\aa.xlsx";
     FileInfo file = new FileInfo(path);
     ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
     using (ExcelPackage package = new ExcelPackage(file))
     {
         DoSomething(package);
     }
 }

 public void DoSomething(ExcelPackage package)
 {
     ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
     worksheet.Cells[2,4].Value = "some value";
     package.Save();
 }

Saving an ExcelPackage with exclusive lock of excel, giving error after opening excel file - C# - EPPlus

2 Answers