如何使用openpyxl并仍保持OOP结构? [英] How do I use openpyxl and still maintain OOP structure?

问题描述

我正在使用python进行一些模拟，并使用openpyxl生成报告.现在，将模拟结果分成一个excel文件的几页.根据OOP的原理，我的结构应具有一个实现基本操作的基础模拟器类和几个实现对模拟器的修改的派生类.由于与类相关的功能应保留在该类中，因此我希望报表由派生类(及其所有样式和格式等)生成.然后，可能是一个驱动程序类或函数将所有这些报告表放入一本工作簿中.但是据我所知，没有办法在openpyxl中复制工作表.现在看来我已经打破了OOP模型.有没有解决的办法?

I am using python to do some simulations and using openpyxl to generate the reports. Now the simulation is results are to be divided into several sheets of an excel file. By the principles of OOP my structure should have a base simulator class which implements basic operations and several derived classes which implement modifications to simulator. Since functions related to a class should remain with the class I want the report sheets to be generated by the derived classes (with all its styling and formatting etc). Then maybe a driver class or function which takes all these report sheets and puts them in one work book. But as far as I can tell there is no way to copy a worksheet in openpyxl. Now it seems like I have broken the OOP models. Is there a way out of this?

修改

这是我的代码的示例.这是一个修剪过的无脂肪版本，真正的课程不是那么简单

Here is an example for my code. This is a trimmed fat free version, the real class is not that simple

from openpyxl import Workbook
from openpyxl.styles import Font

class sim1:#this will inherit from another class sim which has basic operations
    def __init__(self):
        #assume function calling and complex math here
        self.x = 1
    def f1OverRide(self):
        #over rides some function in sim to implement custom method for sim1 (several of these)
        return 23
    def get_sheet(self):
        wb = Workbook()
        ws = wb.active
        ws['A1'] = self.x
        #example formatting real formatting is pretty complex
        ws['A1'].font = Font(size=12,name='Calibri')
        return ws

class sim2:#this will inherit from another class sim which has basic operations
    def __init__(self):
        #assume function calling and complex math here
        self.x = 12
    def f1OverRide(self):
        #over rides some function in sim to implement custom method for sim1 (several of these)
        return 42
    def get_sheet(self):
        wb = Workbook()
        ws = wb.active
        ws['A1'] = self.x
        #example formatting, real formatting is pretty complex
        ws['A1'].font = Font(size=14,name='Calibri',color='ff2223')
        return ws


s1 = sim1()
s2 = sim2()
# now I want to get the sheets for sim1 and sim2 and combine in 1 workbook
wb = Workbook()
ws1 = s1.get_sheet()
ws2 = s2.get_sheet()
# dont know what to do now :( openpyxl can not copy sheet into this workbook

推荐答案

OOP 在工作簿之间复制工作表，例如:

from openpyxl import Workbook
from openpyxl.styles import Font
from copy import copy

class sim():
    def __init__(self, n):
        self.n = n

    def get_sheet(self):
        #...
        wb = Workbook()
        ws = wb.active
        ws['A1'] = 'sim'+str(self.n)
        if self.n == 1:
            ws['A1'].font = Font(size=12,name='Calibri')
        else:
            ws['A1'].font = Font(size=14, name='Calibri', color='ff2223')
        return ws

class sim_Workbook(Workbook):
    # overload Workbook.copy_worksheet
    def copy_worksheet(self, from_worksheet):
        # Create new empty sheet and append it to self(Workbook)
        ws = self.create_sheet( title=from_worksheet.title )

        for row, row_data in enumerate(from_worksheet.rows,1):
            for column, from_cell in enumerate(row_data,1):
                cell = ws.cell(row=row, column=column)
                cell.value = from_cell.value
                cell.font = copy(from_cell.font)
s1 = sim(1)
s2 = sim(2)

wb = sim_Workbook()
wb.copy_worksheet( s1.get_sheet() )
wb.copy_worksheet( s2.get_sheet() )
wb.save('../test/test.xlsx')  

---

#example格式实际格式非常复杂

#example formatting real formatting is pretty complex

您必须按样式复制复杂格式，如示例中的font所示.这可能导致巨大的工作量，具体取决于您必须复制多少个单元格.
阅读此内容以获得有关此内容的提示，但是当您从工作簿复制到工作簿

You have to copy your complex formatting, style by style as shown by font in the example. This could lead to huge workload, depending how many cells you have to copy.
Read this to get a hint about this, but you can't do it 1:1 as you copy from workbook to workbook copying-styles-from-a-range-to-another-range

使用Python:3.4.2测试-openpyxl:2.4.1-LibreOffice:4.3.3.2

Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2

这篇关于如何使用openpyxl并仍保持OOP结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！