Search code examples
javaexcelapache-poilocalecell

Java Apache POI Excel Numeric Cell value with Locale pt-BR

I want to set the XSSFCell in Excel to display the CellType as numeric, and need it to diplay the value as pt-BR Locale (with . for separator instead of , (not decimal/fractional - but still double). I managed to diplay the correct cell value, but the CellTyle only gets String. I know that double have the US configuration 0.000,00, and a simple NumberFormat with Locale do the trick, but the CellType gets to text, not number.

The number already comes configured with Locale, which comes straight from Database. (Eg: 412.000), but double converts it to US (412,000).

So, how can I format the cell to my Locale and still set the CellType as Numeric ?

This is what I got so far:

    XSSFCell cell = row.createCell(anyCellNumber);
    double value = 412.000; //valueThatComesFromDataBaseWithLocaleFormatted
    cell.setCellType(CellType.NUMERIC);
    //At this point, double has already converted the cell value to 412,000 - So, What I did was:
    cell.setCellValue(String.format("%.3f", value).replace(",", ".")); // Replaced the , to . to match Locale

So, this is the point, the CellType is set, but NumberFormat, DecimalFormat, Locale, String.format, etc, gets the cellvalue to String, and thus the CellType is text.

Can´t use Double.parseDouble(valueFormatted.toString()) because it throws Exception, if the String is Locale formatted.

So, how (if it is possible), can I have a CellType.NUMERIC AND the cell value formatted for my Locale (pt-BR)???

Thanks in advance!

Solution

  • As far as I understand you specifically want your Cell to be of type numeric and your display to be something "not Date" ? The only approach that I clould think of would be to define your own cellStyle. This is possible in excel with custom functions. In apache-poi it could be possible when tinkering around with cell Styles:

     ...
 Cell cell = row.createCell(cellIndex);
 CellStyle cellStyle = workbook.createCellStyle();
 CreationHelper createHelper = workbook.getCreationHelper();
 createHelper.createDataFormat("some specific custom data format").
 cell.setCellStyle(cellStyle);

    I am however not sure that there is a way. I am not sure but these cellStyles seem to be limited to the default excel styles https://www.roseindia.net/java/poi/setdataformat.shtml

    The simplest approach would be to make your cell of Type "Date". Even if your result from the Database is a number, it can be converted to Date, since Date is only a layer upon a timestamp. I would highly suggest to look at this approch instead of "hacking" your way around number fromats looking similar to Dates.