Self Help - Excel Connector
This connector enables you to manipulate Excel files, write cell contents, read one or more cells or a range of cells and manage sheets in a workbook.
- The connector uses the first Excel file found in the context of the step where it is called (file added as a file attachment or via a Form utils connector).
- The Excel file is then interpreted by a Java library that will run the formulas.
Notes
See General remarks on standard connectors
- You are not required to install MS Excel on the workstation in order to use an Excel connector.
- The Excel connector cannot be used to manipulate formulas.
- Some Excel functionalities may not be available when using the Java library.
General connector configuration
None
Description of connector methods
Manage sheets
This method is used to list and modify sheets in an Excel workbook.
Input parameters for the method
Action: Action to be performed on workbook sheets.
- Get names: Lists all workbook sheets.
- Add a sheet: Adds a new sheet to the workbook. You must enter the name of the new sheet in the New name field.
- Rename a sheet: Replaces the sheet name in the Current name field with the sheet name in the New name field.
- Delete a sheet: Deletes the sheet indicated in the Current name field.
Current name: Name of the sheet where an action will be performed.
New name: Name of the new or renamed sheet.
Output file (optional): Name of the output Excel file.
Get Cell Values
This method is used to retrieve the value of one or more cells or a range of cells from an Excel sheet.
- The values can be sent to the Excel file as input parameters. They can then be used to change the current value of the cells and/or run calculations using different cells.
- The modified contents of the Excel file will not be kept by the method. Only cell values will be retrieved in the output parameters.
See the use case
Input parameters for the method
Result cells: References of the cells or range of cells whose value should be retrieved.
- Separate the different references using the semicolon (;).
- Specify the cell references in standard Excel format.
example
- Retrieve the value of cell A1 from the sheet called Sheet1 ==> Sheet1!A1
- Also retrieve the value of the range of cells A1 to A100 from the sheet called Sheet2 ==> Sheet1!A1;Sheet2!A1:A100
Key values: List of key-value pairs in cells used to modify other cells.
- The key corresponds to the reference of the cell to be modified, specified in standard Excel format.
- The value corresponds to the new contents of the cell and can be the result of a calculation.
Output parameters for the method
The result output parameter is a complex JavaScript object (key-value pair).
- The keys correspond to the references of cells or range of cells specified in the parameters.
- The values correspond to:
- For individual cells, the value retrieved from the cell
- For a range of cells, the tables containing the values retrieved from cells
How to display values retrieved from cells and stored in output parameters
- To display the value retrieved from a specific cell, the cell reference must start with the sheet name.
- To display the value retrieved from a specific cell within a range of cells, the values retrieved must be stored in an intermediate variable.
example The Excel file contains a sheet called users from which several cell values will be retrieved
- To display the value retrieved from cell H2 ("sales"), use the following formula, result["users!H2"].
The value of the result object will be {"users!H2":"sales"}.
- To display the values retrieved from the range of cells C2 to C5 and from cell H2, use the following formula, users!C2:C5;users!H2.
The value of the result object is {"users!C2:C5":["Berg", "Browne", "Crafts", "Faraday"],"users!H2":"sales"}.
- To display the value of cell C3 ("Browne") out of the values retrieved from the range of cells C2 to C5, you must use an intermediate variable.
- Store the values retrieved from the range of cells C2 to C5 in an intermediate variable using the following formula, var name = result["users!C2:C5"].
- Display the value of cell C2 using the following formula, name[1]. Note: The table starts with the 0 index.
Set Cell Values
This method is used to modify the value of one or more cells or a range of cells in an Excel sheet.
- The contents of the Excel file is modified by the method and can be retrieved in an output parameter.
- The updated Excel file can then be loaded in the context of the step and sent by the Email sending connector or manipulated by the File connector.
See the use case
Input parameters for the method
Output file name (optional): Name of the output Excel file.
Cells modified and their new values: List of key-value pairs in cells to be modified.
- The key corresponds to the reference of the cell to be modified, specified in standard Excel format.
- The value corresponds to the new contents of the cell.
Special cell format (optional): List of key-value pairs in cells for applying a custom format to cells.
- The key corresponds to the reference of the cell to be modified, specified in standard Excel format.
- The value corresponds to the new cell format.
example New format: dd/mm/yyyy
Use case
See detailed step by step on Self Help portal
Set Cell Values method
The procedure enables users to enter expense accounts via a form. The expenses will automatically be formatted in an Excel template using the Set Cell Values method of the Excel connector. A report will then be sent by email to both the user and the Accounting Department using the Email sending connector.
See the detailed procedure
Get Cell Values method
The procedure enables users to enter asset tags via a form. The value will then be sent to an Excel file containing asset tags and processed. The model, manufacturer and cost of the asset corresponding to the asset tag will be retrieved and displayed using the Get Cell Values method of the Excel connector.
See the detailed procedure
List of files to download
- Get Cell Values
- (procedure Getting information about an asset)
- (Asset)
- Set Cell Values
- (procedure Create my expense report)
- (Expense Report)