Service Virtualization

Hi All,

I have tried the below code in Javascript to write a value in Excel file, but am below mentioned error while executing. Please help me on this.

When i execute the same script in eclipse , it is working fine.

Java script: 

String FILE_NAME = "C:/Users/Desktop/Test Data.xlsx";
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
Object[][] datatypes = {
{"Datatype", "Type", "Size(in bytes)"},
{"int", "Primitive", 2},
{"float", "Primitive", 4},
{"double", "Primitive", 8},
{"char", "Primitive", 1},
{"String", "Non-Primitive", "No fixed size"}
};

int rowNum = 0;

for (Object[] datatype : datatypes) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : datatype) {
Cell cell = row.createCell(colNum++);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}

FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
workbook.write(outputStream);
workbook.close();

Error Message:

============================================================================
| Error in Script
============================================================================
| Step: Write to an Excel
----------------------------------------------------------------------------
| Message: Sourced file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 2 : in file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : XSSFWorkbook

----------------------------------------------------------------------------
| Trapped Exception: Sourced file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 2 : in file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : XSSFWorkbook

| Trapped Message: bsh.EvalError: Sourced file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 2 : in file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : XSSFWorkbook

----------------------------------------------------------------------------
STACK TRACE
bsh.EvalError: Sourced file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : Typed variable declaration : Class: XSSFWorkbook not found in namespace : at Line: 2 : in file: inline evaluation of: ``String FILE_NAME = "/C:/Users/Desktop/Test Data.xlsx"; XSSF . . . '' : XSSFWorkbook

at bsh.BSHAmbiguousName.toClass(BSHAmbiguousName.java:73)
at bsh.BSHType.getType(BSHType.java:154)
at bsh.BSHTypedVariableDeclaration.eval(BSHTypedVariableDeclaration.java:75)
at bsh.Interpreter.eval(Interpreter.java:664)
at bsh.Interpreter.eval(Interpreter.java:758)
at bsh.Interpreter.eval(Interpreter.java:747)
at com.itko.lisa.test.ScriptExecHandler.executeScript(ScriptExecHandler.java:636)
at com.itko.lisa.test.ScriptExecHandler.executeScriptOld(ScriptExecHandler.java:528)
at com.itko.lisa.test.ScriptNode._execute(ScriptNode.java:126)
at com.itko.lisa.test.ScriptNode.execute(ScriptNode.java:103)
at com.itko.lisa.test.TestNode.executeNode(TestNode.java:981)
at com.itko.lisa.test.TestCase.execute(TestCase.java:1283)
at com.itko.lisa.test.TestCase.execute(TestCase.java:1198)
at com.itko.lisa.test.TestCase.executeNextNode(TestCase.java:1183)
at com.itko.lisa.editor.WalkThruPanel.prepAndExecNode(WalkThruPanel.java:1050)
at com.itko.lisa.editor.WalkThruPanel.access$900(WalkThruPanel.java:70)
at com.itko.lisa.editor.WalkThruPanel$10.doCallback(WalkThruPanel.java:963)
at com.itko.util.swing.panels.ProcessingDialog$2.run(ProcessingDialog.java:194)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: Class: XSSFWorkbook not found in namespace
at bsh.Name.toClass(Name.java:664)
at bsh.BSHAmbiguousName.toClass(BSHAmbiguousName.java:71)
... 18 more
============================================================================

Please guide me on this.

Thanks in Advance

Ram

Please can anyone help on this.

Looks like you not copy POI Binary Distribution on Devtest hotdeploy and/or lib folder.  Please download Binary Distribution on below path and place them on above mentioned folder.  

Apache POI - Download Release Artifacts  

Also sharing sample code to Read and Write in excel file using POI library. 

Reading excel using POI : 

import org.apache.poi.hssf.usermodel.*

def srcBook= new HSSFWorkbook(new FileInputStream("C:\\Users\\TEST\\Desktop\\TestData\\Data.xls"))

def rowcount =0
def srcSheet= srcBook.getSheetAt(0)
def sourceRow = srcSheet.getRow(rowCounter)

While(rowCounter <= srcSheet.getLastRowNum())
{
sourceRow = srcSheet.getRow(rowCounter)
log.info sourceRow.getCell(0).getStringCellValue()
log.info sourceRow.getCell(2).getStringCellValue()
log.info sourceRow.getCell(3).getStringCellValue()

rowCounter++
}

writing or creating excel using POI : 

import org.apache.poi.hssf.usermodel.*

def reportBook = new HSSFWorkbook()
def reportSheet= reportBook.createSheet("Result Reporting")

def currentRow= reportSheet.createRow((short)0)

currentRow.createCell(0).setCellValue("Column0")
currentRow.createCell(1).setCellValue("Column1")
currentRow.createCell(2).setCellValue("Column2")
currentRow.createCell(3).setCellValue("Column3")

def currentRow= reportSheet.createRow((short)1)

currentRow.createCell(0).setCellValue("val0")
currentRow.createCell(1).setCellValue("val1")
currentRow.createCell(2).setCellValue("val2")
currentRow.createCell(3).setCellValue("val3")

def out = new FileOutputStream("C:\\Users\\rsioj\Desktop\\report.xls")

reportBook.write(out)

Hope this helps !

Thanks, 

Rajesh k Singh

Ramachandran, 

Did Rajesh's recommendations help you out regarding this issue?

Regards,

Reid

Hi Reid,

Yes , its worked for me.

Thanks 

Ram

Hi All,

Thanks a lot for your support and help. Since am new to DevTest , your support helped me to complete my task.

Thanks again. 

Also am placing the code which is worked for me for others reference.

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
FileInputStream fis;
FileOutputStream fos;
XSSFWorkbook wb;
File excel;
XSSFCell cell;
XSSFRow row;
excel = new File("C:\\Users\\ywtyty\\Desktop\\Try.xlsx");
XSSFSheet ws = null;
fis = new FileInputStream(excel);
wb = new XSSFWorkbook(fis);
ws = wb.getSheet("Sheet1");
int r = 0;
int c = 0;
cell = null;
for(r=0;r<13;r++){
row = ws.getRow(r);
cell = row.getCell(c);
cell.setCellValue("Ram");
c = c++;
}
fos = new FileOutputStream(excel);
wb.write(fos);
wb.close();

Regards

Ram