Sahi Pro - Excel Sheet Manipulation APIs

Excel sheets are a favourite among business users.
Sahi has APIs to manipulate Excel Sheets which work on non-Windows machines too.
Sahi uses Apache POI libraries underneath.

_readExcelFile

_readExcelFile($filePath[, $sheetName[, $includeHeader]])

Arguments

$filePath	string	path to Excel file. Can be of extension .xls or .xlsx
$sheetName	string optional	Sheet name. Defaults to `"Sheet1"`
$includeHeader	boolean optional	If true, returns the header row also as the first row. Default false.

Details

Reads the excel sheet and returns data as an associative array.

Given an Excel Sheet like

	A	B	C	D	E	F	G
1	Name	Age
2	Ram	18
3	Amir	20
4

var $data = _readExcelFile("data.xls");

$data is now

[
{"Name":"Ram","Age":"18"}
{"Name":"Amir","Age":"20"}
]

It can be accessed like this:

_log("Name of first user is: " + $data[0]["Name"]); // logs "Ram"
_log("Age of second user is: " + $data[1]["Age"]); // logs "20"

For more complex manipulation _getExcel can also be used. This allows modifying Excel sheets too.

_getExcel

_getExcel($filePath[, $sheetName])

Arguments

$filePath	string	path to Excel file. Can be of extension .xls or .xlsx
$sheetName	string optional	Sheet name. Defaults to `"Sheet1"`

Details

Returns an ExcelPOI object which exposes various APIs to manipulate the Excel.
The example below illustrates the usage of various APIs.

//Get a handle to the Excel sheet
var $excel = _getExcel("D:\\poiTest.xls","Sheet1");

//Set Data
var $excelData=[["ajay","babu","cheeran"],["david","elango","frank"],
["gokul","hari","irfan"],["jo","kumar","latha"],["mani","naraen","ojha"],
["peter","queen","richard"],["shalini","thomas","umesh"]];
$excel.setData($excelData);

//Get Data
var $getdata=$excel.getData();
_assertEqual("irfan",$getdata[2][2]);

//Insert New Row at end
var $insertRowData=["kathir","femina","jandir"];
$excel.insertRow($insertRowData);

//Insert New Row at given index
var $insertRowData=["kathir","femina","jandir"];
$excel.insertRow($insertRowData, 2); // will insert at index 2. Shifts other cells down

//Insert New Rows at end
var $insertRowsData=[["shoba","ravi","vasi"],["kajol","rani","sharuk"]];
$excel.insertRows($insertRowsData);

//Insert New Rows at given index
var $insertRowsData=[["shoba","ravi","vasi"],["kajol","rani","sharuk"]];
$excel.insertRow($insertsRowData, 2); // will insert at index 2. Shifts other cells down

//Delete Row at index 4
var $deleteRowIndex=4;
$excel.deleteRow($deleteRowIndex);

//Delete Rows indexed 1 and 4
var $deleteRowsIndexes=[1,4];
$excel.deleteRows($deleteRowsIndexes);

//Delete Rows from row 3 to end
$excel.deleteRows(3);

//Delete Rows from row 3 to row 8
$excel.deleteRows(3, 8);

//Get Cell Value at cell position 2,2
var $excelGet=$excel.get(2, 2);
_assertEqual("xezwanth", $excelGet);

//Set Cell Value at cell position 2,2
var $excelData=$excel.set(2,2,"444-444-4444");