Up: Component summary

CSV2Excel

Converts a file from CSV format to Excel 97 format. The visual style of the result can be configured extensively. Several CSV files can be written to the same Excel file onto separate sheets. Conversion is done using the Apache POI library.

Styles

Visual style is specified using formatting rules defined in the optional "style" input. Each line of this CSV file contains a visual style that is applied to a set of columns and rows. In case several styles match a particular spreadsheet cell, the first style is used. Any columns may be left out from the style CSV file.

Style column are as follows:

• Sheet: comma-separated list of sheet names or indices (from 1) for which the style applies, or * for all (default). See below for format.
• Row: comma-separated list of row indices (from 1), or * for all (default).
• Column: comma-separated list of column names or indices, or * for all (default).
• Bold: "true" for bold font, "false" or NA otherwise.
• Italic: "true" for italic font, "false" or NA otherwise.
• Underline: "true" for underlined font, "false" or NA otherwise.
• TextColor: text foreground color as hex string, e.g., "#ff0000".
• BGColor: text background color as hex string.
• Format: cell content format, e.g., "0.00" for numeric values with two decimal places.
• Align: horizontal alignment; one of "left", "right", "center" or "justify".
• FontSize: font size in points (10 is the default).
• Font: font family, e.g., "Arial".
• Border: set visible cell borders. Legal values are comma-separated combinations of "left", "right", "top" and "bottom".
• Condition: expression for conditional formatting; see below. If defined, the style is applied only for cells that match the expression.

Sheets, rows and columns are specified with a comma-separeted format supporting the following elements: INDEX,NAME,LOW..HIGH,NAMELOW..NAMEHIGH. Each element can be prefixed with '-' to exclude the element. Rows cannot be defined using names. Example: Col1,3..9,-5,-Col6..Col7 includes columns Col1, and 3 to 9, and excludes 5, and Col6 to Col7.

Conditional formatting

Conditional styles are applied to a given cell only if the value of the cell is below, above or between some thresholds. The "Condition" column contains a simple boolean expression, having one of two forms. One form is "OP VALUE", where OP is one of ">", ">=", "<", "<=", "==" or "!=". There must be a space after OP. The other form is "BETWEEN VALUE1 VALUE2" or "NOTBETWEEN VALUE1 VALUE2", which compare cell values to a range.

VALUE is number, string or cell reference. Strings must be in double quotes, e.g., == ""str"" (with no quotes around the CSV style cell). The following style attributes are not supported in conditional formatting: "Format", "Align", "Font" and "Border". A cell may have both a regular style and multiple conditional styles. Row/column ranges are restricted to rectangles for conditional formatting.

Examples: ">= 3.5" applies the style to cells having a value of at least 3.5; BETWEEN ""str3"" ""str7"" applies to the range of strings from str3 to str7; "!= A7" applies to cells not having the same value as cell A7.

Hyperlinks

The "refs" input can be used to insert hyperlinks to external resources (URLs). The format of "refs" is similar to the CSV2Latex component. The file has three columns: URL, refCol, and valueCol. URL is an URL, which has an $ID$ tag in place of the reference ID. The reference itself is taken from the refCol column. The visible name of the link is taken from the valueCol column, which also indicates the target column for the reference declaration.

Inputs

Outputs

Parameters

Test cases