Excel Export Builder

Excel Export Builder

In this topic ...

How do I...
Builder Limitations
Specifying Inputs
XML Element Handling
Objects this Builder Creates

This builder allows you to export table-like XML data as a Microsoft Excel file. The XML data can be obtained from a page location or an indirect reference in your model. If the data is exported from a page location, the builder will make its best attempt to export the data in a way that preserves the look and feel of the data displayed at the specified page location.
When XML is exported one of two events will happen: the user s browser will automatically launch Excel and display the exported spreadsheet, or the user s browser will display a "Save As" dialog and given them an option to save the exported file. Which event the user sees depends upon how the user s browser is configured and whether or not Excel is installed on their computer.
Notes:

Make sure this builder is not applied to the same tag on a page as the Print Page Contents builder. Both of these builders write the ID attribute to the tag, so that whichever builder is placed last will overwrite the ID attribute provided by the former builder, making the former builder inoperable. If both builders are needed for the same area of the page, wrap one tag by another tag and apply the builders to the different tags.

When the page location specified on the Excel Export builder is placed in an HTML form, the form generated by Excel Export builder will be nested within the existing form, which causes the export function to not work. To solve this problem, make sure the page location specified by the Excel Export builder is not within a form.

Placing this builder on a page that is accessed through SSL or from an iSeries V5R3 server will cause runtime errors. To fix this problem, set the following property to true in the WEB-INF/config/override.properties file (you might need to create this file).
com.bowstreet.ExcelExport.caching.headers.useCustomCachingHeaders=true
We can also use this setting along with the following properties to set additional HTTP headers.
com.bowstreet.ExcelExport.caching.headers.Cache-Control=no translate com.bowstreet.ExcelExport.caching.headers.Pragma=no translate

How do I...

Format Data for export into an Excel Workbook?

This builder requires that exported content be structured as an HTML table so that it can accurately transform rows and columns in the table into corresponding rows and columns in the exported Excel spreadsheet. If the XML data to be exported is obtained from a page location, then it s likely that some table-oriented builder such as Data Page was used to create the page content. In these situations the XML content is already in the HTML table format and can be exported as-is.
When using an indirect reference as the source of XML data we can either ensure the XML is already formatted as an HTML table or we can use an advanced feature of this Builder to recognize table-like structures in the XML and handle them appropriately. See the description of the "Table Element Names" input for additional detail.
Export and View data when Excel is not installed?

When XML is exported, either the user s browser will automatically launch Excel and display the exported spreadsheet or the user s browser will display a "Save As" dialog. If the user has Excel installed, but their browser does not allow direct launching of Excel files, then they can save the exported file with a ".xls" extension and then launch the file manually. When Excel is not installed a user can save the exported file and view it with Excel on a different computer.

Builder Limitations

Excel Installation Required For Viewing - The Builder works in conjunction with Excel for viewing exported data. A copy of Excel must be installed on those computers where a user wishes to view the exported content as an Excel spreadsheet.
Formulas and Charts Since the source data used for export does not contain any Excel metadata (like formulas, charting, etc.) the exported Excel spreadsheet will contain only data and formatting that can be determined by the Builder from the source XML (page location or indirect reference).

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 input group, 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.
Export File Name Specify the file name to be used for the exported Excel content.
Data Export Select a source of the data to export. We can choose:

Export from Page Element - To export the contents of a named element on a page defined in your model. In most cases this page element will contain data created by another Builder (Data Page). For the export to be performed correctly the content must be structured as an HTML table so that the Builder can transform rows and columns in the table into corresponding rows and columns in the exported Excel spreadsheet.
Export from Indirect Reference - To export the content of a schema-typed source such as a variable in the model. As with page element sources, the content of the indirect reference must be structured as an HTML table. You should also review the inputs available in the "XML Element Handling" group for a way to have the Builder automatically convert arbitrary table-like XML into HTML tables prior to export. (See Note below.)

Before we can export the content of an indirect reference call the getExportData method on the LJO generated by the builder. This LJO will have the same name as the builder. Calling this method causes the content to be generated, if necessary, and prepared for export. If you do not call this method at least once prior to attempting an export, then a "Content variable is null" error message will be displayed in the browser signaling that no content was found when the export was initiated.
Source Page This input is available when "Export from Page Element" is selected for the Data Export input.
Use this input to identify the page in your model that contains HTML table data to be exported by the Builder.
Source Element This input is available when "Export from Page Element" is selected for the Data Export input.
Use this input to identify the element on the selected page that will contain the HTML table data to be exported as an Excel file
Source Reference This input is available when "Export from Indirect Reference" is selected for the Data Export input.
Use this input to specify the indirect reference that will provide the HTML table data to be exported as an Excel file.
Add Export Button/Link Use this input to add a page control that, when clicked, causes your data to be exported as an Excel file. We can choose:

Image Button - To use an image as an export button
Button - To add an export button to the page
Link - To add an export link to the page.

Export Button/Link Location Use the Page Location input to specify the page on which the export button/link will be placed.
If "Export from Page Element" is selected for the Data Export input, then the page location should be on the same page where the data to be exported is displayed
See "Locating Control Builders on Pages" for detailed documentation about the Page Location input and page location syntax.
Image File This input is available when Add Export Button/Link is "Image Button."
Specify the image file which would represent/initiate the Excel Export operation.
Default Image File: /factory/images/tools/excel.gif
Alt Image Text This input is available when Add Export Button/Link is "Image Button."
This setting corresponds to the HTML image tag alt= and title= attribute. Enter the text you want to appear when the mouse pointer is placed over the image or when the image cannot be displayed.
Export Button/Link label This input is available when Add Export Button/Link is "Button" or "Link."
Enter a label to that will be displayed as the text of the export button or link.
Example: "Export Sales Data Worksheet"
XML Element Handling
Table Element Names This input is available when Data Export is "Export From Indirect Reference."
Enter a comma-delimited list of XML element names that should be treated as table-like structures.
Before performing an export the Builder will look for the named elements and convert them into HTML tables.
Click here for more about XML element handling.

XML Element Handling

When export data is taken from an indirect reference, that reference must provide XML that is structured as an HTML table. This table structure allows the Builder to transform rows and columns in the XML into corresponding rows and columns in the exported Excel spreadsheet. A tablle structure might not be achievable for all indirect references. The "Table Element Names" input is provided so that table-like elements in the XML can be named and handled correctly by the Builder.
You use this input to provide a comma-delimited list of XML element names that should be treated as table-like structures. Before performing an export the Builder will look for the named elements and convert them into HTML tables. Note that the Builder will look only at the root element of the XML provided by the indirect reference. if the root element does not have a recognized element name, then the XML is exported as-is.
In addition, the root XML element must have a table-like structure. That means it must contain "row" elements that aggregate "column" elements. If this structure is not present, the conversion and export will produce unpredictable results.
For convenience, by default the input contains several names. These names identify to the root XML elements used by various Data Integration Builders. Therefore, if a Data Integration Builder provides the indirect reference specified as the "Source Reference" input, then the data obtained from this reference should be exportable without needing to specify additional names for the "Table Element Names" input.

Objects this Builder Creates

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

Linked Java Objects

<builderName>_SharedVariable - Shared variable LJO that coordinates the generation of the actual XML data to be exported as an Excel spreadsheet. The Builder automatically calls the appropriate methods on this LJO when an export is requested. In general you will never need to invoke any of the methods on this Builder
<builderName> - Helper LJO for the builder. Used to prepare page or indirect reference content for export. Note: the method "getExportData" must be invoked by your model when an indirect reference is used as the source of exportable content. See the "Data Export" builder input description for additional detail.

Variables

Several variables are created, but they are hidden by default and are not intended for direct manipulation

Input name	Description
Name	Enter a name for this builder call. The designer tool displays this name in the builder call list.
Export File Name	Specify the file name to be used for the exported Excel content.
Data Export	Select a source of the data to export. We can choose: Export from Page Element - To export the contents of a named element on a page defined in your model. In most cases this page element will contain data created by another Builder (Data Page). For the export to be performed correctly the content must be structured as an HTML table so that the Builder can transform rows and columns in the table into corresponding rows and columns in the exported Excel spreadsheet. Export from Indirect Reference - To export the content of a schema-typed source such as a variable in the model. As with page element sources, the content of the indirect reference must be structured as an HTML table. You should also review the inputs available in the "XML Element Handling" group for a way to have the Builder automatically convert arbitrary table-like XML into HTML tables prior to export. (See Note below.) Before we can export the content of an indirect reference call the getExportData method on the LJO generated by the builder. This LJO will have the same name as the builder. Calling this method causes the content to be generated, if necessary, and prepared for export. If you do not call this method at least once prior to attempting an export, then a "Content variable is null" error message will be displayed in the browser signaling that no content was found when the export was initiated.
Source Page	This input is available when "Export from Page Element" is selected for the Data Export input. Use this input to identify the page in your model that contains HTML table data to be exported by the Builder.
Source Element	This input is available when "Export from Page Element" is selected for the Data Export input. Use this input to identify the element on the selected page that will contain the HTML table data to be exported as an Excel file
Source Reference	This input is available when "Export from Indirect Reference" is selected for the Data Export input. Use this input to specify the indirect reference that will provide the HTML table data to be exported as an Excel file.
Add Export Button/Link	Use this input to add a page control that, when clicked, causes your data to be exported as an Excel file. We can choose: Image Button - To use an image as an export button Button - To add an export button to the page Link - To add an export link to the page.
Export Button/Link Location	Use the Page Location input to specify the page on which the export button/link will be placed. If "Export from Page Element" is selected for the Data Export input, then the page location should be on the same page where the data to be exported is displayed See "Locating Control Builders on Pages" for detailed documentation about the Page Location input and page location syntax.
Image File	This input is available when Add Export Button/Link is "Image Button." Specify the image file which would represent/initiate the Excel Export operation. Default Image File: /factory/images/tools/excel.gif
Alt Image Text	This input is available when Add Export Button/Link is "Image Button." This setting corresponds to the HTML image tag alt= and title= attribute. Enter the text you want to appear when the mouse pointer is placed over the image or when the image cannot be displayed.
Export Button/Link label	This input is available when Add Export Button/Link is "Button" or "Link." Enter a label to that will be displayed as the text of the export button or link. Example: "Export Sales Data Worksheet"
XML Element Handling
Table Element Names	This input is available when Data Export is "Export From Indirect Reference." Enter a comma-delimited list of XML element names that should be treated as table-like structures. Before performing an export the Builder will look for the named elements and convert them into HTML tables. Click here for more about XML element handling.