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_boundariesdef patch_worksheet():"""This monkeypatches Worksheet.merge_cells to remove cell deletion bughttps://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-workingThank 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 bughttps://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 nothingraise 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 patchworksheet.Worksheet.merge_cells = merge_cells
patch_worksheet()
Source
https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working