Text in spreadsheet formatted using openpyxl not formatted to correct colour in Excel (shows correctly in libreoffice calc)

Question

I have written a python script to format an Excel spreadsheet to be a registration sheet.

The problem I am having is that although the resulting excel sheet looks fine in libreoffice calc, when it opens in Excel the heading text is black on a black background when it should be white on a black background.

This is the code:

#!/usr/bin/python

# Import

from openpyxl.styles import Font, PatternFill, Side, Border, Alignment
from openpyxl import load_workbook
import sys
import datetime

# Variables

file = sys.argv[1]
title = sys.argv[2]
headings = ["Etternavn", "Fornavn", "Signatur"]
date = datetime.datetime.now().strftime("%Y%m%d")

# Load workbook

wb = load_workbook(filename=file)
ws = wb.active

# Delete extra columns

ws.delete_cols(3, amount=31)

# Merge top three cells

ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)

# ws.cell(row=1, column=1).alignment = Alignment(horizontal="left")

# Add correct headings

for i in range(1, 4):
    cell_ref = ws.cell(row=2, column=i)
    cell_ref.value = headings[i - 1]

# Add name to top of spreadsheet

ws.cell(row=1, column=1).value = title
ws.cell(row=1, column=1).font = Font(size=16, bold=True, name='Calibri')

# Set colour, size and background colour for headers

def set_header(col_range):
    for col1 in range(1, col_range + 1):
        cell_header = ws.cell(2, col1)
        cell_header.fill = PatternFill(start_color='000000',
                                       end_color='000000',
                                       fill_type="solid")  # used hex code for red color
        cell_header.font = Font(color="FFFFFF", size=16, bold=True, name='Calibri')


set_header(ws.max_column)

# Set borders for cells

def set_border(wsa, cell_range):
    thin = Side(border_style="thin", color="000000")
    for row1 in wsa[cell_range]:
        for cell in row1:
            cell.border = Border(top=thin, left=thin, right=thin, bottom=thin)


set_border(ws, 'A1:C144')

# Set column width

for col in ['A', 'B']:
    ws.column_dimensions[col].width = 25

ws.column_dimensions['C'].width = 35

# Set row height

for row in range(1, 145):
    ws.row_dimensions[row].height = 30

# Variables for alignment

rows = range(1, 144)
rows2 = range(2, 145)
columns = range(1, 10)


# Set vertical alignment

def set_vert_align(vrows, vcolumns):
    for row2 in vrows:
        for col2 in vcolumns:
            ws.cell(row2, col2).alignment = Alignment(vertical='center', wrap_text=True)


set_vert_align(rows, columns)


# Set font size for names

def set_row_size_font(frows, fcolumns):
    for row3 in frows:
        for col3 in fcolumns:
            ws.cell(row3, col3).font = Font(size=13, name='Calibri')


set_row_size_font(rows2, columns)

# Save Excel spreadsheet

wb.save(date + " " + title + ".xlsx")

Answer 1

The reason for the error in font color is that you are overwriting the font at the end... just before save - in this line set_row_size_font(rows2, columns)

If you comment that line, it will work fine. You need to change rows2 to exclude the 2nd row or create another variable