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:0

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

score:1

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)

score:3

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) 

score:6

Try specifying the 'engine' as openpyxl:

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

score:43

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


Related Query