ExcelInput - Convert Excel Data to Record(s)

The Excel(Input) component reads the specified ExcelBuilder defined content from the input Excel formatted binary data, and writes the data to the output stream's records.

• Gets the values and fromat information of specified cells
• Gets the values and format information in the area specified by range

To use this component, it is necessary to open the ExcelBuilder, specify the Excel template file to read, define the work book cells from which the data is to be read and the ASTERIA fields to which this data is to be written.

For details, please refer to following topic "How to use ExcelBuilder".

Stream Information

Input	Format	Binary
	Number of Inputs	1
	Description	The Excel file binary image.
Output	Format	Record
	Description	Excel data in the area defined by the ExcelBuilder. If multiple record areas are defined, the output stream is displayed only as for the number, and the name of the record becomes the name of each stream. Fields are defined in the ExcelBuilder, not FlowBuilder.

Component Properties

Name	Data Type	Mapping	Description
IgnoreBlankRow	boolean	In & Out	Specifies how to treat the empty records. true [true] - Empty records are ignored and skipped. false [false] - Empty records are read, and the respective fields are set as empty.
RoundUpFormula	boolean	-	Specifies if rounds to the result stored in the workbook or not. When the result is a numerical value, Excel stores a value in the workbook as a floating point binary value of 64 bits. (Ex.) 350 * 0.7 = 244.99999999999997 For this case, to display Excel only up to 15 significant digit digits, it is displayed on the cell as 245. If RoundUpFormula is set to "true", this component returns a value as same as that is displayed in the cell(with maximum digits). If false, this component returns the value stored in the Excel internally. true [true] - Rounds as 15 effective digits' number. false [false] - Don't round
MergedCellValue	choice	-	Specifies how to treat the merged cell. TopLeftCell [parent] - Returns left-top value of the merged cell. OriginalCell [original] - Returns the original value. Except for left-top cell, it returns empty.
SheetNotExist	choice	-	Specifies how to process when the worksheet is missing. Continue - Continues to process. The record in the missing worksheet becomes empty record. null is returned when Simple cell Exception - Raises error
LoopProcess	loopProcess	-	Specifies if it outputs records as an individual stream or as a single stream, when if the ExcelBuilder defined record area results in multiple matches. true [true] - Outputs each record as an indivisual stream false [false] - Outputs all records as a single stream
Sheet	category	In & Out	Sheet names are listed here when the cells or record areas are defined in the ExcelBuilder. If sets the sheet name to the InputSheetName property in runtime, this components gets the values from specified worksheets. This property can be changed by Mapper, you can change the sheet to be read in runtime.
Cell	category	In	The cell definition defined in the ExcelBuilder. This components read the workbook and set corresponding data to this property. This property becomes a stream variable automatically. Thus, it can be used as a stream variable by following Mapper. For details of Stream variables, please refer to Stream Variables in Flow Service manual. Fields are defined in the ExcelBuilder, not FlowBuilder.

Loop

If LoopProcess=true and the ExcelBuilder defined record area results in multiple matches, each record is output as an individual stream, thus creating a 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	-	Cannot read the ExcelBuilder defined cells.
			-	Error converting data to the flow field data type.
			-	Opening an Excel file of an unsupported format. Excel files formatted prior to Excel 95, such as Excel95 or older versions, must be saved as an Excel97 formatted file to be used by this component.

Limitations

Readable files

This component can read Excel formatted files since Excel97. However, this component does not support the reading of Excel files formatted prior to Excel 97, such as Excel95 or older. This component does not support .xlsx file format.
The Excel97-2003 files formatted by Microsoft Access are not supported.

The Excel file of small-block format is not supported.
The small-block format is for the very special format. When there is a few data and macro, the Excel may store by this form to eliminate the file size.
When this component opens this kind of file, the message

Cannot open workbook --- 'small-block format' is not supported.  Please add cell data.

is reported.
For this case, you may avoid by adding some data in this worksheet. You may avoid this error when you add 50 cells of string or 250 to 300 cells of number, or some image object, etc.

This component supports the reading of files up to 10MB in size. A single cell can contain up to 8K of data (4096 full length characters).

Category	Maximum Cell Limit
Number	600,000 cells
Text	400,000 cells of 4 bytes (2 full length characters)
	100,000 cells of 80 bytes (40 full length characters)
	1200 cells of 8K (4096 full length characters)

The amount of data that can be input decreases as the number of cells increases. This occurs because the Excel format stores an additional 10 to 20 bytes of cell formatting data for each cell.

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.

Color Name

The color name character string used to input and output from format information on the Excel file by this component is as follows.
This is the one based on the standard color palette of Excel. The palette of the user definition is not supported.
When writing, the name is case-sensitive. When reading, the name is acquired as a small letter.

Name	Sample	RGB 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 ExcelInput component.

In the ExcelInput 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 ExcelInput component.

1. Puts the ExcelInput component in the flow, and passes the Excel file as an input stream of Binary format.
2. Launches the ExcelBuilder from ExcelInput 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 ExcelInput. Or, double-click the ExcelInput 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       (*No support)
  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       (*No support)
  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.

(*Comment isn't supported.)

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.