Excel Writer

Working with Excel ..

Workshop - Excel Writer

Excel reports often need templates, charts, and fixed layouts.

Here you populate a pre-formatted Sales and Expenses report. You will write multiple sections into one workbook. You will control execution order so writers do not conflict.

What you'll do

Use a template workbook and write to fixed cell positions
Write a report header with Generate rows
Read sales and expense rows from text files
Block parallel flows before writing to the same file
Write multiple sections with Microsoft Excel Writer

By the end, you will know how to write into an Excel template safely. You will also know when to block parallel flows.

Prerequisites: Understanding of basic transformation concepts (steps, hops, preview). Complete Text File Input first.

Estimated time: 35 minutes

Workshop files

Download the following files.

Keep the filenames unchanged.

Save them in your workshop folder.

303B

385B

22KB

39KB

Create a new transformation

Use any of these options to open a new transformation tab:

Select File > New > Transformation
Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

Excel template

The various stages of the transformation write data to a template.xlsx. The template has 2 worksheets:

Sales Chart - this worksheet creates a 3D stacked graph
SourceData - worksheet

Open template.xlsx in Excel:

SourceData - the datasheet. Transformations write to the required cells that are used to create the graph.

Write year

The first workflow is to write the current Year to the SourceData worksheet in the template.xlsx

You can change the year value.

Generate rows - year

Generate rows outputs a fixed number of rows. Here you output a single row that contains the report year.

Start Pentaho Data Integration (Spoon).

Set-Location C:\Pentaho\design-tools\data-integration
.\spoon.bat

Drag the ‘Generate Rows’ step onto the canvas.
Double-click on the step, and configure the following properties:

Close Step.

Summary

Generates a record that holds the Year value – 2023 – in the year stream field.
The Excel template will also need to be formatted yyyy to interpret the Date.

Write sales

Write sales rows into the same workbook. Use a blocking step so the year write completes first.

Text file input - read sales

Read the sales dataset from the workshop file. Keep the header row enabled so field names match the template.

Drag the ‘Text file input’ step onto the canvas.
Double-click on the step, and configure the following properties:

Click on the Content tab, and configure the following properties:

Ensure the Header is selected.
No empty rows
Mixed Format

Click on the Fields tab, and click on ‘Get Fields’ button:

Returns the Header values as stream fields.

Click OK.

PreviousExcel NextXML

Last updated 5 months ago

Was this helpful?

Good afternoon

Excel Writer

Workshop - Excel Writer

Excel template

Write year

Generate rows - year

Excel Writer - year

Write sales

Text file input - read sales

Block until steps finish - wait year

Excel Writer - write sales

Write expenses

Text file input - read expenses

Block until steps finish - wait sales

Excel Writer - Expenses

Run the transformation