ExcelPOIInput - Input From Excel (POI Version)

Read a cell's data from an Excel file, then output it to the stream or property. When you execute a Flow in this component, the below things can be done.

• Get the value or the format information at the position specified in a document.
• Get the record value or the format information from multiple areas specified in a document.

This component uses Apache POI.
Before using this component, you should start up the Excel Builder first, read an Excel file as a template next, then make a connection between the cell of the worksheet and the field definition of the component.
For details, please refer to following topic "How to use ExcelBuilder".

Stream Information

Input	Format	Binary
	Number of Inputs	1
	Description	The binary image of the Excel file to be read.
Output	Format	Record
	Description	The records in the area defined by the Excel Builder in the Excel file. When multiple record areas are defined, the number of the output stream is as same as the number of the record areas, and the record name will be each stream's name. The field name can be defined in the Excel Builder, but can't be changed in the Flow Designer. About the field defined by the String type, if the corresponding cell is number, the string applied to the cell format will be read, namely, the string displayed in the Excel will be read.

Component Properties

Name	Data Type	Mapping	Description
IgnoreBlankRow	boolean	In & Out	Specify how to process a blank row. true [true] - Ignore the blank row in the area defined as record and read it. false [false] - The all fields read the blank row as an empty record.
RoundUpFormula	boolean	-	Specify whether to round up the result saved in the worksheet or not. If the result in the Excel is a number, it will be saved as a 64-bit binary floating point value in the worksheet. Therefore, maybe an error will occur in the result operated by the decimal arithmetic. (Example) 350 * 0.7 = 244.99999999999997 In this case, the Excel can only show 15 signifigicant digits, so in the cell, 245 is displayed. If RoundUpFormula is set as "true", the value got by this component is as same as the value displayed in the cell (if the number of the displayed digit is set to the maximun). If it is set as false, the component will get the value saved inside the Excel directly. true [true] - Round up the result by 15 significant digits. false [false] - Don't round up the result.
MergedCellValue	choice	-	Specify how to process when the value to be read is in a merged cell. TopLeftCell [parent] - Read the value on the upper-right of the merged cell. OriginalCell [original] - Read the value of the object cell directly. In this case, in the cells except the upper-left, blank can be read.
SheetNotExist	choice	-	Specify how to process if the worksheet to be read doesn't exist. Continue - Continue on reading data. If you want to read record area from the worksheet which doesn't exist, it will be read as an empty record. If you want to read a single cell, a null value will be read. Exception - An exception will occur.
LoopProcess	loopProcess	-	Select whether to output the multiple records of the area defined by the Excel Builder in batch or output the records one by one by executing loop. true [true] - Output the records one by one to the stream from the start of the loop. false [false] - Output the all records to the stream in batch.
Sheet	category	In & Out	When you define a single cell or record by the Excel Builder, the sheet name will be listed into the "DefinedSheetName". To assign a value to the "InputSheetName" corresponding with each sheet, you can replace it with the listed sheet name, then the data can be read from the cell of the sheet name. The property value can be modified in the mapper, so you can select a sheet to input dynamically in the Flow.
Cell	category	In	The single cell area defined by the Excel Builder in the Excel file. When the Flow is running, the corresponding cell's data can be read into the property. Also, the property will become the stream variable automatically. In the mapper following to this component, the mapper can be considered as a stream variable. About the stream variable, please refer to the "Introduction"- "More topics" -"Flow elements" -"Variable" - "Stream variable". The field name can be defined in the Excel Builder, but can't be modified in the Flow Designer. About the field defined with the String type, if in the corresponding cell there is a number, the string applied to the cell format will be read, namely, the string displayed in the Excel will be read.

Loop

If multiple records exists in the area defined by the Excel Builder, and LoopProcess is "true", it will be the start of the loop.

Transaction

Commit	Do nothing
Rollback	Do nothing

Exceptions

Type	Parameter	Stream for error handling flow	Error Code	Description
Exception	None	This component's input stream	-	When the data can't be read from the cell defined by the Excel Builder
			-	An error occured when converted to the Flow's field type
			-	When the file you want to open isn't supported by our application. It's necessary to save the Excel file whose version is below Excel97 again by using the Excel97 or above.

Restriction

About the readable files

The readable files are decided by the POI library you are using.
The Excel file which is Excel97 or above can support the xlsx files added from the Excel2007.
Confirming whether 1 file can read the data up to 30MB or not.

Value type	Estimated number of cells.
Number	1,800,000 cells
String	1200000 cells (when there are 4 bytes, 2 double-byte charaters or so)
	300000 cells (when there are 80 bytes, 40 double-byte characters or so)
	3600 cells (when there are 8K bytes, 4096 double-byte characters or so)

In an Excel format, there is 10-byte to 20-byte additional information in a cell, so compared with the data quantity to be read, the number of the cells is decreasing.

About the cell's value got by the String type

About the field defined by the String type in the record area or the cell area, if in the corresponding cell there is a number, the string applied to the cell format will be read, namely, the string displayed in the Excel will be read. For example, if the cell format is set as US$ currency and 2-digit decimal, and 123 is entered into the specified cell, if you specify it as String type by ExcelPOIInput, the string - "US$123.00" can be got. If you specify it as Number type like Integer type or Double type, the value-123 can be got.
If you specified it as some special cell format like telephone number or postcode etc., it's possible that you can't get the correct string.

Merged cell

If you want to get the value or format information of merged cell, please specify the address of left-top cell of the area.
For example, for cell definition, the area is C3:D5, specifies C3.
For the record definition, if the record area has only one row, specifies the left cell. If the record area has multiple rows, specify the left-top cell.

Computational error of Excel

When specifies true for RoundUpFormula, the value which is same as that Excel displays is acquired. Notice that this value is different from the value stored in the Excel file.
For example, in the case that the values are as follows.

  Cell A1： 350
  Cell A2： =A1*0.7
  Cell A3： =A2-244

If you display 15 digits below the decimal point by formatting, you can see as follows in Excel.

  Cell A1： 350.000000000000000
  Cell A2： 245.000000000000000
  Cell A3：   0.999999999999972

Thus, you can see the computational error in the cell of A3.
On the other hand, when RoundUpFormula is set to "true", the value of A2 becomes 245. It becomes 1 if 244 is subtracted from this value with Mapper, and it becomes a value different from cell A3.
So, if you set RoundUpFormula to "true", If the same calculation is done to the value of the cell where the rounding error margin occurs by Excel and this component, it becomes possible to differ the numerical result. Please use RoundUpFormula very noting this respect.

Date-time cell

In the Excel, the actual value of the cell which is formatted as date-time is the floatage decimal value. The value assumes January 1, 1900 to be one. 1 is added every 24 hours. When ExcelInput component and ExcelSlimIntput component read those values, the values equal if the values are read as DateTime or Double. But the values become different if the values are read as String. Against the flotage decimal value is converted to String in ExcelInput component, the ExcelSlimIntput converts DateTime value of Flow Service to String value. This depnds on the behaivior of the library that are used in each component.
It is recommended to use DateTime value for reading values of this kind of cells in this component.

Coloer name string

If it's xls, the color name string is used to input/output from the Excel file format in this component. If it's xlsx, RGB16 decimal value will be used. However, if you create an xlsx file by passing a color name when create it by ExcelPOIOutput, it's possible that the colour name will be used in the xlsx, too.
The color name string is based on the Excel's standurd color palette. The palette defined by the user isn't supported.
The upper-case letter and the lower-case letter aren't distinguished when they are read. All of them will be the lower-case letters when they are read.

Name	Color sample	RGB16 decimal value
aqua		33cccc
black		000000
blue		0000ff
blue gray		666699
bright green		00ff00
brown		993300
coral		ff8080
dark blue		000080
dark green		003300
dark purple		660066
dark red		800000
dark teal		003366
dark yellow		808000
gold		ffcc00
gray 25%		c0c0c0
gray 40%		969696
gray 50%		808080
gray 80%		333333
green		008000
ice blue		ccccff
indigo		333399
ivory		ffffcc
lavender		cc99ff
light blue		3366ff
light green		ccffcc
light orange		ff9900
light turquoise		ccffff
light yellow		ffff99
lime		99cc00
ocean blue		0066cc
olive green		333300
orange		ff6600
pale blue		99ccff
periwinkle		9999ff
pink		ff00ff
plum		993366
red		ff0000
rose		ff99cc
sea green		339966
sky blue		00ccff
tan		ffcc99
teal		008080
teal		008080
turquoise		00ffff
violet		800080
white		ffffff
yellow		ffff00

How to use ExcelBuilder

Development of flows that uses Excel

The following procedures are used to make flow to which the Excel file is generated by using the ExcelPOIInput component.

In the ExcelPOIInput component, the data of the cell that responds the read Excel worksheet is acquired and the data of the record area is acquired by the stream pane of the component in "Cell" tab of the component property. These data can be dynamically treated by arranging the Mapper component just behind the ExcelPOIInput component.

1. Puts the ExcelPOIInput component in the flow, and passes the Excel file as an input stream of Binary format.
2. Launches the ExcelBuilder from ExcelPOIInput component. In the ExcelBuilder, specifies cells or record area of the template Excel files.
3. Selects the data type of the properties in the Cell tab.
4. Selects the data type of the fields in the stream pane.

Launching ExcelBuilder

ExcelBuilder is an add-on software running in the Excel 2000 to Excel 2007. One of these versions of Microsoft Excel has to installed. ExcelBuilder is run as a macro, security level of macro must be lower than Medium in the Excel.

In order to launch ExcelBuilder is:

1. Click the "ExcelBuilder" from right click menu of ExcelPOIInput. Or, double-click the ExcelPOIInput component.
2. Microsoft Excel starts. You can find the add-on icon of "ExcelBuilder" in the toolbar.
3. Open the template Excel file for handling the data.
4. Click the icon of "ExcelBuilder", then ExcelBuilder dialog is displayed.

Cell Definition

In the Cell tab, you can define the cell to get the data from. These data is set to the properties defined in the Cell tab.

New

Adds the cell definition. When clicking the "New" button, the dialog for specifying the cell is displayed. Click the cell in the worksheet or input the reference of the cell. Then click "OK".

Next, the dialog for specifying the cell name is displayed. Input the cell name and click "OK" This name is used for property name of Cell property. Repeat these operations for the number of the cell you want to get the value from. Added cell definitions are displayed in Cell tab as a list.

Edit

Changes the cell definition. When clicking the "Edit" button, the dialog for specifying the cell is displayed. If you want to change it, input a new value. Click "OK" if you don't want to change.

Next, the dialog for specifying the cell name is displayed. Input a new value if you want to change, and click "OK". If you click "Cancel" during these operations, cell definition is not changed.

Delete

Delets the cell definition. Select the cell definition. Then click "Delete" button, the definition is deleted.

Format information

You can specify which format information you can get from the inputs. Select the cell definition, and turn on the checkbox to want to get from "Input Format".
Checked information is able to get from Cell property by the field name as following format.

  Background color     ： [Cell Name]_Background
  Color                ： [Cell Name]_FontColor
  Font                 ： [Cell Name]_FontName
  Size                 ： [Cell Name]_FontSize
  Style                ： [Cell Name]_FontStyle
  Strikethrough        ： [Cell Name]_StruckOut
  Underline            ： [Cell Name]_UnderlineStyle
  Format               ： [Cell Name]_Format
  Formula              ： [Cell Name]_Formula
  Comment              ： [Cell Name]_Comment
  Hyperlink            ： [Cell Name]_Hyperlink

Record Definition

In the Record tab, you can define the cell area to get the data from by records. These data is set to the properties defined in the Record tab.
Multiple record definitions can be defined. When multiple record definitions are defined, the output connector as for the number of record definitions are created. Field definitions are also added in the stream pane.
Record definition with multiple rows in each record can be defined. In that case, please set for the number of lines of the entire record area to become integral multiples of the number of lines a record.

New

Adds the record definition. When clicking the "New" button, the dialog for specifying the area is displayed. Select the cell area in the worksheet or input the reference of the area. Then click "OK".

Next, the dialog for specifying the record name is displayed. Input the record name and click "OK".

Next, the dialog for specifying the rumber of rows is displayed. Input the number of rows, then click "OK". The record definition is added.

Edit

Changes the record definition. When clicking the "Edit" button, the dialog for specifying the area is displayed. If you want to change it, input a new value. Click "OK" if you don't want to change.

Next, the dialog for specifying the record name is displayed. Input a new value if you want to change, and click "OK". If you click "Cancel" during these operations, record definition is not changed.

Next, the dialog for specifying the rumber of rows is displayed. Input a new value if you want to change, and click "OK".

Delete

Delets the record definition. Select the record definition. Then click "Delete" button, the definition is deleted.

Field Definitions

Defines a field name in a record. When selects record definition, "column" and "row" are displayed in the Field Defintions. "row" is only displayed when the number of rows in the record is more than 1. The field names are used for field name of the output stream. By default, the field name is combionation of "column" and "row".

Edit

When you changes the field name, clickthe "Edit" button. The dialog for specifying the field name is displayed. Input a new value, then click "OK". If you click "Cancel", the field name is not changed.
Next, the dialog with the message "Specify record-terminating condition?". If you want to specify the record^terminating condition, click "Yes", then input the condition.

Get field names

Gets the field names from worksheet. When clicking "Get field names" button, the dialog for specifying the area is displayed. Specify the area which includes the field names. The area have to have same number of columns and rows of each record. By default, the upper area of record area is selected.
If there are empty cells in selected area, the field name is not changed. The line feed is removed from the string.

Record-terminating condition

You can specify the record-terminating condition in the ExcelBuilder. In the ExcelBuilder, record area to get the data from is defined. But, in runtime, all of the area may not filled by record data. For this case, by writing some data like "EOR" indicating the end of records, you can get proper number of records. If empty string is specified, the record is recognized as the end of records when the data of this column is empty.

Format information

You can specify which format information you can get from the inputs. Select the field, and turn on the checkbox to want to get from "Input Format".
Checked information is able to get from the field of output stream by the field name as following format.

  Background color     ： [Field Name]_Background
  Color                ： [Field Name]_FontColor
  Font                 ： [Field Name]_FontName
  Size                 ： [Field Name]_FontSize
  Style                ： [Field Name]_FontStyle
  Strikethrough        ： [Field Name]_StruckOut
  Underline            ： [Field Name]_UnderlineStyle
  Format               ： [Field Name]_Format
  Formula              ： [Field Name]_Formula
  Comment              ： [Field Name]_Comment
  Hyperlink            ： [Field Name]_Hyperlink

Format Information

When you check the format information to get in the cell definition or field definition in the record definition, the values you can get are as follows.

• Background color - Color name
• Color - Color name
• Font - Font name
• Size - Integer value of points
• Style - Normal:0, Bold:1, Italic:2, Bold+Italic:3
• Strikethrough - Yes:True、No:False
• Underline - No:0, Single:1, Double:2, Single Accounting:33, Double Accounting:34
• Format - Pattern string
• Formula - Formula. "=" is removed.
• Comment - Comments
• Hyperlink - Hyperlink information in the cell. URL is only able to read.

Exits ExcelBuilder

When finishes the configuration, click the "register" button to register the infromation. Then, exist the ExcelBuilder.

Register

Registers the configurations. When clicking "Register"button, the information are stored and the ExcelBuilder exits.

Close

Exits ExcelBuilder. When clicking "Close", the information are not stored and the ExcelBuilder exits. After the ExcelBuilder exits and exits the Excel, returns to Flow Designer.

Inspector of Excel components

After cell and record definitions are defined by ExcelBuilder, the information are set to Sheet tab, Cell tab, and stream pane of inspector window of Excel component. These values are able to be changed by Mapper component in runtime.