save_spreadsheets

pyhelpers.store.save_spreadsheets(data, path_to_file, sheet_names, mode='w', if_sheet_exists=None, writer_kwargs=None, verbose=False, **kwargs)[source]

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

The file extension can be “.xlsx” (or “.xls”) or “.ods”.

Parameters:

  • data (list | tuple | iterable) – A sequence of dataframes.

  • path_to_file (str | os.PathLike) – Path where a spreadsheet is saved.

  • sheet_names (list | tuple | iterable) – All sheet names of an Excel workbook.

  • mode (str) – Mode to write to an Excel file; 'w' (default) for ‘write’ and 'a' for ‘append’; note that the ‘append’ mode is not supported with OpenDocument.

  • if_sheet_exists (None | str) – Indicate the behaviour when trying to write to an existing sheet; see also the parameter if_sheet_exists of pandas.ExcelWriter.

  • writer_kwargs (dict | None) – Optional parameters for pandas.ExcelWriter; defatuls to None.

  • verbose (bool | int) – Whether to print relevant information in console; defaults to False.

  • kwargs – [Optional] parameters of 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.