Openpyxl单元格合并,openpyxl

发表时间:2020-12-25

1-openpyxl基础用法

2-openpyxl单元格样式处理

3-openpyxl日期格式处理

4-openpyxl单元格合并

openpyxl单元格合并

单元格合并

调用Worksheet的函数merge_cells就可以很方便的实现单元格合并

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 需要合并的左上方和右下方单元格坐标
ws.merge_cells(range_string='A1:B3')
ws.merge_cells(start_row=5, start_column=4, end_row=8, end_column=8)

# 单元格取消合并使用unmerge_cells
ws.unmerge_cells(range_string='A1:B3')
ws.unmerge_cells(start_row=5, start_column=4, end_row=8, end_column=8)

需要注意:
openpyxl的单元格合并会把合并区域左上角单元格的值赋给合并之后的单元格,如果这个单元格没有值,即使区域内其他单元格有值,合并后的单元格也是空白
下面写了一个函数用来解决这个问题,这个函数会合并单元格并且将合并区域最上边最左边有值的单元格赋值给合并后的单元格

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

def merge_cells_value(sheet=None, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
    """
        将靠上靠左的单元格的value赋值给合并后的单元格
    """
    if start_row > end_row:
        fmt = "{end_row} must be greater than {start_row}"
        raise ValueError(fmt.format(start_row=start_row, end_row=end_row))
    if start_column > end_column:
        fmt = "{end_column} must be greater than {start_column}"
        raise ValueError(fmt.format(start_column=start_column, end_column=end_column))
    if range_string is None:
        fmt = '{start_column}{start_row}:{end_column}{end_row}'
        range_string = fmt.format(start_row=start_row,
                                  start_column=get_column_letter(start_column),
                                  end_row=end_row,
                                  end_column=get_column_letter(end_column))

    v = None
    for cells in sheet[range_string]:
        for cell in cells:
            if cell.value is not None:
                v = cell.value
                break
        if v is not None:
            break
    print(range_string)
    sheet.merge_cells(range_string=range_string)
    sheet['{0}{1}'.format(get_column_letter(start_column), str(start_row))] = v
# 使用方式

wb = Workbook()
ws = wb.active

merge_cells_value(sheet=ws, start_row=5, start_column=4, end_row=8, end_column=8)

文章来源互联网,如有侵权,请联系管理员删除。邮箱:417803890@qq.com / QQ:417803890

微配音

Python Free

邮箱:417803890@qq.com
QQ:417803890

皖ICP备19001818号
© 2019 copyright www.pythonf.cn - All rights reserved

微信扫一扫关注公众号:

联系方式

Python Free