● Code · Friday, June 30, 2017

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

By Robert Ellison. 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 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.

Related Posts

Add Comment