Poi ' characted seem to be added automatically

Question

I use POI 3.1 to generate a xlsx file.

This character ' is added automcally in my cell

cell = row.createCell(0);
cell.setCellValue(atm.getEnvelop());

   cell = row.createCell(1);
   cell.setCellType(Cell.CELL_TYPE_NUMERIC);
   cell.setCellValue(atm.getAmount().replace(".", ","));

atm.getEnvelop() and atm.getAmount() are string

atm.getEnvelop() value is 8635 but when i check in the file generated i get:' 8635

same thing for the other one

value is 200,00 i get '200,00

any idea?

Answer 1

Your problem is that you're calling cell.setCellValue(String), which automatically sets the cell type to be a string

If you want to set a numeric cell type (so there's no ' prefixed in Excel), you need to give Apache POI a number not a string.

This code will set '200.00 in Excel:

String s200 = "200.00";
cell.setCellValue(s200);

While this one will give you 200.00 in Excel:

// Once per workbook - tell excel to format with with two decimal points
DataFormat fmt = wb.createDataFormat()
CellStyle cs = wb.createCellStyle();
cs.setDataFormat(fmt.getFormat("0.00"));

// Once per cell
double d200 = 200.0;
cell.setCellValue(d200);
cell.setCellStyle(cs);

For your case, where your number seems to be coming in as a String, you'll need to parser it into a number (eg double) before giving it to POI

Answer 2

AT the place of cell.setCellType(Cell.CELL_TYPE_NUMERIC); Use cell.setCellType(CELL_TYPE_STRING);

Because putting char '' is excel property for numeric value.

it could help you.