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.
Read Excel File
Section titled “Read Excel File”Modes BrowserWindowsJavaAndroidiOSSAP
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 |
Parameters
| Name | Type | Description |
|---|---|---|
$filePath | string | path to Excel file. Can be of extension .xls or .xlsx . Relative path resolves relative to files folder of the current project. |
$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 |
Raw script
var $data = _readExcelFile("data.xls");Sahi Pro Classic API _readExcelFile
For more complex manipulation _getExcel can also be used. This allows modifying Excel sheets too.
Get Excel
Section titled “Get Excel”Modes BrowserWindowsJavaAndroidiOSSAP
The example below illustrates the usage of various APIs.
Parameters
| Name | Type | Description |
|---|---|---|
$filePath | string | path to Excel file. Can be of extension .xls or .xlsx . Relative path resolves relative to files folder of the current project. |
$sheetName | stringoptional | Sheet name. Defaults to sheetname of first sheet. |
Returns
object | ExcelPOI object which exposes various APIs to manipulate the Excel |
Raw script
//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();Sahi Pro Classic API _getExcel

