CODEWITHSUNDEEP
exceldateapache-poi
anish
anish

Reputation: 327

Using POI to write date (without time)

When i write out a date using POI, I also see the time in Excel. (the time shown is the time on my machine when the cell was written). This is the code that i have used

XSSFCellStyle dateStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-mmm-yyyy"));
Calendar cal = Calendar.getInstance();
cal.set(xx.getYear(), xx.getMonthOfYear(), xx.getDayOfMonth());
cell.setCellStyle(dateStyle);
cell.setCellValue(cal);

the date in the cell is correct i.e 12-Dec-2013 but for that cell, IN THE FORMULA bar, the time also shows. So cell shows 12-Dec-2013 and the formula bar show 12-Dec-2013 7:14:39AM. I checked the format of the cell in excel and it shows as custom dd-mm-yyyy, which is what i expect. Just too be clear - the cell itself show 12-12-2012 but for that cell in the formula bar the time also shows. I also replaced Calendar with Date - same issue.

Addl info: In excel i changed the format of the col to 'general' - for the cells that were addined in by POI, i see that the values is xxx.xxx like 41319.3769490278, while when i just enter the date by hand the value looks something like 41319. It looks like the digits after the decimal point is causing the time to show up. Not sure how to avoid this when i use POI to write it out

Upvotes: 2

Views: 2685

Answers (2)

anish
anish

Reputation: 327

Ok solved. Putting this out there for others who run into the same problem. i looked into the POI source code and realized that from the calendar, a double is computed, and the cell value is set to that. From the comments its clear that the digits after the decimal point represent the time. So all i did in my code is to truncate that double. The changed lines are commented in the code snippet below.

XSSFCellStyle dateStyle = wb.createCellStyle();
CreationHelper createHelper = wb.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-mmm-yyyy"));
Calendar cal = Calendar.getInstance();
cal.set(xx.getYear(), xx.getMonthOfYear(), xx.getDayOfMonth());
cell.setCellStyle(dateStyle);
double d = DateUtil.getExcelDate(cal, false); //get double value f
cell.setCellValue((int)d);  //get int value of the double

Upvotes: 8

Gagravarr
Gagravarr

Reputation: 48356

You're not fully clearing the Calendar instance you're getting the date from, that's why the time is coming through

You need to also set the time values to zero, so your code would want to look something like:

Calendar cal = Calendar.getInstance();
cal.set(xx.getYear(), xx.getMonthOfYear(), xx.getDayOfMonth());
calendar.set(Calendar.HOUR, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
calendar.set(Calendar.MILLISECOND, 0);

Upvotes: 2

Related Questions