excel_import_export

Functions to import and export from excel to spine database.

author:

16. Vennström (VTT)

date:

21.8.2018

Module Contents

excel_import_export.SheetData

excel_import_export.import_xlsx_to_db(db, filepath)

reads excel file in ‘filepath’ and insert into database in mapping ‘db’. Returns two list, one with succesful writes to database, one with errors when trying to write to database.

Parameters:

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

Returns:

(Int, List) Returns number of inserted items and a list of error information on all failed writes

excel_import_export.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

excel_import_export.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

excel_import_export.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 whit 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])

excel_import_export.stack_list_of_tuples(data, headers, key_cols, value_cols)

Stacks list of lists or list of tuples and creates a list of namedtuples with stacked data (unpivoted 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 columns that are keys
• value_cols (List[Int]) – List of index for columns containing values to stack

Returns:

List of namedtuples whit fields given by headers and ‘parameter’ and ‘value’ which contains stacked values

Return type:

(List[namedtuple])

excel_import_export.unpack_json_parameters(data, json_index)

excel_import_export.pack_json_parameters(data, key_cols, value_col, index_col=None)

excel_import_export.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:Two list of data for relationship, one with parameter values and the second one with json values Return type:(List, List)

excel_import_export.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:Two list of data for objects, one with parameter values and the second one with json values Return type:(List, List)

excel_import_export.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) –

excel_import_export.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

excel_import_export.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

excel_import_export.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 relationship data give by function get_unstacked_objects

excel_import_export.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.

excel_import_export.read_spine_xlsx(filepath)

reads all data from a excel file where the sheets are in valid spine data format

Parameters:filepath (str) – str with filepath to excel file to read from.

excel_import_export.merge_spine_xlsx_data(data)

Merge data from different sheets with same object class or relationship class.

Parameters:data (List(SheetData)) – list of SheetData Returns:List of SheetData with only one relationship/object class per item Return type:(List[SheetData])

excel_import_export.validate_sheet(ws)

Checks if supplied sheet is a valid import sheet for spine.

Parameters:ws (openpyxl.workbook.worksheet) – worksheet to validate Returns:True if sheet is valid, False otherwise Return type:(bool)

excel_import_export.read_json_sheet(ws, sheet_type)

Reads a sheet containg json array data for objects and relationships

Parameters:

• ws (openpyxl.workbook.worksheet) – worksheet to read from
• sheet_type (str) – str with value “relationship” or “object” telling if sheet is a relationship or object sheet

Returns:

(List[SheetData])

excel_import_export.read_TimeSeries_sheet(ws, sheet_type)

Reads a sheet containg json array data for objects and relationships

Parameters:

• ws (openpyxl.workbook.worksheet) – worksheet to read from
• sheet_type (str) – str with value “relationship” or “object” telling if sheet is a relationship or object sheet

Returns:

(List[SheetData])

excel_import_export.read_parameter_sheet(ws)

Reads a sheet containg parameter data for objects and relationships

Parameters:ws (openpyxl.workbook.worksheet) – worksheet to read from Returns:(List[SheetData])

excel_import_export.read_2d(ws, start_row=1, end_row=1, start_col=1, end_col=1)

Reads a 2d area from worksheet into a list of lists where each line is the inner list.

Parameters:

• ws (openpyxl.workbook.worksheet) – Worksheet to look in
• start_row (Integer) – start row to read, 1-indexed (as excel)
• end_row (Integer) – row to read to, 1-indexed (as excel)
• start_col (Integer) – start column to read, 1-indexed (as excel)
• end_col (Integer) – end column to read to, 1-indexed (as excel)

Returns:

(List) List of all lines read.

excel_import_export.max_col_in_row(ws, row=1)

Finds max col index for given row. If no data exists on row, returns 1.

Parameters:

• ws (openpyxl.workbook.worksheet) – Worksheet to look in
• row (Integer) – index for row to search, 1-indexed (as excel)

Returns:

(Integer) column index of last cell with value.