-
-
Save martic/80177d9891b0d57e061fd2e0d259b781 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment