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.

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.
   

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.

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 
  • PDF file for the use case
  • Sample project (procedure Getting information about an asset)
  • Asset Excel file (Asset)

• Set Cell Values 
  • PDF file for the use case
  • Sample project (procedure Create my expense report)
  • Excel template (Expense Report)