使用Python win32com获取Excel工作表列表 [英] Using Python win32com to get list of Excel worksheets

问题描述

正在寻找一种简单的方法来使用Python 3中的win32com在Excel工作簿中获取工作表列表.我宁愿避免迭代，但找不到找到该函数的函数.

Looking for a simple way to get a list of the worksheets in an Excel workbook using win32com in Python 3. I would prefer to avoid iterating, but can't find a function to do so.

推荐答案

• Callig .Sheets 属性，返回 Sheets集合，< win32com.gen_py.Microsoft Excel 16.0对象库.Sheets实例位于0x1597583762504>
  • 要获取每个工作表，您必须进行迭代，这将返回对象< win32com.gen_py.Microsoft Excel 16.0 Object Library._Worksheet实例，位于0x1597583219080> 每张纸.
  • 要获取每个工作表的名称，必须使用 .Name 方法
  • Callig the .Sheets property of the workbook, returns a Sheets collection, <win32com.gen_py.Microsoft Excel 16.0 Object Library.Sheets instance at 0x1597583762504>
    • In order to get each sheet, you must iterate, which returns an object, <win32com.gen_py.Microsoft Excel 16.0 Object Library._Worksheet instance at 0x1597583219080> for each sheet.
    • To get the name of each sheet, you must use the .Name method
    • ws = {f'ws {i}':i的wb.Sheets(sheet.Name)，枚举中的工作表(wb.Sheets)}
    • 使用 ws ['ws0'].Range('A1').Value = 1
    分配一个值
    • ws = {f'ws{i}': wb.Sheets(sheet.Name) for i, sheet in enumerate(wb.Sheets)}
    • Assign a value with ws['ws0'].Range('A1').Value = 1

    import win32com.client as win32
    from pathlib import Path
    import sys
    
    win32c = win32.constants
    
    def run_excel(f_path: Path, f_name: str) -> list:
    
        filename = f_path / f_name
    
        # create excel object
        excel = win32.gencache.EnsureDispatch('Excel.Application')
    
        # excel can be visible or not
        excel.Visible = True  # False
        
        # try except for file / path
        try:
            wb = excel.Workbooks.Open(filename)
        except com_error as e:
            if e.excepinfo[5] == -2146827284:
                print(f'Failed to open spreadsheet.  Invalid filename or location: {filename}')
            else:
                raise e
            sys.exit(1)
    
        # get worksheet names        
        sheet_names = [sheet.Name for sheet in wb.Sheets]
            
        wb.Close(True)
        excel.Quit()
            
        return sheet_names
    

    设置和函数调用

    # file path
    f_path = Path.cwd()  # file in current working directory
    # f_path = Path(r'c:\...\Documents')  # file located somewhere else
    
    # excel file
    f_name = 'test.xlsx'
    
    # function call
    run_excel(f_path, f_name)
    
    # output
    ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4']
    
    # create a worksheet object
    ws1 = wb.Sheets('Sheet1')
    

    • 如何在其中创建数据透视表带有python win32com的Excel
    • 使用Python自动化Excel
      • 数据透视表示例

      这篇关于使用Python win32com获取Excel工作表列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！