Mar-09-2021, 10:56 AM
Hi
current configuration: python: 3.9 |openpyxl: 3.0.6 | pandas: 1.2.3 |xlrd 2.0.1
From a couple of years I use a really useful function to export pandas dataframe to xlsx with several customization, but now that I've updated python and packages as shown above, I receive the error "zipfile.BadZipFile: File is not a zip file".
Such error did not raise with the older configuration: python: 3.7 |openpyxl: 3.0.5 | pandas: 1.1.2 |xlrd 1.2.0
Could you help to make it working with the updated python and packages? (python: 3.9 |openpyxl: 3.0.6 | pandas: 1.2.3 |xlrd 2.0.1)
The function below can be run as it is, just replace "pathExport" to your export directory if you'd like to test it.
Thank you in adavance for the help
current configuration: python: 3.9 |openpyxl: 3.0.6 | pandas: 1.2.3 |xlrd 2.0.1
From a couple of years I use a really useful function to export pandas dataframe to xlsx with several customization, but now that I've updated python and packages as shown above, I receive the error "zipfile.BadZipFile: File is not a zip file".
Such error did not raise with the older configuration: python: 3.7 |openpyxl: 3.0.5 | pandas: 1.1.2 |xlrd 1.2.0
Could you help to make it working with the updated python and packages? (python: 3.9 |openpyxl: 3.0.6 | pandas: 1.2.3 |xlrd 2.0.1)
The function below can be run as it is, just replace "pathExport" to your export directory if you'd like to test it.
Thank you in adavance for the help
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
truncate_sheet=False,
**to_excel_kwargs):
"""
Append a DataFrame [df] to existing Excel file [filename]
into [sheet_name] Sheet.
If [filename] doesn't exist, then this function will create it.
Parameters:
filename : File path or existing ExcelWriter
(Example: '/path/to/file.xlsx')
df : dataframe to save to workbook
sheet_name : Name of sheet which will contain DataFrame.
(default: 'Sheet1')
startrow : upper left cell row to dump data frame.
Per default (startrow=None) calculate the last row
in the existing DF and write to the next row...
truncate_sheet : truncate (remove and recreate) [sheet_name]
before writing DataFrame to Excel file
to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
[can be dictionary]
Returns: None
(c) [MaxU](https://stackoverflow.com/users/5741205/maxu?tab=profile)
"""
from openpyxl import load_workbook
# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl')
# Python 2.x: define [FileNotFoundError] exception if it doesn't exist
try:
FileNotFoundError
except NameError:
FileNotFoundError = IOError
try:
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
except FileNotFoundError:
# file does not exist yet, we will create it
pass
if startrow is None:
startrow = 0
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
# save the workbook
writer.save()
pathExport = r"F:\PYTHON\NB-Suite_python39\MNE\outputData\df.xlsx"
df1 = pd.DataFrame({'numbers': [1, 2, 3],
'colors': ['red', 'white', 'blue'],
'colorsTwo': ['yellow', 'white', 'blue']
})
append_df_to_excel(pathExport, df1, sheet_name="DF1", index=False, startcol=0, startrow=0)
