Excel.SettableCellProperties interface

Package:

excel

Represents the input parameter of setCellProperties.

API set: ExcelApi 1.9

Remarks

Used by

• Excel.Range: setCellProperties

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/cell-properties.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    const detailedFormatProps: Excel.SettableCellProperties = {
        format: {
            borders: {
                diagonalDown: {
                    color: "#C00000",
                    style: Excel.BorderLineStyle.dash,
                    weight: Excel.BorderWeight.medium
                }
            },
            fill: {
                color: "#D9EAF7",
                pattern: Excel.FillPattern.lightDown,
                patternColor: "#4472C4"
            },
            font: {
                bold: true,
                color: "#1F1F1F",
                name: "Arial",
                size: 12
            },
            horizontalAlignment: Excel.HorizontalAlignment.center,
            protection: {
                formulaHidden: false,
                locked: false
            },
            textOrientation: 15,
            verticalAlignment: Excel.VerticalAlignment.center,
            wrapText: true
        }
    };

    const cell = sheet.getRange("C7");
    cell.setCellProperties([[detailedFormatProps]]);
    cell.format.rowHeight = 45;
    await context.sync();
});

Properties

format	Represents the format property. API set: ExcelApi 1.9
hyperlink	Represents the hyperlink property. API set: ExcelApi 1.9
style	Represents the style property. API set: ExcelApi 1.9
textRuns	Represents the textRuns property.

Property Details

format

Represents the format property.

API set: ExcelApi 1.9

format?: Excel.CellPropertiesFormat;

Property Value

Excel.CellPropertiesFormat

hyperlink

Represents the hyperlink property.

API set: ExcelApi 1.9

hyperlink?: Excel.RangeHyperlink;

Property Value

Excel.RangeHyperlink

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/cell-properties.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    const hyperlinkDetails: Excel.RangeHyperlink = {
        address: "https://learn.microsoft.com/office/dev/add-ins/excel/excel-add-ins-ranges-set-get-values",
        screenTip: "Learn about working with ranges.",
        textToDisplay: "Range documentation",
    };
    const hyperlinkProps: Excel.SettableCellProperties = {
        hyperlink: hyperlinkDetails
    };

    sheet.getRange("B7").setCellProperties([[hyperlinkProps]]);
    await context.sync();
});

style

Represents the style property.

API set: ExcelApi 1.9

style?: string;

Property Value

string

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/cell-properties.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // Create the SettableCellProperties objects for the range.
    // Create these objects once, outside the function, in your add-in.
    const topHeaderProps: Excel.SettableCellProperties = {
        // Set the style property to the name of an Excel style.
        // The `BuiltInStyle` enum lists the built-in style names.
        // A style overwrites formatting, so don't use style and format in the same object.
        style: Excel.BuiltInStyle.heading1
    };

    const headerProps: Excel.SettableCellProperties = {
        // Setting these cell properties doesn't change unspecified format subproperties.
        format: {
            fill: {
                color: "Blue"
            },
            font: {
                color: "White",
                bold: true
            }
        }
    };

    const nonApplicableProps: Excel.SettableCellProperties = {
        format: {
            fill: {
                pattern: Excel.FillPattern.gray25
            },
            font: {
                color: "Gray",
                italic: true
            }
        }
    };

    const matchupScoreProps: Excel.SettableCellProperties = {
        format: {
            borders: {
                bottom: {
                    style: Excel.BorderLineStyle.continuous
                },
                left: {
                    style: Excel.BorderLineStyle.continuous
                },
                right: {
                    style: Excel.BorderLineStyle.continuous
                },
                top: {
                    style: Excel.BorderLineStyle.continuous
                }
            },
            horizontalAlignment: Excel.HorizontalAlignment.center
        }
    };

    const range = sheet.getRange("A1:E5");

    // Use empty JSON objects to leave a cell's properties unchanged.
    range.setCellProperties([
        [topHeaderProps, {}, {}, {}, {}],
        [{}, {}, headerProps, headerProps, headerProps],
        [{}, headerProps, nonApplicableProps, matchupScoreProps, matchupScoreProps],
        [{}, headerProps, matchupScoreProps, nonApplicableProps, matchupScoreProps],
        [{}, headerProps, matchupScoreProps, matchupScoreProps, nonApplicableProps]
    ]);

    sheet.getUsedRange().format.autofitColumns();
    await context.sync();
});

textRuns

Represents the textRuns property.

textRuns?: RangeTextRun[];

Property Value

Excel.RangeTextRun[]

Remarks

API set: ExcelApi 1.18

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/cell-properties.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    const textRunFormats: Excel.RangeTextRun[] = [
        {
            text: "Rich ",
            font: {
                bold: true,
                color: "#C00000",
                size: 14
            }
        },
        {
            text: "text",
            font: {
                color: "#4472C4",
                italic: true,
                underline: Excel.RangeUnderlineStyle.single
            }
        }
    ]
    const richTextProps: Excel.SettableCellProperties = {
        textRuns: textRunFormats
    };

    sheet.getRange("A7").setCellProperties([[richTextProps]]);
    await context.sync();
});