如何将单元格以其设置的样式从一个工作簿复制到另一工作簿? [英] How to copy cell from one workbook to an other workbook with its set style?

问题描述

我一直试图将单元格从一个工作簿复制到另一个工作簿，但格式化失败了.我尝试了很多建议，但是没有用.

I have been trying to copy a cell from one workbook to another with formatting only to fail. I have tried numerous suggestion but nothing works.

我尝试过:

def copyCellStyle(new_cell,cell):
    new_cell.font = copy(cell.font)
    new_cell.border = copy(cell.border)
    new_cell.fill = copy(cell.fill)
    new_cell.number_format = copy(cell.number_format)
    new_cell.protection = copy(cell.protection)
    new_cell.alignment = copy(cell.alignment)

然后按以下方式使用它:

then use it like:

new_cell.value=old_cell.value 
copyCellStyle(new_cell,old_cell)

它运行没有错误，但是工作没有完成.

It runs without errors but the job is not done.

我在styles类中的其他方法出错.何时

I get errors with the other methods in the styles class. When

new_cell._style = copy(old_cell._style)

使用此方法是在保存复制的文件时收到此错误.

this is used I get this error while I am saving the copied file.

    Traceback (most recent call last):
  File "C:\Python Programs\test\test2.py", line 40, in <module>
    wb2.save("Test3.xlsx")
  File "C:\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
    save_workbook(self, filename)
  File "C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
    writer.save()
  File "C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 84, in write_data
    stylesheet = write_stylesheet(self.workbook)
  File "C:\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py", line 240, in write_stylesheet
    xf.alignment = wb._alignments[style.alignmentId]
IndexError: list index out of range

当我检查单元格是否具有以下样式时:

Also when I check if cell has style like:

Traceback (most recent call last)
 File "C:\Python Programs\test\test2.py", line 37, in <module>
    if cell.has_style: 
AttributeError: module 'openpyxl.cell' has no attribute 'has_style'

我正在使用python 3.x.

I am using python 3.x.

谁能告诉我一种可行的方法来实现这一目标.

Can anyone tell me a working proper method to achieve this.

提前谢谢！

推荐答案

您需要了解 copy(cell._style)的作用以了解该异常.每个 openpyxl 工作簿在后台都有受保护的不同样式的属性.例如，属性 _alignments 将保存一个具有工作簿中的对齐方式的 list ，例如:

You need to understand what does copy(cell._style) does to understand that exception. Behind the scenes each openpyxl workbook has protected attributes for different styles. For example the attribute _alignments will hold a list with the alignments in the workbook, such as these:

_style 是一个列表，仅按其索引引用提到的属性，例如:

_style is a list that only refers to the mentioned attributes by their index, as such:

因此，以一个没有应用对齐方式的工作簿为例.在这种情况下，工作簿的 _alignments 列表属性将只有1个项目，即defualt(空)对齐方式，因此:

So, lets take for example a workbook with no alignment styles applied. in that case the _alignments list attribute of the workbook will have only 1 item which is the defualt (empty) alignment so:

len(new_workbook._alignments)== 1

当您将新的工作簿 copy(cell._style)复制到新工作簿时，它会尝试通过引用受保护的属性样式(路线，边框等)来获取新样式.但是，由于新工作簿没有样式，因此引用 _alignment [1] 将引发 IndexError ，因为新工作簿列表中只有一个项目，默认项目为(存储在 _alignments [0] )中.

When you copy(cell._style) to the new workbook it tries to get its new style by reffering to the protected attributes styles (alignments, borders, etc.). But, as the new workbook has no styles the reference _alignment[1] will raise an IndexError as the new workbook list has only one item on that list, the default one (which is stored in _alignments[0]).

您的初始方法是正确的.如果要复制到新工作簿，则应遍历工作表的单元格，复制值，然后复制各个样式:

Your initial approach is the correct one. If you are copying to a new workbook you should iterate over the cells of the sheet copying the value and then the individual styles:

from openpyxl import load_workbook, Workbook
from copy import copy

original_wb = load_workbook('example.xlsx')
original_sheet = original_wb['Sheet1']

new_wb = Workbook()
new_ws = new_wb.active

for row in original_sheet.rows:
    for cell in row:
        new_ws[cell.coordinate] = cell.value
        new_cell = new_ws[cell.coordinate]

        if cell.has_style:
            new_cell.font = copy(cell.font)
            new_cell.border = copy(cell.border)
            new_cell.fill = copy(cell.fill)
            new_cell.number_format = copy(cell.number_format)
            new_cell.protection = copy(cell.protection)
            new_cell.alignment = copy(cell.alignment)

new_wb.save('new.xlsx')

_style 方法在尝试复制到新工作簿时将不起作用，并且在同一工作簿中不是必需的，因为您可以使用 workbook.copy_worksheet (文档).

The _style method will not work when trying to copy to a new workbook and is not necessary within the same workbook as you can use workbook.copy_worksheet(documentation).

这篇关于如何将单元格以其设置的样式从一个工作簿复制到另一工作簿?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！