apache poi formatting issue(accounting)

Question

I am currently working on creating .xlsx file using apache poi. my only issue is when I open my excel it always shows as Custom instead of Accounting. The cell formula or any integer value in cell in excel it shows as Custom instead of Accounting. my code looks good and the cell value are also coming correct as expected but in excel it shows as custom and not accounting. Is there a limitation in apache poi or I am doing something wrong. Any help will be highly appreciated.

For example I want to show this value $88645.00 in cell as Accounting instead of Custom.

Below is my sample code

Decimal2Style = oWorkbookInter.createCellStyle();
Decimal2Style.setFont(TahomaBlackFont);
Decimal2Style.setDataFormat(oWorkbookInter.createDataFormat().getFormat("$#,##0.00"));
SheetRow = null;
SheetCell = null;
SheetRow = oSheetInvoice.getRow(35);
SheetCell = SheetRow.getCell(14);
sCellFormula = "SUM(O22:O33)";
SheetCell.setCellStyle(Decimal2Style);
SheetCell.setCellFormula(sCellFormula);

Answer 1

Only the data formats listed in BuiltinFormats are categorized and will show up as Category in Format Cells dialog box. All other are user defined (custom).

So for category Currency do using builtin formats 0x05 to 0x08. And for category Accounting use builtin formats 0x29 to 0x2C.

Example:

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

class CreateExcelAccountingStyle {

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

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

   CellStyle[] cellStyles = new CellStyle[8];
   int csItem = 0;
   
   //Currency 
   //builtin formats 0x05 to 0x08 -> https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html
   for (int i = 5; i <= 8; i++) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat((short)i); 
    cellStyles[csItem] = cellStyle;
    csItem++;
   }
   //Accounting 
   //builtin formats 0x29 to 0x2C -> https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html
   for (int i = 0x29; i <= 0x2C; i++) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat((short)i); 
    cellStyles[csItem] = cellStyle;
    csItem++;
   }
   
   Sheet sheet = workbook.createSheet();
   
   double value = -1234567.89;
   for (int i = 0; i < cellStyles.length; i++) {
    Row row = sheet.createRow(i);;
    Cell cell = row.createCell(0);;
    cell.setCellValue(value);
    cell.setCellStyle(cellStyles[i]);
   }

   sheet.setColumnWidth(0, 20 * 256);

   workbook.write(fileout);
  }

 }
}