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

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

一、openpyxl介绍安装

1.安装openpyxl

2.Excel中的三大对象

二、openpyxl对Excel的操作

 使用openpyxl读取excel

使用openpyxl写入excel

        三、使用pandas写入excel

---

一、openpyxl介绍安装

1.安装openpyxl

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

1. xlrd库 ：从excel中读取数据，支持xls、xlsx
2. xlwt库 ：对excel进行修改操作，不支持对xlsx格式的修改
3. xlutils库：在xlw和xlrd中，对一个已存在的文件进行修改。
4. 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)

 （2）其他操作

①对指定的excel中的指定sheet表格进行操作

from openpyxl import load_workbook
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active         #选择当前sheet
sheet = workbook["Sheet1"]      #选择指定的sheet1，两种方法都可以

②.append()：向表格中插入行数据

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
data = [ 
       ["唐僧","男","180cm"], 
       ["孙悟空","男","188cm"], 
       ["猪八戒","男","175cm"], 
       ["沙僧","男","176cm"], 
     ] 
for row in data: 
    sheet.append(row) 
workbook.save(filename = "test.xlsx")

③在python中使用excel函数

# 这是我们在 excel 中输入的公式
=IF(RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm") 
# 那么，在 python 中怎么插入 excel 公式呢？
workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet["D1"] = "标准身高" 
for i in range(2,16): 
    sheet["D{}".format(i)] = 
    '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i) 
workbook.save(filename = "test.xlsx")

④ 插入、 删除行和列

sheet.insert_cols(idx=数字编号, amount=要插入的列数)  #在idx列数的左侧插入；
sheet.insert_rows(idx=数字编号, amount=要插入的行数)  #在idx行数的下方插入；

sheet.delete_cols(idx=数字编号,amount要删除的行数) 
sheet.delete_rows(idx=数字编号,amount=要删除的列数)

 

三、使用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'],[…]]的格式。

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

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