When you connect, the component discovers the available columns and reflects both changes in Excel Online and configuration changes you make with the following connection properties. See the following sections to configure aspects of column discovery specific to Excel Online.
Configuring Column Name Detection
You can set Header to detect column names in a header row or you can reference columns alphabetically.
Below are more specifics on how the component detects column names from a header row when Header is set to true.
- The component determines columns from the first row of the worksheet. If no values are provided for the first row of the worksheet, the component creates unique, alphabetized column names that are available only within the scope of that request.
- Headers should not contain special characters.
- Due to a limitation of the Excel Online API, all column headers must be nonempty.
When Header is set to false, alphabetical column names will be dynamically assigned.
The component reports the row number as the primary key. The Id column for each row represents the row number from the top of the sheet.
For example, if you specify a range A3:E6, rows 3, 4, 5, and 6 will be returned.
Configuring Column Value Parsing
The ValueRenderOption and ValueInputOption connection properties can be used to configure how the component parses the Excel Online cell formats in SELECT, INSERT and UPDATE operations.
Value render options for SELECT operations
Set the ValueRenderOption to configure how the component renders values in the output of a SELECT operation. The available value render options are as below.
- FormattedValue: Values will be calculated and formatted in the output according to the cell's formatting. Formatting is based on the workbook's locale, not the requesting user's locale. This is the default ValueRenderOption.
- UnformattedValue: Values will be calculated but not formatted in the output.
- Formula: Values will not be calculated and instead the output will include the formulas. For this reason, every column containing a formula will be exposed as String type regardless of the set TypeDetectionScheme.
Value input options for INSERT and UPDATE operations
Set the ValueInputOption to configure how the component parses values the user submits to the Excel Online source for INSERT and UPDATE operations. The available value input options are as below.
- UserEntered: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc., following the same rules that are applied when entering text into a cell via the Excel Online UI. This is the default ValueInputOption.
- Raw: The values the user has entered will not be parsed and will be stored as-is.
Configuring Data Type Detection
Set the TypeDetectionScheme to configure the data type detection method. The available type detection schemes are as below.
- RowScan: The component will scan rows to heuristically determine the data type. The number of rows the component will scan can be determinated by the RowScanDepth property. This is the default TypeDetectionScheme.
- ColumnFormat: The component will report the data type based on the cell format of the column. This option is applicable only for the 'UnformattedValue' and 'Formula' ValueRenderOption-s. If the ValueRenderOption is set to 'FormattedValue', all columns will be exposed as String types.
- None: The component will return all columns as the string type.