Merge cells using python

Question

In the process of a loop that Reads each workbook (I have like 4 separate excel files), and copy each column, cell by cell, to the new spreadsheet, Could not get the formatting for the title which was merged cells and centered AND the line/cell borders

                                #text formatting DATA
###############################Fomrat, Merge/Center/Bold Title, ceneter cells #########################

wb = openpyxl.load_workbook(excelAutoNamed)
ws = wb['Validation']    #wb.active can also be used here

#auto size the cells 
for idx, col in enumerate(ws.columns, 1):
    ws.column_dimensions[get_column_letter(idx)].auto_size = True
 
# 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=len(df.columns)+1)
cell = ws.cell(row=1, column=1)
cell.value = award
cell.fill = PatternFill("solid", fgColor="0091ea")
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")

I was missing two things in the combined Workbook,

NUMBER 1 - the title which was originally merged, centered, and over all of the columns corresponding to the columns of the excel file IS NO longer merged, centered and over the columns

NUMBER 2 - the border lines are also no longer visible

NUMBER 3 - the headers are not auto.width

Before
vs After

Here is code for the Bolded Borders

#borders every cell
hardworkbook = writer.book
worksheet = writer.sheets['Validation']
border_fmt = hardworkbook.add_format({'bottom':2, 'top':2, 'left':2, 'right':2})
worksheet.conditional_format(xlsxwriter.utility.xl_range(0, 0, (len(df))+2, len(df.columns)), {'type': 'no_errors', 'format': border_fmt})

Call [merge_cells()](https://www.codespeedy.com/python-program-to-merge-excel-cell/#:~:text=Implementation%20Of%20Merging%20Cells%20using,()%20method%20for%20separating%20cells.), [alignment()](https://techoverflow.net/2021/09/24/how-center-text-horizontally-using-alignment-in-openpyxl/), [border()](https://stackoverflow.com/questions/24917201/applying-borders-to-a-cell-in-openpyxl) and [Columns.autofit()](https://stackoverflow.com/a/68118823/16653700). — Alias Cartellano, Jun 24 '22 at 22:51
Can you please elaborate just a little more please @Alias Cartellano — Poptropica, Jun 25 '22 at 04:08

score 0 · Answer 1 · answered Jul 12 '22 at 04:48

I could not replicate #2 and #3. The cell borders and column widths are preserved from the original spreadsheets.

I was able to fix the merged cells by adjusting the min and max columns of the MergedCellRange. They need to be increased by the number of columns added to the sheet so far.

import openpyxl
from openpyxl.utils import range_boundaries, get_column_letter
from copy import copy
import os

# Tuple of filenames
filenames = ("filename1.xlsx", 
             "filename2.xlsx", 
             "filename3.xlsx", 
             "filename4.xlsx", 
            )

# Create a new workbook
new_wb = openpyxl.Workbook()
new_ws = new_wb.active

# column_num is the next column number to be written to in the new spreadsheet
column_num = 1

# Read each workbook, and copy each column, cell by cell, to the new spreadsheet
for filename in filenames:
    wb = openpyxl.load_workbook(filename)
    ws = wb.active
    
    # The min and max columns of every MergedCellRange needs to be shifted
    for range_ in ws.merged_cells.ranges:
        min_col, min_row, max_col, max_row = range_boundaries(range_.coord)
        min_col = get_column_letter(min_col + column_num - 1)
        max_col = get_column_letter(max_col + column_num - 1)
        new_ws.merged_cells.add(f"{min_col}{min_row}:{max_col}{max_row}")
    
    for column in ws.iter_cols():
        for cell in column:
            new_cell = new_ws.cell(row=cell.row, column=column_num, value=cell.value)  
            
            # Styles have to be manually copied
            if cell.has_style:
                new_cell.font = copy(cell.font)
                new_cell.border = copy(cell.border)
                new_cell.fill = copy(cell.fill)
                new_cell.number_format = copy(cell.number_format)
                new_cell.protection = copy(cell.protection)
                new_cell.alignment = copy(cell.alignment)
        
        # Preserve the same column width
        width = ws.column_dimensions[cell.column_letter].width
        new_column = new_ws.column_dimensions[new_cell.column_letter]
        new_column.width = width
        
        column_num += 1

# Save the new workbook to disk
new_filename = "combined.xlsx"
new_wb.save(new_filename)

# Launch the new spreadsheet
os.startfile(new_filename)

Merge cells using python

1 Answers1