Formatting Excel cells with Microsoft Graph

Question

Hello. I currently develop an application that targets to write into Microsoft Excel files on Sharepoint drives. To perform this action, I use Microsoft Graph 5.91.

The application uses .NET Framework 4.8 in C#.

I’d like to perform some cells formatting actions. I wrote this function to achieve this, but I had to figure out how to mitigate the lacks of Microsoft Graph.

public bool FormatDataRange(string excel365FileId, string sheetName, string rangeA1, bool clearBackground, char backgroundRed = (char)255, char backgroundGreen = (char)255, char backgroundBlue = (char)255, char foregroundRed = (char)0, char foregroundGreen = (char)0, char foregroundBlue = (char)0, bool bold = false, bool italic = false, bool underline = false, int? fontSize = null, string horizontalAlignment = "Left", string verticalAlignment = "Top", bool autoFitColumns = false, string borderStyle = "None", string borderWeight = "Thin", char borderRed = (char)0, char borderGreen = (char)0, char borderBlue = (char)0)

{

bool result = false;

try

{

WorkbookRequestBuilder workbookRequestBuilder = driveRequestBuilder.Items[excel365FileId].Workbook; //driveRequestBuilder is of type Microsoft.Graph.Drives.Item.DriveItemRequestBuilder

HttpClient customHttpClient = new HttpClient()

{

BaseAddress = new Uri("https://graph.microsoft.com"),

Timeout = TimeSpan.FromSeconds(timeout)

};

AccessToken accessToken = credentials.GetToken(new TokenRequestContext(new string[] { "https://graph.microsoft.com/.default" }));

customHttpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken.Token);

WorkbookWorksheetItemRequestBuilder worksheetRequestBuilder = workbookRequestBuilder.Worksheets[sheetName];

Uri url = worksheetRequestBuilder.RangeWithAddress(rangeA1).Format.ToGetRequestInformation().URI;

HttpMethod patch = new HttpMethod("PATCH");

HttpRequestMessage request;

HttpResponseMessage response;

if (clearBackground)

{

request = new HttpRequestMessage(HttpMethod.Post, $"{url}/fill/clear");

response = customHttpClient.SendAsync(request).Result;

result = response.IsSuccessStatusCode;

}

else

{

request = new HttpRequestMessage(patch, $"{url}/fill")

{

Content = new StringContent($"{{'color':'#{(int)backgroundRed:X2}{(int)backgroundGreen:X2}{(int)backgroundBlue:X2}'}}", Encoding.UTF8, "application/json")

};

response = customHttpClient.SendAsync(request).Result;

result = response.IsSuccessStatusCode;

}

if (result)

{

accessToken = credentials.GetToken(new TokenRequestContext(new string[] { "https://graph.microsoft.com/.default" }));

customHttpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken.Token);

request = new HttpRequestMessage(patch, $"{url}/font")

{

Content = new StringContent($"{{'color':'#{(int)foregroundRed:X2}{(int)foregroundGreen:X2}{(int)foregroundBlue:X2}','bold':{bold.ToString().ToLower()},'italic':{italic.ToString().ToLower()},'underline':{(underline ? "'Single'" : "'None'")}{(fontSize != null ? $",'size':{fontSize}" : string.Empty)}}}", Encoding.UTF8, "application/json")

};

response = customHttpClient.SendAsync(request).Result;

result = response.IsSuccessStatusCode;

}

foreach (string edge in new string[] { "EdgeTop", "EdgeBottom", "EdgeLeft", "EdgeRight", "InsideHorizontal", "InsideVertical" })

{

if (result)

{

accessToken = credentials.GetToken(new TokenRequestContext(new string[] { "https://graph.microsoft.com/.default" }));

customHttpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken.Token);

request = new HttpRequestMessage(patch, $"{url}/borders('{edge}')")

{

Content = new StringContent($"{{'style':'{borderStyle}','weight':'{borderWeight}','color':'#{(int)borderRed:X2}{(int)borderGreen:X2}{(int)borderBlue:X2}'}}", Encoding.UTF8, "application/json")

};

response = customHttpClient.SendAsync(request).Result;

result = response.IsSuccessStatusCode;

}

}

if (autoFitColumns && result)

{

accessToken = credentials.GetToken(new TokenRequestContext(new string[] { "https://graph.microsoft.com/.default" }));

customHttpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken.Token);

request = new HttpRequestMessage(HttpMethod.Post, $"{url}/autofitColumns");

response = customHttpClient.SendAsync(request).Result;

result = response.IsSuccessStatusCode;

}

if (result)

{

WorkbookRangeFormat body = new WorkbookRangeFormat()

{

HorizontalAlignment = horizontalAlignment,

VerticalAlignment = verticalAlignment

};

WorkbookRangeFormat response1 = worksheetRequestBuilder.RangeWithAddress(rangeA1).Format.PatchAsync(body).Result;

result = response1 != null;

}

}

catch (Exception e)

{

log.Error(e.Message, e);

}

return result;

}

As you can see, I perform a lot of queries (especially for the borders, where I need one query for each border type), which is quite long.

Is there no better/more efficient way to achieve this, because I find it very long for such a purpose ? Theoretically, it could be possible to use the object « WorkbookRangeFormat » for all this stuff, but this doesn’t work (error H.T.T.P. 400 if I set something else than the horizontal and vertical alignments, as shown at the end of the code).
Thanks in advance.

Answer 1

Hi @Augustin Chevrier 

Thank you for posting your question in the Microsoft Q&A forum. 

The Microsoft Graph Excel API defines separate formatting objects for each aspect of a range, including fill, font, borders, and alignment. However, only a subset of these formatting operations is accessible through REST endpoints. 

Currently, the WorkbookRangeFormat resource supports only a limited set of properties for PATCH requests, such as horizontalAlignment and verticalAlignment. Other formatting elements, such as fill color, font styling, and borders, are modeled as navigation properties under /format, each with its own set of operations. 

Although there is no unified formatting endpoint available in Graph v1.0 or beta, you can reduce network overhead by batching multiple supported requests into a single HTTP call using Combine multiple HTTP requests using JSON batching - Microsoft Graph | Microsoft Learn.

Here’s an example: 

{
  "requests": [
    {
      "id": "1",
      "method": "POST",
      "url": "/me/drive/items/{fileId}/workbook/worksheets/{sheetName}/range(address='{rangeA1}')/format/fill/clear",
      "headers": { "Content-Type": "application/json" }
    },
    {
      "id": "2",
      "method": "PATCH",
      "url": "/me/drive/items/{fileId}/workbook/worksheets/{sheetName}/range(address='{rangeA1}')/format/borders('EdgeBottom')",
      "body": { "style": "Continuous", "weight": "Medium", "color": "#000000" },
      "headers": { "Content-Type": "application/json" }
    },
    {
      "id": "3",
      "method": "PATCH",
      "url": "/me/drive/items/{fileId}/workbook/worksheets/{sheetName}/range(address='{rangeA1}')/format",
      "body": { "horizontalAlignment": "Left", "verticalAlignment": "Top" },
      "headers": { "Content-Type": "application/json" }
    }
  ]
}

Borders are represented as individual edge objects, such as EdgeTop, EdgeBottom, and others. Each edge requires a separate request unless you include them in a batch. 

I hope this explanation helps clarify the formatting capabilities. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.