2 Using Excel Templates

By the end of this section you will be able to associate a Microsoft Excel template with a view.

About the Excel templates used in this section

In this tutorial section, you will use an Excel template called debtOverview.xls that is already stored on the PhixFlow server.

We have included a copy of this template in train.zip. To see what the template looks like:

1. Download train.zip and extract the contents to a folder on your computer (indicated as <path> in the following instructions).

2. Open <path>\train\excelTemplates\debtOverview.xls. The file opens with one worksheet, called Formatted Data, which shows how the data will be formatted.

3. If the file is protected, enable editing.

4. There is another, hidden worksheet. To view it, right-click on the Formatted Data worksheet tab and select Unhide.

The Excel file now has a tab called Basic Data. This worksheet is blank, ready for PhixFlow to populate it with data. Once the first sheet is populated, you can use the data in other sheets.

Find the location of Excel templates

1. In the repository, scroll to the bottom of the list and double-click System Configuration.

2. Expand the System Directories section.

3. Make a note of directory specified in the Template Location, which is set to something like /opt/phixflow/excelTemplates.

Specify an Excel template in a view

1. Open the model Screens and Forms 01-08: Views, filters and screens in the workspace.

2. Open the settings tab for the view called Overall Debt Levels. To do this:

   1. First, in the model, click on the 

      icon to open the settings for the  Overall Debt Levels table.

   2. Then, in the settings → Views section, double-click on the view called Overall Debt Levels.

3. Add an Excel template to the view. In settings → Advanced section → Excel Template, enter debtOverview.xls, then click 

   .

4. In the model window, click 

   .

Export view data to Excel

1. Open the View → Overall Debt Levels and check it has data.

2. In the View, click 

    in the toolbar and click  
   .

3. PhixFlow opens the Download Data Options window. Click the Download button.

   1. Ensure you have the Item to Download set to Export All Items otherwise you will only export the selected items. 

   2. Open

      

4. PhixFlow opens a notification pop up to show that the file is ready to download, click Download.

5. Open the Excel file.

   1. If necessary, Enable Editing.

   2. Unhide the Basic Data worksheet to see the exported source data.

   3. Switch to the Formatted Data worksheet and notice that the debt level is colour-coded.

      1. Screenshot of the formatted Excel data:

Open