Archive

How to use Worksheet.Name in the xlwings API way

The Name property of a Worksheet object in the Excel object model is a fundamental attribute used to get or set the name of a worksheet. In xlwings, this property is accessed directly through the Worksheet object, allowing for straightforward retrieval and modification of sheet names within a workbook. This capability is essential for tasks such as dynamically referencing sheets, organizing data across multiple sheets, or automating sheet management processes.

Functionality:
The primary function is to read or change the name of a worksheet. This is useful in scenarios where sheet names need to be updated based on data content, user input, or automated workflows. For example, you might rename sheets after importing data to reflect the dataset’s source or date.

Syntax in xlwings:
In xlwings, the Name property is accessed as an attribute of a Worksheet object. The syntax is simple:

• To get the current name: sheet_name = ws.name
• To set a new name: ws.name = "NewSheetName"
  Here, ws represents a Worksheet object obtained via wb.sheets['Sheet1'] or similar. The name must be a string and adhere to Excel’s naming conventions (e.g., no more than 31 characters, and characters like :, \, /, ?, *, [, ] are not allowed). If an invalid name is provided, xlwings will raise an error.

Code Examples:
Below are practical examples demonstrating the use of the Name property with xlwings.

1. Retrieving a Worksheet Name:
   This example opens an existing workbook and prints the name of the first worksheet.

import xlwings as xw
# Open an existing workbook
wb = xw.Book('example.xlsx')
# Access the first worksheet
ws = wb.sheets[0]
# Get and print the worksheet name
current_name = ws.name
print(f"The worksheet name is: {current_name}")

2. Renaming a Worksheet:
   This example renames a specific worksheet to “DataSummary”.

import xlwings as xw
# Open an existing workbook
wb = xw.Book('example.xlsx')
# Access a worksheet by its current name
ws = wb.sheets['Sheet1']
# Change the worksheet name
ws.name = "DataSummary"
# Save the workbook to persist changes
wb.save()

3. Dynamic Renaming Based on Content:
   This example renames all worksheets in a workbook based on a list of new names, demonstrating batch processing.

import xlwings as xw
# Open an existing workbook
wb = xw.Book('data.xlsx')
# Define new names for each worksheet
new_names = ["January", "February", "March"]
# Iterate through worksheets and rename them
for i, ws in enumerate(wb.sheets):
    if i < len(new_names):
        ws.name = new_names[i]
# Save the workbook
wb.save()

How to use Worksheet.MailEnvelope in the xlwings API way

The MailEnvelope property of a Worksheet object in Excel VBA is used to control email-related features when sending a worksheet via email. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model. The MailEnvelope object allows you to customize the email subject, recipients, and message body when using Excel’s built-in email integration, typically via the SendMail method. It is particularly useful for automating email reports directly from an Excel workbook.

Syntax and Parameters
In xlwings, you access the MailEnvelope property via the api property of a Worksheet object. The basic syntax is:

worksheet.api.MailEnvelope

This returns a MailEnvelope object, which has several key properties and methods. The most commonly used include:

• Subject: Sets or gets the email subject line as a string.
• To: Sets or gets the primary recipients as a string (multiple addresses can be separated by semicolons).
• CC: Sets or gets the carbon copy recipients as a string.
• BCC: Sets or gets the blind carbon copy recipients as a string.
• Introduction: Sets or gets the introductory text in the email body as a string. This text appears above the worksheet in the email.
• Item.Send(): Sends the email. Note that this method may require an email client (like Outlook) to be configured and running.

These properties are straightforward to set by assigning string values. For example, worksheet.api.MailEnvelope.Subject = "Monthly Report" sets the subject. The Introduction property is especially useful for adding descriptive text.

Code Example
Below is a practical xlwings example that sets up and sends a worksheet via email. This assumes you have an active workbook and an email client set up.

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
# Access the first worksheet
ws = wb.sheets[0]

# Access the MailEnvelope property via api
envelope = ws.api.MailEnvelope

# Set email properties
envelope.Subject = "Q4 Sales Data"
envelope.To = "manager@example.com; team@example.com"
envelope.CC = "supervisor@example.com"
envelope.Introduction = "Please find the attached Q4 sales report. Key highlights include a 15% increase in revenue."

# Optional: Save or update the workbook before sending
wb.save()

# Send the email
envelope.Item.Send()

How to use Worksheet.ListObjects in the xlwings API way

In Excel, the ListObjects collection represents all the tables (ListObject) on a specific worksheet. Tables are powerful features for managing and analyzing structured data, offering built-in filtering, sorting, and easy referencing. Through the ListObjects property of a Worksheet object in xlwings, you can programmatically access, create, and manipulate these tables, enabling automation of data organization and analysis tasks.

Functionality:
The ListObjects property provides access to the collection of tables within a worksheet. You can use it to:

• Retrieve a specific table by its name or index.
• Iterate through all tables to perform batch operations.
• Add new tables based on a given range of data.
• Check the number of tables present.

Syntax:
In xlwings, the ListObjects property is accessed from a Worksheet object. The general call format is:

worksheet.api.ListObjects

This returns a COM object representing the Excel ListObjects collection. To work with it more intuitively in xlwings, you often use methods like add() or access items directly.

To create a new table:

worksheet.api.ListObjects.Add(SourceType, Source, LinkSource, HasHeaders, Destination)

• SourceType: Specifies the source of the data. Typically use 1 (xlSrcRange) for a worksheet range.
• Source: The range address as a string (e.g., “A1:D10”) or an xlwings Range object.
• LinkSource: Usually False for data within the workbook.
• HasHeaders: Set to True if the range includes headers; False otherwise.
• Destination: Optional; used if SourceType is xlSrcExternal. Can be omitted for range sources.

To reference an existing table by name:

table = worksheet.api.ListObjects("TableName")

Example:
Consider a worksheet with sales data in the range A1:C5. The following xlwings code demonstrates using ListObjects to create a table, access its properties, and iterate through tables.

import xlwings as xw

# Connect to the active workbook and worksheet
wb = xw.Book.active
ws = wb.sheets['Sheet1']

# Create a table from the range A1:C5
source_range = ws.range('A1:C5')
table = ws.api.ListObjects.Add(
SourceType=1, # xlSrcRange
Source=source_range.api,
LinkSource=False,
HasHeaders=True,
Destination=None
)
table.Name = 'SalesTable' # Set a name for the table

# Access the table by name
sales_table = ws.api.ListObjects('SalesTable')
print(f"Table range: {sales_table.Range.Address}")

# Iterate through all tables in the worksheet
for tbl in ws.api.ListObjects:
    print(f"Found table: {tbl.Name}")

# Count the number of tables
table_count = ws.api.ListObjects.Count
print(f"Total tables: {table_count}")

# Add a total row to the table
sales_table.ShowTotals = True
sales_table.ListColumns(3).TotalsCalculation = -4157 # xlTotalsCalculationSum

# Resize the table to include new data (e.g., extending to row 6)
sales_table.Resize(ws.range('A1:C6').api)

How to use Worksheet.Index in the xlwings API way

The Index member of the Worksheet object in xlwings provides a read-only property that returns the index number of the worksheet within its parent workbook’s Worksheets collection. This index is a 1-based integer, meaning the first worksheet in a workbook has an Index of 1, the second has an index of 2, and so on. This property is particularly useful when you need to programmatically reference or manipulate worksheets based on their positional order, rather than relying on their names. It can be essential for loops, conditional logic, or when organizing sheets dynamically.

Functionality:
The primary function is to retrieve the positional index of a worksheet. This index reflects the sheet’s order as seen in the Excel application’s tab bar. It is determined by the sheet’s position from left to right. If sheets are rearranged, their index values change accordingly. The Index property is often used in conjunction with the Worksheets collection to access specific sheets.

Syntax:
In xlwings, the property is accessed directly from a Worksheet object. The syntax is straightforward as it does not accept any parameters.

worksheet.index

• worksheet: This is an xlwings Sheet object representing the target worksheet. It is typically obtained via wb.sheets['SheetName'] or wb.sheets[0].
• The property returns an integer (int).

Example Usage:
Consider a workbook with three worksheets named “Data”, “Summary”, and “Chart”, in that order from left to right.

import xlwings as xw

# Connect to an existing workbook
wb = xw.Book("report.xlsx")

# Get a worksheet by its name
data_sheet = wb.sheets['Data']
summary_sheet = wb.sheets['Summary']

# Retrieve their indices
print(f"Index of 'Data' sheet: {data_sheet.index}") # Output: 1
print(f"Index of 'Summary' sheet: {summary_sheet.index}") # Output: 2

# Example 1: Using index in a loop to perform an action on every other sheet
for i in range(1, len(wb.sheets) + 1, 2): # Start at 1, step by 2
    sheet = wb.sheets[i-1] # xlwings collection is 0-based for indexing
    print(f"Processing sheet at workbook index {i}: {sheet.name}")

# Example 2: Conditionally act based on sheet position
if summary_sheet.index > data_sheet.index:
    print("The Summary sheet is to the right of the Data sheet.")

# Example 3: Activating a specific sheet by its known index (e.g., the first sheet)
wb.sheets[0].activate() # Uses 0-based index for the collection
# This is equivalent to wb.sheets[wb.sheets[0].index - 1].activate()

How to use Worksheet.Hyperlinks in the xlwings API way

The Hyperlinks member of the Worksheet object in the Excel object model provides access to a collection of all hyperlinks on a specific worksheet. Through xlwings, this collection can be manipulated to add, modify, or retrieve hyperlinks programmatically, enabling dynamic linking to web pages, documents, email addresses, or other cells within the workbook. This functionality is essential for creating interactive and navigable Excel reports.

In xlwings, the Hyperlinks collection is accessed via the api property of a worksheet object. The syntax is ws.api.Hyperlinks, where ws is an xlwings Sheet object representing the worksheet. The Hyperlinks collection has several key methods, most notably Add. The Add method creates a new hyperlink and has the following parameters:

• Anchor: A required parameter specifying the range where the hyperlink will be placed. This is typically a Range object.
• Address: The target address of the hyperlink (e.g., a URL like “https://www.example.com”).
• SubAddress: An optional parameter for linking to a specific location within a file, such as a named range or a cell reference (e.g., “Sheet2!A1”).
• ScreenTip: Optional text that appears when the user hovers over the hyperlink.
• TextToDisplay: The optional visible text for the hyperlink. If omitted, the Address is displayed.

To retrieve an existing hyperlink, you can iterate through ws.api.Hyperlinks or access a specific one by index. Each hyperlink object in the collection has properties like Address, SubAddress, ScreenTip, and Range, which can be read or modified.

Below are practical xlwings code examples demonstrating the use of the Hyperlinks member:

import xlwings as xw

# Connect to an existing workbook and worksheet
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']

# Example 1: Add a hyperlink to a website in cell A1
link_range = ws.range('A1')
ws.api.Hyperlinks.Add(Anchor=link_range.api,
Address='https://www.python.org',
ScreenTip='Visit Python Website',
TextToDisplay='Python.org')

# Example 2: Add a hyperlink to another cell in the same workbook
ws.api.Hyperlinks.Add(Anchor=ws.range('B2').api,
Address='',
SubAddress='Sheet2!C5',
TextToDisplay='Go to Sheet2')

# Example 3: Loop through all hyperlinks on the worksheet and print their addresses
for link in ws.api.Hyperlinks:
    print(f"Hyperlink at {link.Range.Address}: {link.Address}")

# Example 4: Modify an existing hyperlink (e.g., the first one)
if ws.api.Hyperlinks.Count > 0:
    first_link = ws.api.Hyperlinks(1) # Index is 1-based
    first_link.Address = 'https://www.xlwings.org'
    first_link.TextToDisplay = 'xlwings Docs'

# Save and close
wb.save()
wb.close()

How to use Worksheet.HPageBreaks in the xlwings API way

The HPageBreaks member of the Worksheet object in the Excel object model provides access to the collection of horizontal page breaks within a worksheet. In xlwings, this collection is accessible via the api property, which exposes the underlying Excel VBA object model. This allows for programmatic control over where pages break when the worksheet is printed, enabling precise formatting for reports and documents. The primary use is to add, delete, or query horizontal page breaks, which are essential for managing print layout in multi-page data sets.

Syntax and Parameters

In xlwings, you access the HPageBreaks collection through a worksheet object’s api property:

hpagebreaks = ws.api.HPageBreaks

The collection is 1-indexed, similar to Excel VBA. Key methods and properties include:

• Add(Before): Adds a new horizontal page break.
• Before: A required parameter of type Object. It specifies the range above which the page break will be inserted. You typically pass an xlwings Range object’s .api property (e.g., ws.range("A10").api). The break is inserted above the top edge of this range.
• Count (Property): Returns a Long representing the number of horizontal page breaks in the collection.
• Item(Index): Returns a single HPageBreak object from the collection.
• Index: The index number of the page break (1-indexed).
• Location (Property of an HPageBreak object): Returns a Range object representing the cell where the page break is set (the cell immediately below the break line). This is read/write, allowing you to move an existing break.

Code Examples

1. Adding a Horizontal Page Break:
   Inserts a horizontal page break above row 15.

import xlwings as xw
wb = xw.Book("report.xlsx")
ws = wb.sheets["Sheet1"]
# Add a page break above cell A15
ws.api.HPageBreaks.Add(Before=ws.range("A15").api)

2. Counting and Listing Page Breaks:
   Prints the count and location of each horizontal page break.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets[0]
hbreaks = ws.api.HPageBreaks
print(f"Number of horizontal page breaks: {hbreaks.Count}")
for i in range(1, hbreaks.Count + 1):
    break_obj = hbreaks.Item(i)
    # The Location property returns the cell below the break
    location_cell = break_obj.Location.Address
    print(f" Break {i}: Above row {break_obj.Location.Row} (at {location_cell})")

3. Deleting All Horizontal Page Breaks:
   Clears all manually set horizontal page breaks from the sheet. Note: This does not remove automatic breaks inserted by Excel based on page margins and size.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets[0]
hbreaks = ws.api.HPageBreaks
# Loop backwards to avoid index shifting when deleting
for i in range(hbreaks.Count, 0, -1):
    # The HPageBreak object itself doesn't have a .Delete() method.
    # You delete it by clearing the break from its location.
    hbreaks.Item(i).Location.PageBreak = -4142 # xlPageBreakNone
    # Alternatively, reset all page breaks on the sheet:
    # ws.api.ResetAllPageBreaks()

4. Moving an Existing Page Break:
   Changes the position of the first horizontal page break to be above row 25.

import xlwings as xw
wb = xw.Book.active
ws = wb.sheets[0]
hbreaks = ws.api.HPageBreaks
if hbreaks.Count >= 1:
    first_break = hbreaks.Item(1)
    first_break.Location = ws.range("A25").api

How to use Worksheet.FilterMode in the xlwings API way

The FilterMode property of a Worksheet object in the xlwings API is a read-only property that returns a Boolean value indicating whether the worksheet currently has any active autofilters applied. Specifically, it checks if the worksheet is in “filter mode,” meaning one or more columns have filter dropdowns enabled due to an autofilter being turned on. This is useful for programmatically determining the state of filters before performing operations like data processing or clearing filters, ensuring that your automation scripts can adapt dynamically to the worksheet’s condition.

Syntax and Parameters:
In xlwings, you access this property through a Sheet object (which corresponds to an Excel worksheet). The property does not take any arguments.

sheet.api.FilterMode

Here, sheet is an xlwings Sheet object. The .api attribute provides direct access to the underlying Excel object model (via COM on Windows or AppleScript on macOS), allowing you to use properties like FilterMode as defined in the Excel VBA documentation. The property returns True if the worksheet is in filter mode, and False otherwise.

Code Examples:
Below are practical examples of using the FilterMode property in xlwings:

1. Checking Filter Mode State:
   This example opens an Excel workbook, selects a specific sheet, and checks if filters are active.

import xlwings as xw

# Open the workbook and reference the sheet
wb = xw.Book("example.xlsx")
sheet = wb.sheets["Sheet1"]

# Check if the sheet is in filter mode
if sheet.api.FilterMode:
    print("The worksheet has active autofilters.")
else:
    print("No autofilters are currently applied.")

2. Conditional Operations Based on Filter Mode:
   This script uses FilterMode to decide whether to clear existing filters before applying new ones, preventing errors or unintended behavior.

import xlwings as xw

wb = xw.Book("data.xlsx")
sheet = wb.sheets[0]

# If filters are already on, clear them
if sheet.api.FilterMode:
    sheet.api.AutoFilterMode = False # Turn off autofilter mode
    print("Existing filters cleared.")

# Apply a new autofilter to a range (e.g., A1:D100)
sheet.range("A1:D100").api.AutoFilter(1)
print("New autofilter applied.")

3. Monitoring Filter Changes:
   In a more dynamic scenario, you might loop through multiple sheets to report their filter status.

import xlwings as xw

wb = xw.Book("report.xlsx")

for sheet in wb.sheets:
    status = "Active" if sheet.api.FilterMode else "Inactive"
    print(f"Sheet '{sheet.name}' has filters: {status}")

How to use Worksheet.EnableSelection in the xlwings API way

The Worksheet.EnableSelection property in the xlwings API provides control over the types of selections a user can make within a worksheet via the user interface. This property is particularly useful when you want to protect a worksheet but still allow users to interact with specific cells, such as unlocked cells in a form or template. By setting EnableSelection, you can restrict users from selecting locked cells, unlocked cells, or any cells at all, even when sheet protection is enabled. This enhances data integrity and user experience in shared or sensitive workbooks by preventing accidental modifications to critical data.

In xlwings, you access this property through the api property of a Worksheet object, which exposes the underlying Excel object model. The syntax for using EnableSelection is:
worksheet.api.EnableSelection = value
Here, worksheet is an xlwings Worksheet object, and value is an integer that specifies the selection type. The possible values for value are defined in the Excel enumeration xlEnableSelection, which includes:

• xlNoSelection (value: -4142): Prevents any selection in the worksheet.
• xlNoRestrictions (value: 0): Allows selection of all cells (default behavior when protection is off).
• xlUnlockedCells (value: 1): Permits selection only of unlocked cells.

To use these values in xlwings, you can import the constants from the win32com.client module if you are on Windows, or use their numeric equivalents directly for cross-platform compatibility. For example, xlUnlockedCells corresponds to the integer 1. This property is often set in conjunction with the Protect method to customize protection settings.

Below are code examples demonstrating the usage of Worksheet.EnableSelection with xlwings. Ensure you have an active workbook and worksheet object before running these snippets.

Example 1: Allow selection only of unlocked cells after protecting the worksheet. This is common in forms where users should only edit specific input fields.

import xlwings as xw
# Open an existing workbook or create a new one
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
# First, unlock some cells that users are allowed to edit (e.g., range A1:B2)
ws.range('A1:B2').api.Locked = False
# Protect the worksheet with a password (optional) and set EnableSelection
ws.api.Protect(Password='yourpassword', DrawingObjects=True, Contents=True, Scenarios=True)
ws.api.EnableSelection = 1 # xlUnlockedCells
# Now, users can only select and edit the unlocked cells A1:B2

Example 2: Disable all selections in a protected worksheet to make it completely read-only, preventing users from even clicking on cells.

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
# Protect the worksheet without allowing any selections
ws.api.Protect(Password='secure123')
ws.api.EnableSelection = -4142 # xlNoSelection
# Users cannot select any cells, ensuring no accidental interactions

Example 3: Remove restrictions and allow full selection, which might be useful when temporarily disabling protection for editing.

import xlwings as xw
wb = xw.Book('example.xlsx')
ws = wb.sheets['Sheet1']
# If the worksheet is protected, unprotect it first
if ws.api.ProtectContents:
    ws.api.Unprotect(Password='yourpassword')
    # Set EnableSelection to allow all selections
    ws.api.EnableSelection = 0 # xlNoRestrictions
    # Users can now select any cell freely

How to use Worksheet.EnablePivotTable in the xlwings API way

The EnablePivotTable member of the Worksheet object in the Excel object model, accessible via xlwings, is a property that controls whether pivot tables can be manipulated or refreshed on a specific worksheet. This is particularly useful in scenarios where you need to lock down or protect the structure of pivot tables to prevent accidental changes by end-users, while still allowing the underlying data to be updated or other operations to proceed. By setting this property, developers can programmatically enable or disable pivot table interactions, enhancing the control over the workbook’s functionality during automated processes.

In xlwings, this corresponds to the api.EnablePivotTable property of a worksheet object. The property is a Boolean value, meaning it accepts True or False. When set to True, pivot tables on the worksheet are enabled for operations such as refreshing, sorting, or filtering. When set to False, these operations are disabled, effectively locking the pivot tables against modifications. This property is often used in conjunction with worksheet protection features to create a more secure and user-friendly Excel application.

Syntax:
In xlwings, you access this property through the worksheet’s underlying API object. The typical syntax is:

worksheet.api.EnablePivotTable = boolean_value

Here, worksheet is your xlwings Sheet object representing the target worksheet, and boolean_value is either True or False. To retrieve the current setting, you can simply read the property:

current_setting = worksheet.api.EnablePivotTable

This property does not take additional parameters. It directly reflects or sets the state for all pivot tables on that specific worksheet.

Example Usage:
Consider a scenario where you have an Excel report with a pivot table on a sheet named “SalesSummary”. You want to ensure that during an automated data refresh process, the pivot table is not accidentally altered by users or other macros. After refreshing the data source, you can disable the pivot table interactions, and then re-enable them only when specific administrative actions are required.

Below is an xlwings code example that demonstrates this:

import xlwings as xw

# Connect to the active workbook or open a specific one
wb = xw.Book('Report.xlsx')

# Access the specific worksheet
sales_sheet = wb.sheets['SalesSummary']

# Check the current EnablePivotTable setting
print(f"PivotTable enabled initially: {sales_sheet.api.EnablePivotTable}")

# Disable pivot table operations on this worksheet
sales_sheet.api.EnablePivotTable = False
print("PivotTable interactions are now disabled.")

# Perform other operations, like updating cell values or charts, without affecting pivot tables
sales_sheet.range('A1').value = 'Updated Report Title'

# Later, when needed, re-enable pivot table operations
sales_sheet.api.EnablePivotTable = True
print("PivotTable interactions have been re-enabled.")

# Optionally, refresh all pivot tables on the sheet to reflect any underlying data changes
for pivot in sales_sheet.api.PivotTables():
    pivot.RefreshTable()

# Save and close
wb.save()
wb.close()

How to use Worksheet.EnableOutlining in the xlwings API way

EnableOutlining Property in xlwings

In Excel’s object model, the EnableOutlining property of a Worksheet object controls whether outlining (grouping and ungrouping of rows or columns) is allowed on the worksheet. When set to True, users can manually create and manipulate outlines via the Excel interface, such as grouping rows to collapse detail data and show summary rows. When set to False, outlining is disabled, preventing users from creating new groups or modifying existing ones. This property is useful for protecting the structure of a worksheet when distributing workbooks, ensuring that predefined outline levels remain intact.

Syntax in xlwings

In xlwings, the EnableOutlining property is accessed through the api property of a Sheet object (which corresponds to a Worksheet in Excel’s object model). The property is a boolean value.

sheet.api.EnableOutlining = True # Enable outlining
sheet.api.EnableOutlining = False # Disable outlining

• sheet: An xlwings Sheet object representing the worksheet.
• .api: Provides direct access to the underlying Excel object model (via pywin32 on Windows or appscript on macOS).
• EnableOutlining: The property name as defined in the Excel object model. It can be set to True or False.

Parameters and Usage
The property does not take additional parameters. It simply gets or sets a boolean value. Note that enabling or disabling outlining does not affect existing outlines; it only controls whether new outlines can be created or existing ones modified by the user. In Excel, this setting is often used in combination with worksheet protection (Protect method) to lock the outline structure.

Code Examples

1. Enabling Outlining on a Worksheet
   This example opens an Excel workbook, enables outlining on the first sheet, and saves the file. Users will then be able to group rows or columns manually in Excel.

import xlwings as xw

# Open an existing workbook or create a new one
app = xw.App(visible=False)
workbook = app.books.open('example.xlsx')
sheet = workbook.sheets[0]

# Enable outlining
sheet.api.EnableOutlining = True

# Save and close
workbook.save()
workbook.close()
app.quit()

2. Disabling Outlining and Protecting the Worksheet
   Here, outlining is disabled, and the worksheet is protected to prevent any changes to the outline structure. This is common in finalized reports.

import xlwings as xw

app = xw.App(visible=False)
workbook = app.books.open('report.xlsx')
sheet = workbook.sheets['Summary']

# Disable outlining to lock grouping features
sheet.api.EnableOutlining = False

# Protect the worksheet (optional: add a password)
sheet.api.Protect(Password="your_password", AllowFormattingCells=True)

workbook.save('report_locked.xlsx')
workbook.close()
app.quit()

3. Checking the Current Outlining Status
   You can also retrieve the current value of EnableOutlining to conditionally modify the worksheet.

import xlwings as xw

app = xw.App(visible=False)
workbook = app.books.open('data.xlsx')
sheet = workbook.sheets[0]

# Get the current outlining status
is_outlining_enabled = sheet.api.EnableOutlining
print(f"Outlining is enabled: {is_outlining_enabled}")

# If disabled, enable it
if not is_outlining_enabled:
    sheet.api.EnableOutlining = True
    workbook.save()

workbook.close()
app.quit()