pandas.ExcelWriter ValueError: Append mode is not supported with xlsxwriter

Question

I want to add some records to an excel file and I use pandas.ExcelWriter to do this(http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html?highlight=excelwriter#pandas.ExcelWriter ):

import pandas as pd                                                     

df = pd.DataFrame(data={'a':[4], 'b':['corn'], 'c':[0.5]})              

with pd.ExcelWriter("test.xlsx", mode='a') as writer: 
            df.to_excel(writer)

a, b, c are titles name of test.xlsx

run this program, raise a valueError:

ValueError                                Traceback (most recent call last)
<ipython-input-3-c643d22b4217> in <module>
----> 1 with pd.ExcelWriter("test.xlsx", mode='a') as writer:
      2     df.to_excel(writer)
      3 

~/anaconda/lib/python3.6/site-packages/pandas/io/excel.py in __init__(self, path, engine, date_format, datetime_format, mode, **engine_kwargs)
   1935 
   1936         if mode == 'a':
-> 1937             raise ValueError('Append mode is not supported with xlsxwriter!')
   1938 
   1939         super(_XlsxWriter, self).__init__(path, engine=engine,

ValueError: Append mode is not supported with xlsxwriter!

I don't know why?

score 55 · Answer 1 · answered Apr 22 '19 at 06:23

55

Try with this:

with pd.ExcelWriter("existing_file_name.xlsx", engine="openpyxl", mode="a") as writer:
    df.to_excel(writer, sheet_name="name", startrow=num, startcol=num)

You need to specify the engine as "openpyxl".

answered Apr 22 '19 at 06:23

Jorge Puentes Márquez

551
3
4

Doesn't work when using `io.BytesIO()` instead of "existing_file_name.xlsx" – AlexM Feb 23 '21 at 14:59
3

Only the wlxt engine was deprecated in pandas 1.2.0 [(see docs)](https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html#pandas.ExcelWriter). In fact, specifying `engine="openpyxl"` solved the error for me (in pandas 1.4.3) – Lfppfs Jul 26 '22 at 11:55
This appends the data by always adding a new sheet, even if a sheet with the specified name exists. I understood the question (and your answer) to mean 'append the data to the table in this sheet, throw an exception if headers are not appropriate' – julaine Aug 30 '23 at 14:21

score 9 · Answer 2 · answered Apr 15 '19 at 06:01

9

Try specifying the 'engine' as openpyxl:

with pd.ExcelWriter("test.xlsx", engine='openpyxl', mode='a') as writer:
    df.to_excel(writer)

answered Apr 15 '19 at 06:01

Jeremy Lloyd

123
1
4

score 4 · Answer 3 · answered Feb 25 '19 at 10:15

4

As the traceback says, ValueError: Append mode is not supported with xlsxwriter!

I can't answer your question, why, this is a decision of the Pandas developers.

But I was able to make your use-case work with Pandas 0.24 and openpyxl:

df = pd.DataFrame(data={'a':[4], 'b':['corn'], 'c':[0.5]})              

with pd.ExcelWriter("test.xlsx", mode='a') as writer: 
            df.to_excel(writer)

answered Feb 25 '19 at 10:15

Josh Friedlander

10,870
5
35
75

Do you have `openpyxl`? – Josh Friedlander Feb 25 '19 at 12:02
using `conda list` showing openpyxl has been installed – Xu Zhoufeng Feb 25 '19 at 12:08
Yes. `openpyxl` is separate from Pandas. – Josh Friedlander Feb 25 '19 at 12:08
2

try `conda install openpyxl` and try it again. and maybe `conda uninstall xlsxwriter` – Josh Friedlander Feb 25 '19 at 12:14
I uninstall xlsxwriter, my case can run normally, but when I open `test.xlsx`, the record were not successfully add to excel file. – Xu Zhoufeng Feb 25 '19 at 12:26
After trying the suggestion made by @JoshFriedlander please follow the below url to append dataframes to the same sheet: https://stackoverflow.com/questions/47737220/append-dataframe-to-excel-with-pandas – skap May 22 '19 at 09:37

score 2 · Answer 4 · answered Jun 03 '20 at 18:33

2

solved it, use a capital A in mode, that does the trick!

    import pandas as pd                                                     

    df = pd.DataFrame(data={'a':[4], 'b':['corn'], 'c':[0.5]})              

    with pd.ExcelWriter("test.xlsx", mode='A') as writer: 
        df.to_excel(writer)

answered Jun 03 '20 at 18:33

Hari Aravi

78
3

yeah this worked for me too. why would it be though? I looked through the documentation and nowhere is there any mention of a 'A' mode. https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html#pandas.ExcelWriter – Nirjhor Chakraborty Jun 10 '20 at 21:37
1

I have no idea too, I tried this without realizing that my keyboard had Caps lock on! – Hari Aravi Jun 12 '20 at 12:20
9

This does not actually open the file in append mode. – tharen Aug 15 '20 at 22:00
2

it's the same that `mode='w'` – Nicoolasens Apr 08 '21 at 18:45

Frenchy · Answer 5 · 2019-02-25T10:17:53.060

1

hum, i am pretty sure you could no use the function append to an existing xlsx file with xlsxwriter, because xlsxwriter library is only for writing excel files

See the issue on Github

so you could use openpyxl or better write your program to do this function..

but if you look at code inside this libray, it just reads the file in temp environment before writes the final file, its not a real append...

edited Feb 25 '19 at 10:17

answered Feb 25 '19 at 10:11

Frenchy

16,386
3
16
39

pandas.ExcelWriter ValueError: Append mode is not supported with xlsxwriter

5 Answers5

Linked