How to read data from an Excel file in SharePoint using CSOM

Question

How to obtain data from a spreadsheet in SharePoint using the Graph API. I already have an App in Azure AD with the necessary permissions, and I have the clientId, tenantId, and clientSecret. The permissions in Azure AD are correct. I have tested it using Postman and was able to access the spreadsheet data.

However, when using the code below for testing, the context.ExecuteQuery() is returning a 401 error.

string clientId = "xxxxx";
string clientSecret = "xxxxx";
string tenantId = "xxxxxx";

string siteUrl = "https://xxxx.sharepoint.com/sites/ProjetoDSS";
string driveName = "BKP";
string fileName = "bkp_2023-10-05_13_05_29.xlsb";

string authority = $"https://login.microsoftonline.com/{tenantId}";
var appConfidential = ConfidentialClientApplicationBuilder.Create(clientId)
    .WithClientSecret(clientSecret)
    .WithAuthority(new Uri(authority))
    .Build();

ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

var authResult = appConfidential.AcquireTokenForClient(new[] { "https://graph.microsoft.com/.default" })
    .ExecuteAsync().GetAwaiter().GetResult();

using (var context = new ClientContext(siteUrl))
{
    context.AuthenticationMode = ClientAuthenticationMode.Default;
    context.ExecutingWebRequest += (sender, e) =>
    {
        e.WebRequestExecutor.RequestHeaders["Authorization"] = "Bearer " + authResult.AccessToken;
    };

    Web web = context.Web;
    context.Load(web);

    try
    {
        context.Load(web, a => a.ServerRelativeUrl);

        context.ExecuteQuery();

        FileInformation fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(context, "/sites/ProjetoDSS/11_10_2023_08_51.xlsb");
        context.ExecuteQuery();

        var filePath = @"c:\abc\Test\11_10_2023_08_51.xlsb";
        using (var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create))
        {
            fileInfo.Stream.CopyTo(fileStream);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Erro: {ex.Message}");
    }
}

Answer 1

Currently there is no such function to read excel file in SharePoint by CSOM. 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