Read excel (xlsx) file in java using Apoche POI (getting weird numbers)

Question

I have to open a testdate for a schoolproject. This testdate is a excel(xlsx) file that includes number, letter, date and time. But with the code below it reads the excel file, but i get weird numbers like 42538.0 and 1.153481443E9. after date.

public class Page4_Controller implements Initializable {

    @FXML
    private Button button1;

    public void Button1Action(ActionEvent event) throws IOException {
        //Initialize excel file
        FileChooser fc = new FileChooser();
        fc.getExtensionFilters().addAll(
                new ExtensionFilter("Excel Files", "*.xlsx"));
        File selectedFile = fc.showOpenDialog(null);

        // Read file
        readXLSXFile(selectedFile.getAbsolutePath());
    }

    public static void readXLSXFile(String excelFilePath) throws IOException {
        FileInputStream fys = new FileInputStream(new File(excelFilePath));

        //Create workbook instance that refers to .xlsx file
        XSSFWorkbook wb = new XSSFWorkbook(fys);

        //Create a sheet object to retrive the sheet
        XSSFSheet sheet = wb.getSheetAt(0);

        //That is for evalueate the cell type
        FormulaEvaluator forlulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
        DataFormatter df = new DataFormatter();

        //Default data for database (comes later)
        //String airport = sheet.getRow(1).getCell(1).getStringCellValue();
        //Date date = sheet.getRow(2).getCell(1).getDateCellValue();

        for (Row row : sheet) {
            for (Cell cell : row) {
                switch (forlulaEvaluator.evaluateInCell(cell).getCellType()) {
                    //If cell is a numeric format
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        System.out.print(df.formatCellValue(cell) + "\t");
                        break;
                    //If cell is a string format
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getRichStringCellValue() + "\t");
                        break;
                }

            }
            System.out.println();
        }
    }

    @Override
    public void initialize(URL location, ResourceBundle resources) {
    }

}

42538.0 could be a date (2016-06-17 00:00). You need to check if cell type is DATE and if so handle accordingly. — Stefan, Dec 08 '17 at 13:24
The date is written like this: Date Found: 17-Jun-2016 and the cell type is date. — Ömer Uyar, Dec 08 '17 at 13:30

Luke · Answer 1 · 2017-12-13T08:56:42.883

Dates are stored internally in Excel as double values and DataFormatter.formatCellValue(Cell cell) returns the formatted value of a cell as a String regardless of the cell type. So when you call df.formatCellValue(cell) you are returning as a string the double value of the cell.

To print the cell value as a date the code is this:

switch (cell.getCellType()) {
    // ...
    case CellType.NUMERIC:
        // Check if a cell contains a date. Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.
        if (DateUtil.isCellDateFormatted(cell)) {
            // Get the value of the cell as a date. Returns a java.util.Date.
            System.out.println(cell.getDateCellValue());
        } else {
            System.out.println(cell.getNumericCellValue());
        }
    // ...
}

Source:

EDIT

Excel cells containing only time have their date part fixed to 31 december 1899. You can use this information to check if a cell is time or date: extract the year, if it is 1899 it is a time cell.

SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");
SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");

switch (cell.getCellType()) {

  case CellType.NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
        Date dateCellValue = cell.getDateCellValue();
        String year = yearFormat.format(dateCellValue);
        if (year.equals("1899")) 
          System.out.println(timeFormat.format(dateCellValue));
        else
          System.out.println(dateFormat.format(dateCellValue));
    } else {
        System.out.println(cell.getNumericCellValue());
    }

}

I am getting this with your code: Date: Sat Sep 10 00:00:00 CEST 2016, and what i want to get is this: Date: 10-sep-2016 but what i am getting is this: Date: 42623.0 10-sep-2016 with my code above. — Ömer Uyar, Dec 08 '17 at 13:54
You are getting the right date then, you just need to format it as you want. You can use SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); System.out.println(sdf.format(cell.getDateCellValue())); — Luke, Dec 08 '17 at 14:07
Thank you it worked to read the date like i want it to read, but now it reads TIME for example 19:25 like a date too. So it has to be 19:25 but it gives me 31-dec-1899. — Ömer Uyar, Dec 13 '17 at 00:44

score 0 · Answer 2 · answered Dec 08 '17 at 13:30

0

Dates in Excel are represented as a "serial number", where the part in front of the decimal separator are the days since 1-1-1900, and the part after the decimal is the fraction of the day, so noon is 0.5. So if you get the number itself, you have to do the conversion manually.

POI can do the conversion for you. See How to read Excel cell having Date with Apache POI? for an example.

answered Dec 08 '17 at 13:30

SurfMan

1,685
12
19

My english is not so good so and i am new to programming, can you change it in my code and post it. Would be really appreciated :) – Ömer Uyar Dec 08 '17 at 13:37
1

Nope. The link I posted has a great example of how to do it. Edit: code does not format here. See the accepted answer in the linked post. – SurfMan Dec 08 '17 at 13:45
The link you posted has nothing to do with my code, it sais for specific rows but that is not what i want ..... – Ömer Uyar Dec 08 '17 at 13:58
It does exactly what you need. It takes a Cell object, which you already have. Look at the accepted answer. Or see @Luke's answer. – SurfMan Dec 08 '17 at 14:06

score 0 · Answer 3 · edited Dec 12 '17 at 18:19

0

This is the excel file that I have to read in java

edited Dec 12 '17 at 18:19

Sanchit Patiyal

4,910
1
14
31

answered Dec 08 '17 at 13:35

Ömer Uyar

21
1
6

score 0 · Answer 4 · edited Dec 08 '22 at 06:05

0

Its because exponential numbers. try with this.

BigInteger x = new BigDecimal("406770000244E+12").toBigInteger();

edited Dec 08 '22 at 06:05

Suraj Rao

29,388
11
94
103

answered Dec 08 '22 at 06:02

Krishan Chamara

1

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Jordan Dec 12 '22 at 13:24

Read excel (xlsx) file in java using Apoche POI (getting weird numbers)

4 Answers4