zwwcn

Just another WordPress.com site

Category Archives: POI

POI add number cell

Make sure you add cell value as number, otherwise you can’t sort the columns in new file.

Add number:

HSSFRow row = sheet.createRow(sheet.getLastRowNum());
HSSFCell cell = row.createCell(0);
HSSFCellStyle style = workbook.createCellStyle();
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
cell.setCellStyle(style);
cell.setCellValue(Float.parseFloat("21.5"));

Add percentage:

cell.setCellValue(0.123); // set value as number
CellStyle style = workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("0%"));
cell.setCellStyle(style);

 

Advertisements

POI read cell value as string

When you get error for reading cell value as string:

Cannot get a numeric value from a text cell
or Cannot get a boolean value from a text cell

The code below will format the cell properly and return string value:

final DataFormatter df = new DataFormatter();
final XSSFCell cell = row.getCell(cellIndex);
String valueAsString = df.formatCellValue(cell);

POI read numeric value as string

call cell.setCellType(Cell.CELL_TYPE_STRING); before reading the cell