Search code examples
apache-poilocale

Apache POI > handling Special formats?

I am looking into generating an xlsx file, and, for certain cells, apply a "Special" (that is the exact word used in Excel, in Format Cells > Category) category with a specific locale. For instance, my local installation of Excel comes with "Social Insurance Number" for the locale "English (Canada)".

I have checked the POI API, Googled a bit and I am puzzled about how to do that. I have tried creating such cells manually (using Excel directly) then read them using POI.
If I apply getCellStyle().getDataFormat() to my cell, I am returned values equal or superior to 164. Which I guess means it is considered as something user-defined, since POI org.apache.poi.ss.usermodel.BuiltinFormats#FIRST_USER_DEFINED_FORMAT_INDEX constant is 164.

Is what am trying to do achievable at all ? I do not even know where are Excel's Special types defined generally speaking. These do seem to be built-in.

Solution

  • All Excel number formats based on special format pattern. See How to control and understand settings in the Format Cells dialog box in Excel.

    To get what exact format patterns are needed for special formats one can apply the special format to a cell and then have a look at the corresponding custom format then.

    The following is the special format "Social Insurance Number (CH)" - Switzerland:

    enter image description here This is German Excel "Sonderformat" is "Special".

    This corresponds to the custom format 000\.00\.000\.000. You can deduct that by simply changing to category Custom in Excel's dialog Format Cells - Number. Then read the pattern from text field below Type::

    enter image description here This is German Excel "Benutzerdefiniert" is "Custom".

    So the general way is: First choose your special format in Excel's dialog Format Cells - Number. Then change the category to Custom and read the corresponding pattern from the text field below Type:.

    If you have the number format pattern, this can be used using apache poi as follows:

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

class CreateExcelNumberFormat {

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

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

   DataFormat format = workbook.createDataFormat();
   CellStyle specialSIN = workbook.createCellStyle();
   specialSIN.setDataFormat(format.getFormat("000\\.00\\.000\\.000"));

   Sheet sheet = workbook.createSheet(); 

   Cell cell = sheet.createRow(0).createCell(0);
   cell.setCellStyle(specialSIN);
   cell.setCellValue(12345678901d);

   sheet.setColumnWidth(0, 14*256);

   workbook.write(fileout);
  }

 }
}

    Your Social Insurance Number for the locale English (Canada) would must be:

    ...
specialSIN.setDataFormat(format.getFormat("000 000 000"));
...
cell.setCellValue(46454286);
...