Get cell value without currency formatting when converting xls to csv

Hello! I’m converting xls files to csv with workbook.save() method in Java. And I cant find a proper way to get clear number from cells, where currency or financial formatting applied.
The only way I found is to clear custom style from whole workbook, but I’m not shure about performance. What if I need to load heavy document with million cells?

Thats my code to convert document:
File outFile = Files.createTempFile(“out_”, “.csv”).toFile();
workbook.save(outFile.getAbsolutePath(), SaveFormat.CSV);

Thats my code to clear whole worksheet:
for (int i = 0; i <= worksheet.getLastColumnIndex(); i++) {
for (int j = 1; j < worksheet.getRowsCount(); j++) {
Style cellStyle = worksheet.getCellStyle(j, i);
if (!cellStyle.isDateTime()) {
cellStyle.setCustom("");
worksheet.setCellStyle(j,i,cellStyle);
}
}
}

Any help to make it in proper way? Thank you.

@Sqweed,
Instead of iterating through all the cells in the worksheet one by one, you can minimize the cells by considering the cells containing data. Then you can clear formatting of the whole sheet at once with following code:

Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();
worksheet.getCells().clearFormats(cells.getMinDataRow(), cells.getMinDataColumn(), cells.getMaxDataRow(), cells.getMaxDataColumn());

You will be able to get clear numbers from the cells with this code. If your requirements are different then share your template file and expected output.

1 Like