How to manage decimal values with trailing zeros and plain integer values in excel using apache POI

Question

Facing issue to format below 2 values.

1. 25.25
2. 25

---

If I use #.## format then it works fine for both values. But if I want 25.25 to be displayed as 25.2500 then I have to use #.0000 format and with this format 25 also gets converted to 25.0000. I want to format the cell based on type of Cell value but POI has only NumericCellValue which returns double. How we can achieve if we want to display decimal values with trailing 0's and plain integer values.

Answer 1

Only solution comes in my mind is using conditional formatting.

If a cell contains a number which not equals its integer part (means: is not an integer), then use number format 0.0000 else not. So the condition would be a formula: =INT(A1)<>A1.

But there is not a ConditionalFormattingRule.createNumberFormatting upto now. And using the binary *.xls file format, which is HSSF in apache poi, nobody had detected where conditional number formats are stored at all. So only a XSSF solution is possible.

In Office Open XML (XSSF) conditional numberformats are set in dxf element of styles.xml having numFmt element with attributes numFmtId and formatCode. The numFmtId can be got from XSSFDataFormat.getFormat with given formatCode. This dxf element then gets linked in worksheet's cfRule.

The folowing complete example shows this. It provides a method void createNumberFormatting(XSSFConditionalFormattingRule rule, short numFmtId, String formatCode) to create a numberformatting in a given XSSFConditionalFormattingRule.

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

import java.io.FileOutputStream;

public class ConditionalFormattingNumbers {
    
 static void createNumberFormatting(XSSFConditionalFormattingRule rule, short numFmtId, String formatCode) throws Exception {
  java.lang.reflect.Method getDxf = XSSFConditionalFormattingRule.class.getDeclaredMethod("getDxf", boolean.class);
  getDxf.setAccessible(true);
  org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxf dxf = (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDxf)getDxf.invoke(rule, true);
  if (dxf.getNumFmt() == null) dxf.addNewNumFmt();
  dxf.getNumFmt().setNumFmtId(numFmtId);
  dxf.getNumFmt().setFormatCode(formatCode);
 }
    

 public static void main(String[] args) throws Exception {
  XSSFWorkbook workbook = new XSSFWorkbook(); String filePath = "./ConditionalFormattingNumbers.xlsx";
  
  XSSFDataFormat dataFormat = workbook.createDataFormat();

  XSSFSheet sheet = workbook.createSheet();
  XSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  XSSFConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("INT(A1)<>A1");
  createNumberFormatting(rule, dataFormat.getFormat("0.0000"), "0.0000");
  
  XSSFConditionalFormattingRule[] cfRules = new XSSFConditionalFormattingRule[]{rule};

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A1:A1000")};

  sheetCF.addConditionalFormatting(regions, cfRules);
  
  sheet.createRow(0).createCell(0).setCellValue(123);
  sheet.createRow(1).createCell(0).setCellValue(123.45);
  sheet.createRow(2).createCell(0).setCellValue(45);
  sheet.createRow(3).createCell(0).setCellValue(45.678);
  sheet.createRow(4).createCell(0).setCellValue(678);
  sheet.createRow(5).createCell(0).setCellValue(678.9);

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}