CODEWITHSUNDEEP
python-3.xpandaspandas.excelwriter
Xu Zhoufeng
Xu Zhoufeng

Reputation: 625

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

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?

Upvotes: 41

Views: 91404

Answers (6)

SooHana
SooHana

Reputation: 9

The best way to do this is to change the engine to openpyxl like this:

with pd.ExcelWriter('source.xlsx', engine='openpyxl', mode="a",
                if_sheet_exists="overlay"  # => update value only
                ) as writer:
    df.to_excel(writer, sheet_name='your_sheet_name', index=False, startrow=num, header=False)

Note that the startrow is the starting row where you want to paste your data in the excel file, and if you don't want to paste both the index and header, you can just set them to False.

Upvotes: 0

Hari Aravi
Hari Aravi

Reputation: 74

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)

Upvotes: 0

Jorge Puentes
Jorge Puentes

Reputation: 701

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".

Upvotes: 60

Jeremy Lloyd
Jeremy Lloyd

Reputation: 133

Try specifying the 'engine' as openpyxl:

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

Upvotes: 10

Frenchy
Frenchy

Reputation: 17037

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...

Upvotes: 1

Josh Friedlander
Josh Friedlander

Reputation: 11657

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)

Upvotes: 4

Related Questions