# Excel Writer {% hint style="warning" %} #### 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](https://academy.pentaho.com/pentaho-data-integration/data-integration/data-sources/flat-files/text/text-file-input) first. **Estimated time:** 35 minutes {% endhint %} {% embed url="" %} Advanced Excel Writer scenario {% endembed %} *** {% hint style="info" %} **Workshop files** Download the following files. Keep the filenames unchanged. Save them in your workshop folder. {% endhint %} {% file src="" %} {% file src="" %} {% file src="" %} {% file src="" %} ***

{% hint style="info" %} **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) {% endhint %} *** {% tabs %} {% tab title="1. Excel Template" %} {% hint style="info" %} #### 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 {% endhint %} 1. Open `template.xlsx` in Excel:

{% hint style="info" %} SourceData - the datasheet. Transformations write to the required cells that are used to create the graph. {% endhint %}

{% endtab %} {% tab title="2. Write Year" %} {% hint style="info" %} #### 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. {% endhint %}

{% tabs %} {% tab title="1. Generate Rows - Year" %} {% hint style="info" %} #### Generate rows - year Generate rows outputs a fixed number of rows. Here you output a single row that contains the report year. {% endhint %} 1. Start Pentaho Data Integration (Spoon). {% hint style="info" %} {% tabs %} {% tab title="Windows (PowerShell)" %} ```powershell Set-Location C:\Pentaho\design-tools\data-integration .\spoon.bat ``` {% endtab %} {% tab title="macOS / Linux" %} ```bash cd ~/Pentaho/design-tools/data-integration ./spoon.sh ``` {% endtab %} {% endtabs %} {% endhint %} 2. Drag the ‘Generate Rows’ step onto the canvas. 3. Double-click on the step, and configure the following properties:

4. Close Step. *** {% hint style="info" %} **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. {% endhint %} {% endtab %} {% tab title="2. Excel Writer - Year" %} {% hint style="info" %} #### Excel Writer - year Microsoft Excel Writer writes incoming rows into an Excel workbook. Use `xlsx` when you work with templates and charts. {% endhint %} 1. Drag the ‘Excel writer’ step onto the canvas. 2. Create a hop from the ‘Year’ step. 3. Double-click on the step, and configure the following properties:

{% hint style="info" %} Use these paths: * Output: `${Internal.Transformation.Filename.Directory}/Sales_and_Expenses_2023.xlsx` * Template: `${Internal.Transformation.Filename.Directory}/template.xlsx` Select **Replace with new output file** while you develop. It resets the workbook on every run. {% endhint %} 4\. Click on the Content tab, and configure the following properties:

5. Click on ‘Get Fields’ button. 6. Click OK. {% endtab %} {% endtabs %} {% endtab %} {% tab title="3. Write Sales" %} {% hint style="info" %} #### Write sales Write sales rows into the same workbook. Use a blocking step so the year write completes first. {% endhint %}

{% tabs %} {% tab title="1. Text File Input - Read Sales" %} {% hint style="info" %} #### Text file input - read sales Read the sales dataset from the workshop file. Keep the header row enabled so field names match the template. {% endhint %} 1. Drag the ‘Text file input’ step onto the canvas. 2. Double-click on the step, and configure the following properties:

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

{% hint style="info" %} * Ensure the Header is selected. * No empty rows * Mixed Format {% endhint %} 4. Click on the Fields tab, and click on ‘Get Fields’ button: {% hint style="info" %} Returns the Header values as stream fields. {% endhint %} 5. Click OK. {% endtab %} {% tab title="2. Block until Step Finish - Wait Year" %} {% hint style="info" %} #### Block until steps finish - wait year This step waits for specific steps to finish. Use it to prevent parallel writers. {% endhint %} 1. Drag the ‘Block this step until steps finish’ step onto the canvas. 2. Create a hop from the ‘Read Sales’ step. 3. Double-click on the step, and configure the following properties: * Watch step: the step that writes the year (copy `0`) * If you used **Get steps**, remove everything except the year writer step

{% hint style="info" %} This will result in the workflow being blocked until the Write Year step has been completed. {% endhint %} {% endtab %} {% tab title="3. Excel Writer - Write Sales" %} {% hint style="info" %} #### Excel Writer - write sales Write sales rows into the existing workbook. Use **Use existing file for writing**. {% endhint %} 1. Drag the ‘Excel writer’ step onto the canvas. 2. Create a hop from the ‘Wait Year’ step. 3. Double-click on the step, and configure the following properties:

{% hint style="info" %} Use the same output path you used in the year writer: * Output: `${Internal.Transformation.Filename.Directory}/Sales_and_Expenses_2023.xlsx` Select **Use existing file for writing**. {% endhint %} 4. Click on the Content tab, and configure the following properties:

5. Click on the ‘Get Fields’ button. 6. Delete the productline field, as its not required. The template already has the fieldname and you are just writing the data, starting at cell B5. 7. Click OK. {% endtab %} {% endtabs %} {% endtab %} {% tab title="4. Write Expenses" %} {% hint style="info" %} #### Write expenses Write expense rows into the same workbook. Block until the sales write completes. {% endhint %}

{% tabs %} {% tab title="1. Text File Input - Read Expenses" %} {% hint style="info" %} #### Text file input - read expenses Start with loading the Expenses data into the data stream. {% endhint %}

{% endtab %} {% tab title="2. Block until Step finish - Sales" %} {% hint style="info" %} #### Block until steps finish - wait sales Wait until sales data has finished writing to the workbook. {% endhint %}

{% endtab %} {% tab title="3. Excel Writer - Expenses" %} {% hint style="info" %} #### Excel Writer - Expenses Write expenses to `SourceData`. {% endhint %}

{% endtab %} {% endtabs %} {% endtab %} {% tab title="5. RUN" %} {% hint style="info" %} #### Run the transformation Steps initialize in parallel. Use blocking steps to prevent concurrent writes to the workbook. {% endhint %} 1. Click the Run button in the Canvas Toolbar. 2. Open the Sales\_and\_Expenses\_2023.xlsx file.

{% endtab %} {% endtabs %}