2

I've met some problem with generating Excel files using org.apache.poi.ss.usermodel utils: Values above 1000 have dot (.) as a decimal separator and values below 1000 have comma (,) as a decimal separator.

I've trying a lot of different ways put BigDecimal values into 'Cell' but each time the result is the same, regardless of setting up cell type on CELL_TYPE_NUMERIC or CELL_TYPE_STRING or doing something like this:

CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("###0,00"));
cell.setCellStyle(cellStyle);

I managed to get rid of this problem by unifying the decimal separator - I am taking a String value of this BigDecimal, replacing all dots into commas and done! But when opening Excel all the fields are marked up and there is a warning which says that value in this cell is formatted as a text. I can set up it as a number and then making diferent mathematical operations on them. But I'd like to get generated Excel file with no warnings to resolve (and hence - cells set up as numbers to make mathematical operations on them), with BigDecimal values with comma as a decimal separator.

I create my sheet and cells as follows:

Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("mySheet);
int rowNum = 1;
List<MyRecord> records = (...);

for (MyRecord record : records) {
    Row row = sheet.createRow(rowNum++);
    int cellNum = 0;
     for (String attribute : attributeList) {
         String cellData = cellData(record, attribute);
         if(cellData != null) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellData);
        }
        cellNum++;
        }
}
KamilosD
  • 333
  • 1
  • 2
  • 12
  • 2
    `setCellValue(double)` plus `cell.setCellStyle(cellStyle);` should cover everything. (Except floating point rounding) – Joop Eggen Sep 19 '18 at 14:08
  • 1
    I've tried it before but I encountered a problem. When using something like this: `cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("0,00"));` then all values in cell were presented with cutted decimal places. So instead e.g. "4 478,57" there were "4 478". I also tried different format like "#,##" and the result was the same. Additionally setting up cell type to `CELL_TYPE_NUMERIC` doesn't change anything. – KamilosD Sep 24 '18 at 12:11
  • Still don't have a solution for this problem. If someone encountered this problem, please share code examples which seems to work properly. Thanks! – KamilosD Nov 13 '18 at 12:36

0 Answers0