Is it possible to read/store/retrieve data from Excel spreadsheet from Share point site to WPF application?

Question

Is it possible to read/store/retrieve data from Excel spreadsheet from Share point site to WPF application?

Lakshmanan Balasubramanian 121

Hi,

I have connected to Share Point site referring the following blog: https://www.c-sharpcorner.com/article/sharepoint-csom-for-net-standard/

I have created a sample test spreadsheet in the Documents Library named *testSpreadSheet.xlsx
*
I have a quite a few columns in it say: Employee.No, Name, Age, Designation, Department.

Is it possible to retrieve a record like if I provide name of an employee in the C# code and read that record?

Also, If name of the employee is provided, Get the Designation of the employee?

I know, this is easier with Share Point List or SQL database, but I have a requirement where I wanted to read data from Excel spreadsheet that is stored in Share point. Any blog or documentation would be helpful!

Thanks in Advance!

Lakshmanan Balasubramanian 121 Reputation points

2024-08-27T14:40:05.22+00:00

Thanks it worked.

Accepted answer

1 additional answer

Your answer

Lakshmanan Balasubramanian 121 Reputation points

2024-08-27T14:40:05.22+00:00

Thanks it worked.

Answer 1

Asha Kanta Sharma 461

Yes, it is possible to read, store, and retrieve data from an Excel spreadsheet stored in a SharePoint site within a WPF application. Use the SharePoint Client Object Model to authenticate and download the file into a stream. Use EPPlus or another library to open the Excel file and retrieve the data.

Lakshmanan Balasubramanian 121

Hi Thank you for response on the Read part! I am having issues with Writing records into an existing spread sheet in Share point. The problem I am facing is that, the code which I have for inserting works and does not throw any error. Upon checking in Share Point I can see that the "Modified" value is updated by "A few seconds ago" immediately after executing logic but I am not able to see that Records in the sheet.

Image showing Modified status

Here is the code snippet:

 using (var memoryStream = new MemoryStream())
 {
     var stream = dataSheet.OpenBinaryStream();
     M_clientContext.ExecuteQuery();
     stream.Value.CopyTo(memoryStream);
     memoryStream.Position = 0;

     ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

     // 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("Before: " + worksheet.GetValue(rowCount, 1));

         int newRow = rowCount + 1;
         worksheet.Cells[newRow, 1].Value = "1234";
         worksheet.Cells[newRow, 2].Value = "ABC";
         worksheet.Cells[newRow, 3].Value = "20.09.2024";

         memoryStream.Position = 0;
         package.SaveAs(memoryStream);
     }  
     memoryStream.Position = 0;
 
     dataSheet.SaveBinary(new FileSaveBinaryInformation() {
         ContentStream = memoryStream
     });
     M_clientContext.ExecuteQuery();
 }

Here, Console.WriteLine("Before: " + worksheet.GetValue(rowCount, 1)); prints the last inserted record's value. Which confirms that the data are inserted however I am not able to see those data in the Spread sheet. Moreover, when I try to open the sheet it threw the following error!

cannot open workbook

When I download the file to local PC and open it with Excel, it shows the file has been corrupted. Upon repairing, I am able to see the records I have inserted.

Am I doing anything wrong with my logic above?

TIA!

Answer 2

RaytheonXie_MSFT 40,471 Microsoft External Staff

Hi @Lakshmanan B,

Per my research, there is no such function to read excel data in SharePoint by CSOM currently. AS a workaround, I would recommend you to use Graph api. You could refer to the following api

https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/
https://graph.microsoft.com/v1.0/me/drive/root:/{item-path}:/workbook/

Here is the document for reference

https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

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.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Lakshmanan Balasubramanian 121 Reputation points

2024-08-27T14:41:43.67+00:00

Thanks for your suggestion. However I have followed the comment by @Asha Kanta Sharma as I have already authenticated and connected with Share Point CSOM. It was easy to read the data using EPPlus library.
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2024-08-28T01:26:35.86+00:00

Hi @Lakshmanan B,

Thanks for your time in advance. I have convert @Asha Kanta Sharma's comment to answer. Could you kindly accept his answer via the "Accept Answer" button, it would be helpful to others who have similar issue in the future.

Share via

Is it possible to read/store/retrieve data from Excel spreadsheet from Share point site to WPF application?

1 additional answer

Your answer