CODEWITHSUNDEEP
javaexcelapache-poi
Abhinav
Abhinav

Reputation: 71

Importing null date from Excel

I have an excel sheet from where I am reading excel cel values and importing to DB

Using Apache POI :

 if (row.getCell(8) != null) {
          setActualDeparture(DateUtil.getJavaDate(row.getCell(8).getNumericCellValue()));
   }

The excel sheet has null on Cell 8 , so it should not import anything . but it takes dates like 1899-12-31T00:00:00

What could be the issue ?

Upvotes: 0

Views: 700

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

The Row.getCell(int cellnum) returns only NULL if there is nothing stored in the file for this cell. It returns the cell if there is something stored in the file for this cell. Even if this is only a NumberFormat for example or any other information for this cell.

But there is a second method Row.getCell(int cellnum, Row.MissingCellPolicy policy) http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Row.html#getCell%28int,%20org.apache.poi.ss.usermodel.Row.MissingCellPolicy%29 which can be used in your case.

Example: enter image description here

There is nothing in A1 but there is a special NumberFormat and not General applied.

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.InputStream;

class GetEmtyCellTest {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("workbook.xlsx");
   Workbook wb = WorkbookFactory.create(inp);

   Sheet sheet = wb.getSheetAt(0);

   Row row = sheet.getRow(0);

   System.out.println(row.getCell(0).getNumericCellValue()); //0.0
   System.out.println(row.getCell(0, Row.RETURN_BLANK_AS_NULL)); //null


   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);
   fileOut.flush();
   fileOut.close();

  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

Upvotes: 2

Related Questions