Apache-poi decimal formatting is being applied only after selection

Question

Could you please suggest on next case?

I`ve set up dataformat and cell type ("#,###.00", NUMERIC) (I want thounsand separator plus two decimal numbers)

It works as I expected but to have formatted cells I need to select them first Before selection data looks not formatted

In other words I have to select cell so that it is formatted, otherwise it stays without any formatting

CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.RIGHT);
style.setLocked(locked);
style.setDataFormat(workbook.createDataFormat().getFormat("#,###.00"));

cell.setCellStyle(style);
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(<big decimal value>.toString());

Answer 1

Simply do not set cell value as string if you need a numeric cell value. If you set cell value as String, then the cell type also will be string. This is independent of setting CellType before setting cell value. While setting a String cell value the type changes to string always.

See API documentation which shows that Cell.setCellType is deprecated and what Cell.setCellValue methods are possible.

You needs setting a double cell value if cell shall have numeric content.

Example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.math.BigDecimal;
import java.util.Random;

class CreateExcelCellNumberFormat {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   CellStyle style = workbook.createCellStyle();
   style.setDataFormat(workbook.createDataFormat().getFormat("#,###.00"));

   Sheet sheet = workbook.createSheet();

   for (int r = 0; r < 10; r++) {
    Cell cell = sheet.createRow(r).createCell(0);
    cell.setCellStyle(style);

    BigDecimal bigDecimal = new BigDecimal(new Random().nextDouble() * 10000000000000d);
    cell.setCellValue(bigDecimal.doubleValue());

   }

   sheet.setColumnWidth(0, 25 * 256);

   workbook.write(fileout);
  }
 }
}