Skip to content

Using the ExcelStandardModelProvider

iks github user edited this page Mar 2, 2016 · 13 revisions

[Back] (https://github.com/iks-github/MOGLiCodeCreator/wiki/How-to-apply-MOGLiCC%3F)


The ExcelStandardModelProvider represents an extension of the StandardModelProvider. To integrate the ExcelStandardModelProvider it must be referenced in the model file of the StandardModelProvider in the following way:

model TestModel

metainfo useExtensionPlugin ExcelStandardModelProvider


To configure the ExcelStandardModelProvider to contribute to the model data of the StandardModelProvider, some properties must be defined.

The input directory of the ExcelStandardModelProvider must contain the file '_excel.properties'. This file allows to define the excel data source. This data source must refer a model defined a model file of the StandardModelProvider. This definietion is done, by a property key that starts with the name of this valid model:

<name of valid model>.filename=<filename of the excel file>

For excel files located in the same directory as this property file, no path has to be defined, e.g.

TestModel.filname=Testdaten.xlsx


Having defined this first property, information is required for which model classes the ExcelStandardModelProvider has to read data and where to find them within the Excel file. In case, a model class is already defined in the model file of the StandardModelProvider, metainfos and attributes read from the Excel file are added to it. In case, a model class is unknown to the model of the StandardModelProvider, the ExcelStandardModelProvider adds a new class to the model.

For each class a data matrix is read from one of the Excel file sheets. The matrix consists of at least two rows and columns. The matrix is defined by the number of its excel sheet and its "First Cell" (upper left cell of the matrix). The lower right end of the matrix (i.e. the number of rows and columns) is determined automatically by the first empty row or column relative to the First Cell.

Class and matrix are defined by the following syntax:

<name of valid model>.matrix.<classname>=<number of sheet starting with 1>#<number of column of the first cell starting with 1>:<number of row of the first cell starting with 1>

For example:
TestModel.matrix.ExampleClass=2#3:4

In this example the matrix is read from the second Excel sheet, starting with the third column (C) and the fourth row, i.e. cell C4. Alternatively to the sheet number, you can use its name to referenz a sheet. Alternatively to the column number, you can use the column letter.

For example:
TestModel.matrix.ExampleClass=First Sheet#A:1


Within a matrix, attribute names are expected by default in the first column of each row - names of metainfos of attributes are expected in the first row of each column. This means, if the First Cell is 1:1 then the value of cell 2:3 refers by default to the attribute named in cell 1:3 (very left column, third row) and to the metainfo named in cell 2:1 (second column, top row). The content of the First Cell does not play any role.

If your matrix is organized in the way, that rows and columns must be rotated, you can do so by setting

MOGLiCC_JavaBeanModel.ROTATION.MODE=Attribute Names In First Column

The alternative rotation mode 'Attribute Names In First Row' is default.


Furthermore, it is possible to rename attribute names or names of MetaInfos of attributes, if they are not unique. This is done by the following setting:

<name of valid model>.rename.AttributeName.Occurrence.<old name of attribute>.<number of occurence>=<new name of attribute> <name of valid model>.rename.AttributeMetaInfo.Occurrence.<old name of MetaInfo>.<number of occurence>=<new name of metainfo>

Example: Two metainfos 'Size' exist in the matrix. This is problem, because metainfo names must be unique within an attribute. If you do not want to modify the names in the Excel sheet, you can solve this problem like this:

TestModel.rename.AttributeMetaInfo.Occurrence.size.1=ExpectedSize # replaces first occurence of 'size' to 'ExpectedSize' TestModel.rename.AttributeMetaInfo.Occurrence.size.2=ActualSize # replaces second occurence of 'size' to 'ActualSize'

There exist problem: if the raw name you want to replace contains spaces or dots, parsing the property keys becomes difficult. Therefore, when searching for matches between properties and excel data, spaces and dots are ignored and removed for matching. Thus, avoid using spaces and dots in names that you like to rename.

If you want to use multiple occurrences of MetaInfos names, you can allow it by setting: <name of valid model>.AllowMetaInfoDoubles=true


Finally, the matrix may not represent data for a single class, but for list of different classes. In this case only a section of the matrix must be mapped onto a class. This is done by additional info as shown in the following example:

TestModel.matrix.ExampleClassA=1#1:1>2

TestModel.matrix.ExampleClassB=1#1:1>8-12

The symbol '>' reduces the matrix to a subset of attribute(s). In the first example the attribute at position 2 is mapped to ExampleClassA, in the second example the 5 attributes at the position 8-12 are mapped to ExampleClassB.


Have a look onto the MOGLiCC JavaBean Group and the ExcelStandardModelProvider Example. At both links the use of the ExcelStandardModelProvider is illustrated.


[Back] (https://github.com/iks-github/MOGLiCodeCreator/wiki/How-to-apply-MOGLiCC%3F)

Clone this wiki locally