pandas giving Unsupported Operation: truncate() for writing data frame in Excel file

Question

I am using pandas to write my data frames into an Excel file. I have written a function that helps me write the data frame into an Excel file. This is the function:

def touch_excel(
        df: pd.DataFrame,
        file_path: str,
        sheet_name: str = "Sheet1",
        add_df: pd.DataFrame = None):
    """
        this function concatenates two data frames (if given),
        and converts them into an Excel file at the given location

        Args:
            df (pandas.DataFrame): the data frame to be written into the Excel file
            file_path (str): the path to the Excel file
            sheet_name (str): the name of the sheet in the Excel file
            add_df (pandas.DataFrame): the additional data frame to be concatenated
    """
    if add_df is not None:
        df = pd.concat([df, add_df], ignore_index=True)

    try:
        if not os.path.exists(file_path):
            df.to_excel(file_path, sheet_name=sheet_name, index=False)
        else:
            with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)
    except PermissionError:
        raise Exception("File might be open. Close it.")

Now, this function runs in almost all my cases, but for some cases it gives Unsupported Operation message and stops the program execution. Why is it happening? Could someone explain?

Traceback (most recent call last):
  File "E:\Projects\PR-Dashboard-Automation\main.py", line 512, in <module>
    main()
  File "E:\Projects\PR-Dashboard-Automation\main.py", line 130, in main
    touch_excel(df, data_file, 'Previous Data')
  File "E:\Projects\PR-Dashboard-Automation\main.py", line 27, in touch_excel
    with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
  File "E:\Projects\PR-Dashboard-Automation\venv\Lib\site-packages\pandas\io\excel\_base.py", line 1370, in __exit__
    self.close()
  File "E:\Projects\PR-Dashboard-Automation\venv\Lib\site-packages\pandas\io\excel\_base.py", line 1374, in close
    self._save()
  File "E:\Projects\PR-Dashboard-Automation\venv\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 113, in _save
    self._handles.handle.truncate()
io.UnsupportedOperation: truncate

I tried to solve the problem by changing the paths to the folder, also I tried to use other methods like setting the workbook manually. But still it doesn't fixed the problem. But whenever I use

mode = 'r'

instead of

mode = 'a'

It starts working, but doesn't create multiple sheets in the same Excel file. (Asking since this code runs for almost all the programs except some cases)

Thanks in advance :)

Answer 1

is openpyxl installed in your system?
if no:

pip install openpyxl

if yes:

pip install --upgrade pandas openpyxl

and also, in this code block:

else:
    with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', 
        if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)

you can try to change engine type from openpyxl to xlsxwriter so it will be something like this:

else:
    with pd.ExcelWriter(file_path, mode='a', engine='xlsxwriter', 
        if_sheet_exists='replace') as writer:
            df.to_excel(writer, sheet_name=sheet_name, index=False)