python中关于Excel的简单操作

1. 打开桌面的excel.xlsx：

import os
import openpyxl

path = r"C:UsersDesktop"
os.chdir(path)  # 修改工作路径
wb = openpyxl.load_workbook(excel.xlsx)

wb = openpyxl.load_workbook(rC:UsersDesktopexcel.xlsx)

2. 工作表的读取

print(wb.sheetnames)  # 打印所有表

sheet = wb["新的sheet"]  # 获取指定sheet表
print(sheet)
print(表格的尺寸大小为: + sheet.dimensions)  # 获取表格的尺寸大小

sheet1 = wb.active  #获取活动表,关闭Excel前最后查看的表
print(sheet1)

 # 获取单元格的数据
cell1 = sheet[C5]
cell2 = sheet[F15]
print(cell1, cell2)
print(cell1.value, cell2.value) 
cell3 = sheet[E12].value 
print(cell3)
cell4 = sheet.cell(row=1, column=2).value
cell5 = sheet.cell(row=3, column=1).value
print(cell4, cell5)
cell6 = sheet.cell(5, 1)
print(cell6.value, cell6.row, cell6.column, cell6.coordinate)

cell7 = sheet[A1:A5]  # 获取A1到A5的数据
for i in cell7:
    for j in i:
        print(j.value)

cell8 = sheet["A"]
for i in cell8:
    print(i.value)

cell9 = sheet[5]
for i in cell9:
    print(i.value)

print(按行获取值)
for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

print(按列获取值)
for i in sheet.iter_cols(min_row=2, max_row=5, min_col=1, max_col=2):
    for j in i:
        print(j.value)

rows = sheet.max_row
column = sheet.max_column
print(rows, column)