I am getting Null Pointer Exception when Accessing the Cell Which has the Value?

Question

I am using the Apache POI Library to get the data from EXCEL Sheet. I have attached the EXCEL Sheet which has the yellow part highlighted. I am trying to extract the all the data from EXCEL Sheet but I am not getting the data from the highlighted part. It gives the null pointer exception when trying to access these cells.

SAMPLE Document : Document

SAMPLE Code.

FileInputStream inputStream = new FileInputStream(file);

        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet firstSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();

        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                System.out.print(cell.getStringCellValue());
                System.out.print(",");
            }
            System.out.println();
        }

        workbook.close();
        inputStream.close();

When you run the above program you will get some fields are not extracted from the excel sheet(Highlighted Part). When you explicitly try to access those cells you will get the null pointer exception.

Possible duplicate of [What is a NullPointerException, and how do I fix it?](https://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) — Joe C, Nov 24 '17 at 06:15
@JoeC I know what is NullPointerException. Please read the question carefully. The cell contains String value but when I am trying to access it gives null pointer exception. Apache Poi gives null pointer exception when the cell does not contain the value. — Tyson, Nov 24 '17 at 06:19
Iterator iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); for (int i = 0; i < 4; i++) { Cell cell = nextRow.getCell(CellReference.convertColStringToIndex(column[i])); System.out.print(cell.getStringCellValue()); System.out.print(","); } workbook.close(); inputStream.close(); =================== This is the program to access the cell explicitly. i am getting the NPE. — Tyson, Nov 24 '17 at 06:28
Nov 24, 2017 11:56:38 AM formatting_excel.Report_Formatting SEVERE: null java.lang.NullPointerException at formatting_excel.Report_Formatting.(Report_Formatting.java:43) at formatting_excel.TASK.call(TASK.java:29) at formatting_excel.TASK.call(TASK.java:16) at javafx.concurrent.Task$TaskCallable.call(Task.java:1423) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.lang.Thread.run(Thread.java:745) — Tyson, Nov 24 '17 at 06:30
What code is in `Report_Formatting.‌java` in code line 43? — Axel Richter, Nov 24 '17 at 06:46
You can edit your answer to add your code. Would be easier for everyone that way. — suvartheec, Nov 24 '17 at 07:57

score 0 · Answer 1 · answered Nov 24 '17 at 15:54

Cannot reproduce the behavior. If System.out.print(cell.getStringCellValue()); throws a NPE, then cell must be null. But cell cannot be null according to your code since the Row.cellIterator iterates over cells only which are present and not null.

Have downloaded your SAMPLE.xlsx and used the code from Busy Developers' Guide - Getting the cell contents. This code reads all cells without problems.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileInputStream;

class ReadExcelExampleDataFormatter {

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

  Workbook wb  = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));

  DataFormatter formatter = new DataFormatter();
  FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
    System.out.print(cellRef.formatAsString());
    System.out.print(" - ");
    // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
    String text = formatter.formatCellValue(cell);
    System.out.println(text);
   }
  }
  wb.close();
 }
}

Part of the result (your first yellow range):

A15 - Report
Summary
B15 - Real Estate
C15 - Count
D15 - 3
E15 - 0
F15 - 2
A16 - 
B16 - 
C16 - Balance
D16 - $94,263.00
E16 - $0.00
F16 - $94,263.00
A17 - 
B17 - 
C17 - Current
D17 - 2
E17 - 0
F17 - 2
A18 - 
B18 - 
C18 - Delinquent
D18 - 0
E18 - 0
F18 - 0

I am getting Null Pointer Exception when Accessing the Cell Which has the Value?

SAMPLE Code.

1 Answers1