[Python知识库]python 操作EXCEL openpyxl库的使用

1.?安装openpyxl库

pip install openpyxl

2.写入xlsx文件

import openpyxl

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    ws['A1'] = 45
    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)

    #读取指定单元格
    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