The Excel Writer plugin offers support for Excel template files that can be filled in a variety of ways using Kettle a.k.a. PDI. In this post I would like to show how to fill an Excel report template file that has pre-styled cells, formulas and charts on different sheets. The entire report is filled within a single transformation.
In order to follow along the tutorial you’ll need the Excel Writer plugin version 1.2. You will also need Kettle 4.1 or above since I will be using the “Block until steps finish” step introduced in Kettle 4.1. If you are using Kettle 3.2. or 4.0, don’t worry. There is an example transformation for those too. The “Block until steps finish” step has been replaced with a custom javascript step in this case. Get the example files to follow along.
The example report
The example report contains summary data for six months of sales and expenses. It contains three sheets.
- The “Sales Chart” sheet contains a chart displaying stacked bars of sales per category and month.
- The “SourceData” sheet is where the numbers go. It contains the input fields, some calculated values and a pie chart.
- The “Metadata” sheet holds information about the report. It contains the generation date, the name of the transformation that generated it and the host it was generated on.
The template file
The template file for the report has been cleared of all input. The “SourceData” sheet needs to be filled in the highlighted spots now. The rest of the fields are calculated cells.
The “Metadata” sheet also needs some information filled in.
Filling the template file with Kettle
Looking at the data entry spots above it becomes clear, that there are 4 places to insert data.
- the year cell on the “SourceData” sheet
- the sales cells on the “SourceData” sheet
- the expenses cells on the “SourceData” sheet
- the cells on the “Metadata” sheet
There are four places to output data to, so there’s going to be four Excel Writer steps in the transformation. Now there’s a problem. Kettle transformations execute concurrently, so if all four steps started writing to the same Excel file, things would not work. Therefore it is necessary to ensure an order of execution. This can be done by creating four separate transformations and calling them one after another in a job, but this is not always convenient. More often than not related data that needs to go into different parts of an Excel document is produced naturally within a single transformation. Therefore in this tutorial the order is ensured by different means. Check out the transformation graph below.
Ensuring sequential order
The transformation writes to the Excel file in four steps, in the following order: year cell, sales cells, expenses cells, metadata cells. To make this work the Excel writer steps need to be configured to write to the same file, starting with a template file. To achieve this, the year writer step is configured to use the template file and create a new output file. The other Excel writer steps are configured to directly write into the (now existing) output file. In order for this to work it is necessary to also enable the “wait for first row before creating file” option on all the Excel writer steps. This simply makes sure that they start looking for the output file only after they see the first row to process. The idea is to delay that first row until all preceding writing operations on the output file are completed. This is where the “Block until steps finish” steps come in. The year writing step is first in line, it just takes the template file, creates the output file and writes into the year cell. The sales writer waits for the year writer to finish, the expense writer waits for the sales writer, and the metadata writer waits for the expense writer. This results in a nice chain of steps each waiting for their turn to do their work on the Excel report file. The result is a nice report with all the numbers filled in 
If you are using Kettle 3.2 or 4.0, the “Block until steps finish” step is not available. In the example files I replaced this step with a custom javascript implementation of the same functionality. Other than that the transformations are identical.
It’s a question of style
Another aspect of filling an existing sheet with data is to leave the styles of existing cells unchanged. If anybody already did style the cells in the template, they should be left alone. So make sure the corresponding option is enabled on the Excel writer steps
Another task is to recalculate all formula cells of the workbook. Excel (depending on version, file type, and probably some other things) will not update the values of calculated cells automatically when opening the generated file. The Excel writer step can recalculate the formula cells too. The option is available on the content tab. But beware: some functions or naming types may not be supported by the underlying POI library yet, so you might run into errors. In these cases it might help to rephrase the formula, switch to the xls file type, or live with the fact that you have to hit Ctrl+Shift+Alt+F9 after you open the generated file for the first time to have Excel recalculate everything.
Note: if you’re missing these features on the Excel writer step dialog you’ll likely need to upgrade to version 1.2 of the Excel Writer step.
Downloads
Get the example files package. It contains the transformation for Kettle 4.1 and 4.0/3.2 along with all input and template files.
PS: Special thanks go to Dan Keeley for suggesting the “leave existing styles unchanged” and “recalculate formulas” features, and Dan Peacock for an elaborate version of the custom JS implementation of the “Wait for other step to finish” step.
PPS: dizzy brought up a common use case: print a “totals” line at the end of an output. The following sample demonstrates how to do it. It works on Kettle 4.1.x
Enjoy
Slawo
446
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
