Python

Based on our previous OpenPyXL minimal XLSX write example, this code will generate a XLSX with a number in cell A1 that is right-aligned automatically:

from openpyxl import Workbook
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = 4.5
sheet["A1"].alignment = Alignment(horizontal="center")
# Save
wb.save("openpyxl-test.xlsx")

How to add centered alignment

In order to align the cell horizontally, we just need to

from openpyxl.styles.alignment import Alignment

sheet["A1"].alignment = Alignment(horizontal="center")

Full example

from openpyxl import Workbook
from openpyxl.styles.alignment import Alignment
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = 4.5
sheet["A1"].alignment = Alignment(horizontal="center")
# Save
wb.save("openpyxl-test.xlsx")

This will look like this:

This example script uses openpyxl to set a cell’s background color to red for False cells or to green for True cells. Note that we convert the boolean values to "True"or "False" strings based on the method we outlined in our previous post How to write Pandas bool column as True/False instead of 1/0 to XLSX. For more details on how to just set the background color in OpenPyXL, see our post on How to set cell background color in OpenPyXL.

import pandas as pd
from openpyxl.styles import PatternFill

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])
df["a"] = df["a"].map({True: "True", False: "False"})
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    sheet_name = "Bool"
    # Export DataFrame content
    df.to_excel(writer, sheet_name=sheet_name)
    # Set backgrund colors depending on cell values
    sheet = writer.sheets[sheet_name]
    for cell, in sheet[f'B2:B{len(df) + 1}']: # Skip header row, process as many rows as there are DataFrames
        value = df["a"].iloc[cell.row - 2] # value is "True" or "False"
        cell.fill = PatternFill("solid", start_color=("5cb800" if value == "True" else 'ff2800'))

The output from this script looks like this:

If you want to access OpenPyXL columns using indices instead of letters, use

import openpyxl.utils.cell

openpyxl.utils.cell.get_column_letter(idx)

Note that idx is 1-based: index 0 is not a valid argument & index 1 yields column name A 

For example:

openpyxl.utils.cell.get_column_letter(3) # returns "C"

In order to set a cell’s background color in OpenPyXL, use PatternFill with fill_type="solid" and start_color="your_desired_color"  and no end_color. The following example will set a cell’s background to green:

sheet["A1"].fill = PatternFill("solid", start_color="5cb800")

Full example based on our OpenPyXL minimal XLSX write example

from openpyxl import Workbook
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = "This is cell A1"
sheet["A1"].fill = PatternFill("solid", start_color="5cb800")
# Save
wb.save("openpyxl-test.xlsx")

This serves a minimal example of how to write an XLSX file using OpenPyXL:

from openpyxl import Workbook
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = "This is cell A1"
# Save
wb.save("openpyxl-test.xlsx")