python 操作EXCEL openpyxl库的使用

1. 安装openpyxl库

pip install openpyxl

2.写入xlsx文件

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    max_row = ws.max_row    #最大行
    max_col = ws.max_column    最大列
    ws['A1'] = 45    #一种方式写入
    ws.cell(row=1, column=5, value="11111111")    #一种方式写入
    ws.append([1,2,3])    #追加
    wb.save("sample.xlsx")

3. 读取

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.load_workbook("./exam/2104张三丰.xlsx",read_only=True)
    #ws = wb['Sheet1'] #指定sheet表名字
    ws = wb.active  #获取活动的sheet表
    #按行读取
    for row in ws.rows:
        for cell in row:
            print(cell.value,end=" ")
        print("")

    #按列读取
    for col in ws.iter_cols(min_row=2,max_col=4,max_row=4):
        for cell in col:
            print(cell.value)
    #按列读取
    colRes = ws.columns
    for col in colRes:
        print(col)
    #读取第1列
    rowRes = ws.rows
    for cell in rowRes:
        print(cell[0].value)

    #读取指定单元格
    A1 = ws['A1']
    print(A1.value)

    #访问多个单元格
    cell_range = ws['A2':'C4']
    for row in cell_range:
        for cell in row:
            print(cell.value, end=" ")
        print("")

4. 按行的方式追加数据

# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()  # 默认生成一个名为Sheet的sheet
 
# 创建sheet
for name in ['a','b']:
    ws = wb.create_sheet(name)
 
# 追加一行
for sheet in wb:
    sheet.append(['name','name2'])
 
# 在A列和B列追加(参数为字典)
for sheet in wb:
    sheet.append({'A':'dicta','B':'dictb'})
 
wb.save('test.xlsx')

参考：

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.9 documentation