Forum Discussion
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.
- HimanshuTayal5 years agoCommunity Hero
Place xmlbeans jar into ext folder and also check which version is placed in lib folder.
- AnilKumar_95 years agoOccasional Contributor
I have placed suggested jar in ext folder and i am facing this error now
java.lang.NoSuchMethodError: org.apache.xmlbeans.XmlOptions.setEntityExpansionLimit(I)Lorg/apache/xmlbeans/XmlOptions; error at line: 54
i have xmlbeans jar as well in my extent folder but still facing same error and i have attached placed jar list.
Below mentioned i have added code:
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());
}
- HimanshuTayal5 years agoCommunity Hero
AnilKumar_9 : Make sure XMLBeans jar version is same in both Ext and Ant folder and also make sure no older version jar is there.
Below is my code
import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.usermodel.CellType.*; FileInputStream fis = new FileInputStream("F:/temp.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); int r = sheet.getPhysicalNumberOfRows(); for(int i=1; i<=r; i++){ Row row = sheet.getRow(0); int col = row.getLastCellNum(); for(int j = 0 ; j < col ; j++) { Cell row_data = row.getCell(j); CellValue cell = evaluator.evaluate(row_data); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: log.info(cell.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: log.info(cell.getNumberValue()); break; case Cell.CELL_TYPE_STRING: log.info(cell.getStringValue()); break; case Cell.CELL_TYPE_BLANK: log.info(""); break; case Cell.CELL_TYPE_ERROR: break; case Cell.CELL_TYPE_FORMULA: // Evaluting cell CellValue c=evaluator.evaluate(row_data); log.info(c.getNumberValue()); break; } } }
have a look at my ext and lib folder.
Hope this will help you..