Excel Automation with Python xlwings: Install, Basics, and Code Samples

Installation

xlwings is a third‑party library; install it via pip:

pip install xlwings

Prerequisite Knowledge

The core architecture connects xlwings to the Excel application (apps), then to workbooks and sheets, unlike openpyxl which works directly on files.

xlwings : requires Excel installed, supports .xls and .xlsx , can call VBA macros, and works well with matplotlib and pandas . openpyxl : does not need Excel, only supports .xlsx format.

Code Details

1. Open Excel Application

Use App to control visibility and disable alerts.

import xlwings as xw
app = xw.App(visible=True, add_book=False)  # program visible, no new workbook
app.display_alerts = False  # hide alerts
app.screen_updating = False  # disable screen updates

2. Save, Exit, Close

Operations are based on the app object.

path = r"C:\Scientific Research\Python"
wb = app.books.open(path + r'\practice.xlsx')
wb.save()
wb.close()
app.quit()

3. Create a New Excel File

Remember to save, close the workbook, and quit the app.

path = r"C:\Scientific Research\Python"
wb = app.books.add()
wb.save(path + r'
ew_practice.xlsx')
wb.close()
app.quit()

4. Read Data

Example file practice.xlsx:

Reading cells and ranges:

path = r"C:\Scientific Research\Python"
wb = app.books.open(path + r'\practice.xlsx')
sheet = wb.sheets.active

# Single cell
A1 = sheet.range('A1').value
print(A1)

# Horizontal range
A1_A3 = sheet.range('A1:A3').value
print(A1_A3)

# Block range
A1_C4 = sheet.range('A1:C4').value
print(A1_C4)

Alternative indexing syntax works the same way.

# Single cell
A1 = sheet['A1'].value
print(A1)

# Horizontal range
A1_A3 = sheet['A1:A3'].value
print(A1_A3)

# Block range
A1_C4 = sheet['A1:C4'].value
print(A1_C4)

5. Write Data

Examples for writing a single cell, a row/column, and a block.

# Write one cell
sheet.range('A2').value = '大明'

# Write a row horizontally
sheet.range('A1').value = [1, 2, 3]

# Write a column vertically (transpose)
sheet.range('A1').options(transpose=True).value = [1, 2, 3]

# Write a block (table)
sheet.range('A1').options(expand='table').value = [[1,2,3],[4,5,6]]

Adding a new record to practice.xlsx:

import xlwings as xw
app = xw.App(visible=True, add_book=False)
app.display_alerts = False
app.screen_updating = False

path = r"C:\Scientific Research\Python"
wb = app.books.open(path + r'\practice.xlsx')
sheet = wb.sheets.active
sheet.range('A5').value = ['小兰', 23, '女']

wb.save()
wb.close()
app.quit()

6. Get Data Range

Two methods to obtain the used range size:

# Method 1
shape = sheet.used_range.shape
print(shape)

# Method 2
nrow = sheet.api.UsedRange.Rows.count
ncol = sheet.api.UsedRange.Columns.count
print(nrow)
print(ncol)

7. Row Height and Column Width

# Get
print(sheet.range('A1:A2').row_height)
print(sheet.range('A1:A2').column_width)
# Set
sheet.range('A1:A2').row_height = 15
sheet.range('A1:A2').column_width = 10

8. Formulas

xlwings can read and write Excel formulas, which pandas cannot.

# Read formula
print(sheet.range('B2').formula_array)
# Write formula
sheet.range('B2').formula = '=SUM(A1,A2)'

9. Colors

Getting, setting, and clearing cell colors.

# Get color
print(sheet.range('C1').color)
# Set color (RGB)
sheet.range('C1').color = (255, 0, 120)
# Clear color
sheet.range('C1').color = None

For more operations, refer to the official xlwings documentation and compare it with other libraries such as openpyxl.