# Excel Support Service suite offers excellent Excel support from the lead open source POI project Basic calls are #### readExcel(Path:String) → ExcelWrapper Read and excel and returns the Excel Wrapper object, for Excel Wrapper methods, check the Excel Wrapper section above. Keep in mind you can use “{ds}” to indicate path relative to the current script. #### writeExcel(Data: ExcelWrapper, Path:String) → ExcelWrapper Writes an excel wrapper to disk, keep in mind you can use “{ds}” to indicate path relative to the current script. This functionality works in IDE but not during scenario execution. #### createNewExcel(FirstSheeName:String) → ExcelWrapper Creates a new excel wrapper with one sheet. For Excel Wrapper methods, check the Excel Wrapper section above. ## ExcelWrapper Excel wrapper holds a POI 5.0.0 document. It has basic cell editing, for full POI documentation [https://poi.apache.org/](https://poi.apache.org/) #### setValueAt(row:int, column:int, value:Object) Sets a value in a specific cell

```
var sheet = readExcel("myExcel.xlsx");
sheet.setValueAt(2,1,"P0000002643");
```

 

#### getValueAt(row:int, column:int) → value:String Gets the value from a specific cell in String format. If Cell is of type NUMERIC an exception will be thrown.

```
var sheet = readExcel("myExcel.xlsx");
log("Value: "+sheet.getValueAt(2,1));
```

 

For getting the String representation of a cell of type numeric or date can use **toString** method of [XSSFCell](https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFCell.html) object returned by **getCellAt** (see below).

```
var cell = sheet.getCellAt(2,5);
log("cell string representation: "+cell.toString());
```

 

#### getCellAt(row:int, column:int) → XSSFCell Gets the XSSFCell object. More info can be found in [XSSFCell](https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFCell.html)

```
var sheet = readExcel("myExcel.xlsx");
var dateCell = sheet.getCellAt(2,5);
log("Date: "+dateCell.toString());
```

 

```
// COPYING A CELL DATE
var excel = readExcel("myExcel.xlsx");
var cell25 = excel.getCellAt(2, 5);  // value is Date 01-Jan-2020
log("2,5 date value: " + cell25.toString());
cell27 = excel.getCellAt(2, 7)
log("Cell 2,7 raw value: " + cell27.getRawValue());  // null

var policy = org.apache.poi.ss.usermodel.CellCopyPolicy();
cell27.copyCellFrom(cell25,policy);
log("Cell 2,7 value after copying cell 2,5: " + cell27.toString());  // 01-Jan-2020
```

 

#### getWorkbook() → XSSFWorkbook Gets the workbook, full documentation about workbook can be found [https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFWorkbook.html](https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFWorkbook.html) #### setBooleanValueAt(row:int, column:int, value:boolean) → void Set a cell with a boolean value

```
var sheet = readExcel("myExcel.xlsx");
sheet.setBooleanValueAt(2,3,true);
assertEquals(true, sheet.getBooleanValueAt(2,3))
```

 

#### getBooleanValueAt(row:int, column:int) → value:boolean Get boolean value of a cell

```
var sheet = readExcel("myExcel.xlsx");
sheet.setBooleanValueAt(2,3,true);
assertEquals(true, sheet.getBooleanValueAt(2,3))
```

 

#### setFormulaValueAt(row:int, column:int, formula:string) → void Set a cell’s formula

```
var sheet = readExcel("myExcel.xlsx");
sheet.setFormulaValueAt(3,3,'AVERAGE("E1:G1")');
assertEquals('AVERAGE("E1:G1")', sheet.getFormulaAt(3,3))
```

 

#### getFormulaAt(row:int, column:int) → formula:string Get a cell’s formula as a string

```
var sheet = readExcel("myExcel.xlsx");
sheet.setFormulaValueAt(3,3,'AVERAGE("E1:G1")');
assertEquals('AVERAGE("E1:G1")', sheet.getFormulaAt(3,3))
```

 

#### setCellType(row:int, column:int, formula:string, CellType value) → void Set a cell’s cell type as in [Cell Type](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/CellType.html)

```
var sheet = readExcel("myExcel.xlsx");
var cell = sheet.getCellAt(2,4);
cell.setCellType(org.apache.poi.ss.usermodel.CellType.BLANK);
```

 

#### getCellType(row:int, column:int) → formula:[CellType](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/CellType.html) Get a cell’s Cell Type

```
var sheet = readExcel("myExcel.xlsx");
assertEquals('NUMERIC',sheet.getCellType(2,3).toString());
```

 

#### setDateValueAt(row:int, column:int, value:[Date](https://docs.oracle.com/javase/8/docs/api/java/util/Date.html)) → void Set a cell’s cell type as in [Cell Type](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/CellType.html)

```
var sheet = readExcel("myExcel.xlsx");
var cell = sheet.setCellDateAt(2,4, (new java.util.Date).now());
```

 

#### getDateValueAt(row:int, column:int) → date:[Date](https://docs.oracle.com/javase/8/docs/api/java/util/Date.html) Get a cell’s Date object

```
var sheet = readExcel("myExcel.xlsx");
log('NUMERIC',sheet.getDateValueAt(2,3));
```

 

#### setNumericValueAt(row:int, column:int, formula:string, value:double) → void Set a cell’s cell numeric value

```
var sheet = readExcel("myExcel.xlsx");
var cell = sheet.setNumericValueAt(2,5, 43831);
```

 

#### getNumericValueAt(row:int, column:int) → value:double Get a cell’s numeric value as a string

```
var sheet = readExcel("myExcel.xlsx");
log(43831.0,sheet.getDateValueAt(2,3));
```

 

#### getRawValueAt(row:int, column:int) → value:string Get a cell’s raw value formatted as a string

```
var sheet = readExcel("myExcel.xlsx");
assertEquals('43831.0',sheet.getRawValueAt(2,3));
```

 

#### getFormattedValueAt(row:int, column:int) → value:string Get a cell’s formatted value as a string

```
var sheet = readExcel("myExcel.xlsx");
assertEquals('1/1/20',sheet.getFormatetedValueAt(2,3));
```

 

#### setDateStyleAt(row:int, column:int, format:string) → void Sets a cell’s format style

```
var workbook = createNewExcel("Hello World");
workbook.setNumericValueAt(2,2,43831);  // Jan/01/2020
workbook.setDateStyleAt(2,2,"mm/dd/yy");
assertEquals("01/01/20", workbook.getFormattedValueAt(2,2));
```