1分钟带你学会使用Python读取读取xlsx格式的文件

1.安装 openpyxl 扩展

pip install openpyxl    # Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2

2.读取 xlsx 格式文件

# 导入 openpyxl 模块import openpyxl​# 加载工作簿wb = openpyxl.load_workbook(r"./阿里巴巴2020年股票数据.xlsx")​# 获取工作表名称# print(wb.sheetnames)    # ['股票数据', '学生成绩', '订单记录']​# 获取要操作的工作表sheet = wb.worksheets[0]​# 获取工作表中单元格的范围# print(sheet.dimensions)    # A1:G21​# 获取行数和列数# print(sheet.max_row, sheet.max_column)    # 21 7​# 获取指定单元格的值# print(sheet.cell(3,3).value)    # 211.729957​# 获取多个单元格的值# print(sheet["A2:C5"])    # ((<Cell '股票数据'.A2>, <Cell '股票数据'.B2>, <Cell '股票数据'.C2>), (<Cell '股票数据'.A3>, <Cell '股票数据'.B3>, <Cell '股票数据'.C3>), (<Cell '股票数据'.A4>, <Cell '股票数据'.B4>, <Cell '股票数据'.C4>), (<Cell '股票数据'.A5>, <Cell '股票数据'.B5>, <Cell '股票数据'.C5>))​# 获取单元格中所有的数据for row in range(2, sheet.max_row + 1):    for col in "ABCDEFG":        value = sheet[f"{col}{row}"].value        print(value, end="\t")    print()    # 换行的效果

• 执行效果

2020-12-01 00:00:00    213.63333994    210.729957    211.47284    212.482002    673600    212.1084    2020-12-02 00:00:00    214.63333994    211.729957    212.47284    213.482002    673601    213.1084    2020-12-03 00:00:00    215.63333994    212.729957    213.47284    214.482002    673602    214.1084    2020-12-04 00:00:00    216.63333994    213.729957    214.47284    215.482002    673603    215.1084    2020-12-05 00:00:00    217.63333994    214.729957    215.47284    216.482002    673604    216.1084    2020-12-06 00:00:00    218.63333994    215.729957    216.47284    217.482002    673605    217.1084    2020-12-07 00:00:00    219.63333994    216.729957    217.47284    218.482002    673606    218.1084    2020-12-08 00:00:00    220.63333994    217.729957    218.47284    219.482002    673607    219.1084    2020-12-09 00:00:00    221.63333994    218.729957    219.47284    220.482002    673608    220.1084    2020-12-10 00:00:00    222.63333994    219.729957    220.47284    221.482002    673609    221.1084    2020-12-11 00:00:00    223.63333994    220.729957    221.47284    222.482002    673610    222.1084    2020-12-12 00:00:00    224.63333994    221.729957    222.47284    223.482002    673611    223.1084    2020-12-13 00:00:00    225.63333994    222.729957    223.47284    224.482002    673612    224.1084    2020-12-14 00:00:00    226.63333994    223.729957    224.47284    225.482002    673613    225.1084    2020-12-15 00:00:00    227.63333994    224.729957    225.47284    226.482002    673614    226.1084    2020-12-16 00:00:00    228.63333994    225.729957    226.47284    227.482002    673615    227.1084    2020-12-17 00:00:00    229.63333994    226.729957    227.47284    228.482002    673616    228.1084    2020-12-18 00:00:00    230.63333994    227.729957    228.47284    229.482002    673617    229.1084    2020-12-19 00:00:00    231.63333994    228.729957    229.47284    230.482002    673618    230.1084    2020-12-20 00:00:00    232.63333994    229.729957    230.47284    231.482002    673619    231.1084