Issue while opening excel cells with Dates, After writing values with Apache POI Scala

Question

I am writing dates in rows in xlsx file with Apache POI scala.

It is writing in file and showing format as Custom date but values are not reflecting as Custom date until I am clicking double on that cell. After double click it is converting as custom date.

Can someone please tell me why this cell formatting is not reflecting on values. I have tried all ways available on stack overflow for formatting in apache POI.

            val cellStyle = workbook.createCellStyle();
            val df = workbook.createDataFormat();
            cellStyle.setDataFormat(df.getFormat("MMM-yy"))
            cell.setCellValue(r)
            cell.setCellStyle(cellStyle)

Excel value show

this answer did not help? https://stackoverflow.com/a/5794743/2750966 — pme, Jan 29 '20 at 10:26
No, I have tried this too, its only changing cell format as Date but data inside cell is reflecting only after double click. — Abhishek Singh, Jan 29 '20 at 10:37

score 1 · Accepted Answer · answered Jan 29 '20 at 13:06

For completion:

POI provides different setCellValue methods for different formats. See the Java Docs.

Depending on the datatype you have POI will create the according Excel Cell Type.

So in your case you have the following possibilities:

setCellValue(java.util.Date value)
setCellValue(java.time.LocalDateTime value)
setCellValue(java.time.LocalDate value)
setCellValue(java.util.Calendar value)

So your cell.setCellValue(r) must be one of the above if you want a Date Cell.

Issue while opening excel cells with Dates, After writing values with Apache POI Scala

1 Answers1