pyqt QAxWidget 读写Excel文件

QaxWidget相比openpyxl 的方式区别是提供了图形界面，excel的输入修改不用再编写代码．activeX技术是个过时的技术，发挥下余热． 

# -*- coding: utf-8 -*-
from PyQt5.QAxContainer import QAxWidget
from PyQt5.QtWidgets import QWidget, QVBoxLayout, QPushButton, QFileDialog
from PyQt5 import QtWidgets
from pyqtconsole.console import PythonConsole
import os
import getpath
import myglobal
class Window(QWidget):

    def __init__(self, *args, **kwargs):
        super(Window, self).__init__(*args, **kwargs)
        layout = QVBoxLayout(self)
        self.ax = QAxWidget(self)#"Excel.Application",self)
        # self.ax = AxWidget()#QAxWidget("{000208DB-0000-0000-C000-000000000046}",self)#book
        # self.excel.setProperty("Visible", True);
        # self.excel=Application()
        # print(dir(self.axWidget))
        # self.axWidget.initialize(self.excel._dispobj_)#a._dispobj_._oleobj_
        h=QtWidgets.QHBoxLayout()
        h.addWidget(QPushButton('打开excel', self, clicked=self.onOpenWord))
        h.addWidget(QPushButton('getdata', self, clicked=self.getdata))
        h.addWidget(QPushButton('save as', self, clicked=self.saveas))
        h.addWidget(QPushButton('calc', self, clicked=self.calc))
        # h.addWidget(QPushButton('quit', self, clicked=self.quitWord))
        # self.console = interpreter.TerminalPython(self, mainw=self, app=QtWidgets.qApp)

        self.console=PythonConsole()
        self.console.push_local_ns('app', QtWidgets.qApp)
        self.console.push_local_ns('mainw', self)
        self.console.eval_queued()
        self.console.setSizePolicy(QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Fixed)
        self.console.resize(self.console.width(),200)
        h.addWidget(self.console)
        layout.addLayout(h)
        layout.addWidget(self.ax)
        # self.workbooks = self.excel.querySubObject("Workbooks")
        self.ax.setControl(os.path.join(getpath.getpath(),"重量法.XLSX"))
        # self.excel=self.ax.querySubObject("Application")
    def getdata(self):
        s1=self.ax.querySubObject("Activesheet")
        c11=s1.querySubObject("Cells(1,1)")
        print(c11.property("Value"))
        pass
    def getCellValue(self,i,j):
        s1=self.ax.querySubObject("Activesheet")
        c11=s1.querySubObject(f"Cells({i},{j})")
        return(c11.property("Value"))
    def setCellValue(self,i,j,value):
        # 'ws=self.ax.querySubObject("Worksheets")
        # 's1=ws.querySubObject("Item(int)",1)
        s1=self.ax.querySubObject("Activesheet")
        c11=s1.querySubObject(f"Cells({i},{j})")
        c11.setProperty("Value",value)
    def getDblOne(self,i, jstr):
        if self.getCellValue(i, jstr)== "":
            return 1.0
        else:
            r=self.getDbl(i, jstr)
            if r==0:
                r=1.0
            return r
    def getDbl(self,i, jstr):
        s1=self.getCellValue(i, jstr)
        r=myglobal.myfloat(s1)
        # print(s1,r)
        return r
    def save_zhongliang(self,ffbh, ejff, ejjs):
        # Dim r As Range
        # If ActiveSheet Is Nothing Then
        #  Exit Sub
        # End If
        # Set r = ActiveSheet.UsedRange
        # Dim f As String
        # Dim m As String
        # Dim tpbh As String, syy As String, ysry As String, syyqm As String, yqbh As String
        # Dim row_num As Integer
        row_num = self.getRowCount()
        # Dim sid As String
        # Dim i As Integer
        ActiveSheet=self.ax.querySubObject("Activesheet")
        i=7
        while(i<=row_num):
            sid=ActiveSheet.querySubObject(f"Cells({i},{"A"})").property("Value")
            if  sid!= "" and sid!=None:
                break
            i+=1
        f = ActiveSheet.querySubObject(f"Cells({2},{"E"})").property("Value")#ActiveSheet.Cells(2, "E").Value
        if f==None:
            f=""
        ffbh = ActiveSheet.querySubObject(f"Cells({1},{"B"})").property("Value")#ActiveSheet.Cells(1, "B").Value
        syy = ActiveSheet.querySubObject(f"Cells({1},{"K"})").property("Value")#ActiveSheet.Cells(1, "K").Value
        ysry = ActiveSheet.querySubObject(f"Cells({1},{"N"})").property("Value")#ActiveSheet.Cells(1, "N").Value
        syyqm = syy
        yqbh = ActiveSheet.querySubObject(f"Cells({1},{"H"})").property("Value")#ActiveSheet.Cells(1, "H").Value
        item_name = ActiveSheet.querySubObject(f"Cells({2},{"B"})").property("Value")#ActiveSheet.Cells(2, "B").Value
        ActiveSheet.querySubObject(f"Cells({3},{"P"})").setProperty("Value",item_name)#ActiveSheet.Cells(3, "P").Value = item_name
        # Dim row As Integer
        # Dim output_folder As String
        # Dim at As Integer
        # Dim fname As String
        i=row_num
        while i>=1:
            v=ActiveSheet.querySubObject(f"Cells({i},{"A"})").property("Value")
            print(i,v)
            if v!=None and v!="":
                row = i
                break
            i=i-1
        r=ActiveSheet.querySubObject("UsedRange")
        print(row,r.querySubObject("Rows").property("Count"))
        while row + 1 <= r.querySubObject("Rows").property("Count"):
            print(row+1,r.querySubObject("Rows").property("Count"))
            # ActiveSheet.Rows(row + 1).Delete
            ActiveSheet.querySubObject(f"Rows({row+1})").dynamicCall("Delete()")
        at = 1
        ActiveSheet.querySubObject(f"Cells({row+1},{at})").setProperty("Value","ffbh&" + ffbh)#ActiveSheet.Cells(row + 1, at).Value = "ffbh&" + ffbh
        ActiveSheet.querySubObject(f"Cells({row+2},{at})").setProperty("Value","syy&" + syy)# ActiveSheet.Cells(row + 2, at).Value = "syy&" + syy
        ActiveSheet.querySubObject(f"Cells({row+3},{at})").setProperty("Value","ysry&" + ysry)# ActiveSheet.Cells(row + 3, at).Value = "ysry&" + ysry
        ActiveSheet.querySubObject(f"Cells({row+4},{at})").setProperty("Value","yqbh&" + yqbh)# ActiveSheet.Cells(row + 4, at).Value = "yqbh&" + yqbh
        ActiveSheet.querySubObject(f"Cells({row+5},{at})").setProperty("Value","syyqm&" + syyqm)# ActiveSheet.Cells(row + 5, at).Value = "syyqm&" + syyqm
        ActiveSheet.querySubObject(f"Cells({row+6},{at})").setProperty("Value","dict0&" + f)# ActiveSheet.Cells(row + 6, at).Value = "dict0&" + f
        ActiveSheet.querySubObject(f"Cells({row+6},{at})").setProperty("Value","系数")# ActiveSheet.Cells(row + 6, at + 1).Value = "Ñõ»¯ÎïϵÊý"
        ActiveSheet.querySubObject(f"Cells({row+7},{at})").setProperty("Value","ejff&" + ejff)# ActiveSheet.Cells(row + 7, at).Value = "ejff&" + ejff
        ActiveSheet.querySubObject(f"Cells({row+7},{at})").setProperty("Value","二级方法")# ActiveSheet.Cells(row + 7, at + 1).Value = "¶þ¼¶·½·¨"
        ActiveSheet.querySubObject(f"Cells({row+8},{at})").setProperty("Value","ejfxjs&" + ejjs)# ActiveSheet.Cells(row + 8, at).Value = "ejfxjs&" + ejjs
        ActiveSheet.querySubObject(f"Cells({row+8},{at})").setProperty("Value","二级分析技术")# ActiveSheet.Cells(row + 8, at + 1).Value = "¶þ¼¶·ÖÎö¼¼Êõ"
        output_folder = "d:\\"
        # 'output_folder = "\\10.0.114.230\»¯Ñ§·ÖÎöÊÒ\ÒÇÆ÷²É¼¯\EMGA930_test\930"
        # output_folder = "\\10.0.114.230\»¯Ñ§·ÖÎöÊÒ\ÒÇÆ÷²É¼¯\caiji\cl\zhongliang"
        if sid == "":
            fname = "tmp"#Format(Now, "yyyymmdd hhmmss")
        else:
            fname = sid
        filename=os.path.join(output_folder,fname)
        self.ax.dynamicCall("SaveAs(const QString&)",filename)
    def saveas(self):
        self.save_zhongliang("","","")
        return
        filename=os.path.join(getpath.getpath(),"a1.xlsx")
        self.ax.dynamicCall("SaveAs(const QString&)",filename)
        # workbook->synamicCall(“Close(Boolean)”, false); //！ 关闭文件
    def getRowCount(self):
        s1=self.ax.querySubObject("Activesheet")
        r=s1.querySubObject("UsedRange")
        rows=r.querySubObject("Rows")
        return rows.property("Count")
    def runCalc(self):
        excel=self.ax.querySubObject("Application")
        excel.dynamicCall("Run(const QString&)","mycalc")
    def calc(self):
        print("calc")
        # 'Dim f As Double, m2 As Double, m1 As Double, m0 As Double, m3 As Double, m4 As Double, m5 As Double
        # 'Dim row_num As Integer
        # 'Dim mass As Double, V1 As Double, V2 As Double
        # 'Dim item_name As String
        # 'Dim r As Range
        # If ActiveSheet Is Nothing Then
        #  Exit Sub
        # End If
        # Set r = ActiveSheet.UsedRange
        f =self.getDblOne(2, "E")
        item_name =self.getCellValue(2, "B")
        self.setCellValue(3, "P",item_name)
        row_num =self.getRowCount()
        i = 5
        while i<= row_num:
            print(i)
            mass = self.getDbl(i, "D")
            m2 = self.getDbl(i, "H")
            m1 = self.getDbl(i, "I")
            m0 = self.getDbl(i, "J")
            m3 = self.getDbl(i, "K")
            m4 = self.getDbl(i, "L")
            m5 = self.getDbl(i, "M")
            V1 = self.getDbl(i, "F")
            V2 = self.getDbl(i, "G")
            if mass == 0:
                pass
                print("pass")
            else:
                if V2 == 0:
                    V1 = 1
                    V2 = 1
                out = (m2 - m1 - m0 + m3 - m4 - m5) / mass * V1 / V2 * f * 100
                print(m2,m1,m0,m3,m4,m5,mass,V1,V2,f)
                print(out)
                self.setCellValue(i, "P",out)
            i+=1
        self.ax.setFocus()
        pass
    def onOpenWord(self):
        path, _ = QFileDialog.getOpenFileName(
            self, '请选择excel文件', '', 'excel(*.xlsx *.xls)')
        if not path:
            return
        # self.ax.querySubObject("Open(const QString&)", path);
        # 不显示窗体
        # self.axWidget.dynamicCall('SetVisible (bool Visible)', 'false')
        # self.axWidget.setProperty('DisplayAlerts', False)
        self.ax.setControl(path)
    def closeEvent(self,e):
        # self.ax.dynamicCall("Close()");
        self.ax.clear()
        # self.excel.dynamicCall('Quit()')
        print("close")

if __name__ == '__main__':
    import sys
    from PyQt5.QtWidgets import QApplication
    app = QApplication(sys.argv)
    w = Window()
    w.resize(1300,800)
    w.show()
    sys.exit(app.exec_())