How to use Worksheet.Outline in the xlwings API way

The Outline property of a Worksheet object in Excel provides access to the outlining (grouping and ungrouping) features for rows and columns on a sheet. Outlining allows you to collapse or expand sections of data, making it easier to manage and view large datasets by hiding detail rows or columns while showing summary information. In xlwings, you can control these outlining features programmatically through the api property, which exposes the underlying Excel object model.

Syntax and Parameters

In xlwings, you access the Outline property via the api property of a Worksheet object. The general syntax is:

worksheet.api.Outline

This returns an Outline object, which has several key methods and properties for managing outlines. The most commonly used methods include:

• ShowLevels(row_levels, column_levels): This method sets the outline levels to display.
• row_levels (optional): An integer specifying the row outline level to show. If omitted, the current row level is unchanged. Levels typically range from 1 (highest summary) to 8 (most detailed).
• column_levels (optional): An integer specifying the column outline level to show. If omitted, the current column level is unchanged.
• AutomaticStyles: A property that, when set to True, allows Excel to apply automatic styles to summary rows and columns. You can set it using worksheet.api.Outline.AutomaticStyles = True.
• SummaryRow and SummaryColumn: Properties that control the placement of summary rows and columns relative to the detail data. For example, xlAbove (or -4162 as a constant) places summaries above details, while xlBelow (or -4167) places them below. In xlwings, you can use constants from the xlwings.constants module or their numeric equivalents.

Code Examples

Here are practical examples using xlwings to manipulate worksheet outlines:

1. Setting Outline Levels: To collapse all rows to show only the top-level summary (level 1) and all columns to show full detail (level 8), you can use:

import xlwings as xw
wb = xw.Book("example.xlsx")
ws = wb.sheets["Sheet1"]
ws.api.Outline.ShowLevels(row_levels=1, column_levels=8)

2. Enabling Automatic Styles: To apply Excel’s automatic outlining styles for better visual distinction:

ws.api.Outline.AutomaticStyles = True

3. Configuring Summary Row Placement: To set summary rows to appear below the detail data (commonly used for subtotals), you can set the SummaryRow property. Using xlwings constants:

from xlwings.constants import xlBelow
ws.api.Outline.SummaryRow = xlBelow

Alternatively, with a numeric value:

ws.api.Outline.SummaryRow = -4167 # Equivalent to xlBelow

4. Grouping Rows Programmatically: While the Outline property itself doesn’t group data directly, you can use it in conjunction with Excel’s Range objects. For instance, to group rows 5 through 10 and apply outlining:

ws.range("5:10").api.Group()
# After grouping, you can control the outline level
ws.api.Outline.ShowLevels(row_levels=1)