Create from Data Table

Creates or updates a .xlsx file using data from a data table.

Command availability: IBM RPA SaaS and IBM RPA on premises

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

excelReport --file(String) [--usetemplate(Boolean)] --template(String) --dataTable(DataTable) [--getfirstsheet(Boolean)] --sheet(String) --cell(String) [--headers(Boolean)] (Excel)=value

Dependencies

To save the file, use the Close Excel (excelclose) command with the Save parameter enabled.

Input parameters

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label	Script mode name	Required	Accepted variable types	Description
File	`file`	`Required`	`Text`	Full path to save the `.xlsx` file. If you want to update an existing file, use its path.
Use Template	`usetemplate`	`Optional`	`Boolean`	Use an existing template.
Template	`template`	`Required when the Use Template parameter is enabled`	`Text`	Full path of the file used as a template.
Data Table	`dataTable`	`Required`	`Data Table`	Data table to be inserted into the file.
Use First Sheet	`getfirstsheet`	`Optional`	`Boolean`	Enable to enter the data table in the first spreadsheet of the file.
Sheet	`sheet`	`Required when the Use First Sheet parameter is disabled`	`Text`	Name of the worksheet containing the data.
Cell	`cell`	`Required`	`Text`	Cell coordinate to start inserting values. Example: "C5".
Has Headers	`headers`	`Optional`	`Boolean`	Enable to use the worksheet's first line as a header.

Output parameters

Designer mode label	Script mode name	Accepted variable types	Description
File	value	`Excel`	Returns the `.xlsx` file with the data table inserted in the informed spreadsheet.

Example

Example 1: Starts the browser, navigates to a web page and obtains the data table. After obtaining the data table, the command creates a new .xlsx file on the desktop and inserts the obtained data table in it. The data table can be inserted in a specific spreadsheet and start in a specific cell. The Close Excel (excelClose) command saves and closes the file.

defVar --name obtainedDataTable --type DataTable
defVar --name rowsNumber --type Numeric
defVar --name columnsNumber --type Numeric
defVar --name webpage --type String --value "https://www.w3schools.com/html/html_tables.asp"
defVar --name desktopPath --type String
defVar --name fileOutput --type Excel
// Launches the Google Chrome browser
webStart --name browser --type "Chrome"
// Navigates to the web page
webNavigate --url "${webpage}"
webGetTable --selector "Id" --id customers --simulatehuman  rowsNumber=rows columnsNumber=columns obtainedDataTable=value
// Logs the data table content, row, and columns numbers
logMessage --message "Data table content: ${obtainedDataTable}\r\nColumns: ${columnsNumber}\r\nRows: ${rowsNumber} " --type "Info"
// Closes the browser
webClose --name browser --leavebrowseropen
// Gets the desktop path
getSpecialFolder --folder "Desktop" desktopPath=value
// Creates a new file and inserts the obtained data table
excelReport --file "${desktopPath}\\example.xlsx" --dataTable ${obtainedDataTable} --getfirstsheet  --cell A1 fileOutput=value
// Saves and closes the file
excelClose --file ${fileOutput} --save

Example 2: The Open Excel File (excelOpen) command opens a .xlsx file and assigns it to the excel variable. Then, the Get Excel Table command imports its data into a variable named dataTable, and the Create from Data Table command inserts the extracted data table in the .xlsx file entered in the File parameter. The data table can be inserted in a specific spreadsheet and start in a specific cell. In this case, it is inserted in the Sheet1 spreadsheet and starts in the C5 cell.

defVar --name dataTable --type DataTable
defVar --name excelFile --type Excel
defVar --name excel --type Excel
// The Excel file is opened and assigned to the excel variable.
excelOpen --file "C:\\samplefile.xlsx" excel=value
// The data table is extracted from the file and assigned to the dataTable variable.
excelGetTable --file ${excel} --sheet One --fromrow 1 --fromcolumn 1 dataTable=value
// The extracted data table is inserted into the specified file.
excelReport --file "C:\\new_samplefile.xlsx" --dataTable ${dataTable} --sheet Sheet1 --cell A1 excelFile=value
// The file is closed and saved.
excelClose --file ${excelFile} --save

Limitations

The files referenced when running the Open Excel File (excelopen) command must be closed in order for the command to work.