# @File : class_01_do_excel.py
# coding=gbk
#pip install openpyxl
#新建.xlsx,一定要右键新建
from openpyxl import load_workbook #可以对Excel进行读写操作
#1.打开Excel
workbook = load_workbook('http_requests.xlsx')
#2.定位表单
sheet = workbook['Sheet1']
#3.读取单元格数据,读取X行Y列单元格里面的value值,行列值从1开始
cell_data = sheet.cell(1,1).value
print('读取表格中第一行第一列单元格的数据是:{}'.format(cell_data))
#4.获取最大的行列值
max_row = sheet.max_row
max_cow = sheet.max_column
print('最大的行值:',max_row)
print('最大的列值:',max_cow)
#5.单元格内写入数据
# sheet.cell(21,1).value='http://47.107.168.87:8080/futureloan/mvc/api/member/recharge' #第21行第1列,写入数据
# workbook.save('http_requests.xlsx')#保存表格,,一定要确保表格已关闭,否则报错PermissionError: [Errno 13] Permission denied
#6.新建excel表格:保存时,名字不重复就可以新建
# from openpyxl import Workbook # W一定大写
# wb = Workbook()
# wb.save('新建excel表格.xlsx')
#7.读取每一行的数据,存储到一个字典里;读取的所有行的数据存在一个列表中
test_data = []
for i in range(2,sheet.max_row+1):
sub_data = {}
sub_data['url'] = sheet.cell(i,1).value
sub_data['params'] = sheet.cell(i,2).value
sub_data['method'] = sheet.cell(i,3).value
sub_data['excepted'] = sheet.cell(i,4).value
test_data.append(sub_data)
print('http_requests.xlsx读取的结果是:',test_data)