How do I update the values in an Excel File
|
|
This example demonstrates how we can insert or update data into a specific sheet of an Excel File having one or more sheets from within the context.
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 2nd row of the dataset is updated using "ddSetCellValueByName" and the dataset is persisted into an excel file using the "ddPersistDataSetToExcelFile" command.
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' where the values will be set | |||
| 3 | ddCreateDataSet |
DatasetFromExcel | DataDefinitionFromExcel(W) |
| Create dataset From Excel File | |||
| 4 | ddCreateDataSetFromExcelFile |
Workbook_Sheet |
DatasetFromExcel |
| Set the Cell value |
|||
| 5 | ddSetCellValueByName |
DatasetFromExcel(1,Age) | 45 |
| Persist dataset to Excel File for Override |
|||
| 6 | ddPersistDataSetToExcelFile |
Workbook_Sheet||override |
DatasetFromExcel |
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 ddSetCellValueByIndex command to set the data in the specific cell of the sheet by using row and column numbers.
- Index values start from (1,1) in Excel file whereas it starts from (0,0) in a dataset.
- 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).
|
|
|