English

SpreadSheet

Basic concept

Configuration

In the configuration it is defined to which Eliona instance the connection should be made and which reports are created with which template.

Eliona Instance

{"eliona_handler": {
"host": "experimental.eliona.io",
"api": "https://experimental.eliona.io/api/v2", "project_id": 1,
"api_key": "slidfglsadbföabg$$äasfnasilbg"}
}
ConfigurationDescriptionExampleNote

host

Address of the Eliona instance without HTTPS.

develop.eliona.cloud

Optional. Ideally as env. Variable

api

Address of the API used. In this case, it is always the /api/v2. Here with HTTPS.

https://develop.eliona.cloud

/api/v2

Optional. Ideally as env. Variable

project_id

Project ID on the used Eliona instance. (You can get this number by editing the project in the settings and looking into the address bar).

Necessary

api_key

API key for the connection to the Eliona instance

...

Optional. Ideally as env. Variable

dbTimeZone

Database time zone.

Europe/Zurich

Optional. Ideally as env. Variable

Reports

The configuration of the reports is located within a list. This can contain one or more reports. Each report requires at least these configurations.

"reportConfig": [
{
"name": "Report Monatlich Ebene 1", "schedule": "monthly",
"type": "DataEntry",
"templateFile": "./storage/reports/templates/Template_Report_Ebene_1.xlsx", "sheet": "Sheet1",
"separator":";",
"firstRow": "0", "fromTemplate": true,
"reportPath": "Report_Ebene1.xlsx", "tempPath": ""
},
{
"name": "Report Monatlich Ebene 2", "schedule": "monthly",
"type": "DataEntry",
"templateFile": "./storage/reports/templates/Template_Report_Ebene_2.xlsx", "sheet": "Sheet1",
"separator":";",
"firstRow": "0", "fromTemplate": true,
"reportPath": "Report_Ebene2.xlsx",
"tempPath": ""
}
]

ConfigurationDescriptionExample

name

Name of the report definition

"Report Level 1 Monthly"

schedule

Call frequency

= yearly (Annual report is sent in January)

= monthly (Monthly report is sent every month)

type

Definition of report type.

= "DataListSequential" (List among each other)

= "DataListParallel" (List parallel)

= "DataEntry" (Individual entries in the cell)

templateFile

Path specification for the template file

./templates/syn_001.xlsx

sheet

Table name when using Excel tables

Table1, Sheet1

fileType

Setting the file format to be used for saving and reading the data

csv, xls, xlsx

separator

Separator to be used when reading and saving CSV files.

";" // "," // " "

firstRow

First line to be used when reading the template configuration and saving the data when fromTemplate is active.

fromTemplate

Specifies whether the file should be new or taken from the template. A .csv file should not be used to append the data. For an XLSX file, the formatting can be taken over with this option. (Formulas, cell formatting, etc. will be retained).

true / false

Please pay attention to the syntax.

reportPath

Storage path including name of the report

./report_XY_001.xlsx

Templates

The templates should be stored in Excel format and must contain the configuration of the assets. The link to the assets / attributes in Eliona looks like this:

{"assetGai":"H01 Fernwärme Energiezähler IWB", "attribute":"Energie", "raster":"MONTH", "mode":"sum"}

The required information is entered here in JSON format.

KeyValue ExampleNote

"assetGai"

GAI of the asset used.

"attribute"

Here the attributes of the asset type to be read out are entered

"raster"

M1 , M5, M10, M15 H1, H2, H4, H8, H12 DAY

WEEK MONTH YEAR

Depending on the report to be created, the grid must be entered here. For a monthly report, the MONTH grid must be used.

"mode"

last first sum

average

The mode to be read from the grid. For attributes that have an averaging mode, "average" must be used. Attributes with a sum or cumulative summation must use "sum".

Berechnungen

Calculations are performed in Excel. All Excel functions can be used here. Referencing values from your own table as well as using values from Eliona is possible. When using values from Eliona, simply use the JSON part for referencing.

Example: Here a cell content is subtracted from an Eliona Asset attribute.

= ({"assetGai":"K01 Netz Energiezähler 1005", "attribute":"Energie", "raster":"MONTH", "mode":"sum"} - J6)

ATTENTION: Entering such a formula is prevented by Excel. For this purpose, the format of the cell must be adjusted. After entering the formula, the cell type can be reset. It is recommended to choose the number format.

DataListSequential

Has not been used so far. Has only been intended for one project in the bidding phase

DataListParallel (Liste parallel)

Example table

Beispiel Template

Details

Timestamp

[m³] Volumen IWB Brauchwasser Kälteanlage 1004

[°C] Kälteanlage 1004 Wassertemperatur Rückführung WAR

[] IWB Brauchwasser Kälteanlage 1004 Verbindung OK

{"timeStamp":"%Y-%m-

%d %H:%M:%S",

"raster":"H1"}

{"assetGai":"IWB Kälteanlage 1004", "attribute":"Volumen", "mode":"sum"}

{"assetGai":"IWB Kälteanlage 1004", "attribute":"Temperatur", "mode":"average"}

{"assetGai":"IWB Kälteanlage 1004", "attribute":"Status", "mode":"last"}

DataEntry

Beispiel Tabelle:

Beispiel Template:

Date fromDate untilConsumption

{"timeStampStart":"%Y-%m-%d"}

{"timeStampEnd":"%Y-%m-%d"}

{"assetGai":"Trinkwasser Kalt WRO 1007P UG-8.OG

Frischwasserstation", "attribute":"Volumen", "raster":"MONTH", "mode":"sum"}

{"timeStampStart":"%Y-%m-%d"}

{"timeStampEnd":"%Y-%m-%d"}

{"assetGai":"Enthärtetes Wasser Gastro Zuleitung Frischwasser", "attribute":"Volumen", "raster":"MONTH", "mode":"sum"}

{"timeStampStart":"%Y-%m-%d"}

{"timeStampEnd":"%Y-%m-%d"}

= {"assetGai":"H02 Energiezähler BWW 1005", "attribute":"Energie", "raster":"MONTH", "mode":"sum"} * 1000

{"timeStampStart":"%Y-%m-%d"}

{"timeStampEnd":"%Y-%m-%d"}

= (1 - ((J4) / ((J2+J3)*64 + 68000)))*100

Creating a report manually

To create a report manually, the Docker container can be opened and App.py can be called with arguments. A help exists which can be called with -h.

(env311) spreadsheet-report-app % python ./src/spreadsheet-reportapp/
spreadsheet_report_app.py -h
usage: spreadsheet_report_app.py [-h] [-d DATE] [-c CONFIG] [-u USER] [-r REPORT] [-o OUTPUT]
options:
-h, --help show this help message and exit
-d DATE, --date DATE Date in the format: dd.mm.yyyy
-c CONFIG, --config CONFIG
Path to the used configuration file. For Example:
"./config/config.json"
-u USER, --user USER User name that's requested. Name can be read from config.json file.
-r REPORT, --report REPORT
Report name that's requested. Name can be read from config.json file.
-o OUTPUT, --output OUTPUT
Export file path. If empty will be stored under "./temp/file_name"
(env311) spreadsheet-report-app %

ArgumentDescriptionExample

-h, --help

Get-help command

python sp...app.py -h

-d, --date

Enter the desired report date in the format "dd.mm.yyyy". For monthly reports, month and year are important. For yearly reports, the year is important.

python sp...app.py -d "01.01.2021"

-c, --config

Path for the settings file. This is needed to load the Reporting and User Settings. If this is left empty, the default directory will be used.

python sp...app.py -c "./config/config.json"

-u, --user

Select either User or Report Configuration. Depends on the existing configuration which are available. In case of the user, all reports of the user will be created. In case of a report, the single report will be created. However, one of them must be set.

python sp...app.py -u

-r, --report

python sp...app.py -r

-o, --output

Output path for the created reports. If this parameter is left empty, the directory "./tmp_reports/manual_created/" will be used.

python sp...app.py -h "./tmp_files/export/"

PreviousKentixNextHailo

Last updated