save_spreadsheets

pyhelpers.store.save_spreadsheets(data, path_to_file, sheet_names, mode='w', if_sheet_exists=None, autofit_column_width=True, writer_kwargs=None, verbose=False, raise_error=False, **kwargs)

Saves multiple dataframes to a multi-sheet Microsoft Excel (.xlsx, .xls) or OpenDocument (.ods) format file.

The function wraps pandas.ExcelWriter and pandas.DataFrame.to_excel, adding features like error suppression, progress output, column autofit and interactive handling of existing sheets in append mode.

Parameters:

• data (list[pandas.DataFrame] | tuple[pandas.DataFrame] | iterable[pandas.DataFrame]) – Sequence of pandas DataFrames to be saved as sheets in the workbook.

• path_to_file (str | os.PathLike) – File path where the spreadsheet will be saved. Must end with .xlsx, .xls or .ods.

• sheet_names (list[str] | tuple[str] | iterable[str]) – Names of all sheets in the workbook. Must match the length of data.

• mode (str) –

  Mode for writing to the spreadsheet file:

  • 'w' (default): Write mode. Creates a new file or overwrites existing.

  • 'a': Append mode. Adds sheets to an existing file. Note: Not supported for OpenDocument (.ods) files; a write operation will be performed instead.

• if_sheet_exists (None | str) –

  Behavior when trying to write a sheet that already exists:

  • None (default): Prompts the user for action: [pass]|new|replace.

  • 'error': Raises a ValueError (pandas default).

  • 'new': Creates a new sheet with an incremented name (e.g. ‘Sheet11’).

  • 'replace': Overwrites the existing sheet.

  • 'overlay': Writes data on top of existing data (only available with openpyxl).

  • 'pass': Skips saving the sheet.

• autofit_column_width (bool) – Whether to adjust column width to fit content automatically; defaults to True. Requires the openpyxl or odfpy engine.

• writer_kwargs (dict | None) – [Optional] Additional parameters for the class pandas.ExcelWriter(), such as date_format or datetime_format; defaults to None.

• verbose (bool | int) – Whether to print relevant information and sheet saving progress to the console; defaults to False.

• raise_error (bool) – Whether to raise the exception if saving a specific sheet fails; if raise_error=False (default), the error will be suppressed, and the process will continue with the next sheet.

• kwargs – [Optional] Additional parameters for the method pandas.DataFrame.to_excel(), e.g. index=False, header=True.

Returns:

None. The function’s main effect is the side effect of saving the file.

Return type:

None

Raises:

• AssertionError – If path_to_file does not end with a supported file extension (.xlsx, .xls, .ods).

• ValueError – If an invalid option is provided for if_sheet_exists when not None.

• Exception – Any exception raised during saving if raise_error=True.

Examples:

>>> from pyhelpers.store import save_spreadsheets
>>> from pyhelpers.dirs import cd
>>> from pyhelpers._cache import example_dataframe
>>> dat1 = example_dataframe()  # Get an example dataframe
>>> dat1
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418
>>> dat2 = dat1.T
>>> dat2
City          London  Birmingham  Manchester      Leeds
Longitude  -0.127647   -1.902691   -2.245115  -1.543794
Latitude   51.507322   52.479699   53.479489  53.797418
>>> dat = [dat1, dat2]
>>> sheets = ['TestSheet1', 'TestSheet2']
>>> # Save to ODS format (write mode)
>>> pathname = cd("tests", "data", "dat.ods")
>>> save_spreadsheets(dat, pathname, sheets, verbose=True)
Saving "dat.ods" to "./tests/data/" ...
    'TestSheet1' ... Done.
    'TestSheet2' ... Done.
>>> # Save to XLSX format (append mode with interactive prompt)
>>> pathname = cd("tests", "data", "dat.xlsx")
>>> save_spreadsheets(dat, pathname, sheets, verbose=True)
Saving "dat.xlsx" to "./tests/data/" ...
    'TestSheet1' ... Done.
    'TestSheet2' ... Done.
>>> save_spreadsheets(dat, pathname, sheets, mode='a', verbose=True)
Updating "dat.xlsx" at "./tests/data/" ...
    'TestSheet1' ... This sheet already exists; [pass]|new|replace: new
        saved as 'TestSheet11' ... Done.
    'TestSheet2' ... This sheet already exists; [pass]|new|replace: new
        saved as 'TestSheet21' ... Done.
>>> # Save with automatic replacement
>>> save_spreadsheets(dat, pathname, sheets, 'a', if_sheet_exists='replace', verbose=True)
Updating "dat.xlsx" at "./tests/data/" ...
    'TestSheet1' ... Done.
    'TestSheet2' ... Done.
>>> save_spreadsheets(dat, pathname, sheets, 'a', if_sheet_exists='new', verbose=True)
Updating "dat.xlsx" at "./tests/data/" ...
    'TestSheet1' ... saved as 'TestSheet12' ... Done.
    'TestSheet2' ... saved as 'TestSheet22' ... Done.