AnilKumar_9
Occasional Contributor
Joined 5 years ago
User Profile
User Widgets
Contributions
Re: Unable to read data from excel getting org.codehaus.groovy.control.MultipleCompilationErrorsExceptio
HimanshuTayal 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()); }Re: Unable to read data from excel getting org.codehaus.groovy.control.MultipleCompilationErrorsExceptio
HimanshuTayal 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.Re: Unable to read data from excel getting org.codehaus.groovy.control.MultipleCompilationErrorsExceptio
HimanshuTayal 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: 59Unable to read data from excel getting org.codehaus.groovy.control.MultipleCompilationErrorsExceptio
I am trying to read the data from excel.xslx file and i am facing multiple error could some one help in resolving the issue. Error Mgs: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: General error during class generation: java.lang.NoClassDefFoundError: Unable to load class org.apache.poi.xssf.usermodel.XSSFWorkbook due to missing dependency Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorkbook; java.lang.RuntimeException: java.lang.NoClassDefFoundError: Unable to load class org.apache.poi.xssf.usermodel.XSSFWorkbook due to missing dependency Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorkbook; at org.codehaus.groovy.control.CompilationUnit.convertUncaughtExceptionToCompilationError(CompilationUnit.java:1091) at org.codehaus.groovy.control.CompilationUnit.applyToPrimaryClassNodes(CompilationUnit.java:1069) at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:591) at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:569) at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:546) at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:298) at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:268) at groovy.lang.GroovyShell.parseClass(GroovyShell.java:694) at groovy.lang.GroovyShell.parse(GroovyShell.java:706) at groovy.lang.GroovyShell.parse(GroovyShell.java:742) at groovy.lang.GroovyShell.parse(GroovyShell.java:733) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.compile(SoapUIGroovyScriptEngine.java:136) at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:87) at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141) at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.lang.NoClassDefFoundError: Unable to load class org.apache.poi.xssf.usermodel.XSSFWorkbook due to missing dependency Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorkbook; at org.codehaus.groovy.vmplugin.v5.Java5.configureClassNode(Java5.java:391) at org.codehaus.groovy.ast.ClassNode.lazyClassInit(ClassNode.java:265) at org.codehaus.groovy.ast.ClassNode.getUnresolvedSuperClass(ClassNode.java:1004) at org.codehaus.groovy.ast.ClassNode.getUnresolvedSuperClass(ClassNode.java:999) at org.codehaus.groovy.ast.ClassNode.getSuperClass(ClassNode.java:993) at org.codehaus.groovy.ast.ClassNode.isDerivedFrom(ClassNode.java:936) at org.codehaus.groovy.classgen.asm.InvocationWriter.castToNonPrimitiveIfNecessary(InvocationWriter.java:858) at org.codehaus.groovy.classgen.asm.OperandStack.doConvertAndCast(OperandStack.java:347) at org.codehaus.groovy.classgen.asm.OperandStack.doGroovyCast(OperandStack.java:282) at org.codehaus.groovy.classgen.asm.BinaryExpressionHelper.evaluateEqual(BinaryExpressionHelper.java:400) at org.codehaus.groovy.classgen.asm.BinaryExpressionHelper.eval(BinaryExpressionHelper.java:84) at org.codehaus.groovy.classgen.AsmClassGenerator.visitBinaryExpression(AsmClassGenerator.java:638) at org.codehaus.groovy.ast.expr.BinaryExpression.visit(BinaryExpression.java:51) at org.codehaus.groovy.classgen.asm.StatementWriter.writeExpressionStatement(StatementWriter.java:607) at org.codehaus.groovy.classgen.asm.OptimizingStatementWriter.writeExpressionStatement(OptimizingStatementWriter.java:357) at org.codehaus.groovy.classgen.AsmClassGenerator.visitExpressionStatement(AsmClassGenerator.java:620) at org.codehaus.groovy.ast.stmt.ExpressionStatement.visit(ExpressionStatement.java:42) at org.codehaus.groovy.classgen.asm.StatementWriter.writeBlockStatement(StatementWriter.java:84) at org.codehaus.groovy.classgen.asm.OptimizingStatementWriter.writeBlockStatement(OptimizingStatementWriter.java:158) at org.codehaus.groovy.classgen.AsmClassGenerator.visitBlockStatement(AsmClassGenerator.java:566) at org.codehaus.groovy.ast.stmt.BlockStatement.visit(BlockStatement.java:71) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClassCodeContainer(ClassCodeVisitorSupport.java:104) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitConstructorOrMethod(ClassCodeVisitorSupport.java:115) at org.codehaus.groovy.classgen.AsmClassGenerator.visitStdMethod(AsmClassGenerator.java:430) at org.codehaus.groovy.classgen.AsmClassGenerator.visitConstructorOrMethod(AsmClassGenerator.java:387) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitMethod(ClassCodeVisitorSupport.java:126) at org.codehaus.groovy.classgen.AsmClassGenerator.visitMethod(AsmClassGenerator.java:507) at org.codehaus.groovy.ast.ClassNode.visitContents(ClassNode.java:1086) at org.codehaus.groovy.ast.ClassCodeVisitorSupport.visitClass(ClassCodeVisitorSupport.java:53) at org.codehaus.groovy.classgen.AsmClassGenerator.visitClass(AsmClassGenerator.java:233) at org.codehaus.groovy.control.CompilationUnit$16.call(CompilationUnit.java:813) at org.codehaus.groovy.control.CompilationUnit.applyToPrimaryClassNodes(CompilationUnit.java:1055) ... 16 more 1 error My 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()); } My Scenario is to read data from excel and load it to Property step. Any help appreciated. Regards, Anil Kumar
