EBSILON®Professional Online Documentation
Data Transfer and Results Display / Data - Import / Free Excel Import
In This Topic
    Free Excel Import
    In This Topic

    User Excel Import


    General

    You can modify the component specification values by importing data from Microsoft Excel Sheets. Pipe results cannot be imported.

    You can modify these values within one profile or within several profiles simultaneously.

    While in the „Standard Excel” interface the input data have to positioned on fixed positions in the Excel sheet, the "Free Layout Excel" (or "User Excel") interface allows to define the arrangement of data within the Excel sheet by the user.

    This is especially useful if the input data are provided by another software and you want to avoid rearrangements of data by copy and paste within your Excel sheet.

    The assignment of positions in the Excel sheet to the variables in the EBSILON®Professional cycle is generally accomplished by using the Excel “Defined Names” functionality. Alternatively, it is possible to use formatting instructions in the first row of the Excel sheet).

    You must

    • create the Excel sheet,
    • enter the values to be imported in a dedicated arrangement of cells, and
    • define special names for some columns and rows of this Excel sheet,
    • save and close the Excel sheet.

     

    EBSILON®Professional will recognize these Excel Defined Names and will try to read specification values from the sheet by applying some rules.

     

    These rules describe,

    • which cells of the sheet hold values to be read (the source), and
    • the specification values of your document (the destination) to be superseded, and
    • the profiles and components to be considered.

    Sample Excel sheets are shipped with EBSILON®Professional.


    How do I import data from an Excel document?

    To import data from an Excel document, select the command "Data-->User Excel-->Input" from the menu bar.

    The "Import values from Excel file" window is displayed.

    Select the Excel document you have created for importing and click on "Open".

    Excel will be started in the background and the Excel document will be opened.

    The Defined Names of this sheet will be read and the data will be imported from this sheet by applying the rules for importing Excel data. The values stored earlier in your document will be replaced.

    To be able to import a value correctly, you have to specify its unit (e.g. kW, MW). If this unit differs from the unit specified within the document, the value will be converted to the unit specified in the document.


    Rules for importing data from Excel

    Your Excel sheet must be organized keeping in mind the following rules in order to be processed correctly by EBSILON®Professional.

     

    Rules for defined names

    To specify a profile use its profile name.

    To specify a component use its component name.

    To specify a specification value use its identifier.

    A row of an Excel sheet of this type may hold data of a dedicated specification value (values and unit) of a dedicated component. The Defined Name of this row has to

    • start with the string "S_",
    • followed by a component name
    • a dot and
    • a specification value identifier.

    Example: "S_Input_of_value_8.NCV" specifies a row which is expected to hold data for the specification value identified by "NCV" of the component named "Input_of_value_8".

     

    A column of an Excel sheet of this type may hold data of a dedicated profile. The Defined Name of this column must

    • start with the string "P_"
    • be followed by a profile name.

    Example: "P_Variation_C" specifies a column, which is expected to hold data for the profile named "Variation_C".

     

    One column of an Excel sheet of this type must hold data describing the unit of specification values. The Defined Name of this column must be “X_UNIT”.

     

    Other Rules

    If your Excel file contains more than one sheet, EBSILON®Professional, will try to read all sheets. You have to take care that the corresponding names are defined for all sheets. Note that within Excel, names can be defined locally for a specific sheet or globally for the whole workbook. EBSILON®Professional recognizes both types of names.

    If you assign the names to single cells instead of whole rows (or columns), EBSILON®Professional will use the corresponding row (or column, respectively), in which the cell is located.

    Cells that contain values must be formatted as "number". These must not be empty. At least a 0 must be entered.

    Cells that contain strings of units must be formatted as "Standard" or "Text".

     

    Example

    The following is a part of an Excel Sheet. It holds specification values of a component named "Coolant Inlet", which is of type "start value" (33). The identifiers of the specification values to be modified are "P", "T", "H" and "FMAS". Furthermore, it holds specification values of  two profiles named "Variation_A" and "Variation_B".

     

    Rows 4, 5, 6 and 7 and columns B, C and D have to be named. Following the rules listed above, the Defined Names have to be spelled as follows:

    Row / Column

    Defined Name

    4

    S_CoolantInlet.P

    5

    S_CoolantInlet.T

    6

    S_CoolantInlet.H

    7

    S_CoolantInlet.FMAS

    B

    X_UNIT

    C

    P_Variation_A

    D

    P_Variation_B

     

    This is the list of Defined Names as displayed by Excel.

    Cells C4, D4, C5, D5, C6, D6, C7, D7 must be formatted as "number".

    Cells B4, B5, B6, B7 must formatted as "text" or "standard" and must include valid unit strings.


    Error messages

    If EBSILON®Professional cannot assign

    • component names
    • identifiers of a specification value
    • a unit string
    • profile names

    it extracted from the list of Excel Defined Names to the model, you will get an error message and the corresponding data will not be imported.


    Using a Format Line

    Using names for rows and columns offers a very high flexibility for the layout of the Excel sheet. In certain cases, one does not need this flexibility, but instead wants to transfer a large volume of data from EBSILON®Professional directly to Excel, without investing effort in the preparation (such as naming the rows and the columns).

    It is possible here to insert a row at the beginning of the Excel sheet by using the formatting commands:

    There are three commands, which must be provided in special cells:

    The cell A1 must have the command "EbsNames Col I" or "EbsNames Row I". Thereby, "/" is the key letter or the number of the row, which contains the names of the specification or the result values. The name comprises of object names, a dot and the identification of the specification or the result value. In case of measured values (component 46), the specification of the component name is sufficient, the calculated value (".RESULT") is then given automatically.

    The cell B1 must have the command "EbsProfiles Col I to J" or "EbsProfiles Row I to J". Thereby, "I" and "J" are the key letters or the numbers of the first and the last column or row respectively, which are supposed to contain the data coming from EBSILON®Professional. For simplifying the matters, the data is written as one profile after the other, regardless of the profile hierarchy or the profile name. However, with this interface it is not possible to select a profile or to specify, which profile is to be written where.

    The cell C1 can contain the command "EbsUnits Col I" or "EbsUnits Row I". Thereby, "I" is the key letter or the number of the row, which contains the units for the specification values. These units can be different from the ones used in the model.