openpyxl 带格式复制单元格，有一些bug，暂时搞不明白

最新推荐文章于 2024-11-09 15:50:17 发布

原创最新推荐文章于 2024-11-09 15:50:17 发布 · 1.2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#python

本文介绍了一种使用Python的openpyxl库来复制Excel文件中所有样式（包括合并单元格、行高、列宽等）的方法。该方法通过逐行逐列读取源Excel文件，并将读取到的所有样式应用到目标Excel文件中。

import openpyxl
from openpyxl.styles import Font, colors, Alignment
from openpyxl.utils import get_column_letter, column_index_from_string
from copy import copy # 浅拷贝

def info(obj,flag=True):
    for i in dir(obj):
        if flag:
            if not i.startswith('_'):
                print(i)
        else:
            print(i)
    exit()

source_path = r'c:/users/administrator/desktop/bill.xlsx'
source_workbook = openpyxl.load_workbook(source_path)
source_sheet = source_workbook['空表']

target_workbook = openpyxl.Workbook()
target_sheet = target_workbook.active

nrows = 38 #source_sheet.max_row
ncols = 24 #source_sheet.max_column

for it in source_sheet.merged_cells:
    target_sheet.merge_cells(it.coord)

for r in range(1,nrows+1):
    target_sheet.row_dimensions[r].height = source_sheet.row_dimensions[r].height
    for c in range(1,ncols+1):
        if r == 1:
            column_letter = get_column_letter(c)
            column_width = source_sheet.column_dimensions[column_letter].width
            target_sheet.column_dimensions[column_letter].width = column_width
            print(column_letter+str(r),column_width)
            # 这个获取宽度的方法，返回的值不准确，
            
        source_cell = source_sheet.cell(row = r, column = c)
        target_cell = target_sheet.cell(row = r, column = c)
        
        target_sheet.cell(row = r, column = c, value = source_cell.value)
        target_cell.fill = copy(source_cell.fill)
        
        if source_cell.has_style:
            target_cell._style = copy(source_cell._style)
            target_cell.font = copy(source_cell.font)
            target_cell.border = copy(source_cell.border)
            target_cell.fill = copy(source_cell.fill)
            target_cell.number_format = copy(source_cell.number_format)
            target_cell.protection = copy(source_cell.protection)
            target_cell.alignment = copy(source_cell.alignment)
            
        if source_cell.hyperlink:
            target_cell.hyperlink = copy(source_cell.hyperlink)
            
        if source_cell.comment:
            target_cell.comment = copy(source_cell.comment)

target_workbook.save(r'c:/users/administrator/desktop/demo3.xlsx')