Chapter 4: Styling and Formatting Your Spreadsheets

Data is important, but its presentation is what makes it understandable and professional. A well-formatted spreadsheet is easier to read, highlights key information, and looks far more impressive. In this chapter, you'll learn how to control the visual appearance of your cells by applying fonts, colors, fills, and number formatting using openpyxl.

Getting Started: What You'll Need

We will start with the output from our previous chapter, the inventory_with_totals.xlsx file. This file should be in the same directory as your new Python script.

To apply styles, we need to import the necessary classes from the openpyxl.styles module. These classes, like Font, PatternFill, and Alignment, will allow us to create style objects that we can then apply to cells.

Styling the Header Row

A report's header should stand out. Let's make our header row (row 1) bold, with white text on a blue background.

First, we create a Font object for the text and a PatternFill object for the cell's background color.

Create a new Python file named format_report.py and add the following code:

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("inventory_with_totals.xlsx")
ws = wb.active # .active selects the first available sheet

# Define the styles
header_font = Font(name='Calibri', bold=True, color='FFFFFF') # White color
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid') # Blue color

# Apply the styles to each cell in the header row (A1 to D1)
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# Save the changes to see the result
wb.save("formatted_report_temp.xlsx")

Run this script. If you open formatted_report_temp.xlsx, you'll see a nicely formatted header row. The ws[1] syntax is a convenient shortcut in openpyxl to get all the cells in the first row.

Formatting Numbers as Currency

The numbers in our "Price" and "Total Value" columns represent money, but they don't look like it. Let's apply a proper currency format ($#,##0.00) to make them clear and readable. We can do this by setting the number_format attribute on each cell in those columns.

Let's add to our format_report.py script:

# ... (previous code for header styling) ...

# Loop through rows, starting from the second row (to skip the header)
for row_num in range(2, ws.max_row + 1):
    # Format the Price column (column C)
    price_cell = ws.cell(row=row_num, column=3)
    price_cell.number_format = '$#,##0.00'

    # Format the Total Value column (column D)
    total_value_cell = ws.cell(row=row_num, column=4)
    total_value_cell.number_format = '$#,##0.00'

# Save the changes
wb.save("formatted_report_temp.xlsx")

Run the script again. Now, your price columns are formatted as currency.

Putting It All Together: The Final Report

Let's combine everything and add one more touch: adjusting column widths to make sure all our data is visible.

Here is the complete format_report.py script:

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("inventory_with_totals.xlsx")
ws = wb.active

# 1. Define and Apply Header Styles
header_font = Font(name='Calibri', bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 2. Format Currency
for row_num in range(2, ws.max_row + 1):
    ws.cell(row=row_num, column=3).number_format = '$#,##0.00'
    ws.cell(row=row_num, column=4).number_format = '$#,##0.00'

# 3. Adjusting column widths for better readability
ws.column_dimensions['A'].width = 15  # Product
ws.column_dimensions['B'].width = 10  # Quantity
ws.column_dimensions['C'].width = 12  # Price
ws.column_dimensions['D'].width = 15  # Total Value

# 4. Save the final report
wb.save("inventory_report_final.xlsx")

print("Successfully created 'inventory_report_final.xlsx'")

Run this final script. Open inventory_report_final.xlsx to see your polished, professional-looking report.

What You've Accomplished

You can now programmatically transform raw data into a clean, professional report. You've learned to apply essential formatting options, including fonts, background colors, number formats, and column adjustments.

In the next chapter, we'll explore how to make our reports dynamic by writing formulas directly into cells.