populating a field in excel using java HSSF POI - excel does not sort date field properly

So I am populating an excel document using Java's HSSF POI and I am using a excel template which has some headers already entered into it like so...

HSSFWorkbook workbook = readFile(TEMPLATE_LOCATION);

And my issue is that when I populate one of the columns with data in a date format of MM/dd/yyyy like so...

row.createCell((short)column++).setCellValue(Tools.dateToString(rfq.getCreationDate()));

It populates the column appropriately with data like.... 01/01/2011 05/04/2010 03/03/2009

The bug is when I execute a sort on this column (in excel) using say an autofilter->sort ascending then it returns the dates in the wrong order like so....

0开发者_运维问答1/01/2011

03/03/2009

05/04/2010

(As it is reading it like a string and sorting instead of sorting by a date)

I tried to set the column as a "Numeric" column and then sort still no dice....

        cell = row.createCell((short)column++);
        cell.setCellStyle(workbook.createCellStyle());
        cell.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        cell.setCellType(cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(Tools.dateToString(rfq.getCreationDate()));

Also formating this way as well did not help...

SimpleDateFormat formatter = new SimpleDateFormat("M/d/yyyy");
row.createCell((short)column++).setCellValue(formatter.format(order.getCreationDate()));

This is all going on in Excel 2003. No clue how to resolve.

Your problem is that you are converting the data to strings when you populate the cells

row.createCell((short)column++).setCellValue(Tools.dateToString(rfq.getCreationDate()));

I'm not a java user, so not sure, but you could try just

row.createCell((short)column++).setCellValue(rfq.getCreationDate());

If the underlying data is in a form Excel recognises as a date, it should be ok.