How to Write/Store data in an Excel spreadsheet that is located at Share Point?

Question

I am working on writing or storing the data into Excel spreadsheet that is located at a Share Point site. I am able to connect to the Share Point site and obtaining the excel spreadsheet by passing the relative URL. Using Memory Stream, the contents of the excel are read and the worksheet and the name is displayed which means Share point is fine. however, I am not able to write the data into the excel spreadsheet and I get the following error.

error saving data to sharepoint: cannot access a closed stream.

My function here:

public bool insertTestRecords()
 {
     string url = "<relative_url_to_excelsheet>/sample.xlsx";
     SpClient.File dataSheet = M_clientContext.Web.GetFileByServerRelativeUrl(url);
     M_clientContext.Load(dataSheet);
     M_clientContext.ExecuteQuery();
     using (var memoryStream = new MemoryStream())
     {
         var stream = dataSheet.OpenBinaryStream();
         M_clientContext.ExecuteQuery();
         stream.Value.CopyTo(memoryStream);

         // Important: Do not close the stream or it will become inaccessible.
         memoryStream.Position = 0;  // Rewind the stream

         ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

         bool isRecorded = false;

         // Read the Excel file using EPPlus
         using (var package = new ExcelPackage(memoryStream))
         {
             var worksheet = package.Workbook.Worksheets[0]; // Assuming data is in the first worksheet
             int rowCount = worksheet.Dimension.Rows;

             Console.WriteLine("Row Count: " + rowCount);
             Console.WriteLine("Work sheet name: " + package.Workbook.Worksheets[0].Name); 

             // Insert new data in the next empty row
             int newRow = rowCount + 1;
             worksheet.Cells[newRow, 1].Value = "data_1";
             worksheet.Cells[newRow, 2].Value = "data_2";
			 worksheet.Cells[newRow, 2].Value = "data_3";
             package.Save();
         }
         
         // Rewind the memory stream before writing it back to SharePoint
         memoryStream.Position = 0;
         try
         {
             // Write the updated Excel file back to SharePoint
             dataSheet.SaveBinary(new FileSaveBinaryInformation()
             {
                 ContentStream = memoryStream
             });
             M_clientContext.ExecuteQuery();

             Console.WriteLine("Data saved to SharePoint successfully.");

             // Re-load the worksheet to verify the insertion

             memoryStream.Position = 0;  // Rewind the stream again
             using (var package = new ExcelPackage(memoryStream))
             {
                 var worksheet = package.Workbook.Worksheets[0];
                 int rowCount = worksheet.Dimension.Rows;
                 // Verify the data in the last row
                 if (worksheet.Cells[rowCount, 1].Text == data_1)
                 {
                     isRecorded = true;
                 }
             }
         }
         catch (Exception ex)
         {
             Trace.WriteLine($"Error saving data to SharePoint: {ex.Message}");
         }
         return true;
     }
 }

Also, if I remove the reloading of memory stream logic and just insert the record using package and worksheet I could see that the file was modified few seconds/minutes ago after executing the application but the data are not seen in the excel sheet. the spreadsheet is blank.

Where I went wrong? Can some one guide me here? Thanks in Advance!

Answer 1

Hi @Lakshmanan B,

The using statement will close the MemoryStream automatically. You need to move the code in the using() statement. Here is a similar issue with yours, please make a reference

https://stackoverflow.com/questions/10934585/memorystream-cannot-access-a-closed-stream

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.