This article applies to BackOffice Associates® Solutions versions 6.3 and later.
Overview
Excel Integration allows users to import records directly from a worksheet in an Excel Workbook file by one of the following methods:
- Page-level Import — A user can manually import the Excel file via the Page Gear menu > Excel Integration (if the feature is enabled on the page)
- Public WebApp Event — The Public WebApp event, added to the page as a business rule, can import the Excel file without user involvement. This is useful if an Excel file should be imported every day at a certain time from a service page.
Intended Audience
The audience of this article is developers who are implementing Excel Integration. This manual assumes advanced knowledge of DSP navigation and functionality.
Additional Information
Refer to the Data Insiders article Excel Integration for general information about the feature.
The online product help contains additional information about using Excel Integration.
The System Administration help contains additional information about configuring Excel Integration.
Support
Custom application pages that are deployed at customer sites by page designers are permitted; however, standard Product Support does not cover custom pages. Consult with the page designer for customization troubleshooting. If there is an issue in the Excel Integration feature itself, report it to Product Support using the standard Support notification process.
Excel Integration for Page-level Import
Enabling Excel Integration via this method allows a user to access the Excel Integration option in the page gear menu.
When the user selects this option, the Excel Integration panel displays.
Using the Excel Integration panel, the user can download the configured Excel template spreadsheet file or import data into the DSP® from an existing Excel spreadsheet in a format that matches the template.
Enable Excel Integration
To enable Excel Integration for page-level import:
- On the page that should use Excel Integration, click the Design Page icon in the Site toolbar and select Design Page.
- Click the Vertical View icon.
- Under the Advanced Properties tab, check the Enable Excel Integration check box.
- Refresh the original page, and select Excel Integration from the page gear menu to access the Excel Integration panel.
Download the Template
By clicking the Download Template tab on the Excel Integration panel, a user can download the configured Excel template file. This file contains one column for each page column that had been added to the Horizontal View of the page.
NOTE: To omit columns from the Excel template, on the Page Columns page, add a page column entry for that column. Set the View Type to Excel and set the Control Status field to Hide.
To download the template, enter the desired workbook filename (without extension) and worksheet name. Click the Download button.
Import the Spreadsheet
A user can import data from an Excel workbook file that adheres to the configuration of the page’s Excel template.
To import data:
- Click the Import Data tab on the Excel Integration panel.
- Click Click here to browse for your Excel file’; a file open dialog displays.
- Choose the Excel file (.XLSX extension) to import.
NOTE: Files with the extension .XLS are not supported. These documents must be converted to an .XLSX file extension before uploading.
When the upload finishes processing, import options display allowing the user to specify where the data to import is located.
- Verify that the worksheet you selected to import displays in the Choose Worksheet list box.
- Verify that 4 displays in the Data Starts on Row field.
NOTE: This value specifies the row number of the first record to import. In the downloaded template, the first three rows are header rows. Update this value as needed if data in your spreadsheet starts on another row. - Verify that 1 displays in the Column Headers on Row field.
NOTE: This value specifies that the first row in the downloaded spreadsheet contains column names. Update this value if needed. - Click the Import Records button to begin the upload.
NOTE: The panel will provide a progress bar and estimation of time remaining.
Upon completion, the results of the import display, including information for records inserted, skipped or failed. Failures are grouped into error categories. Click a row number to view error information.
Enable Excel Integration via a Public WebApp Event
The Excel Public WebApp event can be used to import data from an Excel XLSX format workbook file. Records will be inserted based on the page configuration for the PageID specified. As opposed to the page-level import method where the user imports and sees results immediately, the following results are stored in the ‘ExcelImportResult’ table in the CranSoft database:
- FailedPreValidation: bit, yes/no value for whether the import failed during pre-validation checks.
- ErrorMessage: Error message received upon import failure.
- FailedRowIDs: Set of IDs for records that did not import correctly.
- SkippedRowIDs: Set of IDs for records that were skipped during import.
- RecordsInserted: Number of records inserted.
- ImportTimeSeconds: Duration of import in seconds.
- InvokedBy: Name of user that triggered import process.
- InvokedOn: Timestamp of when import was started.
To create the Public WebApp Event:
- Navigate to the page that will use the event.
- Click Design Page > Design.
- Click the Events icon for a page.
- Select the event in which the Public WebApp Event will be added to.
NOTE: If it doesn’t exist, create one. - Click the Business Rules icon.
- Click Add. Add a new business rule where the PROCEDURE TYPE is WebAppEvent.
- Click Save.
- Select System Administration: Excel Interoperability from the Event Page ID list box.
- Select Import from the Event Name list box.
- Click Save.
- Create a parameter view to set the columns for import.
Public WebApp Event – Parameters
The fields below are required or optional within the parameter view assigned during configuration of the Excel Integration Public WebApp Event.
Required Fields
- PageID: The target page where the specified Excel file will be imported into.
- FileLocation: The location of the Excel file.
Optional Fields
- WorksheetName: The name of the worksheet to import from. If blank, the page’s title is used.
- RowDataStartNumber: The row (1 based indexing) where the row data begins on. If blank, the row the Excel template download would start on is used.
- RowColumnNameNumber: The row (1 based indexing) where column names are located. If blank, a default of row 1 is used.
- BindingCriteria: The field that will contain the binding criteria string e.g., 'WebAppID,DataSourceID=ID'. The data is pulled from the row itself.
- SharedCriteria: The field that will contain the shared criteria string e.g., 'WebAppID'. The data is pulled from the row itself.
DSP System Fields Related to Excel Integration
The fields listed in this section are manually added to an underlying table to track the user and date/time of data entry during the Excel import process. The values are updated upon completion of the import process.
|
System Field |
Description |
|
AddedOn |
Contains the date/time that the record was inserted into the table. |
|
AddedBy |
Contains the username of the user that imported the record. |
|
AddedVia |
Contains the method used to add the record into the table. This field defaults to ‘ExcelImport’ for records inserted via Excel Integration. |
Appendix A - Excel Integration Controls and Properties
The following outlines which controls are supported in Excel Integration.
- Supported Column Controls
- Unsupported Column Controls
- Supported Formats
- Unsupported Formats
- Supported Page Column Properties
- Unsupported Page Column Properties
- Supported Page Properties</li>
- Unsupported Page Properties
Supported Column Controls
- Check box
NOTE: Check boxes display in the Excel file as a list box with values of "true" and "false".
- Combo Box / List
NOTE: The Excel list box functionality is used. The maximum number of list box items is configurable via the List Record Limit option on the Admin > Configuration > Parameters page in the DSP.
- DateTime
- Numeric
- Text Area
- Text Box
Unsupported Column Controls
- Button
- Category
- File
- HTML Area
- Image
- Label
- Modal Content
- Tab
- Toolbar
- Value
Supported Formats
- Check box Format
- DateTime – Date
- DateTime – Format Date
- DateTime – Format Time
- DateTime – LongDate
- DateTime – Time
- DateTime – Time24
- Numeric – Currency
- Numeric – Positive
- Numeric – Percent
- Numeric – RJ0 (Zero Padded)
- Text Box – Password
- Text Box – Text
Unsupported Formats
- Text Area – SQL
- Text Box – Email
Supported Page Column Properties
- Control Status
- Required
- Default
- List Source
- List Value Field
- List Display Field
- List Where Clause
NOTE: Dependent list boxes are currently not supported. Only page level criteria can be used for substitution.
- List Order By
- List Allow Insert
- Runtime Data Source ID
NOTE: Runtime DataSourceID must be specified via page level criteria (not from the current row).
- List Control View
NOTE: List Control View properties (e.g., boaSwitch) must be specified via page level criteria (not from the current row).
- Consider Valid
- Control Status Field
Unsupported Page Column Properties
- Watermark Text
- HeaderImage
- Dynamic Image
- Dynamic Link Type
- Dynamic Link Column
- ImageID
- Report Follows Link
- Link To Report
- HoverView
- Key
- Allow Space
- Required Signature
- Link To PageID
- Link To Method
- Translate
- List Unique
- List Allow Insert PageID
- List Selection Field
- Show In Navigation
- Spanning Properties
- Tooltip
Supported Page Properties
- Table
- Horizontal View
- Order By
- Insert Method
- Update Method
- User Control View
- Page Control View
- Data Control View
- Parameter View
Unsupported Page Properties
- Hover View