This article describes how to use Excel workbooks and charts APIs in Microsoft Graph to update the properties of the rangeFormat, rangeFill, and rangeFont properties of a specified range.
The result of this set of requests is a table with three cells formatted like the top three cells in the following image.
Example 1: Cell 1 alignment and height
The following request updates the vertical alignment, row height, and column height of the first cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$A$1')/format
Content-type: application/json
{
"columnWidth": 135,
"verticalAlignment": "Top",
"rowHeight": 49,
"wrapText": false
}
// Code snippets are only available for the latest version. Current version is 5.x
// Dependencies
using Microsoft.Graph.Models;
var requestBody = new WorkbookRangeFormat
{
ColumnWidth = 135d,
VerticalAlignment = "Top",
RowHeight = 49d,
WrapText = false,
};
// To initialize your graphClient, see https://learn.microsoft.com/en-us/graph/sdks/create-client?from=snippets&tabs=csharp
var result = await graphClient.Drives["{drive-id}"].Items["{driveItem-id}"].Workbook.Worksheets["{workbookWorksheet-id}"].Range().Format.PatchAsync(requestBody);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
mgc drives items workbook worksheets range-with-address format patch --address {address-id} --drive-id {drive-id} --drive-item-id {driveItem-id} --workbook-worksheet-id {workbookWorksheet-id} --body '{\
"columnWidth": 135,\
"verticalAlignment": "Top",\
"rowHeight": 49,\
"wrapText": false\
}\
'
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
// Code snippets are only available for the latest major version. Current major version is $v1.*
// Dependencies
import (
"context"
msgraphsdk "github.com/microsoftgraph/msgraph-sdk-go"
graphmodels "github.com/microsoftgraph/msgraph-sdk-go/models"
//other-imports
)
requestBody := graphmodels.NewWorkbookRangeFormat()
columnWidth := float64(135)
requestBody.SetColumnWidth(&columnWidth)
verticalAlignment := "Top"
requestBody.SetVerticalAlignment(&verticalAlignment)
rowHeight := float64(49)
requestBody.SetRowHeight(&rowHeight)
wrapText := false
requestBody.SetWrapText(&wrapText)
// To initialize your graphClient, see https://learn.microsoft.com/en-us/graph/sdks/create-client?from=snippets&tabs=go
format, err := graphClient.Drives().ByDriveId("drive-id").Items().ByDriveItemId("driveItem-id").Workbook().Worksheets().ByWorkbookWorksheetId("workbookWorksheet-id").RangeWithAddress(&address).Format().Patch(context.Background(), requestBody, nil)
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFormat = {
columnWidth: 135,
verticalAlignment: 'Top',
rowHeight: 49,
wrapText: false
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$A$1')/format')
.update(workbookRangeFormat);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"columnWidth": 135,
"horizontalAlignment": "General",
"rowHeight": 49,
"verticalAlignment": "Top",
"wrapText": false
}
Example 2: Cell 1 font style, size, and color
The following request updates the font style, size, and color of the first cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$A$1')/format/font
Content-type: application/json
{
"bold": true,
"color": "#4B180E",
"size": 26
}
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFont = {
bold: true,
color: '#4B180E',
size: 26
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$A$1')/format/font')
.update(workbookRangeFont);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"bold": true,
"color": "#4B180E",
"italic": false,
"name": "Calibri",
"size": 26,
"underline": "None"
}
Example 3: Cell 1 background color
The following request updates the background color of the first cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$A$1')/format/fill
Content-type: application/json
{
"color": "#FF0000"
}
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFill = {
color: '#FF0000'
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$A$1')/format/fill')
.update(workbookRangeFill);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"color": "#FF0000"
}
Example 4: Cell 2 alignment and height
The following request updates the vertical alignment, horizontal alignment, row height, and column height of the second cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$B$1')/format
Content-type: application/json
{
"columnWidth": 135,
"horizontalAlignment": "Center",
"verticalAlignment": "Center",
"rowHeight": 49,
"wrapText": false
}
// Code snippets are only available for the latest version. Current version is 5.x
// Dependencies
using Microsoft.Graph.Models;
var requestBody = new WorkbookRangeFormat
{
ColumnWidth = 135d,
HorizontalAlignment = "Center",
VerticalAlignment = "Center",
RowHeight = 49d,
WrapText = false,
};
// To initialize your graphClient, see https://learn.microsoft.com/en-us/graph/sdks/create-client?from=snippets&tabs=csharp
var result = await graphClient.Drives["{drive-id}"].Items["{driveItem-id}"].Workbook.Worksheets["{workbookWorksheet-id}"].Range().Format.PatchAsync(requestBody);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
mgc drives items workbook worksheets range-with-address format patch --address {address-id} --drive-id {drive-id} --drive-item-id {driveItem-id} --workbook-worksheet-id {workbookWorksheet-id} --body '{\
"columnWidth": 135,\
"horizontalAlignment": "Center",\
"verticalAlignment": "Center",\
"rowHeight": 49,\
"wrapText": false\
}\
'
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
// Code snippets are only available for the latest major version. Current major version is $v1.*
// Dependencies
import (
"context"
msgraphsdk "github.com/microsoftgraph/msgraph-sdk-go"
graphmodels "github.com/microsoftgraph/msgraph-sdk-go/models"
//other-imports
)
requestBody := graphmodels.NewWorkbookRangeFormat()
columnWidth := float64(135)
requestBody.SetColumnWidth(&columnWidth)
horizontalAlignment := "Center"
requestBody.SetHorizontalAlignment(&horizontalAlignment)
verticalAlignment := "Center"
requestBody.SetVerticalAlignment(&verticalAlignment)
rowHeight := float64(49)
requestBody.SetRowHeight(&rowHeight)
wrapText := false
requestBody.SetWrapText(&wrapText)
// To initialize your graphClient, see https://learn.microsoft.com/en-us/graph/sdks/create-client?from=snippets&tabs=go
format, err := graphClient.Drives().ByDriveId("drive-id").Items().ByDriveItemId("driveItem-id").Workbook().Worksheets().ByWorkbookWorksheetId("workbookWorksheet-id").RangeWithAddress(&address).Format().Patch(context.Background(), requestBody, nil)
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFormat = {
columnWidth: 135,
horizontalAlignment: 'Center',
verticalAlignment: 'Center',
rowHeight: 49,
wrapText: false
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$B$1')/format')
.update(workbookRangeFormat);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"columnWidth": 135,
"horizontalAlignment": "Center",
"rowHeight": 49,
"verticalAlignment": "Center",
"wrapText": false
}
Example 5: Cell 2 font style and size
The following request updates the font style and size of the second cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$B$1')/format/font
Content-type: application/json
{
"italic": true,
"size": 26
}
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFont = {
italic: true,
size: 26
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$B$1')/format/font')
.update(workbookRangeFont);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"bold": false,
"color": "#000000",
"italic": true,
"name": "Calibri",
"size": 26,
"underline": "None"
}
Example 6: Cell 2 background color
The following request updates the background color of the second cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$B$1')/format/fill
Content-type: application/json
{
"color": "#00FF00"
}
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFill = {
color: '#00FF00'
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$B$1')/format/fill')
.update(workbookRangeFill);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"color": "#00FF00"
}
Example 7: Cell 3 alignment and height
The following request updates the horizontal alignment, vertical alignment, row height, and column height of the third cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$C$1')/format
Content-type: application/json
{
"columnWidth": 135,
"horizontalAlignment": "Right",
"verticalAlignment": "Top",
"rowHeight": 49,
"wrapText": false
}
// Code snippets are only available for the latest version. Current version is 5.x
// Dependencies
using Microsoft.Graph.Models;
var requestBody = new WorkbookRangeFormat
{
ColumnWidth = 135d,
HorizontalAlignment = "Right",
VerticalAlignment = "Top",
RowHeight = 49d,
WrapText = false,
};
// To initialize your graphClient, see https://learn.microsoft.com/en-us/graph/sdks/create-client?from=snippets&tabs=csharp
var result = await graphClient.Drives["{drive-id}"].Items["{driveItem-id}"].Workbook.Worksheets["{workbookWorksheet-id}"].Range().Format.PatchAsync(requestBody);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
mgc drives items workbook worksheets range-with-address format patch --address {address-id} --drive-id {drive-id} --drive-item-id {driveItem-id} --workbook-worksheet-id {workbookWorksheet-id} --body '{\
"columnWidth": 135,\
"horizontalAlignment": "Right",\
"verticalAlignment": "Top",\
"rowHeight": 49,\
"wrapText": false\
}\
'
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
// Code snippets are only available for the latest major version. Current major version is $v1.*
// Dependencies
import (
"context"
msgraphsdk "github.com/microsoftgraph/msgraph-sdk-go"
graphmodels "github.com/microsoftgraph/msgraph-sdk-go/models"
//other-imports
)
requestBody := graphmodels.NewWorkbookRangeFormat()
columnWidth := float64(135)
requestBody.SetColumnWidth(&columnWidth)
horizontalAlignment := "Right"
requestBody.SetHorizontalAlignment(&horizontalAlignment)
verticalAlignment := "Top"
requestBody.SetVerticalAlignment(&verticalAlignment)
rowHeight := float64(49)
requestBody.SetRowHeight(&rowHeight)
wrapText := false
requestBody.SetWrapText(&wrapText)
// To initialize your graphClient, see https://learn.microsoft.com/en-us/graph/sdks/create-client?from=snippets&tabs=go
format, err := graphClient.Drives().ByDriveId("drive-id").Items().ByDriveItemId("driveItem-id").Workbook().Worksheets().ByWorkbookWorksheetId("workbookWorksheet-id").RangeWithAddress(&address).Format().Patch(context.Background(), requestBody, nil)
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFormat = {
columnWidth: 135,
horizontalAlignment: 'Right',
verticalAlignment: 'Top',
rowHeight: 49,
wrapText: false
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$C$1')/format')
.update(workbookRangeFormat);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"columnWidth": 135,
"horizontalAlignment": "Right",
"rowHeight": 49,
"verticalAlignment": "Top",
"wrapText": false
}
Example 8: Cell 3 font style, size, and color
The following request updates the font style, size, and color of the third cell.
Note: The underline property takes Single or Double as values.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$C$1')/format/font
Content-type: application/json
{
"underline": "Single",
"color": "#FFFFFF",
"size": 26
}
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFont = {
underline: 'Single',
color: '#FFFFFF',
size: 26
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$C$1')/format/font')
.update(workbookRangeFont);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"bold": false,
"color": "#FFFFFF",
"italic": false,
"name": "Calibri",
"size": 26,
"underline": "Single"
}
Example 9: Cell 3 background color
The following request updates the background color of the third cell.
Request
PATCH https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$C$1')/format/fill
Content-type: application/json
{
"color": "#0000FF"
}
const options = {
authProvider,
};
const client = Client.init(options);
const workbookRangeFill = {
color: '#0000FF'
};
await client.api('/me/drive/items/{id}/workbook/worksheets/Sheet1/range(address='$C$1')/format/fill')
.update(workbookRangeFill);
Read the SDK documentation for details on how to add the SDK to your project and create an authProvider instance.
Response
The following example shows the response.
Note: The response object shown here might be shortened for readability.
HTTP/1.1 200 OK
Content-type: application/json
{
"color": "#0000FF"
}
Related content