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.
Reads the excel sheet and returns data as an associative array.
Given an Excel Sheet like
Given an Excel Sheet like
| A | B | C | D | E | F | G | |
| 1 | Name | Age | |||||
| 2 | Ram | 18 | |||||
| 3 | Amir | 20 | |||||
| 4 |
Parameters
| $filePath | string | path to Excel file. Can be of extension .xls or .xlsx
path to Excel file. Can be of extension .xls or .xlsx . Relative path resolves relative to files folder of the current project. |
| $sheetName | string optional | Sheet name. Defaults to sheetname of first sheet. |
| $props | object optional | 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. |
Return Value
| two dimensional array of strings | a 2 dimensional array of all values in the excel file |
Modes Supported :
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.
The example below illustrates the usage of various APIs.
Parameters
| $filePath | string | path to Excel file. Can be of extension .xls or .xlsx
path to Excel file. Can be of extension .xls or .xlsx . Relative path resolves relative to files folder of the current project. |
| $sheetName | string optional | Sheet name. Defaults to sheetname of first sheet. |
Return Value
| object | ExcelPOI object which exposes various APIs to manipulate the Excel |
Modes Supported :
Raw Script
//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.insertRows($insertRowsData, 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();
Sahi Pro Classic API :_getExcel