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 `"Sheet1"`
$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 `"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");

//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.