'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!

javaexcelapache-poilocalecell


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 GJohannes

Related Questions