HimanshuTayal Yeah i have placed POI jar in Ext folder and i am using 4.1.2 POI version.
AnilKumar_9 : Add ooxml-schemas-1.4 in your ext folder.
Hope it will resolve your issue
I have place suggested jar in ext folder and i am getting other error
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF) error at line: 59
i have update my script with XSSF for xlsx type and i am facing other error even-though all the jar are place and replaced script in suggested way.
Error : java.lang.NoSuchMethodError: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import java.text.DateFormat;
import java.util.Date;
import java.text.*;
String filePath = testRunner.testCase.getPropertyValue("FilePath")
String sheetName = testRunner.testCase.getPropertyValue("SheetName")
FileInputStream inputStream = new FileInputStream(filePath);
XSSFWorkbook workbook = null;
XSSFRow row;
XSSFCell cell;
int currentRowNumber = testRunner.testCase.getPropertyValue("CurrentRowNumber").toInteger()
currentRowNumber = currentRowNumber
testRunner.testCase.setPropertyValue("CurrentRowNumber",currentRowNumber.toString())
workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheet(sheetName);
//int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
int colCount = sheet.getRow(0).getLastCellNum();
for(int i=0;i<colCount;i++)
{
String colName,colValue;
row=sheet.getRow(0);
cell=row.getCell(i);
if(cell.getCellType()==CellType.STRING)
colName = cell.getStringCellValue();
testRunner.testCase.testSteps['Properties'].setPropertyValue(colName, getCellData(sheet,currentRowNumber,i).trim())
}
String getCellData(XSSFSheet sheet,int rowNo,int colNo)
{
row=sheet.getRow(rowNo);
cell=row.getCell(colNo);
if(cell==null)
return "";
else if (cell == "")
return "";
else if(cell.getCellType()==CellType.STRING)
return cell.getStringCellValue();
else if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA )
{
String cellText = new java.text.DecimalFormat("0").format( cell.getNumericCellValue() )
if (HSSFDateUtil.isCellDateFormatted(cell))
{
Date date = cell.getDateCellValue();
SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
String formatted = format1.format(date)
log.info formatted
cellText = formatted
log.info cellText
}
return cellText;
}
else if(cell.getCellType()==CellType.BLANK)
return "";
else
return String.valueOf(cell.getBooleanCellValue());
}
mentioned updated script and i have attached screenshot for jars.
