L2-DA Demo Workflow for Sessions 4 - Part III

Styling Excel Sheets

Learning objectives:

• Style and organize Excel sheets with KNIME nodes

Workflow description: An Excel sheet is formatted with Continental Nodes for KNIME. A control table is generated based on an existing Excel sheet. Then different parts of the sheet are formatted according to the tags found in the control table. In particular, we change background colors, borders, cell formats, and column widths. Formatting information is passed on through the XLS format connections, with new formatting information added to the existing formatting information.

Reading an Excel sheet

A sheet with store report is read from an Excel file by an Excel Reader node. The sheet Onsite Store is selected. The path for the Excel file is passed on as a flow variable excel-file specified under the Flow Variables tab.

Creating a control table

1. A control table in the shape of the Excel sheet is generated by the XLS Control Table Generator node. Check the option write column header to first row. The resulting table has the same shape as the Excel sheet of interest

2. An Expression node is used to generate a table of tags. Use three expressions, one for each of columns A, B, and C.

   1. For column A, if Row_Number is 1, then output "header", otherwise output "row_header".

   2. For column B, if Row_Number is 1, then output "header", otherwise output "financial".

   3. For column C, if Row_Number is 1, then output "header", otherwise output "number".

Selecting a sheet

A sheet to be formatted is selected by the XLS Sheet Selector node

Setting background colors

Each XLS Background Colorizer node has two input ports. The top input port receives the control table, whereas the bottom input port receives the XLS format connection from the previous formatting node. The new formatting information is added to the existing XLS formatting information.

1. The first XLS Background Colorizer node sets the color to the header cells, by specifying the tag header.

2. The second XLS Background Colorizer node sets the color to the row header cells, by specifying the tag row_header.

Setting borders

The XLS Border Formatter node adds a border at the bottom of the header cells, by specifying the bottom border with the tag header.

Setting cell formats

The XLS Cell Formatter node specifies the format of the cells tagged as financial. These cells contains the total sale amount, thus the financial format with two decimal places is specified.

Setting column widths

The XLS Row and Column Sizer node adjusts the column widths. The column widths are automatically adjusted by the widths of the contents of the cells tagged as header.

Applying formatting information

The XLS Formatter (apply) node applies all the formatting information from all the XLS formatter nodes. The location of the Excel file is passed on by the flow variable excel-file created by the Excel Reader node. The newly formatted Excel file is saved as StoreReport_formatted.xlsx under the data folder, so that the original Excel file is not overwritten.