# -*- coding:UTF-8 -*-
from openpyxl import Workbook, load_workbook
# # 创建一个工作簿
# wb = Workbook() # 创建一个工作簿,且默认创建一个名为sheet的表单
# # 手动创建一个sheet,指定位置:0代表第一个表单(默认是最后一个)
# wb.create_sheet("test1", 0)
# ws = wb.active # workbook.active 获取当前活跃的sheet,默认是第一个sheet [test1,sheet],获取的是test1
#读取一个workbook
wb=load_workbook("D:/PycharmProjects/Oasis_auto/data/excel_write.xlsx")
# 根据sheet名称获取表单
ws = wb["test1"]
# 写入单元格 ,2种方式
ws['A1'] = "a11"
ws.cell(row=1, column=2, value="b1")
ws.cell(row=1, column=3, value="c1")
# 访问多个单元格 cell_range:A1,B1,A2,B2
cell_range = ws["A1":"B2"]
# 按行获取单元格
'''
1.若表格为空,列的范围是第一列,行是个变量
2.若表格有数据,行是变量,比如最多第五列有数据,那列只访问的第五列
'''
row_single = ws[3] # (<Cell 'test1'.A1>, <Cell 'test1'.B1>)
row_range = ws[1:3] # ((<Cell 'test1'.A1>, <Cell 'test1'.B1>), (<Cell 'test1'.A2>, <Cell 'test1'.B2>))
print(row_single)
for row in ws.iter_rows(min_row=1, max_row=2, min_col=2, max_col=3):
for cell in row:
print("cell:",cell) #cell: B1,C1,B2,C2
#获取单元格数据
for row in ws.iter_rows(min_row=1, max_row=2, min_col=2, max_col=3,values_only=True):
for cell in row:
print("cell-rowvalue:",cell)#单元格对应的数据
#按列获取单元格 For performance reasons the method iter_cols is not available in read-only mode.
for col in ws.iter_cols(min_col=1, max_col=2, min_row=1, max_row=2):
for cell in col:
print("cell-col:",cell) #cell: A1,A2,B1,B2
#获取单元格数据
for col in ws.iter_cols(min_col=1, max_col=2, min_row=1, max_row=2,values_only=True):
for cell in col:
print("cell-col-value:",cell) #单元格对应的数据
#获取所有单元格
print("ws_rows:",tuple(ws.rows)) #ws.rows: 按行获取ws中所有单元格
print("ws_cols:",tuple(ws.columns)) #ws.columns: 按列获取ws中所有单元格
#获取单元格数据 --按行读取 ,一行一条数据
for value in ws.values:
print(value) #每行的数值,一个元组,每个元素是对应行每列的数据
wb.save("D:/PycharmProjects/Oasis_auto/data/excel_read.xlsx")