python中关于Excel的简单操作

1. 打开桌面的excel.xlsx：

import os
import openpyxl

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

wb = openpyxl.load_workbook(r'C:\Users\Desktop\excel.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)