关于整合openpyxl.Workbook和pycel.excelcompiler遇到的问题！(语言-python)

Question

整合openpyxl Workbook和pycel excelcompiler遇到的问题

在我处理大量Excel数据过程中用openxl,但是他只能获取pre-calculated数据，而我需要获取数据update以后的最新计算值，所以我又用pycel.excelcompiler做计算。虽然不能做到所有计算公式的evaluate，但是达到了80%的结果。我在整理原始coding过程中，想把自己写的一些code整合到几个class里面，比如ExcelBook, ExcelSheet,ExcelRow,ExcelCol,ExcelCell...，所以想从Workbook inharitance做起。

1）不过第一步就无法跨越我的class constructor。我的极简class：

import openpyxl
from openpyxl import Workbook
class ExcelBook(Workbook) : # inherited from module(workbook) class(Workbook）
    def __init__(self) :
        Workbook.__init__(self, write_only=False, iso_dates=False,) # call base constructor
    def getWorkbook(self, filePath) :
        self.__book = openpyxl.load_workbook(filePath, read_only=True, data_only=True)
        self.path = filePath 
        return self.wbook
    def sheetnames(self) : 
        if hasattr(self, '__book') : return self.__book.sheetnames
        else : return None

设计的应用：

    exls=ExcelBook()
    exls.getWorkbook(r'somefile.xlsx')
    print(exls.path)
    for s in exls.sheetnames() :
        print(s)
        sh = exls.getSheet(s)
        print(sh.getCellValue('L1701'))
        print(sh.getCellValue('N1701').cellType())

跑起来在base constructor上的错：

PS D:\Workspaces\stocks> python MyExcel.py
Traceback (most recent call last):
  File "D:\Workspaces\stocks\MyExcel.py", line 201, in <module>
    exls=ExcelBook()
  File "D:\Workspaces\stocks\MyExcel.py", line 46, in __init__
    Workbook.__init__(self, write_only=False, iso_dates=False,) # call base constructor
  File "C:\Users\dell\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\workbook\workbook.py", line 83, in __init__
    self._sheets.append(Worksheet(self))
  File "C:\Users\dell\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\worksheet\worksheet.py", line 97, in __init__
    _WorkbookChild.__init__(self, parent, title)
  File "C:\Users\dell\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\workbook\child.py", line 47, in __init__
    self.title = title or self._default_title
  File "C:\Users\dell\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\workbook\child.py", line 96, in title
    value = avoid_duplicate_name(self.parent.sheetnames, value)
  File "C:\Users\dell\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\workbook\child.py", line 22, in avoid_duplicate_name
    match = [n for n in names if n.lower() == value.lower()]
TypeError: 'method' object is not iterable

2）pycel excelcompiler不提供median, geomean, stdev 公式的计算
有没有人做过这方面的尝试，扩展它的计算功能？在哪里可以看看样板？

from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell import Cell
import xlrd
from pycel.excelcompiler import ExcelCompiler

class ExcelSheet(Worksheet) :
    def __init__(self, parent, title=None) :
        Worksheet.__init__(self, parent, title)
    def getCellValue(self, address: str) : # address in 'A1' format
        cell = ExcelCell(self[address])                 # get cell & cast into my class
        if not cell.isFormula() : return cell.value     # return none formualted cell
        if cell.value is not None : return cell.value   # return as precomputed cell
        return self.__compute(address)
    def __compute(self, address) :
        if not hasattr(self, '__calculator') : # If the computation engine is not created yet, create it.
            self.__calculator = ExcelCompiler(self.parent.path)
        self.__calculator.recalculate()
        return self.__calculator.evaluate(f"{self.name}!{address}")
class ExcelCell(Cell) :
    def __init__(self, worksheet):
        Cell.__init__(self,worksheet)        
    def cellType(self) : return self.data_type
    def isFormula(self) : return self.data_type == 'f'

第一次参与和提问，请多多指教！

Answer 1

看看了看你的代码，其中类继承的方法有问题，你可以用以下方法进行继承：

import openpyxl
from openpyxl import Workbook


class ExcelBook(Workbook):  # inherited from module(workbook) class(Workbook）
    def __init__(self, write_only, iso_dates):
        super().__init__(write_only, iso_dates)
        self.wbook = None
        self.book = None

    def getWorkbook(self, filePath):
        self.book = openpyxl.load_workbook(filePath, read_only=True, data_only=True)
        self.path = filePath
        return self.wbook

    def getsheetnames(self):
        if hasattr(self, '__book'):
            return self.__book.sheetnames
        else:
            return None


exls = ExcelBook(write_only=False, iso_dates=False)
exls.getWorkbook(r'C:\Users\Desktop\test.xlsx')
print(exls.path)

代码中对初始化以及类中的函数名进行了一些修改，以及新增了一些变量的定义
运算的输出结果为
C:\Users\Desktop\test.xlsx
能够正常运行
如果问题得到解决的话请点 采纳~~~~