Up: Component summary Component

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:

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.

Version 1.3
Bundle tools
Categories Convert Excel
Authors Kristian Ovaska (kristian.ovaska@helsinki.fi), Jianmin Wu (jianmin.wu@helsinki.fi)
Issue tracker View/Report issues
Requires poi-3.10-FINAL-20140208.jar (jar)
Source files component.xml CSV2Excel.java
Usage Example with default values

Inputs

Name Type Mandatory Description
csv CSV Optional CSV file corresponding to sheet 1.
csv2 CSV Optional CSV file corresponding to sheet 2 (optional).
csv3 CSV Optional CSV file corresponding to sheet 3 (optional).
csv4 CSV Optional CSV file corresponding to sheet 4 (optional).
csv5 CSV Optional CSV file corresponding to sheet 5 (optional).
csv6 CSV Optional CSV file corresponding to sheet 6 (optional).
csv7 CSV Optional CSV file corresponding to sheet 7 (optional).
csv8 CSV Optional CSV file corresponding to sheet 8 (optional).
csv9 CSV Optional CSV file corresponding to sheet 9 (optional).
array Array<CSV> Optional Array of CSV files. Each CSV file adds a sheet to the Excel file. Sheets are named using array keys.
style CSV Optional Specifies visual formatting rules for the result spreadsheet.
refs CSV Optional Specifies formatting rules for hyperlinks.

Outputs

Name Type Description
excelFile Excel The output Excel (XLS) file.

Parameters

Name Type Default Description
enableFormulas boolean false If true, cell values starting with "=" are treated as formulas and are evaluated by the spreadsheet application at runtime. If false, such strings are treated as string literals.
frozenColumns int 0 Number of left-hand columns that are frozen, i.e., always remain visible on the spreadsheet when the columns are scrolled.
frozenRows int 1 Number of top rows that are frozen, i.e., always remain visible on the spreadsheet when the rows are scrolled.
missingValue string "NA" The string to be inserted into the Excel file for missing values (NAs) in the input.
sheetNames string "" Comma separated list of sheet names for the result spreadsheet. If a name is empty, a default name is used.

Test cases

Test case Parameters IN
csv
IN
csv2
IN
csv3
IN
csv4
IN
csv5
IN
csv6
IN
csv7
IN
csv8
IN
csv9
IN
array
IN
style
IN
refs
OUT
excelFile
case1 (missing) csv (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing)
case2_style properties csv (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) style refs (missing)

frozenColumns=2,
frozenRows=2,
missingValue=NaN

case3_sheets properties csv csv2 csv3 csv4 csv5 csv6 csv7 csv8 csv9 (missing) style (missing) (missing)

sheetNames=MySheet1,MySheet2

case4_formulas properties csv (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing)

enableFormulas=true

case5_array properties csv (missing) (missing) (missing) (missing) (missing) (missing) (missing) (missing) array style (missing) (missing)

sheetNames=Individual sheet


Generated 2018-12-17 07:42:29 by Anduril 2.0.0