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

Lakshmanan B 81

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 B 81 Reputation points

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

Thanks it worked.

Accepted answer

Asha Kanta Sharma 456 Reputation points

2024-08-20T16:21:35.72+00:00

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.
Please sign in to rate this answer.

2 people found this answer helpful.
Lakshmanan B 81 Reputation points

2024-09-23T15:50:08.49+00:00

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.

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!

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!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

RaytheonXie_MSFT 36,171 Reputation points Microsoft Vendor

2024-08-21T06:05:43.0266667+00:00
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.
Please sign in to rate this answer.
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 B 81 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 36,171 Reputation points Microsoft Vendor

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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

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