How to read/write excel in OATS

Reading excel / CSV sheet in Oracle Application Testing Suite ie Open Script tool is quite simple than other automation tools.

Open script provides inbuilt facilities to read/write excel sheets without any hassle. No Need of any external jars.

Go to command prompt open Open Script tool

or directly open in default locationĀ C:\OracleATS\openScript\OpenScript.exe

Create New Script say testExcelReader (Give any name)

 

Enable Excel reading feature in Newly created script

Navigate to

Script——>Script properties

In the next popup window you can see under Modules , DataTable is not checked by default, To Enable the Excel Reading feature select the check box and click ok

 

and when you select click ok you can observer a code is inserted in the editor window

@ScriptService oracle.oats.scripting.modules.datatable.api.DataTableService datatable;

Now Lets start coding

take example of testExcel Sheet, which looks like below

Say this Sheet is kept in C Drive under Demo Folder, Below code will import Sheet

String sheetName = "Sheet1";

String strFile= "C:\\Demo\\test.xls";

List sheetList = new ArrayList();
sheetList.add(sheetName);
// read
datatable.importSheets(strFile, sheetList, true, true);

 

Below Code Loops and read all rows and update “Y” in active column

int rowcount=datatable.getRowCount();
for (int i=0;i<rowcount;i++)
{
datatable.setCurrentRow(sheetName, i);
String strCompany=(String) datatable.getValue(sheetName,i,"Company");
String strEmpFName=(String) datatable.getValue(sheetName,i,"FirstName");
String strEmpLName=(String) datatable.getValue(sheetName,i,"LastName");
String strEmpID=(String) datatable.getValue(sheetName,i,"EmpID");
String strLocation=(String) datatable.getValue(sheetName,i,"Location");

System.out.println("First Name : "+strEmpFName+", Last Name : "+strEmpLName);

String strActive="Y";
datatable.setValue(sheetName, i, datatable.getColumn(strActive, datatable.getColumnIndex("Active")), Read);

}

 

This line will save the update sheet with ACTIVE column set to “Y”

datatable.exportToExcel("C:\\Demo\\test.xlsx");

 

Here is the Complete Code:

import java.util.ArrayList;
import java.util.List;
import oracle.oats.scripting.modules.basic.api.*;
import oracle.oats.scripting.modules.browser.api.*;
import oracle.oats.scripting.modules.functionalTest.api.*;
import oracle.oats.scripting.modules.utilities.api.*;
import oracle.oats.scripting.modules.utilities.api.sql.*;
import oracle.oats.scripting.modules.utilities.api.xml.*;
import oracle.oats.scripting.modules.utilities.api.file.*;
import oracle.oats.scripting.modules.webdom.api.*;


public class script extends IteratingVUserScript {
@ScriptService oracle.oats.scripting.modules.utilities.api.UtilitiesService utilities;
@ScriptService oracle.oats.scripting.modules.browser.api.BrowserService browser;
@ScriptService oracle.oats.scripting.modules.functionalTest.api.FunctionalTestService ft;
@ScriptService oracle.oats.scripting.modules.webdom.api.WebDomService web;
@ScriptService oracle.oats.scripting.modules.datatable.api.DataTableService datatable;


public void initialize() throws Exception {
}


/**
* Add code to be executed each iteration for this virtual user.
*/
public void run() throws Exception {

//Define Sheet name to be read, and provide comma seperated to read multiple sheets
String sheetName = "Sheet1";
//Mention excel sheet path
String strFile= "C:\\Demo\\test.xls";


//Defined array list to add Sheets to read
List sheetList = new ArrayList();
sheetList.add(sheetName);
// Iports Sheet1
datatable.importSheets(strFile, sheetList, true, true);
//get rowcount
info("Total rows :"+datatable.getRowCount());
int rowcount=datatable.getRowCount();
//Loop to read all rows
for (int i=0;i<rowcount;i++)
{
//Set current row fromw here you need to start reading, in this case start from first row
datatable.setCurrentRow(sheetName, i);
String strCompany=(String) datatable.getValue(sheetName,i,"Company");
String strEmpFName=(String) datatable.getValue(sheetName,i,"FirstName");
String strEmpLName=(String) datatable.getValue(sheetName,i,"LastName");
String strEmpID=(String) datatable.getValue(sheetName,i,"EmpID");
String strLocation=(String) datatable.getValue(sheetName,i,"Location");

//prints first name and last name
System.out.println("First Name : "+strEmpFName+", Last Name : "+strEmpLName);

//Sets ACTIVE column in excel sheet to Y
String strActive="Y";
datatable.setValue(sheetName, i, datatable.getColumn(sheetName, datatable.getColumnIndex("Active")), strActive);

}

//Updates sheet with updated values ie ACTIVE column sets to Y
datatable.exportToExcel("C:\\Demo\\test1.xlsx");

}

public void finish() throws Exception {
}
}

 

 

I will be publishing Video very soon..

1 thought on “How to read/write excel in OATS”

  1. thank you for this help… it helped me lot…
    there are very less tutorials for Open Script

    Keep writing more

Leave a Reply

Your email address will not be published. Required fields are marked *

19 − seventeen =