如何使用pyodbc查询Excel Table（非named range）？

我想用pyodbc查询Excel Table，测试时创建了包含多个命名区域（named range）和一个名为MyTable的Excel Table的文件。通过Microsoft Excel Driver建立连接后，使用Cursor对象的tables方法仅能查看到工作表和命名区域，无法找到MyTable，请问是否有办法查询这个Excel Table？

测试代码及输出 #

1. 查看可用ODBC驱动

import pyodbc
for driver in pyodbc.drivers():
    print(driver)
    if '.xlsx' in driver:
        xlsxDriver = driver

输出：

SQL Server  
ODBC Driver 17 for SQL Server  
Microsoft Access Driver (*.mdb, *.accdb)  
Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)  
Microsoft Access Text Driver (*.txt, *.csv)  

2. 连接Excel文件并查看所有表/区域

conn_str=  (r'DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};'
            r'DBQ=C:\Users\ondre\OneDrive\Documents\openpyxlTEST.xlsx;'
            r'ReadOnly=1')

cnxn = pyodbc.connect(conn_str, autocommit = True)
crsr = cnxn.cursor()

[worksheet for worksheet in crsr.tables()]

输出：

[('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'Sheet1$', 'SYSTEM TABLE', None),
 ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'Sheet2$', 'SYSTEM TABLE', None),
 ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'MyTable2', 'TABLE', None),
 ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'MyTable3', 'TABLE', None),
 ('C:\\Users\\ondre\\OneDrive\\Documents\\openpyxlTEST.xlsx', None, 'MyTable4', 'TABLE', None)]

3. 查询命名区域示例（可正常执行）

crsr.execute("SELECT * FROM MyTable3")
[row for row in crsr]

输出：

[(datetime.datetime(1939, 3, 8, 0, 0), '1939-03-08 00:00:0', 2.0),  
 (datetime.datetime(2063, 5, 13, 0, 0), '2063-05-13 00:00:0', 3.0),  
 (datetime.datetime(2186, 1, 17, 0, 0), '2186-01-17 00:00:0', 5.0),  
 (datetime.datetime(2210, 8, 13, 0, 0), '2210-08-13 00:00:0', 7.0),  
 (datetime.datetime(2397, 12, 5, 0, 0), '2397-12-05 00:00:0', 8.0),  
 (datetime.datetime(2420, 12, 9, 0, 0), '2420-12-09 00:00:0', 5.0),  
 (datetime.datetime(2524, 7, 22, 0, 0), '2524-07-22 00:00:0', 1.0),  
 (datetime.datetime(2623, 7, 19, 0, 0), '2623-07-19 00:00:0', 5.0),  
 (datetime.datetime(2746, 7, 1, 0, 0), '2746-07-01 00:00:0', 10.0),  
 (datetime.datetime(2836, 12, 22, 0, 0), '2836-12-22 00:00:0', 3.0)]

内容的提问来源于stack exchange，提问作者Ondrej