I Thought He Came With You (ITHCWY) is Robert Ellison's blog.

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

Updated on Sunday, September 18, 2022

I needed a console app that reads some inputs from an online Excel workbook, does some processing and then writes back the results to a different worksheet. Because I enjoy pain I decided to use the thinly documented new Microsoft.Graph client library. The sample code below assumes that you have a work or education Office 365 subscription.

using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; using System.Net.Http.Headers; using System.Threading.Tasks; using Microsoft.Graph; using Microsoft.IdentityModel.Clients.ActiveDirectory; using Newtonsoft.Json.Linq; namespace Excel365Test { /// <summary> /// 1) Install Microsoft.Graph NuGet Package /// 2) Install Microsoft.IdentityModel.Clients.ActiveDirectory NuGet Package /// 3) Register app at https://portal.azure.com/ - need app ID and redirct URL below /// </summary> class Program { static void Main(string[] args) { TokenCache tokenCache = new TokenCache(); // load tokens from file string tokenPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Excel365Test"); if (!Directory.Exists(tokenPath)) { Directory.CreateDirectory(tokenPath); } tokenPath = Path.Combine(tokenPath, "tokens.dat"); if (System.IO.File.Exists(tokenPath)) { tokenCache.Deserialize(System.IO.File.ReadAllBytes(tokenPath)); } // this is the OAUTH 2.0 TOKEN ENDPOINT from https://portal.azure.com/ -> Azure Active Directory -> App Registratuons -> End Points var authenticationContext = new AuthenticationContext("https://login.windows.net/your-url-here/", tokenCache); // only prompt when needed, you'll get a UI the first time you run var platformParametes = new PlatformParameters(PromptBehavior.Auto); var authenticationResult = authenticationContext.AcquireTokenAsync("https://graph.microsoft.com/", "your-app-id", // Application ID from https://portal.azure.com/ new Uri("http://some.redirect.thing/"), // Made up redirect URL, also from https://portal.azure.com/ platformParametes).Result; string token = authenticationResult.AccessToken; // save token so we don't need to re-authorize System.IO.File.WriteAllBytes(tokenPath, tokenCache.Serialize()); // use the token with Microsoft.Graph calls GraphServiceClient client = new GraphServiceClient(new DelegateAuthenticationProvider( (requestMessage) => { requestMessage.Headers.Authorization = new AuthenticationHeaderValue("bearer", token); return Task.FromResult(0); })); // test reading from a sheet - in this case I have a test worksheet with a two column table for name/value pairs var readSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["test"]; var readTables = readSheet.Tables.Request().GetAsync().Result; string readTableId = readTables[0].Name; var table = readSheet.Tables[readTableId].Rows.Request().GetAsync().Result; // convert page to a dictionary... this doesn't handle pagination Dictionary<string, decimal> tableValues = table.CurrentPage.ToDictionary(r => r.Values.First.First.ToString(), r => Convert.ToDecimal(r.Values.First.Last, CultureInfo.InvariantCulture)); // test adding a row to a table with four columns // sadly it seems you need this exact format, a regular JArray or JObject fails WorkbookTableRow newRow = new WorkbookTableRow { Values = JArray.Parse("[[\"1\",\"2\",\"3\",\"4\"]]") }; var outputSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["data"]; var outputTables = outputSheet.Tables.Request().GetAsync().Result; string outputTableId = outputTables[0].Name; var outputResult = outputSheet.Tables[outputTableId].Rows.Request().AddAsync(newRow).Result; // the excel unit tests seem to be the most useful documentation right now: // https://github.com/microsoftgraph/msgraph-sdk-dotnet/blob/dev/tests/Microsoft.Graph.Test/Requests/Functional/ExcelTests.cs } } }

Paste the code into a new console project and then follow the instructions at the top to add the necessary NuGet packages. You'll also need to register an application at https://portal.azure.com/. You want a Native application and you'll need the Application ID and the redirect URL (just make up some non-routable URL for this). Under Required Permissions for the app you should add read and write files delegated permissions for the Microsoft Graph API.

Hope this saves you a few hours. Comment below if you need a more detailed explanation for any of the above.

Add your comment...

(All Code Posts)

code, microsoft, excel, graph, office 365

Add Comment

All comments are moderated. Your email address is used to display a Gravatar and optionally for notification of new comments and to sign up for the newsletter.

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

Related Posts

You Might Also Like

Add Comment

Newsletter

Related

Popular Posts

Recent Posts

Recent Tags