使用openpyxl实现excel文件的读取操作
1、环境准备
python3环境、安装openpyxl模块
2、excel文件数据准备
3、为方便直接调用,本代码直接封装成类
1 from openpyxl import load_workbook 2 class DoExcel: 3 def __init__(self,filename): 4 ''' 5 :param filename: excel文件名 6 ''' 7 self.file = filename 8 self.wk = load_workbook(self.file) 9 10 def do_excel(self,sheetname): 11 ''' 12 :param sheetname: 工作簿名称 13 :return: 14 ''' 15 sheet = self.wk[sheetname] 16 max_row = sheet.max_row #最大行 17 max_column = sheet.max_column #最大列 18 data = [] #定义一个空列表,用于存储所有数据 19 for r in range(2,max_row+1): 20 subdata = {} #定义一个字典,用于存储每行数据 21 for c in range(1,max_column+1): 22 key = sheet.cell(1,c).value #取第一行表头数据 23 subdata[key] = sheet.cell(r,c).value #字典格式,表头作为key 24 data.append(subdata) 25 return data 26 27 if __name__ == '__main__': 28 file_name = "test.xlsx" 29 datas = DoExcel(file_name).do_excel('students') 30 for data in datas: 31 print(data)
运行结果: