Excel Functions
Excel sheets are a favourite among business users. Sahi has functions to manipulate Excel Sheets which work on non-Windows machines too. Sahi uses Apache POI libraries underneath.
_readExcelFile
Section titled “_readExcelFile”- Since
- Sahi Pro 4.0
- Desktop Engine 7.0.0
- Mobile Engine 7.5.0
- SAP Engine 9.0.0
Modes BrowserWindowsJavaAndroidiOSSAP
_readExcelFile($filePath[, $sheetName[, $props]])Arguments
| Name | Type | Description |
|---|---|---|
$filePath | string | path to Excel file. Can be of extension .xls or .xlsx |
$sheetName | stringoptional | Sheet name. Defaults to sheetname of first sheet. |
$props | objectoptional | Properties to specify for: ignoreEmptyRows: boolean: Ignores the empty lines. Defaults to false. includeHeader: boolean: If true, returns the header row also as the first row. Defaults to false. |
Returns
two dimensional array of strings | a 2 dimensional array of all values in the excel file |
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"$props can be passed as following:
var $data = _readExcelFile("data.xls");var $data1 = _readExcelFile("data.xls", "Sheet1", {includeHeader:true});var $data2 = _readExcelFile("data.xls", "Sheet2", {includeHeader:true,ignoreEmptyRows:true});var $dataLength = $data.length // returns 4 since includeHeader and ignoreEmptyRows default value is falsevar $data1Length = $data1.length // returns 5 since includeHeader is truevar $data2Length = $data2.length // returns 4 since includeHeader and ignoreEmptyRows are truevar $data3 = _readExcelFile("data.xls", null, {includeHeader:true,ignoreEmptyRows:true}); // In case of the default sheet name use 'null' or use sheet name there.For more complex manipulation _getExcel can also be used. This allows modifying Excel sheets too.
_getExcel
Section titled “_getExcel”- Since
- Sahi Pro 4.0
- Desktop Engine 7.0.0
- Mobile Engine 7.5.0
- SAP Engine 9.0.0
Modes BrowserWindowsJavaAndroidiOSSAP
_getExcel($filePath[, $sheetName])Arguments
| Name | Type | Description |
|---|---|---|
$filePath | string | path to Excel file. Can be of extension .xls or .xlsx |
$sheetName | stringoptional | Sheet name. Defaults to sheetname of first sheet. |
Returns
object | 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 sheetvar $excel = _getExcel("D:/poiTest.xls","Sheet1");
//Set Datavar $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 Datavar $getdata=$excel.getData();_assertEqual("irfan",$getdata[2][2]);
//Insert New Row at endvar $insertRowData=["kathir","femina","jandir"];$excel.insertRow($insertRowData);
//Insert New Row at given indexvar $insertRowData=["kathir","femina","jandir"];$excel.insertRow($insertRowData, 2); // will insert at index 2. Shifts other cells down
//Insert New Rows at endvar $insertRowsData=[["shoba","ravi","vasi"],["kajol","rani","sharuk"]];$excel.insertRows($insertRowsData);
//Insert New Rows at given indexvar $insertRowsData=[["shoba","ravi","vasi"],["kajol","rani","sharuk"]];$excel.insertRows($insertRowsData, 2); // will insert at index 2. Shifts other cells down
//Delete Row at index 4var $deleteRowIndex=4;$excel.deleteRow($deleteRowIndex);
//Delete Rows indexed 1 and 4var $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,2var $excelGet=$excel.get(2, 2);_assertEqual("xezwanth", $excelGet);
//Set Cell Value at cell position 2,2var $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 objectvar $workbook = $excel.getRawWorkbook();
