Re: Dates in POI Sql2Xls

["Beppe Costagliola" <beppecosta@xxxxxxxx>]

... 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.