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

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
        }
    }
}