Self Help - Excel Connector

Last modified on 2023/07/25 18:23

   The installation and update of connectors is performed by EasyVista. Please contact your EasyVista consultant to submit your requests for any creation or update.

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.

          Excel connector.png

Notes

     Open url.png 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.

          Excel connector - Method Manage sheets.png

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. Open url.png See the use case

          Excel connector - Method Get cells values.png

Input parameters for the method

Best Practice icon.png  To specify a range of cells, you can use the custom name associated with the range of cells in the Excel file.

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.

Caution: The value must comply with the existing Excel file format.

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

          Excel connector - Method Get cells values - Example - Excel file.png

  • 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.
              Excel connector - Method Get cells values - Example - Formula 1.png  Excel connector - Method Get cells values - Example - Formula 2.png

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. Open url.png See the use case

          Excel connector - Method Set cells values.png

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.

Best Practice icon.png  To specify a range of cells, you can use the custom name associated with the range of cells in the Excel file.

  • The value corresponds to the new contents of the cell.

Caution: The value must comply with the existing Excel file format.

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.

     Open url.png See the detailed procedure

          Excel Connector - Set Cells Values Method - Use case - Procedure.png

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.

     Open url.png See the detailed procedure

          Excel Connector - Get Cells Values Method - Use case - Procedure.png

List of files to download

Tags:
Powered by XWiki © EasyVista 2022