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...
Related Posts
You Might Also Like
(All Code Posts )
