Formatting Excel Cells with Python

Python offers a powerful set of tools for automating Excel file creation and formatting through libraries such as openpyxl and xlsxwriter. These libraries provide the ability to programmatically modify Excel workbooks, apply formatting styles, set data types, and even insert charts and conditional formatting. Below is a comprehensive overview of how to format Excel cells using Python, with a focus on practical examples and use cases.

Libraries for Excel Formatting

1. openpyxl

This library is ideal for working with .xlsx files. It supports reading and writing Excel 2010+ files and allows formatting of individual cells, rows, and columns.

2. xlsxwriter

This library is known for its extensive formatting options and is often used when you need to create highly formatted Excel files from scratch.

Installing Required Libraries

bash
CopyEdit
pip install openpyxl xlsxwriter

Formatting with openpyxl

Creating and Formatting a Workbook

python
CopyEdit
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

# Create workbook and worksheet
wb = Workbook()
ws = wb.active
ws.title = "Formatted Sheet"

# Define styles
bold_font = Font(bold=True, color="FFFFFF")
fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
alignment = Alignment(horizontal="center", vertical="center")
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# Apply formatting to header row
headers = ["ID", "Name", "Score"]
for col, header in enumerate(headers, start=1):
    cell = ws.cell(row=1, column=col)
    cell.value = header
    cell.font = bold_font
    cell.fill = fill
    cell.alignment = alignment
    cell.border = thin_border

# Add data
data = [(1, "Alice", 90), (2, "Bob", 85), (3, "Charlie", 78)]
for row_num, row_data in enumerate(data, start=2):
    for col_num, value in enumerate(row_data, start=1):
        cell = ws.cell(row=row_num, column=col_num)
        cell.value = value
        cell.border = thin_border
        cell.alignment = alignment

# Adjust column width
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter
    for cell in col:
        if cell.value:
            max_length = max(max_length, len(str(cell.value)))
    ws.column_dimensions[column].width = max_length + 2

# Save the workbook
wb.save("formatted_openpyxl.xlsx")

Formatting with xlsxwriter

python
CopyEdit
import xlsxwriter

# Create workbook and worksheet
workbook = xlsxwriter.Workbook('formatted_xlsxwriter.xlsx')
worksheet = workbook.add_worksheet('Formatted Sheet')

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

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

# Write header row
headers = ["ID", "Name", "Score"]
worksheet.write_row('A1', headers, header_format)

# Write data
data = [(1, "Alice", 90), (2, "Bob", 85), (3, "Charlie", 78)]
row = 1
for record in data:
    worksheet.write_row(row, 0, record, cell_format)
    row += 1

# Set column widths
worksheet.set_column('A:A', 5)
worksheet.set_column('B:B', 15)
worksheet.set_column('C:C', 10)

# Save workbook
workbook.close()

Advanced Formatting Features

Conditional Formatting (xlsxwriter)

python
CopyEdit
worksheet.conditional_format('C2:C4', {
    'type':     'cell',
    'criteria': '>=',
    'value':    85,
    'format':   workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'})
})

Merging Cells

python
CopyEdit
worksheet.merge_range('A6:C6', 'Merged Cell Example', header_format)

Number Formatting

python
CopyEdit
currency_format = workbook.add_format({'num_format': '$#,##0.00'})
worksheet.write('D2', 1234.56, currency_format)

Choosing Between Libraries

If your goal is to modify existing Excel files or integrate formatting into larger data processing pipelines, openpyxl is more suitable. For highly customized, polished Excel outputs from scratch, xlsxwriter provides finer control and richer formatting features.

Common Use Cases

1. Automated Reporting: Use xlsxwriter to create polished reports with formatted tables and conditional highlights.

2. Data Cleaning and Annotation: Use openpyxl to read, process, and re-save .xlsx files while applying styles based on logic.

3. Finance Sheets: Create dashboards with currency, date formatting, and formulas.

4. Survey/Grading Systems: Apply visual cues for grades using conditional formatting or color codes.

Final Tips

• Always adjust column widths for readability.

• Use freeze_panes() to lock header rows.

• Avoid mixing libraries in a single project to reduce compatibility issues.

• For large datasets, prefer xlsxwriter due to better performance in writing operations.

With Python and these libraries, you can efficiently automate Excel formatting tasks, saving time and reducing manual errors in spreadsheet management.