我有一个 excel 表,在某些单元格中已经有一些值。

例如:-

        A      B      C      D 
1      val1   val2          val3 
2             valx   valy         

我希望 Pandas 在不接触任何其他单元格、表格等的情况下写入特定单元格

这是我试过的代码。
import pandas as pd 
from openpyxl import load_workbook 
 
df2 = pd.DataFrame({'Data': [13, 24, 35, 46]}) 
book = load_workbook('b.xlsx') 
writer = pd.ExcelWriter('b.xlsx', engine='openpyxl') 
 
df2.to_excel(writer, "Sheet1", startcol=7,startrow=6) 
 
writer.save() 

但是,此代码会删除较旧的单元格值。

我引用过:- How to write to an existing excel file without overwriting data (using pandas)?
但这个解决方案不起作用。

请您参考如下方法:

更新2:将数据附加到现有 Excel 工作表,保留其他(旧)工作表:

import pandas as pd 
from openpyxl import load_workbook 
 
fn = r'C:\Temp\.data\doc.xlsx' 
 
df = pd.read_excel(fn, header=None) 
df2 = pd.DataFrame({'Data': [13, 24, 35, 46]}) 
 
writer = pd.ExcelWriter(fn, engine='openpyxl') 
book = load_workbook(fn) 
writer.book = book 
writer.sheets = dict((ws.title, ws) for ws in book.worksheets) 
 
df.to_excel(writer, sheet_name='Sheet1', header=None, index=False) 
df2.to_excel(writer, sheet_name='Sheet1', header=None, index=False, 
             startcol=7,startrow=6) 
 
writer.save() 

更新:您的 Excel 文件没有标题,因此您应该相应地处理它:
In [57]: df = pd.read_excel(fn, header=None) 
 
In [58]: df 
Out[58]: 
     0    1 
0  abc  def 
1  ghi  lmn 
 
In [59]: df2 
Out[59]: 
   Data 
0    13 
1    24 
2    35 
3    46 
 
In [60]: writer = pd.ExcelWriter(fn) 
 
In [61]: df.to_excel(writer, header=None, index=False) 
 
In [62]: df2.to_excel(writer, startcol=7,startrow=6, header=None, index=False) 
 
In [63]: writer.save() 



旧答案:

您可以使用以下技巧:

首先将excel文件的现有内容读入一个新的DF:
In [17]: fn = r'C:\Temp\b.xlsx' 
 
In [18]: df = pd.read_excel(fn) 
 
In [19]: df 
Out[19]: 
       A      B     C      D 
0   val1    NaN  val3   val4 
1  val11  val22   NaN  val33 

现在我们可以将其写回并附加一个新的 DF2:
In [20]: writer = pd.ExcelWriter(fn) 
 
In [21]: df.to_excel(writer, index=False) 
 
In [22]: df2.to_excel(writer, startcol=7,startrow=6, header=None) 
 
In [23]: writer.save() 


评论关闭
IT干货网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!