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, **kwargs)[source]¶

Save multiple dataframes to a multi-sheet Microsoft Excel or OpenDocument format file.

The file extension can be .xlsx (or .xls) for Microsoft Excel files or .ods for OpenDocument files.

Parameters:

data (list | tuple | iterable) – Sequence of dataframes to be saved as sheets in the workbook.
path_to_file (str | os.PathLike) – File path where the spreadsheet will be saved.
sheet_names (list | tuple | iterable) – Names of all sheets in the workbook.
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 (not supported for OpenDocument).
if_sheet_exists (None | str) – Behaviour when trying to write to an existing sheet; defaults to None; see also the parameter if_sheet_exists of pandas.ExcelWriter().
autofit_column_width (bool) – Whether to autofit column width; defaults to True.
writer_kwargs (dict | None) – [Optional] Additional parameters for the class pandas.ExcelWriter().
verbose (bool | int) – Whether to print relevant information to the console; defaults to False.
kwargs – [Optional] Additional parameters for the method pandas.DataFrame.to_excel().

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']
>>> pathname = cd("tests\data", "dat.ods")
>>> save_spreadsheets(dat, pathname, sheets, verbose=True)
Saving "dat.ods" to "tests\data\" ...
    'TestSheet1' ... Done.
    'TestSheet2' ... Done.
>>> 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_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.