我有一个 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()