Procedure for learning kettle in Excel writer

Step for learning kettle in excelwriter

 

In the excelwriter step, you can enter content in an Excel template file and write content to different sheets of the same file. This article provides an example to illustrate how to generate a report through a conversion.

The excelwriter step is available in kettle4.1 or later versions. We also use the "block until steps finish" step (available in kettle4.1). If you use a version of 3.2 or 4.0, use the custom Objective C step instead. The sample code in this article is as follows:HereDownload.

 

Final export report file format

The example report file contains the sales and expense information for six months. It contains three sheets: "sales chart" sheet, which contains a chart showing the sales status of each type each month.

"Sourcedata" sheet is the source data that needs to be filled in through kettle, including some fields, some calculated values and pie charts.

"Metadata" sheet is some information about the report itself. It is generated on the date of generation, the name of the conversion, and generated on that host.

 

Template File

In addition to the format of the template file, there is no data before the input. "sourcedata" sheet needs to fill in the corresponding data. Other fields are automatically calculated (with formulas already available ).

 

"Metadata" sheet also needs to fill in some information.

 

Use kettle to fill in data in the template file

To analyze the Template File above, you need to enter the data entry point. There are four aspects to insert data.

1. Year cells on "sourcedata" sheet;

2. Sales Information Data on "sourcedata" sheet;

3. "sourcedata" sheet fee information data;

4. "metadata" sheet cell;

 

Since data needs to be output in four places, you need to define four Excel writer steps in conversion. The problem is that kettle is executed in parallel during conversion. If an Excel file is written in four steps at the same time, an error occurs. Therefore, make sure that the execution is performed in order. We can define four transformations and then call the four transformations in sequence in the job. Because the job is executed sequentially, it is not very convenient. Usually unrelated data needs to be filled in to the same Excel file. It is best to define data in a conversion for easy maintenance. This article uses another method to implement sequential execution. See:

Ensure sequential execution

Writing an Excel file in conversion requires four steps, in the order of year, sales, expense, and report metadata cells. In the Excel writer step, you must configure the file to be written to the same file, that is, the file copied from the template file. Therefore, configure the template file and create a new output file in the first step (year writing step. For other Excel writing steps, you only need to configure the output file (because it already exists ), however, you also need to select the "wait for first row before creating file" option (do not create a file before receiving data ), this ensures that the output file is searched only after the first data is received.

When receiving the first line of data, all the operations in the previous Excel write step are completed. Here, the "block until steps finish" block the step. The year write step is the first execution, so you need to configure the template file and create the output file, and write the year data. The sales write step is waiting for the year step to complete, the cost step is waiting for the sales step to complete, and the metadata step is waiting for the cost step. The result is like a precise chain step, waiting for each other, and finally writing their own data.

If you use version 3.2 or 4.0, the blocking step is invalid. Check the corresponding version in the attachment and use the Javascript step instead.

Style Problems

Write Data to the template file and keep the style unchanged. Select the corresponding options in the Excel writer step.

In addition, formulas in the workbook need to be recalculated. Because of the Excel version, file type, and other possible conditions, the calculated cell value is not automatically updated when the generated file is opened, the cell formula can be automatically re-calculated in the Excel writing step, and corresponding options are available. However, some functions or Naming types are not supported by the poi Library at the moment, and an error may be reported, try to rewrite the formula, change the xls file type, or manually activate the technology (CTRL + Shift + ALT + F9 ).

 

 

Procedure for learning kettle in Excel writer