Adjust worksheet display settings

Excel is often used for reporting scenarios where you want to share worksheet data with others. Your Office Add-in can reduce visual clutter and help focus attention by controlling the appearance of the worksheet. The Office JavaScript API supports changing several visual aspects of the worksheet.

Page layout and print settings

Add-ins have access to page layout settings at a worksheet level. These control how the sheet is printed. A Worksheet object has three layout-related properties: horizontalPageBreaks, verticalPageBreaks, and pageLayout.

Worksheet.horizontalPageBreaks and Worksheet.verticalPageBreaks are PageBreakCollection objects. These are collections of PageBreak objects, which specify ranges where manual page breaks are inserted. The following code sample adds a horizontal page break before row 21.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.horizontalPageBreaks.add("A21:E21"); // The page break precedes this range.
    await context.sync();
});

Worksheet.pageLayout is a PageLayout object. This object contains layout and print settings that aren't dependent on any printer-specific implementation. These settings include margins, orientation, page numbering, title rows, and print area. The following code sample centers the page (both vertically and horizontally), sets a title row to be printed at the top of every page, and sets the printed area to a subsection of the worksheet.

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

    // Center the page in both directions.
    sheet.pageLayout.centerHorizontally = true;
    sheet.pageLayout.centerVertically = true;

    // Set the first row as the title row for every page.
    sheet.pageLayout.setPrintTitleRows("$1:$1");

    // Limit the area to be printed to the range "A1:D100".
    sheet.pageLayout.setPrintArea("A1:D100");

    await context.sync();
});

Turn data type icons on or off (preview)

Note

To use this feature, you must use the preview version of the Office JavaScript API library from the Office.js content delivery network (CDN). The type definition file for TypeScript compilation and IntelliSense is found at the CDN and DefinitelyTyped. You can install these types with npm install --save-dev @types/office-js-preview.

For more information on our upcoming APIs, please visit Excel JavaScript API requirement sets.

Data types can display an icon next to the value in the cell. When you have large tables with many data types, the icons may add visual clutter.

Use the Worksheet.showDataTypeIcons property to toggle data type icons on or off. For more information about data types and their icons, see Overview of data types in Excel add-ins. The showDataTypeIcons property performs the same action as the user toggling data type icons by using the View > Data Type Icons checkbox. The visibility settings for data type icons are saved with the worksheet and are seen by anyone co-authoring at the time they are changed.

The following code sample shows how to turn off data type icons on a worksheet.

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

Note

If a linked data type displays a ? icon, this can’t be toggled on or off. Excel needs the user to disambiguate the cell value to find the correct data type. For more information, see Excel data types: Stocks and geography.

Show or hide the worksheet gridlines (preview)

Note

To use this feature, you must use the preview version of the Office JavaScript API library from the Office.js content delivery network (CDN). The type definition file for TypeScript compilation and IntelliSense is found at the CDN and DefinitelyTyped. You can install these types with npm install --save-dev @types/office-js-preview.

For more information on our upcoming APIs, please visit Excel JavaScript API requirement sets.

Gridlines are the faint lines that appear between cells on a worksheet. These can be distracting if you use shapes, icons, or have specific line and border formats on data.

Turn the gridlines on or off with the Worksheet.showGridlines property. This is the same as using the View > Gridlines checkbox in the Excel UI. The visibility settings for gridlines are saved with the worksheet and are seen by anyone co-authoring at the time they are changed.

The following example shows how to turn off gridlines on a worksheet.

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

Toggle headings (preview)

Note

To use this feature, you must use the preview version of the Office JavaScript API library from the Office.js content delivery network (CDN). The type definition file for TypeScript compilation and IntelliSense is found at the CDN and DefinitelyTyped. You can install these types with npm install --save-dev @types/office-js-preview.

For more information on our upcoming APIs, please visit Excel JavaScript API requirement sets.

Headings are the Excel row numbers that appear on the left side of the worksheet (1, 2, 3) and the column letters that appear at the top of the worksheet (A, B, C). The user may not want these in their report.

Turn the headings on or off with the Worksheet.showHeadings property. This is the same as using the View > Headings checkbox in the Excel UI. The following example shows how to turn headings off on a worksheet.

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

See also

• Excel JavaScript object model in Office Add-ins
• Work with worksheets using the Excel JavaScript API