I'm working with SharePoint Client Object Model and EPPlus in C# to edit an Excel file retrieved from SharePoint. While fetching the file and saving it back work fine using OpenBinaryDirect and SaveBinaryDirect, I encounter an issue where Worksheets[0] is empty, preventing any edits.
Here's the simplified code:
// Fetch the file
var filePathSource = "/sites/.../file.xlsx";
FileInformation fileInformation = Microsoft.SharePoint.Client.File.OpenBinaryDirect(ctx, filePathSource);
// Attempt to edit with EPPlus
using (var package = new ExcelPackage(fileInformation.Stream))
{
if (package.Workbook.Worksheets.Count > 0) {
var worksheet = package.Workbook.Worksheets[0];
worksheet.Cells[1, 2].Value = "New Value"; // Edit attempt
} else {
// The issue: Worksheet[0] is empty or not accessible
Console.WriteLine("Worksheet is empty.");
}
using (var editedStream = new MemoryStream())
{
package.SaveAs(editedStream);
editedStream.Position = 0; // Prepare for upload
// Save back to SharePoint
var filePathDest = "/sites/.../editedFile.xlsx";
Microsoft.SharePoint.Client.File.SaveBinaryDirect(ctx, filePathDest, editedStream, true);
}
}
Despite the file being successfully fetched and saved, Worksheets[0] appears empty, and I'm unable to make any edits.
Question: How can I access and edit the Excel file's content when Worksheets[0] comes up empty? Is there a step I'm missing or a common pitfall with EPPlus and SharePoint integration?
Looking for guidance on this issue. Thanks!
Edit: When I save it locally it works fine. But in my case I can't do it. It all should be stored in the memory.