讀excel

from openpyxl import load_workbook

fileName = 'data.xlsx'

wb = load_workbook(fileName)

sheet = wb.active # 獲取當(dāng)前被激活的工作薄

print(sheet.max_row) # 最大行數(shù)
print(sheet.max_column) # 最大列數(shù)

'''
方法一: 
- 通過獲取sheet的所有行數(shù)據(jù), 并獲取每行數(shù)據(jù)返回的cell對象得到其value值
- row值為元組數(shù)據(jù)
'''
for row in sheet.rows:
    name, sex = row
    print('%s--%s' % (name.value, sex.value))
    
'''
方法二:
- 獲取sheet的最大行數(shù), 并利用range()對sheet進行遍歷
- 從2開始目的是為了排除A1,B1等標(biāo)題行
- cell()可通過數(shù)字獲取對應(yīng)單元格
'''
for ri in range(2, sheet.max_row + 1):
    name = sheet.cell(row = ri, column = 1)
    sex = sheet.cell(row = ri, column = 2)
    print('%s--%s' % (name.value, sex.value))

結(jié)果為:

寫excel

from openpyxl import Workbook

# 操作工作表
wb = Workbook()

# 創(chuàng)建工作薄, 并在最前
sheet = wb.create_sheet('Sheet1', 0)

# 待寫入的數(shù)據(jù)
data = [
    ['Mike', '18'],
    ['Jack', '18'],
    ['Jerry', '18'],
    ['Jacky', '18'],
    ]

# 定義標(biāo)題行
sheet.cell(1, 1, 'name')
sheet.cell(1, 2, 'age')

# 將數(shù)據(jù)以行的形式添加到工作薄中
for row in data:
    sheet.append(row)

# 保存工作表
wb.save('examples.xlsx')

結(jié)果為: