Re: Dates in POI Sql2Xls

THANK YOU!

From: "Beppe Costagliola" <beppecosta@xxxxxxxx>
Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Dates in POI Sql2Xls
Date: Fri, 11 Mar 2005 17:10:59 +0100

... but for Time fields we should use a different approach.

Excel cannot handle time alone, it converts the time
to a datetime format, so if we had, say, 12:01:00 PM
HSSF converts that into java.sql.time, 12/31/1899
12:01:00 using
double excelDate = HSSFDateUtil.getExcelDate((Time)cellValue);
theCell.setCellValue(excelDate);

Since HSSF does not handle dates prior to 1/1/1900 it
puts a bad date (-1).

Therefore convert the time to string and setting the
time format.

....
D usrFmtTime      S             24    Inz('h:mm:ss')
....
// ... Time
when  SQLTYPE = 388 or SQLTYPE = 389;
....
setCellValStr(cell:tstring);         // set cell value
setCellStyle(cell:styleTime);        // set to time
....

----- Original Message -----
From: "Beppe Costagliola" <beppecosta@xxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, March 11, 2005 1:10 PM
Subject: Re: Dates in POI Sql2Xls

Bob,
you can try to modify the sql2xls to handle dates in this way:

 * Style (date m/d/yy ) ----------------------------------------------
D styleDate       S               O   Class(*JAVA
D                                     :'org.apache.poi.hssf.usermodel-
D                                     .HSSFCellStyle')

D usrFmtDate      S             24    Inz('m/d/yy')
D styleFmtDate    S              5I 0

D Cell_Date       S               D
D Xls_StartDate   S               D   inz(d'1900-01-01')

// create Date Style
styleDate   = createCellStyle(wb);

// create a DataFormatter, a User Format and set the Style for Date
df = createDataFormat(wb);
usrformat = createString(usrFmtDate);
usrformat = trimString(usrformat);
stylefmtDate = getFormat(df:usrformat);
setDataFormat(styleDate:stylefmtDate);

// ... Date
when  SQLTYPE = 384 or SQLTYPE = 385;
  Cell_Alfa = *blanks;
  Cpybla(%addr(Cell_Alfa): SQLDATA: 10);
  if Cell_Alfa = *blanks and not wrt0cell;
    // do nothing
  else;
    cell = createCell(row:c-1);
    Cell_Date = %date(Cell_Alfa:*DMY);
    setCellType(cell:0);                 // type 0=Numeric
    cell_int = %diff(Cell_Date:Xls_StartDate:*D) + 2;
    setCellValNum(cell:Cell_Int);
    setCellStyle(cell:styleDate);        // set to date
  Endif;

Regards.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.