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()
''' 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]'''
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')
实际的解决方案是在包含库之后,通过包含此代码段来修补库代码,从而解决问题。(注意:不要担心缺少定义,例如COORD_RE,即补丁是自包含的)
来源 https://bitbucket.org/openpyxl/openpyxl/issues/365/styling-merged-cells-isnt-working
简单方法 一。转到文件夹C:\Python\Lib\site packages\openpyxl\worksheet folder\
打开worksheet.py文件
在merge_cells函数中注释以下行
保存它。 就这些,对我有用。
我知道这是老问题,但我也遇到了同样的问题,而且修补程序对我不起作用,所以我找到了一个解决方法,使用文档中的函数为合并单元格添加样式,然后在所有合并单元格上循环并在每个范围上调用该函数
相关问题 更多 >
编程相关推荐