Introduction

Enzo Server
  Installation
  Quick Start
  Core Features
    Async Calls
    ConnectionStrings
    Edge Cache
    HTTP Access
    Scheduling
    Views
  Advanced Capabilities
    Change Data Capture
  Administration
    Configuration Settings
    Logins & ACL
    Auditing
    Current Executions
    Linked Server
    SSL

  User Guides
     Sharding
     Sharding Overview

     SharePoint

   Adapters
    All Adapters


  SDK
    Overview
    Installation
    DevHost
    Create A Simple Adapter
    Best Practices
    Developer Guide
      Handler Columns
      Handler Options
      Handler Decorators
      Dynamic Columns
      Table & Table Enumerators
      Virtual Tables



XLS Adapter








The XLS adapter allows you to query and write to XLSX workbooks using SQL and REST commands.


The Write handler is currently in development and available as a preview



Configuration Settings



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

Handlers



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 fileNameThe 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 fileNameThe name of the output file
Boolean existsReturns 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 sheetNameThe 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 fileNameThe name of the output file
String sheetNameThe name of the output file
Boolean existsReturns 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 fileNameThe name of the output file
Int64 rowCountNumber of records written (excluding the header)
Double timeTakenSecTotal seconds taken to create the file