Re: Dates in POI Sql2Xls -- RPG400-L


Hello,

Forgive me if I'm off target here, I've never heard of SQL2XLS, but I have used POI from ILE RPG, so I'll see if I can answer your question.

I have one more issue with the POI sql2xls programs. I was trying to save an employee's hire date into a date data type field in a physical file and then move that date into the excel sheet the POI program creates. It doesn't seem to do what I want it to do (of course).

Dates in Excel spreadsheets are just numbers. They're numbers that represent the number of days since Jan 1st, 1900, with a few peculiarities.

To represent a time as well as a date, you use a fraction. For example, 1.5 would be noon on Jan 1, 1900.

I use the following routine to convert an RPG date field into an Excel date:

     P date2xls        B                   EXPORT
     D date2xls        PI             8F
     D   peDate                        D   value

     D wwStrDate       s               d   inz(d'1900-01-01')
     D wwDays          s              8F

      ** Dates in Excel are simply double-precision floating point
      ** numbers that represent the number of days since Jan 1, 1900
      ** with a few quirks:
      **     1)  Jan 1st 1900 is considered day #1, not 0.
      **     2)  1900 is counted as a leap year (despite that it wasn't)
      **     3)  Any fraction is considered a time of day.  For example,
      **              1.5 would be noon on Jan 1st, 1900.
      **

      /free

         wwDays = %diff(peDate: wwStrDate: *DAYS) + 1;

         // Excel incorrectly thinks that 1900-02-29 is
         //  a valid date.

         if (peDate > d'1900-02-28');
              wwDays = wwDays + 1;
         endif;

         return wwDays;
      /end-free

     P                 E

Once you have the correct number set into the cell in HSSF, all you need to do is create a cell style with the appropriate data format to make it display the date the way you want it.

In my case, I like to have MM/DD/YY dates, so I use the following code:

   Dates = HSSFWorkbook_createCellStyle(book);
   DataFmt = HSSFWorkbook_createDataFormat(book);
   TempStr = new_String('m/d/yy');
   DateFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
   HSSFCellStyle_setDataFormat(Dates: DateFmt);

(You may have to change that if you're not using the same prototype names that I am, but if you're familiar with HSSF/POI, the code above should make sense)