CODEWITHSUNDEEP
exceldatetimenullapache-poi
qbnKracker
qbnKracker

Reputation: 1

Apache POI to excel - Zero Date


Please make sure you understand my problem before replying, it is not as simple as it looks. Please don't just do a google search and post the link to the results; I already looked.

I have a VB.Net application that we are replacing with a Java application. The purpose of the application is to write an excel sheet (.xls). The file is then sent over to a second party and they process the data in it. I am using the APACHE POI to write the file.

The final product is being rejected by the second party because two time fields are "not valid". After scratching my head for a while, I noticed that Java produced file and VB.Net produced file are handling 0 date values differently. Let's say the time is suppose to be 3:30 PM in military time, the data appears as 15:30 on both files. The problem is the date portion of the field:
VB.Net generated: 1/0/1900 15:30
Java generated: 1/1/1970 15:30

I can't seem to find a way to have the apache POI mimic the way excel handles 0 dates. The following are some of the things I tried.
I set my date/time variable in the java application as 1/0/1900 15:30. This gives me an error in the application.
I set my variable as a string and pass it to the worksheet and then set the format of the cell. I don't get an error, but the data stays as 'general' until I double click on the cell and press Enter. This process is suppose to be automated so this is not an option.
I set the formula of the cell to =TIMEVALUE("15:30"), but this was not accepted by the 2nd party.

Has anyone else ran into this problem? Can anyone think of a way around this? Having the second party change the way they read the file is not an option.

Upvotes: 0

Views: 1020

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

What you need to know is that Excel stores datetime values as floating point double values. There 0 = 00:00:00 and 1 = 24:00:00 = 01/01/1900 00:00:00. Also 0.5 = 12:00:00 and 1.5 = 36:00:00 = 01/01/1900 12:00:00. So in other words, Excels datetime values are starting with 0 and 1 is one day and is 01/01/1900. Also 1/24 is one hour, 1/24/60 is one minute and 1/24/60/60 is one second.

The problem using a Java Date is that the months in Calendar constructors are 0 based. So month 0 is January and new GregorianCalendar(1900, 0, 1, 15, 30, 0) will be 01/01/1900 15:30:00. And there is not a day 0, so new GregorianCalendar(1900, 0, 0, 15, 30, 0) will be 12/31/1899 15:30:00 and this will be -1 for Excel.

Because the problems with Excels date behavior are known, apache poi provides DateUtil.

Using this we can do:

import java.io.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Calendar;
import java.util.GregorianCalendar;

class XSSFNullDateTest {

 public static void main(String[] args) {
  try {

   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("Sheet1");

   CreationHelper creationHelper = wb.getCreationHelper();
   CellStyle cellStyleTime = wb.createCellStyle();

   cellStyleTime.setDataFormat(creationHelper.createDataFormat().getFormat("hh:mm:ss"));

   //using a Calendar:
   Calendar calendar = new GregorianCalendar(1900, 0, 1, 15, 30, 0);
   System.out.println(calendar.getTime()); //01/01/1900 15:30:00
   double doubleTime = DateUtil.getExcelDate(calendar, false);
   System.out.println(doubleTime); //1.6458333333333335
   Cell cell = sheet.createRow(0).createCell(0);
   cell.setCellValue(doubleTime-1); //subtract 1 so we have day 0
   cell = sheet.getRow(0).createCell(1);
   cell.setCellValue(doubleTime-1); //subtract 1 so we have day 0
   cell.setCellStyle(cellStyleTime);

   //using a string:
   doubleTime = DateUtil.convertTime("15:30:00");
   System.out.println(doubleTime); //0.6458333333333334 = day 0 already
   cell = sheet.createRow(1).createCell(0);
   cell.setCellValue(doubleTime);
   cell = sheet.getRow(1).createCell(1);
   cell.setCellValue(doubleTime);
   cell.setCellStyle(cellStyleTime);


   OutputStream out = new FileOutputStream("XSSFNullDateTest.xlsx");
   wb.write(out);
   wb.close();

  } catch (Exception ex) {
   ex.printStackTrace();
  }
 }
}

Upvotes: 2

Related Questions