Apache POI > Unable to Read Excel Sheet

Question

Iam successfully able to write data to Excel sheet
When i use the code to access another sheet within the Excel document it dosnt work

Following code Works:

        priceband = p.getProperty("priceband");
    if(priceband.contains("nationala")) {
        ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData,"NationalA");
        ExcelUtils.setCellData("example22222", 1, 1);
    }

Following code dosnt work when trying to access the other worksheet within the same workbook, i get a null pointer exception when running the code:

    if(priceband.contains("nationala")) {
    ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData,"NationalB");
    ExcelUtils.setCellData("example22222", 1, 1);
}

public class ExcelUtils {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Cell;
private static XSSFRow Row;
public FileInputStream fis = null;
public FileOutputStream fileOut = null;
public static String priceband;

// This method is to set the File path and to open the Excel file, Pass
// Excel Path and Sheetname as Arguments to this method
public static void setExcelFile(String Path, String SheetName) throws Exception {
    try {
        // Open the Excel file
        FileInputStream ExcelFile = new FileInputStream(Path);
        // Access the required test data sheet
        ExcelWBook = new XSSFWorkbook(ExcelFile);
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
    } catch (Exception e) {
        throw (e);
    }
}

// This method is to read the test data from the Excel cell, in this we are
// passing parameters as Row num and Col num
public static String getCellData(int RowNum, int ColNum) throws Exception {
    try {
        Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
        String CellData = Cell.getStringCellValue();
        return CellData;
    } catch (Exception e) {
        return "";
    }
}

// This method is to write in the Excel cell, Row num and Col num are the
// parameters
public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {
    try {
        Row = ExcelWSheet.getRow(RowNum);
        Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
        if (Cell == null) {
            Cell = Row.createCell(ColNum);
            Cell.setCellValue(Result);
        } else {
            Cell.setCellValue(Result);
        }

        // Constant variables Test Data path and Test Data file name
        FileOutputStream fileOut = new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);
        ExcelWBook.write(fileOut);
        fileOut.flush();
        fileOut.close();

    } catch (Exception e) {
        throw (e);
    }
}

public static void setupExcelPriceband() throws Exception {
    Properties p = new Properties();
    FileInputStream fi = new FileInputStream("C:\\Users\\gpb7642\\Desktop\\PhAutomationFramework\\PhFramework\\src\\main\\java\\PhFramework\\testData\\Setup.properties");
    p.load(fi);
    priceband = p.getProperty("priceband");
    if(priceband.contains("nationala")) {
        ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData,"NationalA");
        ExcelUtils.setCellData("example22222", 1, 1);
    }
    else if (priceband.contains("nationalb")) {
        ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData, "NationalB");
        ExcelUtils.setCellData("df", 1, 1);
    }else {
    }
}

}

Excel file

use XSSFSheet st=wb.getSheetAt(1); 1 indicates second sheet of the workbook — Ravi Potnuru
//New Method XSSFSheet st = ExcelWBook.getSheetAt(1); ExcelUtils.setCellData("lol", 1, 1); — Gbru
No need to create new method instead you can add setCellType(cell.CELL_TYPE_STRING); before setting cell value. you can change cell type accordingly — Ravi Potnuru

Gbru Gbru · Accepted Answer · 2016-10-03T13:44:32

@Potnuru Ravi thanks for your help

The following code seems to have done the trick:

public void setCellData(String sheetName, int colNum, int rowNum, String str) {
    int index = workbook.getSheetIndex(sheetName);
    sheet = workbook.getSheetAt(index);
    row = sheet.getRow(rowNum);
    cell = row.createCell(colNum);
    cell.setCellValue(str);
    try {
        fileOut = new FileOutputStream(path);

        try {
            workbook.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
}

Apache POI > Unable to Read Excel Sheet

1 Answers