The Standard Excel Workbook

1.    General

With the standard Excel Workbook you can display

• values and units of specification values
• characteristic lines
• values and units of results
• Texts of text fields

view and modify by importing from

• values and specification values
• characteristic lines.
• Texts of text fields

Thus you can edit specification values not only by using the graphical interface but also by using the Excel workbook interface and editing these Excel sheets.

The workbook is a Microsoft Excel document (Excel sheets). It consists of many sheets, whereby each sheets contains data of one component.

There are three supplementary sheets:

• one (named “Model“) that displays the data of the model

• one (named “Texts“) that contains texts of the text fields of the model

• one (named “Pipes“) that contains data for all pipes of the model

The sheets were created from templates by EBSILON^®Professional itself when displaying and writing respectively. The templates were supplied together with EBSILON^®Professional . You can either use these templates or your own ones.

The allocation of a sheet to a component is based on the "Excel Sheet name”. The "Excel Sheet name” is only generated when the component is exported into the Excel workbook for the first time. The Excel Sheet name of a component is displayed on the tab “Basic Properties“ in the component properties dialog (at the very bottom) and can be changed there too.

Changing the "Excel Sheet name” (in the properties window) becomes necessary if another sheet name is to be used in Excel. Please note that the name is NOT automatically changed in the Excel document, i.e. if a sheet for this components exists there already and the data are still needed, it will have to be renamed in Excel as well.

If you want to or have to rename, this should be done in Excel first as Excel has rather strong limitations for sheet names with regard to special characters.

The file name of the Excel workbook can be viewed and changed in the Model Options “Excel Workbook“ in the item ”Excel workbook filename”.
By default, the workbook is created in the same folder as the model file. In this case, the model name can be specified as follows:

$(ModelFolder)$(ModelName).$(DefaultExcelExt)

You also receive this setting by clicking the button “Reset Excel workbook file name“.

 

When exporting to a workbook, you can filter out the components to be considered.

• you can highlight each component to be reported or not
• you can mark each component type (or component number respectively) to be reported or not
• you can combine both these possibilities.

 

When you import data from a workbook EBSILON^®Professional always reads and takes over all values from all the sheets included in the workbook. This means, that all values of all sheets must be specified correctly.

• report (or write respectively) to the workbook
• edit the sheets of the workbook with Excel, changing the values you want,
• import (or read respectively) the workbook.

 

If you want only some or one components data to be modified by importing, delete all the other sheets from the workbook.

Profiles are supported by the workbook functionality.

You can report        

• to all profiles, or
• to the active profile

and import        

• for all profiles
• for the active profile

 

By default the Excel document (Workbook) associated with a document is stored in the same directory as your document itself. The workbook has nearly the same file name as your document, but its extension is .xls. Example: If your document is called Cycle_abc, the documents file name is Cycle_abc.ebs, the workbooks file name is Cycle_abc.xls.

You can change the workbook associated with your document by modifying the Excel-Filename. Select the ExtrasàModel Options entry from the menu bar and select the Excel tab of the Model Options window to do so.

It is strongly recommended not to run Excel on your PC, while using the workbook. There might be some bothering interferences.

2.    The Component Sheet

Each sheet of the workbook has a name. The name is written on the tab of the sheet. The name of any component sheet is equal to the name of the component it is assigned to.

There is a template sheet for each component.

The workbook of an Ebsilon2000 document can contain a maximum of one sheet for each of your components. When you write into the workbook, sheets are inserted there.

When a sheet is inserted into the workbook,

• the corresponding template sheet is copied into the workbook as a new sheet,
• it receives the component name, and
• it is filled with data of the document component it is allocated to.

 

A component sheet includes

• a header, including the components number and type,
• a line for each of the components specification values,
• a line for each of the components results and
• all characteristic lines of the component.

 

Each specification value or result line includes

• a continuous number
• the identifier of the specification value or the result value respectively
• the description
• its units
• the values for one or more profiles.

 

For each characteristic line it includes:

• a continuous number
• the identifier of the characteristic line
• the description
• up to ten sampling points (x axis)
• the corresponding values for one or more profile, and
• the chart displaying graphically the characteristic lines of one or more profiles. 
  

The column G holds all the values of the Design profile. Each column on its right side may hold all the values of one sub profile.

 

Characteristic lines can be saved in the workbook in three different ways (A) (B) (C). These differ regarding the saving of the x-values of the characteristic lines. As long as in a model the same x-values are used for all characteristic lines in all profiles, the default setting (A) can be used (“only for root profile“).

If different x-values are specified for a characteristic line in different profiles, you can choose whether for each profile

• (B) the x-values are output together with the y-values in one column (in this case, characteristic lines are not graphically represented in the workbook), or whether
• (C) two columns are used for each profile (one column for the x-values, the second one for the y-values).

In the latter case (C) this means that the existing allocation between column and profile changes. Therefore after shifting to (C) the existing workbook should be
deleted and a new one should be created. The allocation of the profiles to the columns can be viewed and changed respectively in the Model Options "Excel Workbook"
under ”Edit Profile-Excel-columns…“.

 

 

 

Characteristics in workbook at default setting (A)

 

Characteristics in workbook at B) x and y values in a column

 

Characteristics in workbook at C) Two columns per profile (separated for x and y values)    

3.  The Pipelines Sheet

The sheet containing the pipelines data is called Pipes.

A pipeline sheet includes

1. a header, and
2. nine line for each pipeline of the document.

 

These nine lines display the results and properties of a pipeline.

The values of column G are those of the Design profile.

Each column on its right side may hold all the values of one sub profile.

Lines 17, 26 and so on include the composition of the fluid. Its contents extends over several columns. This does not matter, because it is independent of profiles.

4.  The Model Sheet

The sheet containing the calculation parameters for the model is called Model.

5.  The Text Sheet

The sheet containing the text fields is called Texts. 

This sheet offers a convenient way to edit text fields. This example contains the text of the Plant Economy block in block750.ebs. To create such a sheet, 

• select the text fields with a frame drawn with the left mouse button pressed down.
• double-click to open the multi-selection property sheet
• activate the Write to Excel Workbook flag
• export to Excel by DataàStandard ExcelàWrite to Workbook
• open the workbook
• open the Texts sheet.

 

 

If you have a text field that does not depend on profiles, it will be written to each profile column in case of export to Excel.

When you read such a text field from Excel, EBSILON^®Professionalwill take care of profile-specific texts. The flag Text depends on Profile for the text field will be changed automatically.

6.  Units

The unit specified in the workbook  with each specification value will be considered in importing only.

If you specify the unit psia in the workbook with a pressure specification value, it will be considered to be psia on importing (e.g. 5 psia will be considered to be 0.344738 bar).

On exporting the units in the workbook will be superseded by those specified in the document.

7.  How to start Workbook Activities?

Select one of the entries from the submenu of the DataàStandard ExcelàOpen workbook entry of the menu bar.

Open Workbook opens the workbook associated with your document and displays it in an Excel window on the screen, as if you would have opened the workbook by Excel.

Read active Profile from Workbook tries to import all data (values of specification values) from the workbook into the active profile of your document. All values of the active of your document will be replaced.

Write active Profile to Workbook exports all data (values of specification values) of the active profile from your document into the workbook.

Read Workbook tries to import all data of all profiles (values of specification values) from the workbook into the corresponding profiles of your document. All values of your document will be replaced.

Write to Workbook exports all data (values of specification values) of all profiles from your document into the workbook.

Or click on of the buttons from the EBSILON bar, which are marked with a green X:

• The left one is equivalent to DataàStandard ExcelàOpen Workbook.
• The middle one is equivalent to DataàStandard ExcelàRead active Profile from Workbook.
• The right one is equivalent to DataàStandard ExcelàWrite active Profile to Workbook.

Profile Data and Excel Sheets

The column G of every Excel sheet  holds the values of the root profile of the profile tree. This profile is named Design by default. Each columns right of it (from H upwards) may hold data of one sub profile.

On exporting data the name and the number (identifier) of one or all profiles are written to the Excel sheet. Each name is followed by a list of profile names in square brackets, which indicates its parent profiles at the moment of exporting.

Because

• you may rename profiles at any time,
• you may modify the structure of the profile tree any time, and
•  the profile names are not unique,

you cannot use the profiles names to assign one column of a sheet to a sub profile of the document when reading from a workbook.

That is why profile names are not used to assign the data of a column to a sub profile of profile tree This is done via the identifier of a sub profile, which is a positive integer number. This number is displayed within the profile tree and in row 6 of  the Excel sheet.

After having written the workbook, you can edit it within Excel, to modify some data. Afterwards you may want to read the workbook, to import the modifications inside your document.

 

On importing data

• EBSILON^®Professional gets the identifier of the sub profile from the current profile tree
  
  • (this is the identifier of the active profile, if you are reading active profile data only, or
  • the identifiers of all profiles in a loop, if you are reading the whole workbook),
    
• EBSILON^®Professionaldetermines the profile column of the Excel sheet, where it expects
  the values to read from (see table above),
• EBSILON^®Professional, tries to read the values from this column, and
• EBSILON^®Professional replaces the document values with the values read.

 

Before importing data from an Excel sheets for one or all sub profiles, be sure, that the assignment as described above meets your requirements. Normally this will match. But be careful, if the profile tree of the document was modified since the workbook you took for the basis of your Excel editing was written.