Tuesday, 29 April 2014

NextReports: Generate an excel file based on existing template

NextReports 7.1 will introduce a new feature which will allow to generate an excel file based on one existing template file.

What does it mean? You may have an excel file with 2 (or more) sheets. One sheet is the actual data sheet and the others are computations made on values from data sheet like pivots for example.

Let's say you have a data sheet like:

and a pivot created on this data on a separate sheet:

This excel file is our template. By modifying data inside 'Data' sheet we will have by default this pivot in our resulted file. To make the pivot automatically refresh when we open the file, in the PivotTable Options dialog box, on the Data tab, select the 'Refresh data when opening the file' check box.

Now lets see how is this template used inside NextReports.

You create a simple report that prints the data in the form shown in 'Data' sheet. We have a simple query like:

 with a report showing just header and detail (no formatting needed):

Running such report as EXCEL will show us just a sheet (see previously Data sheet).

To specify that we have a template, some new properties were added on the Report node from layout tree:

 Here we can select a template excel file and the sheet number (starting from 1) where NextReports engine will write the data. In our example is sheet number 2. And that is all!

When you use some actions like Publish, Download from designer, the template file will be automatically passed between designer and server. On the server, when we do an upload, a new file upload field will allow to add a template. Also, Download and Edit in designer server actions will pass the template file to the client machine.

1 comment: