save_spreadsheets

pyhelpers.store.save_spreadsheets(spreadsheets_data, sheet_names, path_to_spreadsheet, mode='w', index=False, confirmation_required=True, verbose=False, **kwargs)

Save data to a multi-sheet Microsoft Excel file.

The file extension can be “.xlsx” or “.xls”.

Parameters

  • spreadsheets_data (list or tuple or iterable) – a sequence of pandas.DataFrame

  • sheet_names (list or tuple or iterable) – all sheet names of an Excel workbook

  • path_to_spreadsheet (str or os.PathLike[str]) – path where a spreadsheet is saved

  • mode (str) – mode to write to an Excel file; 'w' (default) for ‘write’ and 'a' for ‘append’

  • index (bool) – whether to include the index as a column, defaults to False

  • confirmation_required (bool) – whether to prompt a message for confirmation to proceed, defaults to True

  • verbose (bool or int) – whether to print relevant information in console, defaults to False

  • kwargs – [optional] parameters of pandas.ExcelWriter

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]
>>> dat_sheets = ['TestSheet1', 'TestSheet2']
>>> dat_pathname = cd("tests\data", "dat.xlsx")

>>> save_spreadsheets(dat, dat_sheets, dat_pathname, index=True, verbose=True)
Saving "dat.xlsx" to "tests\data\" ...
    'TestSheet1' ... Done.
    'TestSheet2' ... Done.

>>> save_spreadsheets(dat, dat_sheets, dat_pathname, mode='a', index=True, 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, dat_sheets, dat_pathname, mode='a', index=True,
...                   confirmation_required=False, verbose=True)
Updating "dat.xlsx" at "tests\dataz" ...
    'TestSheet1' ... Failed. Sheet 'TestSheet1' already exists and if_sheet_exists is se ...
    'TestSheet2' ... Failed. Sheet 'TestSheet2' already exists and if_sheet_exists is se ...

>>> save_spreadsheets(dat, dat_sheets, dat_pathname, mode='a', index=True,
...                   confirmation_required=False, verbose=True, if_sheet_exists='replace')
Updating "dat.xlsx" at "tests\data\" ...
    'TestSheet1' ... Done.
    'TestSheet2' ... Done.