Icon

Format Multiple Excel Sheets

This workflow snippet provides you with an example of how you can set up a loop that iterates over each sheet in a file and use a Control Table node that generates a control table, the table simply serves as a template table containing certain tags for the cells, which are going to be formatted. In this example, we use Rule Engine to tag the first row as “header” and all the remaining rows as “body”. You can then use the Community Extension: Continental Nodes for KNIME for formatting your Excel file based on the tags. After the loop is completed, multiple XLS Formatting port objects are collected and merged into one before it is applied.

Modifying multiple sheets with a format pattern in an Excel file

This workflow iterates over multiple sheets in an Excel file using a loop and uses the control table to tag the "header" and "body" for each sheet that has to be formatted. After the completion of the loop, a PortObject for format pattern is genrated for each sheet which is then megred into one, before it is applied.

Iterate over sheets
Table Row to Variable Loop Start
XLS Formatting
Reading one sheet at a time
Excel Reader
copy/pastable component that recursively merges a column's XLS Formatter port objects to one
Merge Ports
Generate control table
XLS Control Table Generator
Write a new Excel file with formatted sheets
XLS Formatter (apply)
Reading excel file with multiple sheets
Read Excel Sheet Names
Collect XLS formatting for all sheets as PortObjects
Loop End
Define rules for header and body
Rule Engine
Generate control table with the tags "header and "body"
XLS Control Table Generator

Nodes

Extensions

Links