blocks|key|4696536|text|Pandas+docs说它对xlsx文件使用openpyxl。快速浏览一下ExcelWriter中的代码会给出一些线索，比如下面这样的代码可能会成功：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4696537|import+pandas
from+openpyxl+import+load_workbook

book+=+load_workbook('Masterfile.xlsx')
writer+=+pandas.ExcelWriter('Masterfile.xlsx',+engine='openpyxl')+
writer.book+=+book

##+ExcelWriter+for+some+reason+uses+writer.sheets+to+access+the+sheet.
##+If+you+leave+it+empty+it+will+not+know+that+sheet+Main+is+already+there
##+and+will+create+a+new+sheet.

writer.sheets+=+dict((ws.title,+ws)+for+ws+in+book.worksheets)

data_filtered.to_excel(writer,+"Main",+cols=['Diff1',+'Diff2'])

writer.save()|code-block|syntax|javascript|4696538|entityMap^0|11|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Pandas docs says it uses openpyxl for xlsx files. Quick look through the code in <code>ExcelWriter</code> gives a clue that something like this might work out:

<pre><code>import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 
writer.book = book

## ExcelWriter for some reason uses writer.sheets to access the sheet.
## If you leave it empty it will not know that sheet Main is already there
## and will create a new sheet.

writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()
</code></pre>

blocks|key|4696552|text|def+append_sheet_to_master(self,+master_file_path,+current_file_path,+sheet_name):
++++try:
++++++++master_book+=+load_workbook(master_file_path)
++++++++master_writer+=+pandas.ExcelWriter(master_file_path,+engine='openpyxl')
++++++++master_writer.book+=+master_book
++++++++master_writer.sheets+=+dict((ws.title,+ws)+for+ws+in+master_book.worksheets)
++++++++current_frames+=+pandas.ExcelFile(current_file_path).parse(pandas.ExcelFile(current_file_path).sheet_names[0],
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++header=None,
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++index_col=None)
++++++++current_frames.to_excel(master_writer,+sheet_name,+index=None,+header=False)

++++++++master_writer.save()
++++except+Exception+as+e:
++++++++raise+e|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4696553|这工作得很好，唯一的事情是主文件(我们添加新工作表的文件)的格式丢失了。|unstyled|4696554|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>def append_sheet_to_master(self, master_file_path, current_file_path, sheet_name):
 try:
 master_book = load_workbook(master_file_path)
 master_writer = pandas.ExcelWriter(master_file_path, engine='openpyxl')
 master_writer.book = master_book
 master_writer.sheets = dict((ws.title, ws) for ws in master_book.worksheets)
 current_frames = pandas.ExcelFile(current_file_path).parse(pandas.ExcelFile(current_file_path).sheet_names[0],
 header=None,
 index_col=None)
 current_frames.to_excel(master_writer, sheet_name, index=None, header=False)

 master_writer.save()
 except Exception as e:
 raise e
</code></pre>

This works perfectly fine only thing is that formatting of the master file(file to which we add new sheet) is lost.

blocks|key|4696563|text|在openpyxl版本2.4.0和pandas版本0.19.2中，@ski的过程变得更简单了：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4696564|import+pandas
from+openpyxl+import+load_workbook

with+pandas.ExcelWriter('Masterfile.xlsx',+engine='openpyxl')+as+writer:
++++writer.book+=+load_workbook('Masterfile.xlsx')
++++data_filtered.to_excel(writer,+"Main",+cols=['Diff1',+'Diff2'])
#That's+it!|code-block|syntax|javascript|4696565|entityMap^0|1|8|B|5|H|6|P|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

With <code>openpyxl</code>version <code>2.4.0</code> and <code>pandas</code>version <code>0.19.2</code>, the process @ski came up with gets a bit simpler:

<pre><code>import pandas
from openpyxl import load_workbook

with pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') as writer:
 writer.book = load_workbook('Masterfile.xlsx')
 data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])
#That's it!
</code></pre>

blocks|key|190800|text|老问题，但我猜有些人还在找这个-所以...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|190801|我发现这种方法很好用，因为所有工作表都被加载到由pandas使用sheetname=None选项创建的表名和数据帧对的字典中。在将电子表格读入dict格式和从dict中将其写回之间，添加、删除或修改工作表是很简单的。对我来说，xlsxwriter在速度和格式方面比openpyxl更适合这项特定的任务。|190802|注意:+pandas+(0.21.0%2B)的未来版本将把"sheetname“参数改为"sheet_name”。|190803|#+read+a+single+or+multi-sheet+excel+file
#+(returns+dict+of+sheetname(s),+dataframe(s))
ws_dict+=+pd.read_excel(excel_file_path,
++++++++++++++++++++++++sheetname=None)

#+all+worksheets+are+accessible+as+dataframes.

#+easy+to+change+a+worksheet+as+a+dataframe:
mod_df+=+ws_dict['existing_worksheet']

#+do+work+on+mod_df...then+reassign
ws_dict['existing_worksheet']+=+mod_df

#+add+a+dataframe+to+the+workbook+as+a+new+worksheet+with
#+ws+name,+df+as+dict+key,+value:
ws_dict['new_worksheet']+=+some_other_dataframe

#+when+done,+write+dictionary+back+to+excel...
#+xlsxwriter+honors+datetime+and+date+formats
#+(only+included+as+example)...
with+pd.ExcelWriter(excel_file_path,
++++++++++++++++++++engine='xlsxwriter',
++++++++++++++++++++datetime_format='yyyy-mm-dd',
++++++++++++++++++++date_format='yyyy-mm-dd')+as+writer:

++++for+ws_name,+df_sheet+in+ws_dict.items():
++++++++df_sheet.to_excel(writer,+sheet_name=ws_name)|code-block|syntax|javascript|190804|对于2013年问题中的示例：|190805|ws_dict+=+pd.read_excel('Masterfile.xlsx',
++++++++++++++++++++++++sheetname=None)

ws_dict['Main']+=+data_filtered[['Diff1',+'Diff2']]

with+pd.ExcelWriter('Masterfile.xlsx',
++++++++++++++++++++engine='xlsxwriter')+as+writer:

++++for+ws_name,+df_sheet+in+ws_dict.items():
++++++++df_sheet.to_excel(writer,+sheet_name=ws_name)|190806|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Old question, but I am guessing some people still search for this - so...

I find this method nice because all worksheets are loaded into a dictionary of sheet name and dataframe pairs, created by pandas with the sheetname=None option. It is simple to add, delete or modify worksheets between reading the spreadsheet into the dict format and writing it back from the dict. For me the xlsxwriter works better than openpyxl for this particular task in terms of speed and format.

Note: future versions of pandas (0.21.0+) will change the "sheetname" parameter to "sheet_name".

<pre><code># read a single or multi-sheet excel file
# (returns dict of sheetname(s), dataframe(s))
ws_dict = pd.read_excel(excel_file_path,
 sheetname=None)

# all worksheets are accessible as dataframes.

# easy to change a worksheet as a dataframe:
mod_df = ws_dict['existing_worksheet']

# do work on mod_df...then reassign
ws_dict['existing_worksheet'] = mod_df

# add a dataframe to the workbook as a new worksheet with
# ws name, df as dict key, value:
ws_dict['new_worksheet'] = some_other_dataframe

# when done, write dictionary back to excel...
# xlsxwriter honors datetime and date formats
# (only included as example)...
with pd.ExcelWriter(excel_file_path,
 engine='xlsxwriter',
 datetime_format='yyyy-mm-dd',
 date_format='yyyy-mm-dd') as writer:

 for ws_name, df_sheet in ws_dict.items():
 df_sheet.to_excel(writer, sheet_name=ws_name)
</code></pre>

For the example in the 2013 question:

<pre><code>ws_dict = pd.read_excel('Masterfile.xlsx',
 sheetname=None)

ws_dict['Main'] = data_filtered[['Diff1', 'Diff2']]

with pd.ExcelWriter('Masterfile.xlsx',
 engine='xlsxwriter') as writer:

 for ws_name, df_sheet in ws_dict.items():
 df_sheet.to_excel(writer, sheet_name=ws_name)
</code></pre>

blocks|key|190842|text|我知道这是一个较旧的线程，但这是您在搜索时找到的第一个项目，如果您需要在已创建的工作簿中保留图表，则上述解决方案不起作用。在这种情况下，xlwing是一个更好的选择-它允许您写入excel+book并保留图表/图表数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|190843|简单的例子：|190844|import+xlwings+as+xw
import+pandas+as+pd

#create+DF
months+=+['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
value1+=+[x+*+5%2B5+for+x+in+range(len(months))]
df+=+pd.DataFrame(value1,+index+=+months,+columns+=+['value1'])
df['value2']+=+df['value1']%2B5
df['value3']+=+df['value2']%2B5

#load+workbook+that+has+a+chart+in+it
wb+=+xw.Book('C:\\data\\bookwithChart.xlsx')

ws+=+wb.sheets['chartData']

ws.range('A1').options(index=False).value+=+df

wb+=+xw.Book('C:\\data\\bookwithChart_updated.xlsx')

xw.apps[0].quit()|code-block|syntax|javascript|190845|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I know this is an older thread, but this is the first item you find when searching, and the above solutions don't work if you need to retain charts in a workbook that you already have created. In that case, xlwings is a better option - it allows you to write to the excel book and keeps the charts/chart data.

simple example:

<pre><code>import xlwings as xw
import pandas as pd

#create DF
months = ['2017-01','2017-02','2017-03','2017-04','2017-05','2017-06','2017-07','2017-08','2017-09','2017-10','2017-11','2017-12']
value1 = [x * 5+5 for x in range(len(months))]
df = pd.DataFrame(value1, index = months, columns = ['value1'])
df['value2'] = df['value1']+5
df['value3'] = df['value2']+5

#load workbook that has a chart in it
wb = xw.Book('C:\\data\\bookwithChart.xlsx')

ws = wb.sheets['chartData']

ws.range('A1').options(index=False).value = df

wb = xw.Book('C:\\data\\bookwithChart_updated.xlsx')

xw.apps[0].quit()
</code></pre>

blocks|key|190872|text|writer+=+pd.ExcelWriter('prueba1.xlsx'engine='openpyxl',keep_date_col=True)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|190873|"keep_date_col“希望能帮助你|unstyled|190874|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>writer = pd.ExcelWriter('prueba1.xlsx'engine='openpyxl',keep_date_col=True)
</code></pre>

The "keep_date_col" hope help you

blocks|key|4696648|text|book+=+load_workbook(xlsFilename)
writer+=+pd.ExcelWriter(self.xlsFilename)
writer.book+=+book
writer.sheets+=+dict((ws.title,+ws)+for+ws+in+book.worksheets)
df.to_excel(writer,+sheet_name=sheetName,+index=False)
writer.save()|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4696649|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>book = load_workbook(xlsFilename)
writer = pd.ExcelWriter(self.xlsFilename)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name=sheetName, index=False)
writer.save()
</code></pre>

blocks|key|190931|text|从pandas+0.24开始，您可以使用ExcelWriter的mode关键字参数来简化这一过程|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|190932|import+pandas+as+pd

with+pd.ExcelWriter('the_file.xlsx',+engine='openpyxl',+mode='a')+as+writer:+
+++++data_filtered.to_excel(writer)+|code-block|syntax|javascript|190933|entityMap^0|K|B|W|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Starting in pandas 0.24 you can simplify this with the <code>mode</code> keyword argument of <code>ExcelWriter</code>:

<pre class="lang-py prettyprint-override"><code>import pandas as pd

with pd.ExcelWriter('the_file.xlsx', engine='openpyxl', mode='a') as writer: 
 data_filtered.to_excel(writer) 
</code></pre>

blocks|key|4696835|text|在pandas+0.24中有一个更好的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4696836|with+pd.ExcelWriter(path,+mode='a')+as+writer:
++++s.to_excel(writer,+sheet_name='another+sheet',+index=False)|code-block|syntax|javascript|4696837|之前：|4696838|​|4696839|📷|atomic|offset|length|4696840|4696841|之后：|4696842|4696843|4696844|4696845|所以现在就升级你的熊猫吧：|4696846|pip+install+--upgrade+pandas|4696847|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/ff9fc3803b7c2ed953ae03a3a961e740.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/d286dec6301c80c9dfd21f5efea99d48.png^0|0|0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1D|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1E|8|@]|9|@$N|1F|O|1G|1|1H]]|A|$]]|$1|P|3|J|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|J|5|6|7|1K|8|@]|9|@]|A|$]]|$1|T|3|L|5|M|7|1L|8|@]|9|@$N|1M|O|1N|1|1O]]|A|$]]|$1|U|3|J|5|6|7|1P|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|X|3|Y|5|D|7|1R|8|@]|9|@]|A|$E|F]]|$1|Z|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|10|$11|$5|12|13|14|A|$15|16|17|-4]]|18|$5|12|13|14|A|$15|19|17|-4]]]]

There is a better solution in pandas 0.24:

<pre><code>with pd.ExcelWriter(path, mode='a') as writer:
 s.to_excel(writer, sheet_name='another sheet', index=False)
</code></pre>

before:

<a href="https://i.stack.imgur.com/ZJ6hP.png" rel="noreferrer"><img src="https://i.stack.imgur.com/ZJ6hP.png" alt="enter image description here"></a>

after:

<a href="https://i.stack.imgur.com/etMhg.png" rel="noreferrer"><img src="https://i.stack.imgur.com/etMhg.png" alt="enter image description here"></a>

so upgrade your pandas now:

<pre><code>pip install --upgrade pandas
</code></pre>

blocks|key|4696908|text|@MaxU的解决方案工作得很好。我只有一个建议：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4696909|如果指定了truncate_sheet=True，则不应从现有工作表中保留"startrow“。我建议：|4696910|++++++++if+startrow+is+None+and+sheet_name+in+writer.book.sheetnames:
++++++++++++if+not+truncate_sheet:+#+truncate_sheet+would+use+startrow+if+provided+(or+zero+below)
++++++++++++++++startrow+=+writer.book[sheet_name].max_row|code-block|syntax|javascript|4696911|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Solution by @MaxU worked very well. I have just one suggestion:
If truncate_sheet=True is specified than &quot;startrow&quot; should NOT be retained from existing sheet. I suggest:
<pre><code> if startrow is None and sheet_name in writer.book.sheetnames:
 if not truncate_sheet: # truncate_sheet would use startrow if provided (or zero below)
 startrow = writer.book[sheet_name].max_row

</code></pre>

blocks|key|4696926|text|我使用了here描述的答案|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4696927|from+openpyxl+import+load_workbook
writer+=+pd.ExcelWriter(p_file_name,+engine='openpyxl',+mode='a')
writer.book+=+load_workbook(p_file_name)
writer.sheets+=+{ws.title:ws+for+ws+in+writer.book.worksheets}
df.to_excel(writer,+'Data',+startrow=10,+startcol=20)
writer.save()|code-block|syntax|javascript|4696928|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas^0|4|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

I used the answer described <a href="https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas">here</a>
<pre><code>from openpyxl import load_workbook
writer = pd.ExcelWriter(p_file_name, engine='openpyxl', mode='a')
writer.book = load_workbook(p_file_name)
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
df.to_excel(writer, 'Data', startrow=10, startcol=20)
writer.save()
</code></pre>

I use pandas to write to excel file in the following fashion:

<pre><code>import pandas

writer = pandas.ExcelWriter('Masterfile.xlsx') 

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()
</code></pre>

Masterfile.xlsx already consists of number of different tabs. However, it does not yet contain "Main".

Pandas correctly writes to "Main" sheet, unfortunately it also deletes all other tabs.

How to write to an existing excel file without overwriting data (using pandas)?

我使用pandas以如下方式写入excel文件：import pandaswriter = pandas.ExcelWriter('Masterfile.xlsx') data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])writer.save()...

问如何在不覆盖数据的情况下写入现有的excel文件(使用pandas)？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不覆盖数据的情况下写入现有的excel文件(使用pandas)？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不覆盖数据的情况下写入现有的excel文件(使用pandas)？
EN