0

Trying to convert xlsx file to csv, where after conversation the integer value has been converted to float in output file. But other string values are resulted as expected.

Below is the example

source record

converted record

code:

        Workbook workbook = null;
        String ext = FilenameUtils.getExtension(inputFile.toString());
        workbook = new XSSFWorkbook(fis);
        int numberOfSheets = workbook.getNumberOfSheets();
        Row row;
        Cell cell;
        // Iterate through each rows from first sheet
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                row = rowIterator.next();
                
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    cell = cellIterator.next();
                    CellType type = cell.getCellType();
                    switch (type) {
                        case BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");
                           
                            break;
                        case NUMERIC:
                            data.append(cell.getNumericCellValue() + ",");
                           
                            break;
                        case STRING:
                            data.append(cell.getStringCellValue() + ",");
                            
                            break;
                        case BLANK:
                            data.append("" + ",");
                            break;
                        default:
                            data.append(cell + ",");
                    }
                }
                data.append('\n'); 
            }
        }
        fos.write(data.toString().getBytes());
        fos.close();
Charles
  • 9
  • 4
  • Which entry in the switch is the culprit? – Thorbjørn Ravn Andersen Jul 23 '21 at 20:45
  • For the numeric cell you'll get a double. `Cell.getCellStyle()` and then the DataFormat, with BuiltinFormats can then say that it actually is an integral type (like `long`). Some effort. – Joop Eggen Jul 23 '21 at 21:04
  • If you read the javadoc on getNumericCellValue, you'll see it suggests using DataFormatter (follow up to @JoopEggen 's answer) – PJ Fanning Jul 23 '21 at 23:23
  • 2
    See https://stackoverflow.com/questions/66670794/excel-to-text-conversion-properly-handle-formula-and-empty-cells/66673552#66673552 and https://stackoverflow.com/questions/3819633/how-to-get-the-formatted-value-of-a-number-for-a-cell-in-apache-poi/66241266#66241266 – Axel Richter Jul 24 '21 at 04:44

0 Answers0