Message Boards Message Boards


Import parameters into System Modeller from Excel?

Posted 6 months ago
5 Replies
4 Total Likes

Hi, I am have several models of dynamical systems with a significant number of parameters for each. The best way to work with these would be to use an Excel sheet/tabs, and have SystemModeller use this data. With Dymola, I could use ExternData which allows one to specify sheet/row/column of Excel for parameters. Unfortunately, this does not work in SM as it seems to have a dependence on Dymola. There are some workaround for OpenModelica, but I have not had any luck in getting these to work in SM.

Could anyone suggest a way to get ExternData to work in SM, or perhaps some hacks to it?

The alternative is to use Records and Combi-Time and .rtf, but this is really taking a two decade step backwards. ExternData would be great if it could be made to work.

Thanks for the help.

5 Replies


Look at the example Modelica->Utilities>Examples->readRealParameterModel. It reads a text file for a list of parameters. Here is the example file that it reads:

// Motor data

J        = 2.3     // inertia
w_rel0   = 1.5*2;  // relative angular velocity
phi_rel0 = pi/3;

You could save the excel file as text and read it as in the example. This is a very human readable format so its not a bad option for a large amount of data. (There is also a matrix reading example but it appears to crash -- I'll report it to WSM but the underlying functions may work.)

Alternatively, you can write a simple C external function that reads a parameter file or a matrix of data and call it.

These are the two ways I am aware of -- maybe the WSM people have another suggestion.



Posted 6 months ago

H Neil,

Thank you very much for this. I had looked at the Utilities, but did see them for what they are. With what you pointed out, I can have all in an Excel sheet with all my additional data to the right of a column of '//'. Once saved at txt - which works nicely as an achieve copy which is needed anyway, it works nicely.

The BIG advantage of this over using ExternData is that parameters are not locked into cell position. In ExternData, one had to specify row/column. When a new parameter was added and one wanted to group them in the Excel sheet, it required a very tedious reassignment of parameter to cell location. What you showed searches for the variable name, independent of position - much easier.

Thank you.

That library uses constructs that aren't valid according to the Modelica specification. However, you can use it in SystemModeler if you use the external objects and their functions directly, like this adapted XLSTest for example:

model XLSTest "Excel XLS file read test"
  extends Modelica.Icons.Example;
  parameter Types.ExternXLSFile xls = Types.ExternXLSFile(Modelica.Utilities.Files.loadResource("modelica://ExternData/Resources/Examples/test.xls"), "UTF-8", true);
  Modelica.Blocks.Math.Gain gain1(k = Functions.XLS.getReal("B2", "set1", xls)) annotation(Placement(transformation(extent = {{-15, 60}, {5, 80}})));
  Modelica.Blocks.Math.Gain gain2(k = Functions.XLS.getReal("B2", "set2", xls)) annotation(Placement(transformation(extent = {{-15, 30}, {5, 50}})));
  Modelica.Blocks.Sources.Clock clock annotation(Placement(transformation(extent = {{-50, 60}, {-30, 80}})));
  connect(clock.y, gain1.u) annotation(Line(points = {{-29, 70}, {-17, 70}}, color = {0, 0, 127}));
  connect(clock.y, gain2.u) annotation(Line(points = {{-29, 70}, {-22, 70}, {-22, 40}, {-17, 40}}, color = {0, 0, 127}));
  annotation(experiment(StopTime = 1), Documentation(info = "<html><p>This example model reads the gain parameters from different cells and sheets of the Excel XLS file <a href=\"modelica://ExternData/Resources/Examples/test.xls\">test.xls</a>. For gain1 the gain parameter is read as Real value using the function <a href=\"modelica://ExternData.XLSFile.getReal\">ExternData.XLSFile.getReal</a>. For gain2 the String value is retrieved by function <a href=\"modelica://ExternData.XLSFile.getString\">ExternData.XLSFile.getString</a> and converted to a Real value (using the utility function <a href=\"modelica://Modelica.Utilities.Strings.scanReal\">Modelica.Utilities.Strings.scanReal</a>). For timeTable the table parameter is read as Real array of dimension 3x2 by function <a href=\"modelica://ExternData.XLSFile.getRealArray2D\">ExternData.XLSFile.getRealArray2D</a>. The read parameters are assigned by parameter bindings to the appropriate model parameters.</p></html>"));
end XLSTest;



When you say

That library uses constructs that aren't valid...

I assume you are referring to ExternData and not the Utility that I referenced, is that correct?




yes that is correct I was referring to ExternData.


Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract