Publishing to Excel with a Template

Having the ability to publish data to Excel is a great feature to have.  But what happens when you want to refresh your data, but preserve all the Excel formatting and charting in a spreadsheet?  This article will show you how to accomplish just that task by using Data360 Analyze.

Download the Example Files 

The following link contains an example that we'll reference when walking through this tutorial.  

• Excel Template Example
  Zip file containing all the files necessary to run this example, along with the LNA file to use as an example.

Download the file and extract to the location of your choosing.  Once the files have been extracted, continue with the instructions below.

Importing the Example 

Import the example data flow by following the instructions in the section titled "Importing and exporting data flows and library nodes" within the "Sharing Documents" help section.

Creating an Excel Template File

We first need to create a template file to use with our Output Excel node.  The format and worksheets used will depend on your particular use-case.  However, in this example we created a template with three separate sheets.  The template in your downloaded examples is called: Template.xlsx

1. Sales Formatted
   The first worksheet.  This worksheet contains formatted column headers, a pivot table, and a chart reporting the aggregated sales by state.
   
   
2. Sales Data
   A placeholder for the unformatted sales data that will be populated by Data360 Analyze.
   
   
3. Temperature Data
   Average temperature data for a given state.  This will also be populated by Data360 Analyze.

Configuring the Data Flow

Open Data360 Analyze and navigate to the imported Excel Template Example data flow.  Once the data flow is opened, you'll notice a property in the Properties Panel called "ProjectRoot."  

Replace the contents of that property with the path to the directory where you extracted the example files.  This property is used in subsequent steps within the example.  If you are creating your own example from scratch, you may skip this step and proceed to the next section.

Configuring the Output Excel Node

Once you've placed an Output Excel node onto the canvas, you'll need to configure it to output the new file, along with instructing it where to find the Excel template.  

1. View the Output Excel properties
   Click on the Output Excel node to see the properties for that node
   
   
2. Fill in the File property with the path to the file you want created.  
   This file will be created using the template file you'll specify below.  In the example, we are making use of the data flow property that we created and referencing it by using the syntax {{^ProjectRoot^}}. This is optional, and you may instead place the full path to your output file here instead.
   
   
3. Specify the path to the TemplateInputFile.
   In this example, we are again making use of the Data Flow property that we created.  This is an optional feature and you are free to use the direct path to the template file here as well.
   
   
   
4. Specify the output file specifications
   Since we are publishing into a template, we need to make some minor configuration changes to the "OutputSpec" property to instruct Data360 Analyze where the given data should be placed in the Excel file. We can follow the OutputSpecExample to code the specifications for outputting data to sheet 2 and 3 in the Excel file.  
   
   

The Final Output

Once the latter steps have all been completed, the data flow can be run in its entirety.  This will create an Excel file which, in the case of this example, is named "Output.xlsx".  Opening up the file shows the data published into sheets 2 and 3, as we specified.  Sheet 1 is also populated, as it references the data in sheet 2. Refresh the pivot table by selecting it, navigating to the Analyze menu and selecting "Refresh" (or whatever steps you would use to refresh pivot table data in your current version of Excel.  The end result should look similar to the image shown at the top of this article.