Writing an Excel file with Kettle has a lot in common with writing a text file. Except for a couple of settings specific for Excel files, configuring an Excel Output step is quite similar to configuring a Text file output step. One of the differences is that when you write an Excel file, you add a sheet to the file. What if you want to write more than one sheet in the same file?
Suppose you have a datasource containing books and their authors and you want to create an Excel file with two sheets. In the first sheet, you want the authors and in the second, the books' titles. This recipe teaches you how to do this.
In order to run this recipe, you will need a database with books and authors with the structure described in Appendix, Data Structures.
Carry out the following steps, in order to create the sheet with the authors' details:
- Create a new transformation.
- Drop a Table Input step into the canvas, in order to read the author information:
SELECT * FROM Authors order by lastname
- Add an Excel Output step.
- In the Filename textbox under the File tab, write the destination path and the name of the file (Books).
- As the Extension, leave the default value
xls. - Under the Content tab, make sure the Header checkbox is selected.
- In the Sheet name textbox, type
Authors. - Select the Fields tab and click on the Get fields button to fill the grid with the author data. The grid should look like the one shown in the following screenshot:
Tip
If you find that the default types or formats of the fields are not correct, you can fix them manually.
Carry out the following steps, in order to create the sheet with the book's details:
- Create a new transformation.
- Drop a Table Input step into the canvas, in order to read the book's titles information:
SELECT * FROM Books order by title
- Add an Excel Output step and set the same filename and extension configured in the previous transformation (Books and
xls). - Under the Content tab, make sure the Header checkbox is selected.
- In the Sheet name textbox type
Titles. - Under the same tab, make sure to check the Append checkbox.
- Select the Fields tab and press the Get fields button to fill the grid with book titles. The grid should look like the one shown in the following screenshot:
- Create a job and drop a Start job entry into the canvas.
- Then add two Transformation job entries and configure them for running the two transformations you created in the previous steps. The job should look like the following:
- Run the job. It will generate an Excel file with two sheets, one for authors and the other for titles. It should look like the following screenshot:
The intuitive way to generate an Excel file with two sheets would be to create a single transformation with two Excel Output steps, one for each sheet. However, that approach does not work because Kettle cannot manage concurrent access to the same Excel file in a single transformation.
One way to avoid this issue is to create different transformations, one for each sheet, and then calling these transformations from a job. With this approach, the transformations are executed sequentially, which means that the sheets are generated one at a time, avoiding the concurrency problem.
Another way to assure the sequential generation of the sheets would be using the Block this step until steps finish step from the Flow category. Using this step, the writing of the second sheet will wait for the first sheet to complete its writing process. For our recipe, the transformation should look like the following:
