Cannot invoke method because the return value is null

Question

I am working on automating some excel task and when running the program, I stumbled across an error that says this:

Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" because the return value of "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" is null at com.excel.auto.ExcelAuto.main(ExcelAuto.java:43)

Below is the code that I have typed so far:

public class ExcelAuto {

    public static void main(String args[]) throws IOException {

        // To get File Path
        Scanner scannerFilePath = new Scanner(System.in);
        System.out.println("Please Enter File Path: ");
        String excelFilePath = scannerFilePath.nextLine();
        FileInputStream inputStream = new FileInputStream(excelFilePath);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);

        // To get Sheet Name
        Scanner scannerSheet = new Scanner(System.in);
        System.out.println("Please Enter Excel Sheet Name: ");
        String excelSheetName = scannerSheet.nextLine();
        XSSFSheet sheet = workbook.getSheet(excelSheetName);


        // To set matching transactions to yellow background
        CellStyle stylePerfectTrades = workbook.createCellStyle();
        stylePerfectTrades.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        stylePerfectTrades.setFillPattern(FillPatternType.SOLID_FOREGROUND);



        DataFormatter df = new DataFormatter();
        for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
            Cell cellTitleWPBO = sheet.getRow(i).getCell(9);
            Cell cellAmountWPBO = sheet.getRow(i).getCell(10);
            Cell cellCheckStatusWPBO = sheet.getRow(i).getCell(6);

            for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {

                Cell cellDescriptionSaxo = sheet.getRow(j).getCell(2);
                Cell cellNetChangeSaxo = sheet.getRow(j).getCell(3);
                Cell cellCheckStatusSaxo = sheet.getRow(j).getCell(5);

                if (df.formatCellValue(cellCheckStatusSaxo).contains("-")) {
                    if (df.formatCellValue(cellDescriptionSaxo).contains(df.formatCellValue(cellTitleWPBO))) {
                        if (df.formatCellValue(cellNetChangeSaxo).contains(df.formatCellValue(cellAmountWPBO))) {
                            cellAmountWPBO.setCellStyle(stylePerfectTrades);
                            cellDescriptionSaxo.setCellStyle(stylePerfectTrades);
                            cellNetChangeSaxo.setCellStyle(stylePerfectTrades);
                            cellTitleWPBO.setCellStyle(stylePerfectTrades);
                            cellCheckStatusSaxo.setCellValue("Checked");
                            cellCheckStatusWPBO.setCellValue("Checked");
                            System.out.println("Equals");
                            break;
                        }
                        // Transaction with same title but not same amount.
                        else {//If cell has a different value as the one in the row below it
                            System.out.println("Not Equals lah");
                        }
                    } else if (df.formatCellValue(cellTitleWPBO).contains("Conversion")) {
                        if (df.formatCellValue(cellDescriptionSaxo).contains("DEPOSIT")) {
                            if (df.formatCellValue(cellNetChangeSaxo).contains(df.formatCellValue(cellAmountWPBO))) {
                                cellAmountWPBO.setCellStyle(stylePerfectTrades);
                                cellDescriptionSaxo.setCellStyle(stylePerfectTrades);
                                cellNetChangeSaxo.setCellStyle(stylePerfectTrades);
                                cellTitleWPBO.setCellStyle(stylePerfectTrades);
                                cellCheckStatusSaxo.setCellValue("Checked");
                                cellCheckStatusWPBO.setCellValue("Checked");
                                System.out.println("Equals (Conversion Deposit)");
                                break;
                            } else {
                                System.out.println("Not Equals Amount(Conversion)");
                            }
                        } else if (df.formatCellValue(cellDescriptionSaxo).contains("WITHDRAWAL")) {
                            if (df.formatCellValue(cellNetChangeSaxo).contains(df.formatCellValue(cellAmountWPBO))) {
                                cellAmountWPBO.setCellStyle(stylePerfectTrades);
                                cellDescriptionSaxo.setCellStyle(stylePerfectTrades);
                                cellNetChangeSaxo.setCellStyle(stylePerfectTrades);
                                cellTitleWPBO.setCellStyle(stylePerfectTrades);
                                cellCheckStatusSaxo.setCellValue("Checked");
                                cellCheckStatusWPBO.setCellValue("Checked");
                                System.out.println("Equals (Conversion Deposit)");
                                break;
                            } else {
                                System.out.println("Not Equals Amount(Conversion)");
                            }
                        } else {
                            System.out.println("Not Equals (Conversion)");
                        }
                    } else if (df.formatCellValue(cellTitleWPBO).contains("stamp duty")) {
                        if (df.formatCellValue(cellDescriptionSaxo).contains("WITHDRAWAL")) {
                            if (df.formatCellValue(cellNetChangeSaxo).contains(df.formatCellValue(cellAmountWPBO))) {
                                cellAmountWPBO.setCellStyle(stylePerfectTrades);
                                cellDescriptionSaxo.setCellStyle(stylePerfectTrades);
                                cellNetChangeSaxo.setCellStyle(stylePerfectTrades);
                                cellTitleWPBO.setCellStyle(stylePerfectTrades);
                                cellCheckStatusSaxo.setCellValue("Checked");
                                cellCheckStatusWPBO.setCellValue("Checked");
                                System.out.println("Equals (stamp duty)");
                                break;
                            } else {
                                System.out.println("Not Equals Amount(stamp duty)");
                            }
                        } else {
                            System.out.println("Not Equals (stamp duty)");
                        }
                    } else if (df.formatCellValue(cellTitleWPBO).contains("Dividend")) {
                        if (df.formatCellValue(cellDescriptionSaxo).contains("Corporate Actions ")) {
                            if (df.formatCellValue(cellNetChangeSaxo).contains(df.formatCellValue(cellAmountWPBO))) {
                                cellAmountWPBO.setCellStyle(stylePerfectTrades);
                                cellDescriptionSaxo.setCellStyle(stylePerfectTrades);
                                cellNetChangeSaxo.setCellStyle(stylePerfectTrades);
                                cellTitleWPBO.setCellStyle(stylePerfectTrades);
                                cellCheckStatusSaxo.setCellValue("Checked");
                                cellCheckStatusWPBO.setCellValue("Checked");
                                System.out.println("Equals (dividend)");
                                break;
                            } else {
                                System.out.println("Not Equals Amount (Dividend)");
                            }
                        } else {
                            System.out.println("Not Equals (Dividend)");
                        }
                    } else if (df.formatCellValue(cellTitleWPBO).contains("Withdrawal")) {
                        if (df.formatCellValue(cellDescriptionSaxo).contains("WITHDRAWAL")) {
                            if (df.formatCellValue(cellNetChangeSaxo).contains(df.formatCellValue(cellAmountWPBO))) {
                                cellAmountWPBO.setCellStyle(stylePerfectTrades);
                                cellDescriptionSaxo.setCellStyle(stylePerfectTrades);
                                cellNetChangeSaxo.setCellStyle(stylePerfectTrades);
                                cellTitleWPBO.setCellStyle(stylePerfectTrades);
                                cellCheckStatusSaxo.setCellValue("Checked");
                                cellCheckStatusWPBO.setCellValue("Checked");
                                System.out.println("Equals (Withdrawal)");
                                break;
                            } else {
                                System.out.println("Not Equals Amount (Withdrawal)");
                            }
                        } else {
                            System.out.println("Not Equals (Withdrawal)");
                        }
                    }


                } else {
                    System.out.println("Checked");
                }
            }

            FileOutputStream outputStream = new FileOutputStream(excelFilePath);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            System.out.println("Done");
        }
    }
}

[_What is a NullPointerException, and how do I fix it?_](https://stackoverflow.com/q/218384/230513) — trashgod, Jun 01 '22 at 18:22

Cheshiremoe · Accepted Answer · 2022-06-02T16:17:47.383

2

Change code blocks like this:

Cell cellTitleWPBO = sheet.getRow(i).getCell(9);
Cell cellAmountWPBO = sheet.getRow(i).getCell(10);
Cell cellCheckStatusWPBO = sheet.getRow(i).getCell(6);

to Code like this:

XSSFRow row = sheet.getRow(i);
if (row != null) {
  Cell cellTitleWPBO = row.getCell(9);
  Cell cellAmountWPBO = row.getCell(10);
  Cell cellCheckStatusWPBO = row.getCell(6);
}

edited Jun 02 '22 at 16:17

answered Jun 01 '22 at 18:48

Cheshiremoe

792
1
5
14

Cannot invoke method because the return value is null

1 Answers1