class DoExcel:
def __init__(self,file_name,sheet_name):
self.file_name=file_name
self.sheet_name=sheet_name
def get_data(self):
#打开excel
wb=load_workbook(self.file_name)
#定位sheet
sheet=wb[self.sheet_name]
#定位单元格
test_data=[]
for i in range(2,sheet.max_row+1):
sub_data = {}
sub_data['url']=sheet.cell(i,1).value
sub_data['data']=sheet.cell(i,2).value
sub_data['method']=sheet.cell(i,3).value
test_data.append(sub_data)
return test_data
if __name__ == '__main__':
res=DoExcel('test_data.xlsx','text').get_data()
print(res)
第二种:获取excel中第一行标题数据
from openpyxl import load_workbook
class DoExcel:
def __init__(self,file_name,sheet_name):
self.file_name=file_name
self.sheet_name=sheet_name
def get_header(self):
'''获取第一行标题'''
header=[]
wb=load_workbook(self.file_name)
sheet=wb[self.sheet_name]
for j in range(1,sheet.max_column+1):
header.append(sheet.cell(1,j).value)
return header
def get_excel(self):
'''获取excel所有值'''
wb=load_workbook(self.file_name)
sheet=wb[self.sheet_name]
header=self.get_header()
test_data=[]
for i in range(2,sheet.max_row+1):
sub_date={}
for j in range(1,sheet.max_column+1):
sub_date[header[j-1]]=sheet.cell(i,j).value#列表从是0开始;所有j要-1
test_data.append(sub_date)
return test_data
if __name__ == '__main__':
print(DoExcel('test_data.xlsx','text').get_excel())