load_spreadsheets¶

pyhelpers.store.load_spreadsheets(path_to_file, as_dict=True, verbose=False, prt_kwargs=None, **kwargs)[source]¶

Load one or multiple sheets from a Microsoft Excel or an OpenDocument format file.

Parameters:

path_to_file (str | os.PathLike) – Path where the spreadsheet file is saved.
as_dict (bool) – Whether to return the retrieved data as a dictionary; defaults to True.
verbose (bool | int) – Whether to print relevant information to the console; defaults to False.
prt_kwargs (dict | None) – [Optional] Additional parameters for the function pyhelpers.store.ldr._check_loading_path(); defaults to None.
kwargs – [Optional] Additional parameters for the method pandas.ExcelFile.parse().

Returns:

Data of all worksheets in the file from the specified pathname path_to_file.

Return type:

list | dict

Note

Example data can be referred to in the functions save_multiple_spreadsheets() and save_spreadsheet().

Examples:

>>> from pyhelpers.store import load_spreadsheets
>>> from pyhelpers.dirs import cd
>>> dat_dir = cd("tests\data")
>>> path_to_xlsx = cd(dat_dir, "dat.ods")
>>> wb_data = load_spreadsheets(path_to_xlsx, verbose=True, index_col=0)
Loading "tests\data\dat.ods" ...
    'TestSheet1'. ... Done.
    'TestSheet2'. ... Done.
>>> list(wb_data.keys())
['TestSheet1', 'TestSheet2']
>>> wb_data['TestSheet1']
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418
>>> path_to_xlsx = cd(dat_dir, "dat.xlsx")
>>> wb_data = load_spreadsheets(path_to_xlsx, verbose=True, index_col=0)
Loading "tests\data\dat.xlsx" ...
    'TestSheet1'. ... Done.
    'TestSheet2'. ... Done.
    'TestSheet11'. ... Done.
    'TestSheet21'. ... Done.
    'TestSheet12'. ... Done.
    'TestSheet22'. ... Done.
>>> list(wb_data.keys())
['TestSheet1',
 'TestSheet2',
 'TestSheet11',
 'TestSheet21',
 'TestSheet12',
 'TestSheet22']
>>> wb_data = load_spreadsheets(path_to_xlsx, as_dict=False, index_col=0)
>>> type(wb_data)
list
>>> len(wb_data)
6
>>> wb_data[0]
            Longitude   Latitude
City
London      -0.127647  51.507322
Birmingham  -1.902691  52.479699
Manchester  -2.245115  53.479489
Leeds       -1.543794  53.797418