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

Advanced Excel Writer scenario

---

Workshop files

Download the following files.

Keep the filenames unchanged.

Save them in your workshop folder.

303B

sales.txt

Open

385B

expenses.txt

Open

22KB

template.xlsx

Open

39KB

tr_write_excel.ktr

Open

---

Sales & Expenses

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)

---

1. Excel Template

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

1. Open template.xlsx in Excel:

Blank template

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

Blank SourceData

2. Write Year

3. Write Sales

4. Write Expenses

5. RUN