format datacell to comma(?)

Question

Hi i want to edit these cells to have a comma instead of a decimal point. they are exported from my database to a excel sheet, but instead of the decimal point i want a comma ( , ) can anyone help please?

            int r = 1;
            while (rs.next() )
            {
                
                String vpid = rs.getString("VP_ID");
                String vtb = rs.getString("partner");
                String bs = rs.getString("Bonus");
                String bo = rs.getString("Bonus2");
            
                

    
                 row = sheet.createRow(r++);
                    row.createCell(0).setCellValue(vpid);
                    row.createCell(1).setCellValue(vtb);
                    row.createCell(2).setCellValue(bs);
                    row.createCell(3).setCellValue(bo);

Answer 1

That question seems to be a good example for a XY problem.

One gets numbers as text values from a database and writes them into a Excel sheet:

...
String bs = rs.getString("Bonus");
String bo = rs.getString("Bonus2");
...
row.createCell(2).setCellValue(bs);
row.createCell(3).setCellValue(bo);
...

Let bs be "123.45" and bo be "67.89", then the Excel cells will contain the texts "123.45" and "67.89" after that instead of the numbers 123.45 and 67.89.

Now one looks at that and uses Excel in a locale which has set comma as the decimal separator instead of dot. The misinterpretation now is, that Excel's interpretation of the numbers as text is because the wrong decimal separator. So one asks how to change the decimal separator.

But the real reason for Excel's interpretation of the numbers as text is because the code really sets text values instead of numbers. The decimal separator is not a format in Excel but only determined by used locale or by a extended application property. If numbers shall be cell contents then the cell value needs to be set using Cell.setCellValue(double value) and not using Cell.setCellValue(java.lang.String value). Using what decimal separator the number then gets showed in the sheet depends on used locale or the extended application property for decimal separator.

So the correct solution is to set the cell values as doubles. Either by doing:

...
row.createCell(2).setCellValue(Double.valueOf(bs));
row.createCell(3).setCellValue(Double.valueOf(bo));
...

But better would be to save the values correctly as numbers in the database and to retrieve them from the database like so:

...
double bs = rs.getDouble("Bonus");
double bo = rs.getDouble("Bonus2");
...
row.createCell(2).setCellValue(bs);
row.createCell(3).setCellValue(bo);
...