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());

Please show the code you are using to create and set the cell style as well as to set the cell value. Without code no help is possible since thousands of `apache poi`users are doing what you are describing without having the problems you are describing. — Axel Richter, May 24 '19 at 14:06
The code only shows how you create and set the cell style. You still have not shown how you set the cell value. Please show [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). — Axel Richter, May 26 '19 at 13:04

Axel Richter · Accepted Answer · 2019-05-26T13:52:27.623

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

Apache-poi decimal formatting is being applied only after selection

1 Answers1