Why can’t I get a specific XLS Formatting node to work?
The XLS formatting nodes work only in combination, therefore a single node will not do the job. Please refer to the overview for the basic concept behind the XLS Formatting functionality
Will the XLS Formatter (apply) node overwrite the content in my XLS file?
No. This node only applies formatting to cells and sheets, it does not overwrite the existing XLS file’s cell contents.
Where can I find information about the XLS Formatting nodes?
There are three sources of information:
- The conceptual overview
- The node descriptions
- The KNIME Hub - for learning purposes, we recommend studying the Pretty Table workflow
What is a XLS Control Table?
This is a regular KNIME data table, which follows XLS naming logic for column headers (A, B, C, …) and RowIDs (1, 2, 3, …). It is designed to feed user specific content into the formatting nodes, e.g. colors, column widths, hyperlinks, etc. These tables are never directly written to XLS files by the extension, their content is rather translated to the green port objects, which are ultimately written in one pass to an XLS file via the XLS Formatter (apply) node.
How do I define an XLS Control Table depending on cell content?
We recommend using the XLS Control Table Generator node and its ‘unpivot result table’ option. See the example on the KNIME Hub to learn this pattern. For more sophisticated use cases, you can always diverge from this recommendation, e.g. start with the XLS Control Table Generator, perform some data wrangling, and then pivot it back to a wide table layout yourself. Remember to fix the column headers and row IDs, e.g. by applying the XLS Control Table Generator node with the ‘unpivot result table’ option unchecked.
Can I preserve my original file's formatting and just add some specific other formats?
No, currently this is not possible. The original idea of this extension is to add formatting to XLS files written by KNIME. Internally, the XLS specification works with styles (as opposed to storing formatting instructions per cell). To avoid matching existing with the new required styles and even potentially having incompatibilities with new XLS features not yet covered in the standard open source libraries, we limit the applicability of our nodes to previously unformatted files. Please refer to the KNIME Forum and its many useful hints on how to use e.g. Python snippets to fulfill this task.
Can I apply dynamic formatting rules?
Yes, definitely. This is the strength of the extension. XLS Control Tables can be derived from your input data and can hence adjust to the structure of the target file. Please refer to the example on the KNIME Hub.
Why can't I change some configuration dialog options as they are greyed out?
Some nodes in the extension behave differently depending on the input data you connect. E.g. the XLS Control Table Generator will recognize whether you used the same node previously and now want to pivot the table back to the wide layout. Please refer to the node descriptions and examples on the KNIME Hub to learn more about how to configure these nodes via your choice of input tables rather than the greyed-out dialog elements.
Which nodes have a 'direct mode' and take information from input tables rather than tags?
XLS Control Table contents are usually arbitrary tags that will never make it to the final XLS file. But in some cases, they are also used to feed user specific content to the formatting nodes instead of tags. These are
- XLS Hyperlinker, which takes URLs,
- XLS Cell Commenter, which takes text that will be added as cell comment,
- XLS Background Colorizer, which takes cell background colors, and
- XLS Row and Column Sizer, which takes row height or column width values (in this case even exceptionally as double-valued columns).
Do the XLS formatting nodes need to follow a specific sequence?
Some sequences are obvious, e.g. XLS Control Table Generator is rather at the start and XLS Formatter (apply) at the end.
Node sequence becomes relevant especially in case of the XLS Sheet Selector node. It must be placed in front of any formatting instructions for that sheet. If the XLS Format Merger is used to combine instructions targeting different sheets, no additional formatting instructions are allowed afterwards.
Is the extension compatible with future versions?
All workflows created in a specific version will continue to work in future versions of KNIME and the extension. The same holds for XLS Formatting port object content written to disk via the Model Writer node.
