I am trying to read an excel file and execute my test case accordingly, now the error I am getting is java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell Here is my implementation of the code: You might notice that I have user String.valueOf(); but to no avail.
public static List<Map<String, String>> getTestDetails() { List<Map<String, String>> list = null; FileInputStream fileInputStream = null; try { fileInputStream = new FileInputStream(FrameworkConstants.getExcelsheetspath()+"TestCasesToBeExecuted.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbook.getSheet("RunManager"); int lastRowNum = sheet.getLastRowNum(); int lastColNum = sheet.getRow(0).getLastCellNum(); Map<String, String> map = null; list = new ArrayList<Map<String,String>>(); for(int i=1; i<lastRowNum; i++) { map = new HashMap<String, String>(); for(int j=0; j<lastColNum; j++) { String key = sheet.getRow(0).getCell(j).getStringCellValue(); System.out.println("Value of key:: "+key); String value = String.valueOf(sheet.getRow(i).getCell(j).getStringCellValue()); System.out.println("Value of value:: "+value); map.put(key, value); } list.add(map); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } if(Objects.nonNull(fileInputStream)) { try { fileInputStream.close(); } catch (IOException e) { e.printStackTrace(); } } return list; }
And here is my code for method call:
public List<IMethodInstance> intercept(List<IMethodInstance> methods, ITestContext context) { List<Map<String, String>> list = ExcelUtils.getTestDetails(); List<IMethodInstance> result = new ArrayList<IMethodInstance>(); for(int i=0; i<methods.size(); i++) { for(int j=0; j<list.size(); j++) { if(methods.get(i).getMethod().equals(list.get(j).get("Test Name"))) { if(list.get(j).get("Execute").equalsIgnoreCase("yes")) { methods.get(i).getMethod().setDescription(list.get(j).get("Test Description")); methods.get(i).getMethod().setInvocationCount(Integer.parseInt(list.get(j).get("Count"))); methods.get(i).getMethod().setPriority(Integer.parseInt(list.get(j).get("Priority"))); result.add(methods.get(i)); } } } } return result; }
My imports are as follows
import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Objects; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.util.NumberToTextConverter; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.ss.usermodel.Cell;
Advertisement
Answer
You can have check of Cell_Type
before getting the cell value.
String value = null; if (Sheet.getRow(i).getCell(0).getCellType() == Cell.CELL_TYPE_NUMERIC) { value = NumberToTextConverter.toText(Sheet.getRow(i).getCell(0).getNumericCellValue()); } else value = Sheet.getRow(i).getCell(0).getStringCellValue(); }
Imports:
import org.apache.poi.ss.util.NumberToTextConverter; import org.apache.poi.ss.usermodel.Cell;
Maven dependencies:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency>
Remove the below import
import org.apache.poi.ss.usermodel.CellType;