External Microsoft Excel file

 

Since the version 17 it is possible to insert Excel file with calculation into Engineering report with possibility to do later modification of its content.

Selecting the source file

After insertion of Excel file report item it is necessary to specify the file with the calculation. It can be done using the property Path to Excel file. Once the file is specified the full content of all visible Excel sheets is shown in Engineering Report. The way of presentation or selection of Excel sheets to be presented is described in following chapter.

 

Defining the Presentation of the Excel file

The way of presentation of the content of the Excel file can be defined in the "Setting of presentation" dialogue. The dialogue can be opened via double-click on preview of the Excel file or via the Edit button on the ribbon.

The left part of the dialogue shows the list of sheets available in the Excel file with marking about their status (visible / hidden, empty).

Each Excel sheet can be presented by different types of presentation. It is possible to define different (and also multiple) presentation for each Excel sheet. On following picture you can see an example with two types of presentation of the sheet "Deska".

The types of presentations for selected sheet can be added using buttons [All used cells], [User defined range of cells] and [Print-like presentation].

Button [Delete presentation] deletes the presentation currently selected in the list "Presentations of sheet".

Button [Delete all presentations] all presentations for all sheets. It is useful especially in case you need to present just one selected sheet.In this case you can easily delete all presentations and then just add the one you need.

 

Available types of presentation

• All used cells - shows the screen shot of rectangular area with all used cells

Note: Graphs and other graphical objects are not recognised as used cells and can exceed displayed area (see the Troubleshooting chapter)

• User defined range of cells - shows the screen shot of rectangular area defined by the user

• Print like presentation - performs the Excel in-build printing of the sheet using the setting stored in the Excel. The printing is then displayed as a picture in Engineering Report. In this case user can select between keeping whole printed pages as they were printed or to cut white margins to be able to e.g. fit more outputs at one page.

 

Picture size

All types of presentations (except Print-like presentation with Keep printed pages) enables to define the size of the output using the Picture size setting. There are available same types of sizes as in case of Report pictures.

In case of User defined size it is possible to keep one of Height and Width equal to 0. In such case the aspect ratio of the output is kept and size of the output is calculated using the non-zero value.

In case of Print-like presentation with Keep printed pages the output always covers whole page while the size of page is adapted to match the printed output from the Excel.

 

Editing the content of Excel file

The content of the Excel file can be edited using the ribbon button Edit external file.

This button launches application linked with the extension of the file. After saving the file and closing of the application the content of the report item needs to be updated using the Regeneration selected or Regenerate outdated. In case of Embedded Excel file, the regeneration is done automatically.

See the chapter Embedding for more details related to saving of Excel file.

 

Embedding

External Excel file can be inserted as link to file on the hard drive or as "Embedded".

Link to file on the hard drive: When the Excel files are inserted as a link their validity status informs about changes in the source files on the hard drive. They are marked as invalid when the source file changes. Then during regeneration their content is updated and redrawn. Those Excel files do not increase the size of the project but they are not displayed when the project is opened on different computer and the report item is regenerated.

Editing of content of linked Excel file modifies the file on hard drive itself.

Embedded text file: Embedding of the Excel file means storing of its source file inside the project. Such Excel files are independent on the files on hard drives. They are displayed correctly when the report item is regenerated on another computer. Embedded Excel files increase the size of the project.

Editing of embedded Excel file keeps original file on hard drive untouched.

 

Trouble shooting

Graphical objects in Print-like presentation has different size than in Excel

In case you get different relative size and positioning of graphical objects (as shown on example bellow) you need to switch the default system printer to some virtual PDF printer (e.g. the PDF Creator).

Original in Excel Wrong size in Engineering Report

 

Part of graphical object is missing

In case the part of graphical object is missing in presentation type "All used cells" the problem is caused by the fact the Excel ignores the graphical objects when evaluation the range of used cells

Original in Excel Missing part of graphical object in Engineering Report

 

The problem can be easily solved by adding any value into top left cell and bottom right cells. .