Excel Export Block

About

Block Connection Stubs

 

About

The Excel export block exports the data coming in to it to an Excel workbook at a specific location.  By default, this block requires excel to be installed on the machine that Analytics Canvas runs on if excel templates with formatting are to be used.   However it is possible to generate excel workbooks without excel (see the end of this section).

 

Block Connection Stubs

Inputs Outputs
1. The data set that is to be exported to the Excel workbook at the sheet location specified in the block. None

 

 

Creating Excel workbooks and reports

 

To create an excel workbook, first put an Excel export block on the canvas, and select it.  You will see in the block configuration area at the bottom of the screen the following controls:  Press "New Workbook", and a new Excel workbook definition will be created.

 

 

After selecting new workbook, you will be given the following options-  you can either generate a complete new workbook, starting with a blank workbook each time, or you can start with a template by specifying an existing Excel workbook to start with.  The template workbook should not have the same name as the workbook to be generated, to avoid overwriting it.  The location, and name structure of the Excel workbook to be created can be specified here.  This workbook will be created at the beginning of the canvas run, and all the Excel export blocks that reference it will be able to write data into it.

 

 

The final step is to specify where the data should be written into the workbook.  If you use a template, you will be able to pick the exact location, and will be able to see the contents of the template to properly position the data- to do this, press the "Pick" button, or specify the worksheet name.

 

 

 

You can use the "Write this data to template" button to insert the selected data set to temporarily insert the data to help create the template.  It is important to delete it from the template, after to ensure that no test data remains during the regular run.  Then press "Open template" and excel will open and the template will be loaded- make whatever changes you wish to make- add graphs, etc., remove the test data, then save the template and close excel.  Now when you run the canvas, the data will be inserted where specified, and the template you have created will be used as the starting point- and a new workbook will be created.

 

In this example, three Excel output blocks write three different data sets into a single output workbook:

 

By selecting one of the output blocks, then clicking on "Pick" to see where the data is going to be inserted, you will be able to see inside the excel template.  The data set that you are positioning now will be in green.  Any data sets inserted by other Excel export blocks in the canvas will be shown in Yellow.

 

 

 

By building a workbook that has graphs linked to these ranges, entire dashboards can be easily created:

 

 

Generating Excel files without installing Excel

There are to methods available for writing into excel export files.
  1. Method 1- requires Excel installed (default)
  2. Method 2- does not require Excel installed (but does not support advanced excel features)


 

By default, the method used supports all functionality in excel. To use this method it is necessary to have Excel installed on the machine that is running Analytics Canvas. This is also true for automation server - if you use this method, the server running Analytics Canvas must have Excel installed, and be able to run Visual Basic scripts (.vbs files) To be able to generate excel files even without having excel installed on the machine Analytics Canvas is using, it is possible to disable the advanced features by editing the workbook definition: Examples of some of the advanced features that are not supported are;