How to create Excel table with the Microsoft Graph .NET SDK

Create Excel table

Suppose, you have an Excel file stored in your personal OneDrive (in root folder) and you would like to:

• create a table of your projects
• change the name of the table
• change the style of the table
• disable filtering of for the table

Table should look like this

Open Visual Studio or any other IDE, create a new C# console application and add the following NuGet packages (latest versions):

• Microsoft.Graph
• Azure.Identity

First, we need to get drive item from personal OneDrive

var driveId = "Me";
var filePath = "Table.xlsx"
var graphClient = new GraphServiceClient(credential);
var driveItem = await graphClient.Drives[driveId]
                    .Root
                    .ItemWithPath(filePath)
                    .GetAsync();

If you know the relative path of the file, you can use the method ItemWithPath.

Suppose that the excel file has an empty worksheet named List1 and we want to create a table in this sheet.

var worksheetName = "List1";
var tableDefinition = new Microsoft.Graph.Drives.Item.Items.Item.Workbook.Worksheets.Item.Tables.Add.AddPostRequestBody
{
    Address = "A1:G1",
    HasHeaders = true
};
var workbookTable = await graphClient.Drives[driveId]
                       .Items[driveItem.Id]
                       .Workbook
                       .Worksheets[worksheetName]
                       .Tables
                       .Add
                       .PostAsync(tableDefinition);

AddPostRequestBody has the property Address set to A1:G1 and HasHeaders set to true. It means that the table has headers and contains seven columns.

Now, we want to update name and style, and hide filter button of the table.

workbookTable.Name = "MyTable";
workbookTable.Style = "TableStyleMedium7";
workbookTable.ShowFilterButton = false;
await graphClient.Drives[driveId]
    .Items[driveItem.Id]
    .Workbook
    .Tables[workbookTable.Id]
    .PatchAsync(workbookTable);

Allowed values for the property Style are:

• TableStyleLight1 through TableStyleLight21
• TableStyleMedium1 through TableStyleMedium28
• TableStyleDark1 through TableStyleDark11

To update table headers, update table's columns names:

var headers = new[] { "Project ID", "Project Name", "Start", "Completed", "Dev.costs", "Revenue", "Profit" };
for (var index = 0; index < headers.Length; index++)
{
    var tableColumn = new WorkbookTableColumn
    {
        Name = headers[index]
    };
    await graphClient.Drives[driveId]
       .Items[driveItem.Id]
       .Workbook
       .Worksheets[worksheetName]
       .Tables[workbookTable.Id]
       .Columns[$"{index + 1}"]
       .PatchAsync(tableColumn);
}

Be aware that updating table's columns names one by one can take several seconds. Currently, there is no way to update columns names in a batch.

Finally, we can add table's rows.

var rowIndex = 0;
var body = new Microsoft.Graph.Drives.Item.Items.Item.Workbook.Tables.Item.Rows.Add.AddPostRequestBody
{
    // collection of rows
    Values = new UntypedArray(new List<UntypedArray>
    {
        // first row, collection of columns
        new UntypedArray(new List<UntypedNode>
        {
            // value for first row and first column in the table
            new UntypedInteger(++rowIndex),
            // value for first row and second column in the table
            new UntypedString("Tundra"),
            // value for first row and third column in the table
            new UntypedString("01/01/2020"),
            // value for first row and fourth column in the table
            new UntypedBoolean(true),
            // value for first row and fifth column in the table
            new UntypedString("$ 25000"),
            // value for first row and sixth column in the table
            new UntypedString("$ 50000"),
            // value for first row and seventh column in the table
            new UntypedString($"=F{rowIndex + 1}-E{rowIndex + 1}")
        }),
        // second row
        new UntypedArray(new List<UntypedNode>
        {
            new UntypedInteger(++rowIndex),
            new UntypedString("Manta"),
            new UntypedString("02/02/2021"),
            new UntypedBoolean(true),
            new UntypedString("$ 10500"),
            new UntypedString("$ 682000"),
            new UntypedString($"=F{rowIndex + 1}-E{rowIndex + 1}")
        }),
        // third row
        new UntypedArray(new List<UntypedNode>
        {
            new UntypedInteger(++rowIndex),
            new UntypedString("EDIS"),
            new UntypedString("03/03/2023"),
            new UntypedBoolean(false),
            new UntypedString("$ 66000"),
            new UntypedString("$ 10000"),
            new UntypedString($"=F{rowIndex + 1}-E{rowIndex + 1}")
        }),
        // fourth row
        new UntypedArray(new List<UntypedNode>
        {
            new UntypedInteger(++rowIndex),
            new UntypedString("SkyNet"),
            new UntypedString("06/01/2024"),
            new UntypedBoolean(false),
            new UntypedString("$ 500000"),
            new UntypedString("$ 0"),
            new UntypedString($"=F{rowIndex + 1}-E{rowIndex + 1}")
        })
    })
};
await graphClient.Drives[driveId]
    .Items[driveItem.Id]
    .Workbook
    .Tables[workbookTable.Id]
    .Rows
    .Add
    .PostAsync(body);

Be aware that the class AddPostRequestBody for adding table's rows has the same name as the class for creating table. Both classes are from different namespaces.

The property Values in AddPostRequestBody is a two-dimensional array of unformatted values of the table rows. The @odata type of the property Values is microsoft.graph.Json and represents data returned in JSON format. In Microsoft Graph .NET SDK, microsoft.graph.Json is represented by a class UntypedNode. The class UntypedNode is a base class for the following classes:

• UntypedInteger: represents an untyped node with integer value
• UntypedLong: represents an untyped node with long value
• UntypedDecimal: represents an untyped node with decimal value
• UntypedFloat: represents an untyped node with float value
• UntypedDouble: represents an untyped node with double value
• UntypedString: represents an untyped node with text
• UntypedBoolean: represents an untyped node with true/false
• UntypedArray: represents a collection of untyped nodes
• UntypedNull: represents an untyped node without the value

When working with the workbook API, excel range values are represented as a two-dimensional array. You can expect that the Microsoft Graph .NET SDK will represent range values as UntypedArray