XLSX Simple Excel Format Apply (sheet select)

Provides a simplified interface to basic formatting using the Continental XLS Formatting community extension.

Simply attach this node after an Excel Writer and pass it the path variable identifying the XLSX file to which basic formatting is to be applied.

(This path variable can be most easily created within the Excel Writer node itself, by clicking the "V" button next to the file path, and supply a variable name next to "Create")

Basic formatting of header row, data text colour and font size plus numeric and date format is possible without having to learn how to manipulate the Continental nodes themselves.


Please note that the nature of this component is such that it needs to be initially executed with the path variable selected so that it can read the Excel sheet names. On first configuring, you will need to select the required path variable. Once selected, click Apply, and then re-execute the component. It will probably fail. Now you can configure it, and then apply/execute.

(If config options not showing, execute after selecting path variable and then re-configure)


@takbb Brian Bates 16 September 2023

Options

Freeze Panes

Freeze Top Row and Left column?

Open File after applying formatting (ignored if apply formatting not selected)

Choose whether to open the file in Excel after formatting has been applied.

Apply formatting

Select if formatting is to be applied by this component, or de-select if this will be chained onto further XLSX Format components (i.e formatting will be applied by a subsequent component)

Choose source path variable

Choose the path variable that contains the file name that you wish to edit

Freeze pane at column

If a subsequent XLS Control Format Template component is applied and the freeze pane option is ticked, specify the column at which it should freeze the pane. %%00010%%00010e.g. To freeze just column 1, set it to 1

Heading font size

Enter the Font size for heading in pt (default 14pt)

Data font size

Enter the Font size for row date in pt (default 14pt)

Sheets to format (if no sheets visible, click apply, then execute component and reconfigure)

Select the sheets that formatting should be applied to

Heading Text

Specify the colour of the heading text

Heading Background

Specify the colour of the heading background

Data Row Text

Specify the colour of the data row text

Data Background

Specify the colour of the data rows background

Data Alternate Banding

Use the data background colour on alternate lines to produce a banding effect

Footer Row Format

Choose additional format options for footer row

Suffix to append to file name for formatted xlsx

Enter a suffix that will be appended to the existing xlsx file name to create the formatted version of the file. (The original file will remain unchanged)

Number format (for Doubles / non-integer)

Enter the required number format for Doubles, or non-integer values

Date format

Enter the required format for dates

Integer format

Enter the required number format for Integer values

Apply footer special formatting to the following sheets: (comma delimited list)

Specify a list of sheets to which any specified footer (last row) formatting should be applied. This is a comma delimited list. You may use * to apply to all sheets.

Columns to be highlighted (Comma delimited list. Column may optionally be prefixed by sheetname e.g. sheet1.mycolumn)

Specify a list of columns to which highlighting is to be applied. A comma-separated list can be supplied.%%00010%%00010Column names are the exact heading names of columns, but may include wildcards. If the update is to apply only to that column on a specific sheet, then prefix with sheet name and period.%%00010%%00010e.g the following list:%%00010%%00010Sales, Amount*,summary.Total%%00010%%00010%%00010would appy highlighting to the column Sales and any column beginning Amount on any sheet, and additionally the Total column on the summary sheet.%%00010%%00010To make the column BOLD, it will use the reverse of the background/foreground colours chosen for the data rows (ignroing "banding")

Nodes

• String Configuration42 ×
• String Manipulation (Variable)38 ×
• Component Input21 ×
• Component Output21 ×
• Variable to Table Row20 ×
• Show all 80 nodes

Extensions

• FeatureContinental Nodes for KNIME
• FeatureKNIME Base nodes
• FeatureKNIME Basic File System Connectors
• FeatureKNIME Data Generation
• FeatureKNIME Ensemble Learning Wrappers
• Show all 9 modules

Links

• No links available