Of course! The xlwt library is a popular choice in Python for writing data and formatting spreadsheets in the legacy .xls format (used by older versions of Microsoft Excel).

Here's a comprehensive guide covering everything from basic usage to advanced formatting.

Installation

First, you need to install the library. Open your terminal or command prompt and run:

pip install xlwt

Basic Usage: Creating a Simple .xls File

This is the "Hello, World!" of xlwt. We'll create a workbook, add a worksheet, write some data, and save it.

import xlwt
# 1. Create a new workbook
workbook = xlwt.Workbook()
# 2. Add a worksheet to the workbook
# The name can be up to 31 characters long
worksheet = workbook.add_sheet('My First Sheet')
# 3. Write data to the worksheet
# The write() method takes (row, column, label)
# Row and column numbers are 0-indexed.
worksheet.write(0, 0, 'Hello')  # Row 0, Column 0
worksheet.write(0, 1, 'World')  # Row 0, Column 1
worksheet.write(1, 0, 'This is a test.')
worksheet.write(1, 1, 123)     # You can write numbers too
# 4. Save the workbook to a file
# The file extension should be .xls
workbook.save('hello_world.xls')
print("File 'hello_world.xls' created successfully!")

When you run this code, you'll get a file named hello_world.xls. If you open it, it will look like this:

Formatting Cells (The Power of xlwt)

One of the main reasons to use xlwt is its ability to apply rich formatting to cells. This is done using xlwt.easyxf() (easy X-Format).

How to Apply Styles:

1. Create a style object using xlwt.easyxf().
2. Define properties within the style string (e.g., font: name Arial, bold True;).
3. Pass the style object to the write() method's style argument.

Common Formatting Options:

• Fonts: name, colour, bold, italic, underline
• Borders: left, right, top, bottom (with colour and line_style)
• Patterns: pattern (e.g., solid, solid_pattern) and fore_colour (fill color)
• Alignment: horiz (horizontal: CENTER, LEFT, RIGHT) and vert (vertical: CENTER)
• Number Formats: num_format_str (e.g., '#,##0.00', 'YYYY-MM-DD')

Example: Formatted Data

import xlwt
from datetime import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Formatted Data')
# --- Style Definitions ---
# Style 1: Bold Header
header_style = xlwt.easyxf(
    'font: name Arial, bold True, height 200;'
    'pattern: pattern solid, fore_colour grey25;'
    'align: horiz center;'
    'borders: left thin, right thin, top thin, bottom thin;'
)
# Style 2: General Text
text_style = xlwt.easyxf(
    'font: name Arial;'
    'borders: left thin, right thin, top thin, bottom thin;'
)
# Style 3: Currency
currency_style = xlwt.easyxf(
    'font: name Arial;'
    'num_format_str: "$#,##0.00";'
    'borders: left thin, right thin, top thin, bottom thin;'
)
# Style 4: Date
date_style = xlwt.easyxf(
    'font: name Arial;'
    'num_format_str: "YYYY-MM-DD";'
    'borders: left thin, right thin, top thin, bottom thin;'
)
# --- Writing Data with Styles ---
# Write headers
worksheet.write(0, 0, 'Product', header_style)
worksheet.write(0, 1, 'Description', header_style)
worksheet.write(0, 2, 'Price', header_style)
worksheet.write(0, 3, 'Launch Date', header_style)
# Write data rows
worksheet.write(1, 0, 'Laptop Pro 15"', text_style)
worksheet.write(1, 1, 'A powerful laptop for professionals.', text_style)
worksheet.write(1, 2, 1499.99, currency_style)
worksheet.write(1, 3, datetime(2025, 10, 26), date_style) # xlwt handles datetime objects
worksheet.write(2, 0, 'Wireless Mouse', text_style)
worksheet.write(2, 1, 'Ergonomic wireless mouse with long battery life.', text_style)
worksheet.write(2, 2, 45.50, currency_style)
worksheet.write(2, 3, datetime(2025, 11, 1), date_style)
# --- Save the file ---
workbook.save('formatted_report.xls')
print("File 'formatted_report.xls' created successfully!")

Column Width and Row Height

You can control the dimensions of your columns and rows.

• Column Width: The col() method takes the column index and a "0-width" value. A good starting point for a standard width is 2560.
• Row Height: The row() method takes the row index and the height in 1/20th of a point. For example, 320 is a standard height.

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sized Sheet')
# Set column widths
# Column 0 will be wide enough for a long title
worksheet.col(0).width = 5120  # Wider column
worksheet.col(1).width = 2560  # Standard width
# Set row heights
worksheet.row(0).height_mismatch = True # Required to set custom height
worksheet.row(0).height = 500 # Taller header row
worksheet.row(1).height_mismatch = True
worksheet.row(1).height = 320 # Standard row height
worksheet.write(0, 0, 'This is a very long header title that needs a wide column.')
worksheet.write(1, 0, 'Data in the wide column.')
worksheet.write(1, 1, 'Data in the standard column.')
workbook.save('sized_sheet.xls')
print("File 'sized_sheet.xls' created successfully!")

Merging Cells

You can merge a block of cells into a single larger cell. This is useful for titles or spanning headers.

• Use worksheet.write_merge() instead of worksheet.write().
• It takes (row_range, col_range, label, style).
• row_range and col_range are tuples: (first_row, last_row) and (first_col, last_col).

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Merged Cells')
# A style for the merged titlestyle = xlwt.easyxf('font: name Arial, bold True, height 240; align: horiz center;')
# Merge cells from row 0, column 0 to row 0, column 4
# This creates a title that spans 6 columns (0 to 5)
worksheet.write_merge(0, 0, 0, 5, 'Monthly Sales Report', title_style)
# Write some column headers
worksheet.write(2, 0, 'Product')
worksheet.write(2, 1, 'Q1')
worksheet.write(2, 2, 'Q2')
worksheet.write(2, 3, 'Q3')
worksheet.write(2, 4, 'Q4')
worksheet.write(2, 5, 'Total')
workbook.save('merged_cells.xls')
print("File 'merged_cells.xls' created successfully!")

Complete Example: A Dynamic Invoice Generator

Let's put it all together to create a more realistic and useful script.

import xlwt
from datetime import datetime
def create_invoice(invoice_data, filename):
    """
    Creates an invoice spreadsheet using xlwt.
    :param invoice_data: A dictionary containing invoice details.
    :param filename: The name of the output .xls file.
    """
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('Invoice')
    # --- Styles ---style = xlwt.easyxf('font: name Arial, bold True, height 300; align: horiz center;')
    header_style = xlwt.easyxf('font: name Arial, bold True; pattern: pattern solid, fore_colour grey25; borders: thin;')
    item_style = xlwt.easyxf('font: name Arial; borders: thin;')
    total_style = xlwt.easyxf('font: name Arial, bold True; borders: