python操作excel

日常生活中少不了對於資料的一些批次處理以及複雜邏輯，對於不同的資料來源(excel、資料庫)，可以採用不同的處理方式.本文將介紹python對於excel資料的基本操作.
1.前置條件
在電腦上安裝了Pycharm,在Pycharm中新建了專案,給專案安裝了第三方工具openpyxl
2.python操作excel
2.1 建立excel檔案

    from openpyxl import Workbook
    # Workbook()可以建立一個新的Excel
    wb = Workbook()
    # save(檔名稱)可以將檔案儲存
    wb.save('test1.xlsx')

2.2 excel寫入資料

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = '使用者表'
ws['A1'] = '序號'
ws['B1'] = '姓名'
ws['A2'] = '1'
ws['B2'] = '張三'
ws['A3'] = '2'
ws['B3'] = '李四'
ws['A4'] = '3'
ws['B4'] = '王五'
wb.save('user.xlsx')

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = '使用者表'
# cell(行，列，值)
ws.cell(1, 1, '序號')
ws.cell(1, 2, '姓名')
ws.cell(2, 1, '1')
ws.cell(2, 2, '張三')
ws.cell(3, 1, '2')
ws.cell(3, 2, '李四')
ws.cell(4, 1, '3')
ws.cell(4, 2, '王五')
wb.save('user.xlsx')

2.3 excel批次寫入資料

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = '成績表'
data = [
    {'name': '周杰倫', 'score': 100},
    {'name': '王力宏', 'score': 99},
    {'name': '陶喆', 'score': 98},
    {'name': '林俊杰', 'score': 97},
    {'name': '楊冪', 'score': 60}
]
ws.cell(1, 1, '姓名')
ws.cell(1, 2, '成績')
row = 2
for item in data:
    name = item['name']
    score = item['score']
    ws.cell(row, 1, name)
    ws.cell(row, 2, score)
    row += 1
wb.save('score.xlsx')

2.4 excel更新資料

from openpyxl import load_workbook
# load_workbook(要開啟的excel路徑): 返回workbook物件
wb = load_workbook('score.xlsx')
ws = wb.active
# wb[sheet名稱] 切換不同的sheet
# ws = wb['成績表']
ws['B3'] = 80
ws.cell(6, 2, 65)
wb.save('score.xlsx')

2.5 excel讀取資料

from openpyxl import load_workbook
wb = load_workbook('score.xlsx')
ws = wb.active
# 獲取單個單元格的資料
# 方括號語法
print(ws['A2'].value)
# cell語法
print(ws.cell(3, 1).value)
# 獲取整行資料
print(ws['3'][0].value)
print(ws['3'][1].value)
# 獲取整列資料
print('*' * 50)
for name in ws['A'][1:]:
    print(name.value)
# 讀取所有行
for item in ws.iter_rows():
    # 姓名：xxx，成績：xx分
    print(f'姓名：{item[0].value}，成績：{item[1].value}分')
# 讀取所有列
for item in ws.iter_cols():
    for i in item:
        print(i.value)
wb.save('score.xlsx')