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')