ERROR: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.iterator()" because "utilities.ExcelUtility.ExcelWSheet" is null
at utilities.ExcelUtility.findCells(ExcelUtility.java:70)
The code:
package utilities;
public class ExcelUtility {
private static XSSFWorkbook ExcelWBook;
private static XSSFSheet ExcelWSheet;
/*
* Set the File path, open Excel file
* @params - Excel Path and Sheet Name
*/
public static void setExcelFile(String path, String sheetName) throws Exception {
try {
// Open the Excel file
FileInputStream ExcelFile = new FileInputStream(path);
// Access the excel data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(sheetName);
} catch (Exception e) {
throw (e);
}
}
public static String[][] getTestData(String tableName) {
String[][] testData = null;
try {
// Handle numbers and strings
DataFormatter formatter = new DataFormatter();
XSSFCell[] boundaryCells = findCells(tableName);
XSSFCell startCell = boundaryCells[0];
XSSFCell endCell = boundaryCells[1];
int startRow = startCell.getRowIndex() + 1;
int endRow = endCell.getRowIndex() - 1;
int startCol = startCell.getColumnIndex() + 1;
int endCol = endCell.getColumnIndex() - 1;
testData = new String[endRow - startRow + 1][endCol - startCol + 1];
for (int i=startRow+1; i<endRow; i++) {
for (int j=startCol+1; j<endCol; j++) {
// testData[i-startRow][j-startCol] = ExcelWSheet.getRow(i).getCell(j).getStringCellValue();
Cell cell = ExcelWSheet.getRow(i).getCell(j);
testData[i - startRow][j - startCol] = formatter.formatCellValue(cell);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return testData;
}
public static XSSFCell[] findCells(String tableName) {
DataFormatter formatter = new DataFormatter();
String pos = "begin";
XSSFCell[] cells = new XSSFCell[2];
for (Row row : ExcelWSheet) {
for (Cell cell : row) {
// if (tableName.equals(cell.getStringCellValue())) {
if (tableName.equals(formatter.formatCellValue(cell))) {
if (pos.equalsIgnoreCase("begin")) {
cells[0] = (XSSFCell) cell;
pos = "end";
} else {
cells[1] = (XSSFCell) cell;
}
}
}
}
return cells;
}
}
I wanted to use the variables to test username a password from my excel file but seems I cant even open it. I checked the file is in the correct path and the code but I cant seem to find the issue please help