*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_date(): Shortcut for inserting a new cell that contains * a date value into a given row of a sheet * * This is just a wrapper around the hssf_date2xls() and * hssf_num() routines. (Dates in Excel are simply double * precision floating point numbers) * * peRow = Row object that cell should be created in * peCol = column number of new cell * peNumber = numeric value to place in cell * peStyle = cell style object to associate with cell *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_date B Export D hssf_date PI D peRow like(HSSFRow) D peCol 5I 0 value D peDate D value D peStyle like(HSSFCellStyle) D wwDate s like(jDouble) /free If peRow = *NULL ; sendEscape('CPF9898' : 'hssf_date() cannot deal with *NULL row') ; EndIF ; wwDate = hssf_date2xls(peDate); hssf_num(peRow: peCol: wwDate: peStyle); /end-free P hssf_date E *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_date2xls(): * service program utility to convert an RPG date to a * number that can be formatted as a date in Excel * * peDate = RPG date to convert * * returns the date formatted for Excel *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_date2xls B Export D hssf_date2xls PI like(jdouble) D peDate D value D wwStrDate s d inz(d'1900-01-01') D wwDays s like(jdouble) ** 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 hssf_date2xls E *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_xls2date(): * service program utility to convert an Excel date to * an RPG date field * * peXls = Number used as a date in Excel * * returns the RPG date *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_xls2date B Export D hssf_xls2date PI D D peXls like(jdouble) value D wwStrDate s d inz(d'1900-01-01') D wwDate s d ** ** See hssf_date2xls for comments on how the Excel date format works ** /free wwDate = wwStrDate + %days(%int(peXls) - 1); // Excel incorrectly thinks that 1900-02-29 is // a valid date. if (wwDate > d'1900-02-28'); wwDate = wwDate - %days(1); endif; return wwDate; /end-free P hssf_xls2date E *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_xls2time(): * service program utility to convert an Excel time to * an RPG time field * * peXls = Number used as a time in Excel * * returns the RPG date *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_xls2time B Export D hssf_xls2time PI T D peXls like(jdouble) value D wwFract s 8F D wwSecs s 10I 0 D wwTime s T D SECSPERDAY c 86400 ** ** See hssf_date2xls for comments on how the Excel date/time ** format works. ** /free wwFract = peXls - %int(peXls); wwSecs = %inth(SECSPERDAY * wwFract); wwTime = t'00.00.00' + %seconds(wwSecs); return wwTime; /end-free P hssf_xls2time E *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_time2xls(): * service program utility to convert an RPG time field * to an Excel time * * peTime = RPG time field to convert * * returns the Excel time, which is a floating point number * (you have to apply a cell format to make it look like a time) *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_time2xls B Export D hssf_time2xls PI like(jdouble) D peTime T value D wwFract s like(jdouble) D wwSecs s 10I 0 D SECSPERDAY c 86400 ** ** See hssf_date2xls for comments on how the Excel date/time ** format works. ** /free wwSecs = %diff(peTime: t'00.00.00': *SECONDS); wwFract = wwSecs / SECSPERDAY; return wwFract; /end-free P hssf_time2xls E *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_xls2ts(): * service program utility to convert an Excel date/time value * to an RPG timestamp field * * peXls = Excel date/time value to convert * * returns the RPG timestamp *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_xls2ts B Export D hssf_xls2ts PI Z D peXls like(jdouble) value D wwDate s D D wwTime s T D wwTs s Z ** ** See hssf_date2xls for comments on how the Excel date/time ** format works. ** /free wwDate = hssf_xls2date(peXls); wwTime = hssf_xls2time(peXls); wwTs = wwDate + wwTime; return wwTs; /end-free P hssf_xls2ts E *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * hssf_ts2xls(): * service program utility to convert an RPG timestamp field * to an Excel date/time value * * peTS = RPG timestamp field to convert * * returns the Excel date/time, which is a floating point number * (you have to apply a cell format to make it look like a TS) *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ P hssf_ts2xls B Export D hssf_ts2xls PI like(jdouble) D peTS Z value D wwDate s like(jdouble) D wwTime s like(jdouble) ** ** See hssf_date2xls for comments on how the Excel date/time ** format works. ** /free wwTime = hssf_time2xls(%time(peTS)); wwDate = hssf_date2xls(%date(peTS)); return wwDate + wwTime; /end-free P hssf_ts2xls E