ExcelSlimInput

ExcelSlimInput - Convert Excel Data to Record(s) (light version)

The ExcelSlimInput 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

Compared to ExcelInput component, this component runs faster but readable workbooks are limited.
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
Output	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

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 Excel95. 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.

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

Category Maximum Cell Limit

Number 1,800,000 cells

Text 1,200,000 cells of 4 bytes (2 full length characters)

300,000 cells of 80 bytes (40 full length characters)

3600 cells cells of 8K (4096 full length characters)

Category	Maximum Cell Limit
Number	1,800,000 cells
Text	1,200,000 cells of 4 bytes (2 full length characters)
300,000 cells of 80 bytes (40 full length characters)
3600 cells 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

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 ExcelSlimInput component.

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

Puts the ExcelSlimInput component in the flow, and passes the Excel file as an input stream of Binary format.
Launches the ExcelBuilder from ExcelSlimInput component. In the ExcelBuilder, specifies cells or record area of the template Excel files.
Selects the data type of the properties in the Cell tab.
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:

Click the "ExcelBuilder" from right click menu of ExcelSlimInput. Or, double-click the ExcelSlimInput component.
Microsoft Excel starts. You can find the add-on icon of "ExcelBuilder" in the toolbar.
Open the template Excel file for handling the data.
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.