Excel Import Builder

 

In this topic ...

Quick Tips

Specifying Inputs

Related Topics ...

How to Use the Attribute Sequencer Builder

** This feature might not be available with this version of the Factory. Click here to find out how to purchase this feature.**

This Builder allows you to import the content of a Microsoft Excel workbook. This Builder will import most .XLS formatted files generated by Microsoft Excel 97 through 2003. Once imported into the model, Excel file content is transformed into a schema-typed XML document and stored in a variable for easy manipulation and display using the various page-oriented Builders such as Data Page.

An Excel workbook must be added to your project before this Builder can import it. The project directory in which the Excel file is placed does not matter to the Builder, however, the file should be placed under the project s web-inf subdirectory if the file contains data that you do not want exposed in an uncontrolled way.

For example, if the Excel file is placed in a servable content directory, anyone who knows the name and path of the file will be able to download it from the application server with a browser.

 

Quick Tips

  • In order to populate the variable (<buildername>_Content) with Excel data, first we need to call the <buildername>.getContent method. Note that the variable is created automatically by the builder.

  • If the Excel content we need to import is variable in shape, use "Automatic" content selection. The Builder will find and select all of the cells in the smallest rectangular range that includes all of the defined content from the chosen workbook sheet. In this way your Excel workbook can change and those changes will be automatically picked up when the Builder is regenerated.

  • Header Rows - If the Excel content we need to import contains a header row, then remember to check the "Has Header Row" input and include this row in the selected range of cells.  This will allow the Builder to generate a more meaningful XML schema describing the imported content.

 

Builder Limitations

  • Excel Versions Supported - The imported Excel workbook must be in the .XLS format and have been created by Microsoft Excel 97 through 2003.

  • Rich Text - Workbooks that contain rich text cells (those that have multiple fonts and styles) are not supported. Any attempt to import a workbook that has rich text cells will throw a runtime exception.

  • Boolean Formulas - Cells that contain Boolean formulas are not imported correctly.  They will appear as the XML as "NaN".

 

Specifying Inputs

This Builder takes the inputs described in the table below. For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor."

 

Input name Description
Name Enter a name for this builder call. The designer tool displays this name in the builder call list.
File to Import Use the picker to locate an Excel Workbook file that has been added to the project.  The file must be in the .XLS format and have been created by Microsoft Excel 97 through 2003.

Workbooks that contain rich text cells (those that have multiple fonts and styles) are not supported. Any attempt to import a workbook that has rich text cells will throw a runtime exception.

Generate Schema We can select the creation of schema in a variety of ways. We can choose:

  • On Designer Regen - To create the schema every time the model is regenerated within the Designer

  • On Every Regen - To create the schema every time the model is generated within the Designer and the Application Server

  • Only When Builder Changes Are Detected - To create the schema and preserve it (unchanged) on subsequent model generations. If a subsequent change is made to any of this builder input, a new schema will be generated.
Content selection Method We can select the Workbook content to import in a variety of ways. We can choose:

  • Automatic (let Builder find content) - To let the Builder determine the content to import. The Builder will select all of the cells in the smallest rectangular range that includes all of the defined content from the chosen sheet.  Note: this content may include cells that contain formatting but otherwise are blank.

  • Bounded (specify a range of cells) - Requires you to manually enter the range of cells that contains the content you want to import. The range is given by the "Upper Left Cell" and "Lower Right Cell" inputs. For example: A1-H99.

  • By Range (use a named range from the workbook) - To import the content of a named range of spreadsheet cells. If no named ranges are defined for the workbook, this input cannot be used to select Excel content.
Sheet Use this input to select the workbook sheet that contains the content you want to import. All of the sheets defined in the workbook are available for selection.

Example: Sheet 2

Range Available when By Range is the Content Selection Method above.

Select the named Workbook range that represents the data you want to import.

Upper Left Cell Available when Bounded is the Content Selection Method above.

Enter the coordinates of the cell that represents the upper left boundary of the rectangular cell range you wish to import. This input can be left blank to have the Builder automatically find the upper left cell.

Example: B15

Lower Right Cell Available when Bounded is the Content Selection Method above.

Enter the coordinates of the cell that represents the lower right boundary of the rectangular cell range you wish to import. This input can be left blank to have the Builder automatically find the lower right cell.

Example: G35

Has Header Row This input allows the Builder to accommodate a content header row.

Enable to make the Builder use the first row in the selected cell range as the header row. Such a row might contain column labels or other text notation that identifies the data in the columns.

The header row is used to generate the XML schema element names that describe the data that is imported from the workbook.

Preserve Empty  Rows This input allows the Builder  to preserve empty rows when the Excel content is imported into a model as XML.

 By default this builder strips  empty rows to provide a compact XML representation of the imported Excel content

 Enable this input if you want to preserve empty Excel rows for custom formatting of the Excel content.

Imported Excel content with empty rows will appear differently on a page depending upon the HTML formatting applied to the page location where the content is displayed.  In some cases empty rows might have a negligible height and be difficult to see.

Preserve Cell Formatting Enabling this checkbox will cause the builder to keep the cell formatting when content is fetched from Excel.

Example: In Excel, when you apply formats such as "Currency", a cell value such as "12" will appear as "$12". The "Preserve Cell Formatting" input allows you to decide whether to display the cell value "12" as simply "12" or "$12" in the imported XML.

This input has no effect on cell color, font, or formula settings.

 

Objects this Builder Creates

A variety of WebApp artifacts are created by the Excel Import builder. These artifacts include the following LJO and variables:

Linked Java Objects

  • <buildername> - LJO that includes a <buildername>.getContent method, causes the selected Excel content to be imported, transformed into XML, and stored into <buildername>_Content variable created by the builder.

Variables

  • <buildername>_Schema - XSD schema representing the Excel content that will be imported. The element names are derived from the header row values (if a header row was configured) and column names.

  • <buildername>_Content - This schema-typed variable will contain the imported Excel content after the "invoke" method of the builder s LJO is called.