I have two sheets in an Excel file and the first one is a cover sheet which I don't need to edit. There are a few merged cells in the cover sheet, and when I edit the file using openpyxl, without even touching the cover sheet, I lose borders from the merged cells. I am using load_workbook('excel file') to load the Excel file and saving it with a different filename.
Is there any way to fix this problem?
Actual solution is to patch the libraries code by including this snippet after including the library, it fixes the problem. (Note: don't worry about missing definitions, e.g. COORD_RE, i.e. the patch is self-contained)
from itertools import product
import types
import openpyxl
from openpyxl import worksheet
from openpyxl.utils import range_boundaries
def patch_worksheet():
"""This monkeypatches Worksheet.merge_cells to remove cell deletion bug
https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
Thank you to Sergey Pikhovkin for the fix
"""
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1)
This is monkeypatched to remove cell deletion bug
https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
"""
if not range_string and not all((start_row, start_column, end_row, end_column)):
msg = "You have to provide a value either for 'coordinate' or for\
'start_row', 'start_column', 'end_row' *and* 'end_column'"
raise ValueError(msg)
elif not range_string:
range_string = '%s%s:%s%s' % (get_column_letter(start_column),
start_row,
get_column_letter(end_column),
end_row)
elif ":" not in range_string:
if COORD_RE.match(range_string):
return # Single cell, do nothing
raise ValueError("Range must be a cell range (e.g. A1:E1)")
else:
range_string = range_string.replace('$', '')
if range_string not in self._merged_cells:
self._merged_cells.append(range_string)
# The following is removed by this monkeypatch:
# min_col, min_row, max_col, max_row = range_boundaries(range_string)
# rows = range(min_row, max_row+1)
# cols = range(min_col, max_col+1)
# cells = product(rows, cols)
# all but the top-left cell are removed
#for c in islice(cells, 1, None):
#if c in self._cells:
#del self._cells[c]
# Apply monkey patch
worksheet.Worksheet.merge_cells = merge_cells
patch_worksheet()
Source https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
I know this is old but I got the same issue and the patch didn't work for me so I found a work around by using the function in the documentation that adds a style for merged cells and then looping on all the merged cells and calling the function on each range
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill,
Alignment
def style_range(ws, cell_range, border=Border(), fill=None, font=None,
alignment=None):
"""
Apply styles to a range of cells as if they were a single cell.
:param ws: Excel worksheet instance
:param range: An excel range to style (e.g. A1:F20)
:param border: An openpyxl Border
:param fill: An openpyxl PatternFill or GradientFill
:param font: An openpyxl Font object
"""
top = Border(top=border.top)
left = Border(left=border.left)
right = Border(right=border.right)
bottom = Border(bottom=border.bottom)
first_cell = ws[cell_range.split(":")[0]]
if alignment:
ws.merge_cells(cell_range)
first_cell.alignment = alignment
rows = ws[cell_range]
if font:
first_cell.font = font
for cell in rows[0]:
cell.border = cell.border + top
for cell in rows[-1]:
cell.border = cell.border + bottom
for row in rows:
l = row[0]
r = row[-1]
l.border = l.border + left
r.border = r.border + right
if fill:
for c in row:
c.fill = fill
file = 'file.xlsx'
wb = load_workbook(file)
ws = wb['Table 1']
thin = Side(border_style="thin", color="000000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
for range in ws.merged_cells.ranges:
style_range(ws, str(range), border=border)
wb.save('newExcel.xlsx')
For me this code work perfectly:
wb = load_workbook(template_path)
ws = wb.active
for merged_cells in ws.merged_cells.ranges:
style = ws.cell(merged_cells.min_row, merged_cells.min_col)._style
for col in range(merged_cells.min_col, merged_cells.max_col + 1):
for row in range(merged_cells.min_row, merged_cells.max_row + 1):
ws.cell(row, col)._style = style
I simply find all merged cells and apply style of left top cell to the rest of the cells.
If you are using openpyxl 2.4, the solution offered by micki_mouse may not work. I managed to make it work by tweaking it this way:
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries
wb = load_workbook(template_path)
ws = wb.active
for merged_cells in ws.merged_cell_ranges:
min_col, min_row, max_col, max_row = range_boundaries(merged_cells)
style = ws.cell(row=min_row, column=min_col)._style
for col in range(min_col, max_col + 1):
for row in range(min_row, max_row + 1):
ws.cell(row=row, column=col)._style = style
Basically, you have to use "merged_cell_ranges" (deprecated in 2.5.0-b1) to correctly access the merged cells within the worksheet. You also must specify explicitely in the cell constructor the row and column
Simple method
Go to the folder C:\Python\Lib\site-packages\ openpyxl\worksheet folder\
Open worksheet.py file
In merge_cells function comment the following lines
min_col, min_row, max_col, max_row =
range_boundaries(range_string)
rows = range(min_row, max_row+1)
cols = range(min_col, max_col+1)
cells = product(rows, cols)
all but the top-left cell are removed
for c in islice(cells, 1, None):
if c in self._cells:
del self._cells[c]
Save it.
That's all , it works for me.
