使用openpyxl编辑Excel文件时丢失合并的单元格边框 [英] Losing merged cells border while editing Excel file with openpyxl
问题描述
我在Excel文件中有两张纸,第一张是不需要编辑的封面.封面中有一些合并的单元格,当我使用openpyxl编辑文件时,甚至没有接触封面,我就失去了合并单元格的边界.我正在使用load_workbook('excel file')
加载Excel文件并将其保存为其他文件名.
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?
推荐答案
实际的解决方案是在包含库之后通过包含此代码段来修补库代码,从而解决了该问题. (请注意:不要担心缺少定义,例如COORD_RE,即补丁是独立的)
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()
来源 https://bitbucket.org/openpyxl/openpyxl/issues /365/styling-merged-cells-isnt-working
这篇关于使用openpyxl编辑Excel文件时丢失合并的单元格边框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!