How do I retrieve the values from an Excel File
 |
|
This example demonstrates how to retrieve data from a specific sheet of an Excel File having two or more sheets.
In the solution shown below, an excel workbook context is created from the excel file using "excelSetWorkbookContext" command and worksheet is selected using "excelSetWorksheetContextWithinWorkbook" command. A dataset is declared using "ddCreateDataset" command and created from the worksheet using "ddCreateDataSetFromExcelFile" command. The value in the column "Age" in 1st row of the dataset is retrieved using "ddGetCellValueByName".
Solution:
| # | Command | Target | Value |
|---|---|---|---|
| Set Workbook Content From Excel File | |||
| 1 | excelSetWorkbookContext | Health Insurance Details.xlsx||xlsx | Workbook_Content |
| Set Worksheet Context Within Workbook | |||
| 2 | excelSetWorksheetContextWithinWorkbook | Workbook_Content||Insurance Details | Workbook_Sheet |
| Declare the dataset name with a data definition specified in the value field containing the column name 'Age' | |||
| 3 | ddCreateDataSet | DatasetFromExcel | DataDefinitionFromExcel(W) |
| Create dataset From Excel File |
|||
| 4 | ddCreateDataSetFromExcelFile |
Workbook_Sheet | DatasetFromExcel |
| Get the Cell value from the dataset |
|||
| 5 | ddGetCellValueByName |
DatasetFromExcel(0,Age) | Age |
Tips, Tricks, Gotchas & Best Practices:
- It is mandatory to have an Excel File with one or more worksheets uploaded into Files in the Worksoft SaaS.
- You should use the same dataset name which is created by using the "ddCreateDataSet" command.
- As a best practice, it is always advisable to define the variable names without any spaces and if you want to differentiate the words, use underscores.
- The variable name used in the 'Value' field of the "store" command should not contain the following special characters which are not allowed: >, <, ==, !=, >=, <=, +, -, *, /, %, (, ).
- You can also use the ddGetCellValueByIndex command to retrieve the data in the specific cell of the sheet by using row and column numbers.
- You can optionally pass the status of the Data Definition along with its name in "ddCreateDataSet" command. Allowed values for status are 'W'(WIP) and 'A'(Active). If the status value is not passed, the status is assumed to be 'W'(WIP).
- Index values start from (1,1) in Excel file whereas it starts from (0,0) in a dataset.
|
|
|