spinetoolbox.spine_io.exporters.excel

Framework for exporting a database to Excel file.

author:

16. Vennström (VTT), A. Soininen (VTT)

date:

31.1.2020

Module Contents

Functions

_get_objects_and_parameters(db)	Exports all object data from spine database into unstacked list of lists
_get_relationships_and_parameters(db)	Exports all relationship data from spine database into unstacked list of lists
_unstack_list_of_tuples(data, headers, key_cols, value_name_col, value_col)	Unstacks list of lists or list of tuples and creates a list of namedtuples
_get_unstacked_relationships(db)	Gets all data for relationships in a unstacked list of list
_get_unstacked_objects(db)	Gets all data for objects in a unstacked list of list
_write_relationships_to_xlsx(wb, relationship_data)	Writes Classes, parameter and parameter values for relationships.
_write_json_array_to_xlsx(wb, data, sheet_type)	Writes json array data for object classes and relationship classes.
_write_TimeSeries_to_xlsx(wb, data, sheet_type, data_type)	Writes spinedb_api TimeSeries data for object classes and relationship classes.
_write_objects_to_xlsx(wb, object_data)	Writes Classes, parameter and parameter values for objects.
_get_object_groups(db)	Exports all group data from spine database into a dict.
_write_object_groups_to_xlsx(wb, group_data)	Writes classes, groups and members for object groups.
_write_alternatives_to_xlsx(wb, alternative_data)	Writes names, and description for alternatives.
_write_scenarios_to_xlsx(wb, scenario_data)	Writes names, active flag, and description for scenarios.
_write_scenario_alternatives_to_xlsx(wb, scenario_alternative_data)	Writes scenario names, alternative names, and before alternative names for scenario alternatives.
export_spine_database_to_xlsx(db, filepath)	Writes all data in a spine database into an excel file.

spinetoolbox.spine_io.exporters.excel._get_objects_and_parameters(db)

Exports all object data from spine database into unstacked list of lists

Parameters:db (spinedb_api.DatabaseMapping) – database mapping for database Returns:(List, List) First list contains parameter data, second one json data

spinetoolbox.spine_io.exporters.excel._get_relationships_and_parameters(db)

Exports all relationship data from spine database into unstacked list of lists

Parameters:db (spinedb_api.DatabaseMapping) – database mapping for database Returns:(List, List) First list contains parameter data, second one json data

spinetoolbox.spine_io.exporters.excel._unstack_list_of_tuples(data, headers, key_cols, value_name_col, value_col)

Unstacks list of lists or list of tuples and creates a list of namedtuples with unstacked data (pivoted data)

Parameters:

• data (List[List]) – List of lists with data to unstack
• headers (List[str]) – List of header names for data
• key_cols (List[Int]) – List of index for column that are keys, columns to not unstack
• value_name_col (Int) – index to column containing name of data to unstack
• value_col (Int) – index to column containing value to value_name_col

Returns:

List of list with headers in headers list (List): List of header names for each item in inner list

Return type:

(List[List])

spinetoolbox.spine_io.exporters.excel._get_unstacked_relationships(db)

Gets all data for relationships in a unstacked list of list

Parameters:db (spinedb_api.DatabaseMapping) – database mapping for database Returns:stacked relationships, stacked JSON, stacked time series and stacked time patterns Return type:(list, list, list, list)

spinetoolbox.spine_io.exporters.excel._get_unstacked_objects(db)

Gets all data for objects in a unstacked list of list

Parameters:db (spinedb_api.DatabaseMapping) – database mapping for database Returns:stacked objects, parsed JSON, parsed time series and parsed time patterns Return type:(list, list, list, list)

spinetoolbox.spine_io.exporters.excel._write_relationships_to_xlsx(wb, relationship_data)

Writes Classes, parameter and parameter values for relationships. Writes one sheet per relationship_class.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• relationship_data (List[List]) – List of lists containing relationship
• give by function get_unstacked_relationships (data) –

spinetoolbox.spine_io.exporters.excel._write_json_array_to_xlsx(wb, data, sheet_type)

Writes json array data for object classes and relationship classes. Writes one sheet per relationship/object_class.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• data (List[List]) – List of lists containing json data give by function
• and get_unstacked_relationships (get_unstacked_objects) –
• sheet_type (str) – str with value “relationship” or “object” telling if data is for a relationship or object

spinetoolbox.spine_io.exporters.excel._write_TimeSeries_to_xlsx(wb, data, sheet_type, data_type)

Writes spinedb_api TimeSeries data for object classes and relationship classes. Writes one sheet per relationship/object_class.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• data (List[List]) – List of lists containing json data give by function
• and get_unstacked_relationships (get_unstacked_objects) –
• sheet_type (str) – str with value “relationship” or “object” telling if data is for a relationship or object

spinetoolbox.spine_io.exporters.excel._write_objects_to_xlsx(wb, object_data)

Writes Classes, parameter and parameter values for objects. Writes one sheet per relationship/object_class.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• object_data (List[List]) – List of lists containing object data give by function get_unstacked_objects

spinetoolbox.spine_io.exporters.excel._get_object_groups(db)

Exports all group data from spine database into a dict.

Parameters:db (spinedb_api.DatabaseMapping) – database mapping for database Returns:mapping class_name, to a list of (group_name, member_name) tuples sorted by group_name Return type:dict

spinetoolbox.spine_io.exporters.excel._write_object_groups_to_xlsx(wb, group_data)

Writes classes, groups and members for object groups. Writes one sheet per object_class.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• group_data (dict) – containing group data as given by function _get_object_groups

spinetoolbox.spine_io.exporters.excel._write_alternatives_to_xlsx(wb, alternative_data)

Writes names, and description for alternatives. Writes one sheet.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• object_data (List[List]) – List of lists containing object data give by function get_unstacked_objects

spinetoolbox.spine_io.exporters.excel._write_scenarios_to_xlsx(wb, scenario_data)

Writes names, active flag, and description for scenarios. Writes one sheet.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• object_data (List[List]) – List of lists containing object data give by function get_unstacked_objects

spinetoolbox.spine_io.exporters.excel._write_scenario_alternatives_to_xlsx(wb, scenario_alternative_data)

Writes scenario names, alternative names, and before alternative names for scenario alternatives. Writes one sheet.

Parameters:

• wb (openpyxl.Workbook) – excel workbook to write too.
• object_data (List[List]) – List of lists containing object data give by function get_unstacked_objects

spinetoolbox.spine_io.exporters.excel.export_spine_database_to_xlsx(db, filepath)

Writes all data in a spine database into an excel file.

Parameters:

• db (spinedb_api.DatabaseMapping) – database mapping for database.
• filepath (str) – str with filepath to save excel file to.