How to use Application.Columns in the xlwings API way

The Application.Columns property in the xlwings API provides a powerful way to reference and manipulate entire columns within an Excel application context. It returns a Range object that represents all the columns in the active sheet, or more specifically, it can be used to refer to columns in a general sense, though its direct use is often through the active workbook’s sheets. In xlwings, this is typically accessed via the app object, which represents the Excel Application.

Functionality:
The primary function is to obtain a Range object representing all columns on a worksheet. This is useful for applying formatting, setting column widths, or performing operations across every column. It serves as a starting point for more specific column selections, such as Columns(1) for the first column or Columns("A:C") for a range of columns.

Syntax:
In xlwings, the syntax is:
app.api.Columns
or, more commonly when working with a specific sheet:
sheet.api.Columns

This accesses the underlying Excel VBA Columns property via the api object. The property itself can take an optional index argument to specify which column(s) to return.

• Index (Optional): Can be a column number (integer) or a column letter (string). If omitted, it returns a collection of all columns on the worksheet.
• Example: Columns(1) or Columns("A") returns the first column.
• Example: Columns("A:C") returns columns A through C.

Examples:

1. Set the width of all columns:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.open('example.xlsx')
sheet = wb.sheets[0]

# Set width of every column to 15
sheet.api.Columns.ColumnWidth = 15

2. Format the first column (A) with bold font:

sheet.api.Columns(1).Font.Bold = True
# Alternatively using column letter:
sheet.api.Columns("A").Font.Bold = True

3. Hide columns B through D:

sheet.api.Columns("B:D").Hidden = True

4. AutoFit a range of columns:

# AutoFit columns A to E
sheet.api.Columns("A:E").AutoFit()

5. Apply a border to all columns:

from xlwings.constants import LineStyle, BorderWeight
all_columns = sheet.api.Columns
all_columns.Borders.LineStyle = LineStyle.xlContinuous
all_columns.Borders.Weight = BorderWeight.xlThin

Notes:

• Using app.api.Columns directly (without a sheet reference) typically targets the active sheet. It’s more reliable to explicitly reference a sheet object.
• The Columns property is part of the Worksheet object in Excel’s object model. In xlwings, you access it via sheet.api.Columns.
• This property is read-write; you can both retrieve and set properties on the returned Range object.