JAVA Apache POI Custom Format

I am Using poi version : 3.14

I access an Excel (.xlsx) file

this.workbook = WorkbookFactory.create(new FileInputStream(f.getPath()));
this.workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
this.sheet = workbook.getSheetAt(0);

Here is a custom format found in .xlsx file applied to a cell. I want to display the cell value in my code.

As you can see the cell value visible in file is "031642153283700100". Also when i clic on this particular cell, value is changing to "42153283700100" (data without custom format applied on).

EDIT

Original cell type is CELL_TYPE_NUMERIC.

==>

How can i display the formatted value "031642153283700100" in java code ?

I tried :

cell.toString() => "42153283700100"
cell.getNumericCellValue() => "42153283700100"

With previous cell type conversion :

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.getStringCellValue() => "42153283700100"
cell.getRichStringCellValue() => "42153283700100"
Old HSSFDataFormatter with formatCellValue(cell) method => "421532837001000316"

Solution

For number formats you should use CellGeneralFormatter or CellNumberFormatter.

Example:

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

import org.apache.poi.ss.format.*;

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

class ReadExcelWithFormats {

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

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

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {

    for (Cell cell : row) {

     String formatstring = cell.getCellStyle().getDataFormatString();
     System.out.println(formatstring);

     switch (cell.getCellType()) {

      //...

      case Cell.CELL_TYPE_NUMERIC:
       double cellvalue = cell.getNumericCellValue();

       System.out.println(cellvalue);

       String formattedvalue = "";

       if ("general".equals(formatstring.toLowerCase())) {
        formattedvalue = new CellGeneralFormatter().format(cellvalue);
       } else {
        formattedvalue = new CellNumberFormatter(formatstring).format(cellvalue);
       }

       System.out.println(formattedvalue);

      break;

      //...

      default:
       System.out.println();
     }
    }
   }

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

Edit

OK, let's have a more general example. The above will not work with dates (what I had known already) but also not with all number formats. The latter I thought the CellNumberFormatter should do but it does not. Unfortunately it will even produce errors with some proper number formats.

In Excel a number format can contain up to 4 parts delimited with semicolon. The first part is for numbers greater than 0, the second part is for numbers lower than 0, the third part is for numbers equal 0 and the fourth part is for text.

format > 0;format < 0;format = 0;text

Since the CellNumberFormatter does not handle this properly, we should do it before using the CellNumberFormatter.

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

import org.apache.poi.ss.format.*;

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

import java.util.Date;

class ReadExcelWithFormats {

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

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

   Sheet sheet = wb.getSheetAt(0);

   for (Row row : sheet) {

    for (Cell cell : row) {

     String formatstring = cell.getCellStyle().getDataFormatString();
     System.out.println(formatstring);

     switch (cell.getCellType()) {

      //...

      case Cell.CELL_TYPE_NUMERIC:

       String formattedvalue = "";
       String[] formatstringparts = formatstring.split(";");

       if (DateUtil.isCellDateFormatted(cell)) {

        Date date = cell.getDateCellValue();
        System.out.println(date);

        String dateformatstring = "";
        if (cell.getCellStyle().getDataFormat() == 14) { //default short date without explicit formatting
         dateformatstring = "yyyy-MM-dd"; //default date format for this
        } else if (cell.getCellStyle().getDataFormat() == 22) { //default short datetime without explicit formatting
         dateformatstring = "yyyy-MM-dd hh:mm"; //default datetime format for this
        } else { //other data formats with explicit formatting
         dateformatstring = formatstringparts[0];
        }
        formattedvalue = new CellDateFormatter(dateformatstring).format(date);
       } else {

        double cellvalue = cell.getNumericCellValue();
        System.out.println(cellvalue);

        switch (formatstringparts.length) {
         case 4:
         case 3:
          if (cellvalue > 0) {
           if ("general".equals(formatstringparts[0].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);
           }
          }
          if (cellvalue < 0) {
           if ("general".equals(formatstringparts[1].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[1]).format(cellvalue);
           }
          }
          if (cellvalue == 0) {
           if ("general".equals(formatstringparts[2].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[2]).format(cellvalue);
           }
          }
         break;
         case 2:
          if (cellvalue >= 0) {
           if ("general".equals(formatstringparts[0].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);
           }
          }
          if (cellvalue < 0) {
           if ("general".equals(formatstringparts[1].toLowerCase())) {
            formattedvalue = new CellGeneralFormatter().format(cellvalue);
           } else {
            formattedvalue = new CellNumberFormatter(formatstringparts[1]).format(cellvalue);
           }
          }
         break;
         default:
          if ("general".equals(formatstringparts[0].toLowerCase())) {
           formattedvalue = new CellGeneralFormatter().format(cellvalue);
          } else {
           formattedvalue = new CellNumberFormatter(formatstringparts[0]).format(cellvalue);
          }    
         }

       }

       System.out.println(formattedvalue);

      break;

      //...

      default:
       System.out.println();
     }
    }
   }

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

Important note:

This code sample is from 2016 and is for apache poi 3.14.

As of the current apache poi versions 3.17 and 4.x.y this should not be used anymore. In current versions this is as simple as:

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

   Sheet sheet = wb.getSheetAt(0);

   DataFormatter formatter = new DataFormatter();

   for (Row row : sheet) {
    for (Cell cell : row) {
     String formattedvalue = formatter.formatCellValue(cell);
     System.out.println("Using DataFormatter: " + formattedvalue);
    }
   }
...