|
Name
|
Description
|
Default |
| Boolean _isDefault |
Make this the default configuration. Defaults to true if configName is already the default. |
|
| String name |
The name of the configuration |
|
| String sheetName |
The name of the sheet (if any) |
|
| String columns |
List of columns to return |
|
| String range |
The range inside the sheet (leave blank for entire sheet) |
|
| Boolean firstRowNames |
True if the first row contains column names |
|
| String defaultPath |
The full path to the Excel document |
|
| String defaultPassword |
The password of the Excel document |
|
| Boolean treatAsText |
Treats all data as text to avoid data type conflicts |
|
| String userId |
Default User ID for network login |
|
| String password |
Default password for network login |
|
createFile
|
Create or recreate an empty new Excel workbook with a default WorkSheet
details
Arguments
|
Name
|
Description
|
Default |
|
fileName
|
Full path of the Excel document; if left empty, a TMP file will be created.
|
|
|
override
|
When 1, create or recreate the file
|
|
SQL Call
exec xls.createFile 'c:\tmp\myexcelfile.xlsx'
exec xls.createFile 'c:\tmp\myexcelfile.xlsx', 1
Http Call
/xls/createfile
Output Columns
| String fileName | The name of the output file |
|
createSheet
sheets
|
Creates a new Sheet in an Excel file with optional override
details
Arguments
|
Name
|
Description
|
Default |
|
fileName R
|
Full path of the Excel document
|
|
|
sheetName
|
The name of the sheet (case insensitive)
|
|
|
override
|
When 1, deletes then recreates the sheet if it exists
|
|
SQL Call
exec xls.createSheet 'c:\tmp\myexcelfile.xlsx', 'Customers'
Http Call
/xls/createsheet
Output Columns
This call does not return data.
|
deleteSheet
sheets
|
Deletes a Sheet in an Excel file if it exists
details
Arguments
|
Name
|
Description
|
Default |
|
fileName R
|
Full path of the Excel document
|
|
|
sheetName
|
The name of the sheet (case insensitive)
|
|
SQL Call
exec xls.deleteSheet 'c:\tmp\myexcelfile.xlsx', 'Customers'
Http Call
/xls/deletesheet
Output Columns
This call does not return data.
|
fileExists
|
Checks whether an Excel file already exists
details
Arguments
|
Name
|
Description
|
Default |
|
fileName
|
Full path of the Excel document
|
|
SQL Call
exec xls.fileExists 'c:\tmp\myexcelfile.xlsx'
Http Call
/xls/fileexists
Output Columns
| String fileName | The name of the output file | | Boolean exists | Returns true if the file exists |
|
ListSheets
sheets
|
List the worksheets in an Excel Workbook (XLS, XLSX, XLSB, XLSM).
details
Arguments
|
Name
|
Description
|
Default |
|
fileName
|
Full path of the Excel document (xls); leave null to use the defaultPath setting.
|
|
SQL Call
exec xls.ListSheets 'c:\tmp\myexcelfile.xlsx'
Http Call
/xls/listsheets
Output Columns
| String sheetName | The name of the sheet |
|
ReadXls
data
|
Read the specified worksheet's rows and columns from an Excel Workbook (XLS, XLSX, XLSB, XLSM).
details
Arguments
|
Name
|
Description
|
Default |
|
definitionName
|
Name of previously stored definition
|
|
|
fileName
|
Full path of the Excel document; leave null to use the defaultPath setting.
|
|
|
sheetName
|
If NULL uses the default sheetName setting or the first sheet if that is also NULL
|
|
|
range
|
Excel notation for column and row range, e.g. 'A1:B50' - 50 rows, 'A1:D' - all rows.
|
|
|
maxRows
|
Maximum number of rows to return (0 for all rows)
|
0 |
|
where
|
Where clause (used for EXEC calls only)
|
|
SQL Call
SELECT * FROM xls.data@xlconfig
exec xls.readXls 'xlconfig'
exec xls.readXls 'xlconfig', 'filename'
exec xls.readXls 'xlconfig', 'filename', 'myWorkSheet', 'A:ZZ', 100
exec xls.readXls 'xlconfig', 'filename', 'myWorkSheet', 'A:ZZ'
Http Call
/xls/readxls
Output Columns
This call does not return data.
|
renameSheet
sheets
|
Renames a Sheet in an Excel file (the sheetName must exist)
details
Arguments
|
Name
|
Description
|
Default |
|
fileName R
|
Full path of the Excel document
|
|
|
sheetName R
|
The name of the sheet (case insensitive)
|
|
|
newSheetName
|
The new name for the sheet
|
|
SQL Call
exec xls.renameSheet 'c:\tmp\myexcelfile.xlsx', 'Sheet1', 'Customers'
Http Call
/xls/renamesheet
Output Columns
This call does not return data.
|
sheetExists
|
Checks whether a Sheet name exists in an Excel file
details
Arguments
|
Name
|
Description
|
Default |
|
fileName
|
Full path of the Excel document
|
|
|
sheetName
|
The name of the sheet (case insensitive)
|
|
SQL Call
exec xls.sheetExists 'c:\tmp\myexcelfile.xlsx'
Http Call
/xls/sheetexists
Output Columns
| String fileName | The name of the output file | | String sheetName | The name of the output file | | Boolean exists | Returns true if the file exists |
|
WriteXls
|
Writes the output of an SQL stamtent as data into an Excel Workbook.
details
Arguments
|
Name
|
Description
|
Default |
|
sourceConnection R
|
The source connection string (or Central Connection String name)
|
|
|
sqlread R
|
The source SQL Statement that extracts data from the source system
|
|
|
fileName
|
Full path of the Excel document; if left empty, a TMP file will be created.
|
|
|
sheetName
|
If NULL uses the default sheetName setting or Sheet1 if that is also NULL
|
|
|
cell
|
The top left cell where data will be written (ex: B12)
|
A1 |
|
override
|
When 1, overrides an existing file
|
|
SQL Call
exec xls.WriteXls 'xlconfig', 'SELECT * FROM MyTable1'
Http Call
/xls/writexls
Output Columns
| String fileName | The name of the output file | | Int64 rowCount | Number of records written (excluding the header) | | Double timeTakenSec | Total seconds taken to create the file |
|
