Excel Sheet Manipulation APIs

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

Since Sahi Pro: 4.0
Since Sahi OS: NA

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

Arguments

$filePath	string	path to Excel file. Can be of extension .xls or .xlsx
$sheetName	string optional	Sheet name. Defaults to sheetname of first sheet.
$includeHeader	boolean optional	If true, returns the header row also as the first row. Default false.
$ignoreEmptyRows	boolean optional	ignores the empty lines. Defaults to 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

Since Sahi Pro: 4.0
Since Sahi OS: NA

_getExcel($filePath[, $sheetName])

Arguments

$filePath	string	path to Excel file. Can be of extension .xls or .xlsx
$sheetName	string optional	Sheet name. Defaults to sheetname of first sheet.

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");

//Get String array of all Sheet Names on that Excel.
var $arrayOfSheetNames = $excel.getSheetNames();

//Get ExcelPoi Workbook object
var $workbook = $excel.getRawWorkbook();

info Since 6.0.0, the following changes have been done.

If a cell has a formula, getData and get functions return value instead of formula. Added a method getCellFormula that returns formula.
Added method getSheetNames that returns all the sheet names as a String array
Added method getRawWorkbook which returns a Workbook POI object. This gives the user the flexibility to deal with the Workbook object directly
Added getCellObject that returns a Cell POI object.
Added getCellFont that returns a Font POI object.

Since 6.1.0, $excel.getDataForDataDrive() API has been added which returns data in a format appropriate for passing into _dataDrive.
See getDataForDataDrive sample usage.