Read system parameters from excel sheet

[larsviktorlarsson]

Is your improvement request related to a problem? Please describe. Excel is very convenient for storing parameter data and provides a nice overview. Big risks occur, however, when the same data is located in several places, which is the case when Excel is used as a database for a Hopsan model.

Describe the solution you'd like I think it should be possible to load system parameters from an excel file. I also think it should be possible to save the current system parameter set in an excel file. This makes it easier to start using it, since the correct format will be provided from Hopsan. In Hopsan, I would like to have 2 buttons, one called something like "Load from excel sheet", and another called something like "save current parameter set to excel sheet"

[robbr48]

Would it be sufficient to use CSV files instad of regular Excel files (XLSX)? I see several advantages:

1. It is much easier to read a CSV file than an XLSX file (which is a actually zipped XML-file)
2. CSV is supported by many other tools as well
3. CSV is a more robust solution for the future, since the format will not change with e.g. new versions of Excel
4. We already have this feature for hopsancli, it just needs to be added to hopsangui as well

The format of the CSV file will then just have the parameter identifiers (System|Component#Parameter) in the first column and the parameter value in the second.

[robbr48]

Another nice feature would be to actually link the parameters to the external file, so that they are automatically updated before starting each simulation. Affected parameters should then be greyed out in the component properties dialog, to avoid confusion.

[larsviktorlarsson]

Would it be sufficient to use CSV files instad of regular Excel files (XLSX)? I see several advantages:

1. It is much easier to read a CSV file than an XLSX file (which is a actually zipped XML-file)
2. CSV is supported by many other tools as well
3. CSV is a more robust solution for the future, since the format will not change with e.g. new versions of Excel
4. We already have this feature for hopsancli, it just needs to be added to hopsangui as well

The format of the CSV file will then just have the parameter identifiers (System|Component#Parameter) in the first column and the parameter value in the second.

A benefit with the XLSX format is the convenient overiview it provides when opened in excel. I guess that this benefit will be lost if CSV is used instead? I do, however, agree with your list of advantages, in particular concerning robustness. Perhaps it would be possible to export a csv from excel at least?

[larsviktorlarsson]

Another nice feature would be to actually link the parameters to the external file, so that they are automatically updated before starting each simulation. Affected parameters should then be greyed out in the component properties dialog, to avoid confusion.

I also like the idea of linking parameters directly to the csv file. It is, however, really important to implement it in such a way that the user understands that the data is provided externally, to avoid confusion.

[robbr48]

As I understand it, it should be possible to link a "real" Excel sheet to a CSV file so that the CSV file is updated automatically when the data in the XLSX file is changed. That CSV file could then in turn be linked to a Hopsan model. I have not actually tried this though.

My vision is that the linked parameters should be grayed out, maybe also with some label indicating that it is linked to a csv file. That should hopefully prevent any confusion.

[larsviktorlarsson]

As I understand it, it should be possible to link a "real" Excel sheet to a CSV file so that the CSV file is updated automatically when the data in the XLSX file is changed. That CSV file could then in turn be linked to a Hopsan model. I have not actually tried this though.

Yes, if possible, this seems like a good solution!

My vision is that the linked parameters should be grayed out, maybe also with some label indicating that it is linked to a csv file. That should hopefully prevent any confusion.

This also seems like a good solution!

[robbr48]

I have been looking a little more into this. There are several libraries on GitHub that can read data from XLSX files. This would make it possible to assign a parameter in Hopsan to a cell in an Excel workbook, for example as c:/path/file.xlsx:Sheet1!E17. The value would then be evaluated at the beginning of every simulation.

Unfortunately, none of these libraries are header-only; they all need to be compiled. This is most likely because XLSX files are zip archives that needs to be unzipped. The problem is that this feature needs to be added to hopsancore, where we want to avoid compiled dependencies. If we add it to hopsangui only, models with this feature will not work in e.g. hopsancli or exported FMUs.

What is your opinion @peterNordin? Should we try to add the source files manually and compile them together with hopsancore, just like we do with Sundials?