Skip to main content

XlsxWriter Complete Guide

XlsxWriter is a Python library for creating Excel files with complex formatting.

1. Creating a Workbook and Worksheet​

Save to File​

import xlsxwriter

workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet('Sheet1')
# ... write data ...
workbook.close()

Save to Memory (for Pyodide/download)​

import xlsxwriter
import io

buffer = io.BytesIO()
workbook = xlsxwriter.Workbook(buffer)
worksheet = workbook.add_worksheet('Report')
# ... write data ...
workbook.close()
buffer.seek(0)
# buffer.getvalue() contains the Excel file bytes

2. Using with Pandas ExcelWriter​

import pandas as pd
import io

buffer = io.BytesIO()

# Create Excel writer
with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
    # Write DataFrame
    df.to_excel(writer, sheet_name='Data', index=False, startrow=5)

    # Access workbook and worksheet for formatting
    workbook = writer.book
    worksheet = writer.sheets['Data']

    # Add formatting here...

# Get the Excel file bytes
buffer.seek(0)
excel_bytes = buffer.getvalue()

3. Writing Data​

# Write single cell
worksheet.write(0, 0, 'Hello')         # Row 0, Col 0
worksheet.write('A1', 'Hello')         # Same as above
worksheet.write(0, 1, 12345)           # Number
worksheet.write(0, 2, 123.45)          # Float

# Write row of data
worksheet.write_row(0, 0, ['A', 'B', 'C', 'D'])

# Write column of data
worksheet.write_column(0, 0, [1, 2, 3, 4, 5])

Row and Column Reference​

     A    B    C    D    E
   (0)  (1)  (2)  (3)  (4)  <- Column index
1 (0)
2 (1)
3 (2)  <- Row index
4 (3)
5 (4)
Remember

In xlsxwriter, indexing starts at 0!

  • Row 1 in Excel = Row 0 in xlsxwriter
  • Column A in Excel = Column 0 in xlsxwriter

4. Cell Formatting​

Creating Formats​

# Basic format
bold = workbook.add_format({'bold': True})

# Multiple properties
header_format = workbook.add_format({
    'bold': True,
    'font_size': 12,
    'font_color': 'white',
    'bg_color': '#4472C4',
    'align': 'center',
    'valign': 'vcenter',
    'border': 1
})

Common Format Properties​

Font Formatting​

font_format = workbook.add_format({
    'bold': True,
    'italic': True,
    'underline': True,
    'font_size': 14,
    'font_name': 'Arial',
    'font_color': 'red',       # or '#FF0000'
})

Alignment​

align_format = workbook.add_format({
    'align': 'center',         # left, center, right
    'valign': 'vcenter',       # top, vcenter, bottom
    'text_wrap': True,         # Wrap text
    'rotation': 45,            # Rotate text
})

Borders​

border_format = workbook.add_format({
    'border': 1,               # All borders (thin)
    'border': 2,               # All borders (medium)
    # Or individual borders:
    'top': 1,
    'bottom': 2,
    'left': 1,
    'right': 1,
})

Background Color​

bg_format = workbook.add_format({
    'bg_color': '#FFFF00',     # Yellow
    'pattern': 1,              # Solid fill
})

Number Formats​

number_format = workbook.add_format({
    'num_format': '#,##0',           # 1,234
})

decimal_format = workbook.add_format({
    'num_format': '#,##0.00',        # 1,234.56
})

currency_format = workbook.add_format({
    'num_format': '$#,##0.00',       # $1,234.56
})

percent_format = workbook.add_format({
    'num_format': '0.00%',           # 12.34%
})

date_format = workbook.add_format({
    'num_format': 'dd/mm/yyyy',      # Date
})

Applying Formats​

# When writing
worksheet.write(0, 0, 'Header', header_format)
worksheet.write(1, 0, 1234567, number_format)

# To a range (set column format)
worksheet.set_column('B:D', 15, number_format)  # Columns B-D, width 15

# To entire row
worksheet.set_row(0, 30, header_format)  # Row 0, height 30

5. Currency Format (Indonesian Rupiah)​

# Indonesian number format (dot as thousand separator)
idr_format = workbook.add_format({
    'num_format': '#,##0',      # Will show: 1,234,567
    'align': 'right'
})

# Or create custom for "Rp"
rp_format = workbook.add_format({
    'num_format': '"Rp" #,##0',  # Will show: Rp 1,234,567
})

6. Merging Cells​

Basic Merge​

# merge_range(first_row, first_col, last_row, last_col, data, format)

# Merge A1:C1 (horizontal merge)
worksheet.merge_range(0, 0, 0, 2, 'Company Name', header_format)

# Merge A1:A3 (vertical merge)
worksheet.merge_range(0, 0, 2, 0, 'Category', header_format)

# Merge A1:C3 (block merge)
worksheet.merge_range(0, 0, 2, 2, 'Title', header_format)

# Using Excel notation
worksheet.merge_range('A1:C1', 'Company Name', header_format)

Practical Merge Examples​

Report Header​

title_format = workbook.add_format({
    'bold': True,
    'font_size': 18,
    'align': 'center',
    'valign': 'vcenter'
})

# Merge for title (columns A to N)
worksheet.merge_range(0, 0, 0, 13, 'PT. COMPANY NAME', title_format)
worksheet.merge_range(1, 0, 1, 13, 'AR/AP Aging Report', title_format)
worksheet.merge_range(2, 0, 2, 13, 'As of December 2024', title_format)

Grouped Header (Like Aging Report)​

# Create this header structure:
#    | CURRENT | 1-30 Days | 31-60 Days |
#    | FC | Rp | FC | Rp   | FC  | Rp   |

header_format = workbook.add_format({
    'bold': True,
    'align': 'center',
    'valign': 'vcenter',
    'border': 1,
    'bg_color': '#D9E1F2'
})

# Row 6: Main headers (merged)
worksheet.merge_range(6, 2, 6, 3, 'CURRENT', header_format)    # C7:D7
worksheet.merge_range(6, 4, 6, 5, '1-30 Days', header_format)  # E7:F7
worksheet.merge_range(6, 6, 6, 7, '31-60 Days', header_format) # G7:H7

# Row 7: Sub headers
worksheet.write(7, 2, 'FC', header_format)
worksheet.write(7, 3, 'Rp', header_format)
worksheet.write(7, 4, 'FC', header_format)
worksheet.write(7, 5, 'Rp', header_format)
worksheet.write(7, 6, 'FC', header_format)
worksheet.write(7, 7, 'Rp', header_format)

7. Setting Column and Row Sizes​

Column Widths​

# Set individual column width
worksheet.set_column('A:A', 30)   # Column A, width 30
worksheet.set_column('B:B', 15)   # Column B, width 15

# Set range of columns
worksheet.set_column('C:F', 12)   # Columns C to F, width 12

# Using index
worksheet.set_column(0, 0, 30)    # Column 0 (A), width 30
worksheet.set_column(1, 5, 15)    # Columns 1-5 (B-F), width 15

# Auto-width based on data (manual calculation)
for col_num, column in enumerate(df.columns):
    max_length = max(
        df[column].astype(str).map(len).max(),  # Max data length
        len(column)  # Header length
    ) + 2  # Add padding
    worksheet.set_column(col_num, col_num, max_length)

Row Heights​

# Set single row height
worksheet.set_row(0, 30)    # Row 0, height 30

# Set row height with format
worksheet.set_row(0, 30, header_format)

# Multiple rows
for row in range(10):
    worksheet.set_row(row, 20)

8. Conditional Formatting​

from xlsxwriter.utility import xl_range

# Highlight cells based on value
# Apply to range C3:C100

# Values greater than 1000000 -> Green background
worksheet.conditional_format('C3:C100', {
    'type': 'cell',
    'criteria': '>',
    'value': 1000000,
    'format': workbook.add_format({'bg_color': '#C6EFCE'})
})

# Values less than 0 -> Red background
worksheet.conditional_format('C3:C100', {
    'type': 'cell',
    'criteria': '<',
    'value': 0,
    'format': workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
})

# Using xl_range for dynamic ranges
start_row = 3
end_row = 3 + len(df)
worksheet.conditional_format(
    xl_range(start_row, 2, end_row, 3),  # row1, col1, row2, col2
    {
        'type': 'cell',
        'criteria': '>',
        'value': 1000000,
        'format': workbook.add_format({'bg_color': '#C6EFCE'})
    }
)

9. Complete Table Example​

import pandas as pd
import xlsxwriter
import io

# Sample data
data = [
    {"customer": "PT. ABC", "currency": "IDR", "current": 1000000, "overdue": 500000},
    {"customer": "XYZ Corp", "currency": "USD", "current": 5000, "overdue": 2000},
    {"customer": "PT. DEF", "currency": "IDR", "current": 2000000, "overdue": 0},
]
df = pd.DataFrame(data)

# Create workbook
buffer = io.BytesIO()
workbook = xlsxwriter.Workbook(buffer)
worksheet = workbook.add_worksheet('Report')

# Define formats
header_format = workbook.add_format({
    'bold': True,
    'bg_color': '#4472C4',
    'font_color': 'white',
    'align': 'center',
    'valign': 'vcenter',
    'border': 1
})

cell_format = workbook.add_format({
    'border': 1,
    'valign': 'vcenter'
})

number_format = workbook.add_format({
    'border': 1,
    'num_format': '#,##0',
    'align': 'right'
})

total_format = workbook.add_format({
    'bold': True,
    'border': 2,
    'num_format': '#,##0',
    'bg_color': '#D9E1F2'
})

# Set column widths
worksheet.set_column('A:A', 25)  # Customer
worksheet.set_column('B:B', 10)  # Currency
worksheet.set_column('C:D', 15)  # Numbers

# Write title
title_format = workbook.add_format({'bold': True, 'font_size': 16})
worksheet.write(0, 0, 'Customer Aging Report', title_format)

# Write headers (starting at row 2)
headers = ['Customer', 'Currency', 'Current', 'Overdue']
for col, header in enumerate(headers):
    worksheet.write(2, col, header, header_format)

# Write data (starting at row 3)
for row_idx, row_data in df.iterrows():
    excel_row = row_idx + 3  # Offset for title and header
    worksheet.write(excel_row, 0, row_data['customer'], cell_format)
    worksheet.write(excel_row, 1, row_data['currency'], cell_format)
    worksheet.write(excel_row, 2, row_data['current'], number_format)
    worksheet.write(excel_row, 3, row_data['overdue'], number_format)

# Write totals
total_row = len(df) + 3
worksheet.write(total_row, 0, 'TOTAL', total_format)
worksheet.write(total_row, 1, '', total_format)
worksheet.write(total_row, 2, df['current'].sum(), total_format)
worksheet.write(total_row, 3, df['overdue'].sum(), total_format)

workbook.close()

10. XlsxWriter Cheat Sheet​

OperationCode
Create workbookworkbook = xlsxwriter.Workbook('file.xlsx')
Add worksheetworksheet = workbook.add_worksheet('Name')
Write cellworksheet.write(row, col, value, format)
Merge cellsworksheet.merge_range(r1, c1, r2, c2, value, fmt)
Set column widthworksheet.set_column('A:A', 15)
Set row heightworksheet.set_row(0, 30)
Create formatfmt = workbook.add_format({...})
Close workbookworkbook.close()

Common Formats Quick Reference​

# Bold header with border
header = {'bold': True, 'border': 1, 'align': 'center'}

# Number with thousand separator
number = {'num_format': '#,##0', 'align': 'right'}

# Currency IDR
idr = {'num_format': '"Rp" #,##0'}

# Percentage
pct = {'num_format': '0.00%'}

# Date
date = {'num_format': 'dd/mm/yyyy'}