TRANSFORMER
Excel source
An Excel source uses an Excel file (all versions) as source for a Transformer project. The file to be used must first be imported to Resources. See Resources for information on how to import a file.
Creating an Excel source
Right click in the Transformer design panel and choose Source -> Excel source.
It is possible to 'Drag and Drop' an .xls file from the Resources panel to the Design panel and use it as an Excel source.
Most File resources can be previewed by clicking the Refresh button (
) in the Preview panel. For Resources of types that require a special program to be opened (e.g. .xls, .doc, etc.) click the View button in the Preview panel.
Double-click the Excel source icon to configure it.
Properties
General
-
Name - the name "Excel source" is generated automatically but it may be changed
-
Description - an optional text describing the source
Configuration
-
Allow web upload - If checked the user starting the project via the web page can upload a replacement file to be used for the run. If unchecked the file selected in source object will be used.
-
Rich text as HTML - applies only if the source data is formatted text data from SQL, MS Excel, or a BMP table. Select for Corporater BMP to read the data as HTML and retain its formatting after import.
When users view it on Web, the data displays in the same formatting as in the data source.
Corporater BMP can retain the following text formatting types:-
Styles – bold, italic, underline
-
Colors – character and background color
-
Lists – bulleted and numbered lists
-
Paragraphs – line breaks
For optimum results, Corporater recommends that:-
The source data doesn’t already include HTML tags.
-
The data target for the Excel source is an object target that’s mapped to a rich text property.
If you select Rich text as HTML and the data target is a plain text property, when you preview the data in the Preview panel or when you view it on Web,
Corporater BMP displays it with HTML tags. -
Audit
-
Modified by - the name of the last user to make changes to the object.
-
Last modified - the date and time when the object was last changed.
-
Created by - the name of the user who created the object.
-
Created - the date and time when the object was created.
To select a file, go to the Custom tab.
Custom
-
File name - specify the Excel file to use as source by clicking Choose, clicking the file you want in Select resource, and then clicking OK.
-
Sheet - choose the desired sheet from the drop-down list. By default the first sheet is used.
A number of rows from the source file are displayed below Data Range, so you can check that the correct file has been selected.
Next you need to describe the layout of the data in the file. Mark Auto range, and Transformer will select the top row as Header and the other rows as Data.
Or you can remove the mark in Auto range and specify Header Range and Data Range manually. Click and drag the mouse to mark the cells you want to use as headings and click the Set button to the right of Header range. Repeat the process for Data range. You can also enter cell IDs directly in the range fields.
-
Header Range - the cells to use as headings
A Header Range must be defined, i.e. it may not be empty
-
Data Range - the cells containing the data to be used
Now check the displayed rows to see that the range positions specified are correct.
Click OK to save the configuration or Cancel to reject.
|
1. Only formulas that are supported by Aspose are supported. You can see a list of supported formulas here: http://www.aspose.com/docs/display/cellsjava/Supported+Formula+Functions 2. No links to other sheets. 3. The first row in the sheet will be treated as a Header row, if Auto range is used. 4. No empty cells allowed in Header row. 5. No empty rows allowed. 6. Empty cells will be given the following default values. If you want other default values you must adjust them in the Excel source file.
7. If the first cell in a column is empty, Transformer will treat a number column it as a string and all empty cells will be blank instead of NaN. |
There are some restriction to the Excel files Transformer can use as source.