Adding a title using python-CodePudding

Basically I generated this whole excel data file using python. Some small details I am overcomplicating:

1- Merge and center cells for a title over the number of columns for that data (col numbers vary) (the title is a different color and bolded)

2- Center the numbers in the data below (is this quick fix?)

Here is what I have:

Here is what I attempt to gain:

The title is merged, centered , bolded, RED fill over the number of columns pretaining to it

The column Numbers vary for example a different data file:

CodePudding user response：

I assume you are using openpyxl.

To merge the cell: You can use the command merge_cells() as described in the docs here and access it by the name "A1".

To fill the background of the cell: Refer to Avik Samaddar's answer on stackoverflow here.

To write into the cell: You should know how to do that ;)

Hope I could help!

CodePudding user response：

ws.max_column returns the maximum column index containing data. I'm assuming it is the same as your last column of data, and that you don't have cells to the right of that for other purposes.

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

filename = r"Pensky File.xlsx"
new_filename = r"Pensky File (Formatted).xlsx"

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

# Merge the top cells for the title
# Make the fill red, and text white, bolt, size 16, and centred
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column)
cell = ws.cell(row=1, column=1)
cell.value = "DATA REPORT LOG"
cell.fill = PatternFill("solid", fgColor="ff0000")
cell.font = Font(b=True, size=16, color="ffffff")
cell.alignment = Alignment(horizontal="center", vertical="center")

# Centre all the cells excluding the title, column headers, and row indexes
for row in ws.iter_rows(min_row=3, min_col=2):
    for cell in row:
        cell.alignment = Alignment(horizontal="center", vertical="center")

wb.save(new_filename)
os.startfile(new_filename)