Formatting Columns and Adjusting Cell Sizes

When working with Excel, there are times when you need to apply accounting formats, display currency symbols, or represent date and time data in a specific format.

In this lesson, we will learn how to use openpyxl to apply accounting formatting to Excel columns and adjust cell widths and heights.

Accounting format is frequently used for displaying amounts with clear currency symbols and decimal places.

In openpyxl, you can set these formats using the number_format attribute.

Copy
from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Input amount data into the cell
ws['A1'] = 1500

# Apply accounting format
ws['A1'].number_format = '"$"#,##0.00'

# Save the file
wb.save("output_file.xlsx")

In the above code, the data in cell A1 is formatted to display in US dollars ($).

The "\"$"#,##0.00 format displays the currency symbol ($), includes commas for thousands, and shows two decimal places (0.00).

If you want to display August 27, 2024 as 2024-08-27, how would you do it?

Handling date and time data often requires displaying the values in a specific format.

Using the number_format attribute, you can effortlessly specify the format for date and time data.

Copy
from openpyxl import Workbook
from datetime import datetime

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Input date data into the cell
ws['C1'] = datetime(2024, 8, 27)

# Set date format (YYYY-MM-DD format)
ws['C1'].number_format = 'YYYY-MM-DD'

# Save the file
wb.save("output_file.xlsx")

The above code formats the date in cell C1 as YYYY-MM-DD.

The YYYY-MM-DD format displays the year (YYYY), month (MM), and day (DD) in sequence.

If you prefer to use the US date format MM/DD/YYYY, you can set the number_format to MM/DD/YYYY.