本文主要讲述openpyxl库对excel文件的读取写入操作以及Pandas库对excel文件的写入操作。

一、openpyxl介绍安装

1.安装openpyxl

python中与excel操作相关的模块：

xlrd库 ：从excel中读取数据，支持xls、xlsx
xlwt库 ：对excel进行修改操作，不支持对xlsx格式的修改
xlutils库：在xlw和xlrd中，对一个已存在的文件进行修改。
openpyxl ：主要针对xlsx格式的excel进行读取和编辑。

安装方式：pip install openpyxl

2.Excel中的三大对象

WorkBook：工作簿对象
Sheet：表单对象
Cell：表格对象

二、openpyxl对Excel的操作

创建一个工作薄：wb = openpyxl.Workbook()
新增一个sheet表单：wb.create_sheet(‘test_case’)
保存case.xlsx文件：wb.save(‘cases.xlsx’)
打开工作簿：wb = openpyxl.load_workbook(‘cases.xlsx’)
选取表单：sh = wb[‘Sheet1’]
读取第一行、第一列的数据：ce = sh.cell(row = 1,column = 1)
按行读取数据：row_data = list(sh.rows)
按列读取数据：columns_data = list(sh.columns)
关闭工作薄：wb.close()
写入数据之前，该文件一定要处于关闭状态
写入数据：例第1行第4列的数据 value = ‘result’：sh.cell(row = 1,column = 4,value = ‘result’)
获取最大行总数、最大列总数：sh.max_row、sh.max_column
del 删除表单的用法：del wb[‘sheet_name’]
remove 删除表单的用法：sh = wb[‘sheet_name’] wb.remove(sh)

使用openpyxl读取excel

import openpyxl
# 创建一个工作簿
wb = openpyxl.Workbook()
# 创建一个test_case的sheet表单
wb.create_sheet('test_case')
# 保存为一个xlsx格式的文件
wb.save('cases.xlsx')

# 读取excel中的数据
# 第一步：打开工作簿
wb = openpyxl.load_workbook('cases.xlsx')
# 第二步：选取表单
sh = wb['Sheet1']
# 第三步：读取数据
# 参数 row:行  column：列
ce = sh.cell(row = 1,column = 1)   # 读取第一行，第一列的数据
print(ce.value)

'''按行读取数据 list(sh.rows)'''
print(list(sh.rows)[1:])     # 按行读取数据，去掉第一行的表头信息数据
for cases in list(sh.rows)[1:]:
    case_id =  cases[0].value
    case_excepted = cases[1].value
    case_data = cases[2].value
    print(case_excepted,case_data)
# 关闭工作薄
wb.close()

使用openpyxl写入excel

（1）尝试将 Pandas 的DataFrame保存到现有的 Excel 的sheet工作表中

使用 openpyxl.load_workbook(filepath) 可以对已存在的 .xlsx 进行追加数据，不会覆盖

import pandas as pd
from openpyxl import load_workbook

path = r'C:\Users\whw\Desktop\55297400.xlsx'
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = book

'''在原excel表中建立新sheet,并将df数据写入'''
df.to_excel(writer, sheet_name="新增sheet",index=False)
writer.save()
writer.close()

（2）在已有excel表中新建sheet页并添加多行数据项

import openpyxl as op

orderIds = [1,2,3]
items = ['A','B','C']
myData = [10,20,30]
testData = [orderIds,items,myData]
filename = r'C:\Users\whw\Desktop\测试1.xlsx'

op_toexcel(testData,filename)

def op_toexcel(data,filename): # openpyxl库储存数据到excel
    wb = op.Workbook() # 创建工作簿对象
    ws = wb['Sheet'] # 创建子表
    ws.append(['序号','项目','数据']) # 添加表头
    for i in range(len(data[0])):
        d = data[0][i], data[1][i], data[2][i]
        ws.append(d) # 每次写入一行
    wb.save(filename)

三、使用pandas写入excel

(1)多个dataframe以多个sheet的形式保存到一个excel文件中

df.to_excel多次写入不同Sheet，主要需要 pd.ExcelWriter( 文件路径 ) 方法

import pandas as pd

"""
d_f1,d_f2均为sql查询来的数据
文件.xlsx 为要生成保存的excel文件地址
"""
write = pd.ExcelWriter('文件.xlsx')


df1 = pd.DataFrame(d_f1)
excel_header = ['日期','年龄']#excel的标题
df1.to_excel(write,sheet_name='Sheet1',header=excel_header,index=False)
df2 = pd.DataFrame(d_f2)
excel_header = ['日期','人数']
df2.to_excel(write,sheet_name='Sheet2',header=excel_header,index=False)

write.save()

小结
openpyxl 与 xlsxwriter 类似，需要逐行写入，如第一行需要得到 [ 1, ‘A’ , 10 ] 的格式，但不同的是使用 openpyxl.load_workbook(filepath) 可以对已存在的 .xlsx 进行追加数据，不会覆盖；pandas 则需先按列组合，如 [[ 1,2,3],[‘A’,’B’,’C’],[…]]的格式。

暂时补充到这里，更多应用方法持续更新。

喜欢的话，关注收藏一下吧！

文章出处登录后可见！

已经登录？立即刷新

利用Python中的openpyxl/Pandas库操作excel