使用Python模块win32com.client运行Excel，进行Python版VBA编程（一）

示例如下：

# -*- coding: utf-8 -*-

import xlrd
from xlwt import *
from openpyxl import load_workbook
from win32com.client import Dispatch

import win32com.client
import win32api
import os

class ExcelHelper:
    def __init__(self, filename=None):
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        self.xlApp.Visible  = True #是否可视化编辑
        self.xlApp.ScreenUpdating = True#画面刷新显示
        self.xlApp.DisplayAlerts = True
        if filename:
            self.filename = filename
            if os.path.exists(self.filename):
                self.xlBook = self.xlApp.Workbooks.Open(filename)
            else:
                self.xlBook = self.xlApp.Workbooks.Add()
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = 'Untitle'

    def saveAs(self, newfilename=None):
        if newfilename:
            self.filename = newfilename
        self.xlBook.SaveAs(self.filename)

    def save(self):
        self.xlBook.Save()
    def close(self,Save=False):
        self.xlBook.Close(SaveChanges=Save)
        self.xlApp.Quit()
        del self.xlApp


    def copySheet(self,sheetname, before):
        "copy sheet"
        shts = self.xlBook.Worksheets
        shts(sheetname).Copy(None, shts(sheetname))

    def newSheet(self, newSheetName):
        sheet = self.xlBook.Worksheets.Add()
        sheet.Name = newSheetName
        sheet.Activate()

    def activateSheet(self, sheetName):
        self.xlBook.Worksheets(sheetName).Activate()

    def activeSheet(self):
        return self.xlApp.ActiveSheet

    def getCell(self, row, col, sheet=None):
        "Get value of one cell"
        if sheet:
            sht = self.xlBook.Worksheets(sheet)
        else:
            sht = self.xlApp.ActiveSheet
        return sht.Cells(row, col).Value

    def setCell(self, row, col, value, sheet=None):
        "set value of one cell"
        if sheet:
            sht = self.xlBook.Worksheets(sheet)
        else:
            sht = self.xlApp.ActiveSheet

        sht.Cells(row, col).Value = value

    def getRange(self, row1, col1, row2, col2, sheet=None):
        "return a 2d array (i.e. tuple of tuples)"
        if sheet:
            sht = self.xlBook.Worksheets(sheet)
        else:
            sht = self.xlApp.ActiveSheet
        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value

    def mergeCell(self, row1, col1, row2, col2, sheet=None):
        #单元格合并
        if sheet:
            sht = self.xlBook.Worksheets(sheet)
        else:
            sht = self.xlApp.ActiveSheet
        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Merge()

    def rowsCount(self):
        "return used rows count"
        sht = self.activeSheet()
        return sht.UsedRange.Rows.Count




if __name__ == "__main__":
    execl_filename = r'C:\Users\H\Desktop\execlopen\test.xlsx'
    # workbook = xlrd.open_workbook(execl_filename)
    # print(workbook.sheet_names()) #打印所有sheet
    # table = workbook.sheets()[0]  # 打开第一张表
    # print(table.nrows)  # 打印表的行数   nrows = table.nrows
    # xl.DisplayAlerts = 0


    xl = ExcelHelper(filename=execl_filename)
    """
    显示当前工作簿中的所有sheet
    """
    print(f"sheet number:{xl.xlBook.Worksheets.Count}")#sheet数量
    for i in range(1,xl.xlBook.Worksheets.Count + 1):#遍历所有sheet 并打印sheet的name
        sh = xl.xlApp.Worksheets(i)
        print(sh.Name)

    """
        复制一个sheet
    """
    sheetName = "Sheet1"

    # xl.copySheet(sheetName,sheetName)#复制Sheet1 放在sheet1后面 #方法1

    sheetCopySrcName = "Sheet2"#复制sheet1所有内容到sheet2
    # xl.xlBook.Worksheets(sheetName).Copy()
    # xl.xlBook.Worksheets(sheetCopySrcName).Paste()


    '''
    匹配单元格内容
    '''
    sheet = 'Sheet1'
    ret = xl.xlBook.Worksheets(sheet).UsedRange.Find("0x0001")#匹配单元格内容 寻找单元格内容为0x0009的单元格 若无则返回None
    if ret != None:
        print(f"row:{ret.row},col:{ret.Column}")
    else:
        print("Not Find")


    """
    复制单元格内容
    """
    sheet1 = 'Sheet1'
    sheet2 = 'Sheet2'
    sht = xl.xlBook.Worksheets(sheet1)
    xl.xlBook.Worksheets(sheet1).Activate()
    sht.Range(sht.Cells(11,2),sht.Cells(11,5)).Select()#选择单元格(11,2)->(11,5)的内容进行复制
    xl.xlApp.Selection.Copy()#复制选中内容

    sht = xl.xlBook.Worksheets(sheet2)
    sht.Activate()
    sht.Range(sht.Cells(11,2),sht.Cells(11,5)).Select()#选择单元格(11,2)->(11,5)的内容进行粘贴
    xl.xlApp.ActiveSheet.Paste()
    xl.close(Save=True)
    pass