示例:本地存在一个Excel文件如下,下面我们希望将一个DataFrame写入到已存在数据的工作表中,并保留原始数据。
如果我们想直接通过pandas的api实现几乎是不可能的,因为官方文档to_excel方法明确说了:
Once a workbook has been saved it is not possible write further data without rewriting the whole workbook.
但我个人在研究了pandas的Excel写出源码后,有了很简单的直接使用pandas向工作表追加数据的方法,后续再演示。
测试数据:
import pandas as pd
df = pd. DataFrame( [ [ 1 , 2 , 3 ] ,
[ 4 , 5 , 6 ] ,
[ 7 , 8 , 9 ] ] ,
columns= [ 'A' , 'B' , 'C' ] )
openpyxl向工作表追加Pandas数据
下面我们首先看看,直接用openpyxl如何实现,完整代码如下:
from openpyxl import load_workbook
book = load_workbook( "first.xlsx" )
sheet = book. active
address = "G8"
start_row, start_col = sheet[ address] . row- 1 , sheet[ address] . column- 1
for j, v in enumerate ( df. columns, 1 ) :
sheet. cell( start_row+ 1 , start_col+ j) . value = v
for i, row in enumerate ( df. values, 2 ) :
for j, v in enumerate ( row, 1 ) :
sheet. cell( start_row+ i, start_col+ j) . value = v
book. save( "test.xlsx" )
结果如下,成功向G8单元格写入数据:
xlwings向工作表追加Pandas数据
如果我们windows系统本地有安装office或目前版本的wps,就可以通过xlwings向工作表追加Pandas数据,完整代码如下:
import xlwings as xw
app = xw. App( add_book= False )
wb = app. books. open ( "first.xlsx" )
sht = wb. sheets. active
sht. range ( 'G8' ) . options( index= False ) . value = df
wb. save( "test.xlsx" )
app. quit( )
实现了与上述openpyxl代码一样的效果,save方法与openpyxl也一样,不传参数则覆盖原始文件。
options参数可以设置DataFrame的写出形式,与to_excel的参数几乎一致。
Pandas直接向工作表追加数据
最终完整代码如下(在pandas1.2.4版本测试成功):
writer = pd. ExcelWriter( "first.xlsx" , engine= 'openpyxl' , mode= 'a' )
writer. sheets. update( { sht. title: sht for sht in writer. book. worksheets} )
df. to_excel( writer, sheet_name= writer. book. active. title,
index= False , startrow= 7 , startcol= 6 )
writer. close( )
上述代码会向Excel表中的激活的工作表追加参数,sheet_name 参数也可以指定向哪个工作表追加写对应的字符串。
在1.4.0以上版本使用如下代码即可:
writer = pd. ExcelWriter( "first.xlsx" , engine= 'openpyxl' ,
mode= 'a' , if_sheet_exists= "overlay" )
df. to_excel( writer, sheet_name= writer. book. active. title,
index= False , startrow= 7 , startcol= 6 )
writer. close( )
默认情况下pandas无法向Excel工作表追加数据的根本原因在于没有任何读取原本工作表的动作,根据源码可以看到永远都会新建工作表(Pandas 1.2.x以下版本的情况):
这样我们只需要让sheets获取到已存在的工作表信息即可。
但是我在1.3.5版本中运行以上代码却报出如下错误:
这是因为从1.3.0版本开始,pd.ExcelWriter增加了if_sheet_exists 选项:
上述我实现的功能在1.4.0以上版本已经实现,使用如下代码即可:
writer = pd. ExcelWriter( "first.xlsx" , engine= 'openpyxl' ,
mode= 'a' , if_sheet_exists= "overlay" )
df. to_excel( writer, sheet_name= writer. book. active. title,
index= False , startrow= 7 , startcol= 6 )
writer. close( )
但是目前阿里云还没有同步1.4.X版本的pip源,我目前还只能通过阿里云升级到1.3.5的版本。为了提前尝鲜,我修改了如下源码:
首先在pandas\io\excel\_base.py
新增有效选项:
然后在pandas\io\excel\_openpyxl.py
中新增overlay的处理逻辑:
这样我就提前享受到了这个效果,以后pandas升级到1.4.0以上版本后,代码依然兼容。