JAVA Apache POI Custom Format

Question

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"

Answer 1

You could try this

           row = sheet.getRow(RowNo);
           // RowNo on which Row cell should have appeared
           cell = row.getCell(CellNo);
           // CellNo on which Position cell should have appeared on Row
           cell.setCellValue("031642153283700100");

  // You could also get CellFormula by using cell.getCellFormula();

Answer 2

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) {
  }
 }
}