How to write dates in Apache POI which would be filtered properly

I have a block of code which write data to .xlsx file. I need to write some date in “dd.MM.yy HH:MM” format. All is work properly, but when I try to open result file and test filter work, I got issue with that. Filter perceives dates just like strings, instead of dates, and I can’t get filter with year and month.

Filter which I want (in Russian btw, sorry) Expected filter

Filter which I get Current filter

Code of cell style

        defaultCellStyle = (XSSFCellStyle) workbook.createCellStyle();
        defaultCellStyle.setFont(defaultFont);
        defaultCellStyle.setWrapText(true);
        defaultCellStyle.setBorderBottom(BorderStyle.THIN);
        defaultCellStyle.setBorderTop(BorderStyle.THIN);
        defaultCellStyle.setBorderLeft(BorderStyle.THIN);
        defaultCellStyle.setBorderRight(BorderStyle.THIN);

        dateCellStyle = (XSSFCellStyle) defaultCellStyle.clone();
        CreationHelper createHelper = workbook.getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd.MM.yy hh:mm"));
        dateCellStyle.setAlignment(HorizontalAlignment.RIGHT);

Code of value setting

        if (cellValue instanceof Date) {
            DateFormat dateFormat = new SimpleDateFormat("dd.MM.yy hh:mm");
            Date date = (Date)cellValue;
            cell.setCellValue(dateFormat.format(date));
        }

Actually I can’t get right filter with any other date formats like standard “dd.MM.yyyy”

Edited: I got my data from prepared statement, and in result date value stored in Timestamp type. maybe it will be useful

Edited 2: I didn’t give the code where I set the style to the cell, but I do it. It seemed to me that this does not require a description. Sorry for that. Also I tried to do this without using of DataFormat, but it didn’t work

Answer

You can set the cell style by setting DataFormat like below,

Code:

CellStyle cellStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd.MM.yy hh:mm"));
Cell cell = sheet.createRow(3).createCell(2);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

Output:

Update:

You need to just convert the cellValue to String

if (cellValue instanceof Date) {
// convert cellValue to String
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd.MM.yy hh:mm"));
Date date = new SimpleDateFormat("dd.MM.yy hh:mm").parse(cellValue);
cell.setCellValue(date);
  }