Fpspreadsheet patch set: date,time functions, overflow protection lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- BigChimp · 2013-11-23 10:55 Pls find attached my current patchset against fpspreadsheet. - Added DATE, TIME worksheet functions in Excel. Date output works ok; however you'll still need to manually switch formatting to date/time - use fpc- provided tzipper function if on FPC 2.7.1 or higher as it has zip64 support (had already supplied this to bugtracker) edit: can't remember if I had posted this already to the bugtracker, don't think so. - Respect 32767 character limit in string fields in biff8/xlsx, same with limits in biff2, biff5 and deal with them the same as other overflow is dealt with: throw an exception when writing rather than (as currently) silently corrupting the output. Previously reported as mantis #24988 - cosmetic: added some more references to the openoffice excel documentation chapters Edit: have only tested with empty times up to now but dates work fine; testing using something like: Code: [Select]                  SetLength(ExcelRPNFormula, 9);                   ExcelRPNFormula[0].ElementKind := fekNum; // Getting data out of a dataset; // of course easier to just use a tdatetime or // even specify a constant number for testing...                   ExcelRPNFormula[0].DoubleValue := YearOf(TDataset(FDatasets[i]).Fields[TheColumn].AsDateTime);                   ExcelRPNFormula[1].ElementKind := fekNum;                   ExcelRPNFormula[1].DoubleValue := MonthOf(TDataset(FDatasets[i]).Fields[TheColumn].AsDateTime);                   ExcelRPNFormula[2].ElementKind := fekNum;                   ExcelRPNFormula[2].DoubleValue := DayOf(TDataset(FDatasets[i]).Fields[TheColumn].AsDateTime);                   ExcelRPNFormula[3].ElementKind := fekDate; //end of date formula                   ExcelRPNFormula[4].ElementKind := fekNum;                   ExcelRPNFormula[4].DoubleValue := HourOf(TDataset(FDatasets[i]).Fields[TheColumn].AsDateTime);                   ExcelRPNFormula[5].ElementKind := fekNum;                   ExcelRPNFormula[5].DoubleValue := MinuteOf(TDataset(FDatasets[i]).Fields[TheColumn].AsDateTime);                   ExcelRPNFormula[6].ElementKind := fekNum;                   ExcelRPNFormula[6].DoubleValue := SecondOf(TDataset(FDatasets[i]).Fields[TheColumn].AsDateTime);                   ExcelRPNFormula[7].ElementKind := fekTime; //end of time formula                   ExcelRPNFormula[8].ElementKind := fekAdd; //add date and time                   ExcelWorksheet.WriteRPNFormula(TheRow, TheColumn, ExcelRPNFormula); gives formulas like Quote=DATE(2011,5,2)+TIME(0,0,0) Would be happy to split this out in new patches and upload to mantis if somebody wants to commit As usual, comments welcome as well. Edit: removed attachment; newer version in other thread ---------------------------------------------------------------------- BigChimp · 2013-11-24 10:22 Oh, the complete modified fpspreadsheet is used in my bankconvert tool: https://bitbucket.org/reiniero/smalltools/src directory bankconvert ---------------------------------------------------------------------- Bart · 2013-11-24 11:31 Did you post your fixes in bugtracker (Lazarus CCR section)? (I'm too lazy to search the bugtracker myself ) Bart ---------------------------------------------------------------------- BigChimp · 2013-11-24 12:38 Hi Bart: as I mentioned in the first post: I posted the overflow fix against the fpspreadsheet package. I actually think I haven't posted the rest yet... As none of it has been committed, committing the entire thing in one go or split out is fine by me but before I put more work in it I'd like to know if somebody has the time and inclination to do it ---------------------------------------------------------------------- Bart · 2013-11-24 15:01 Felipe Monteiro de Carvalho and Jose Mejuto seem to "own" the fpSpreadSheet section of Lazarus CCR. You could try asking one of the on Lazarus(Dev) mailinglist. Bart ---------------------------------------------------------------------- BigChimp · 2013-11-24 15:17 Thanks. AFAIU at least one of them is very busy lately Anyway, I'll keep at it for a while; I think I'm going on a Don Quichotte like quest to try and implement xls date format support ---------------------------------------------------------------------- BigChimp · 2013-11-27 08:39 Seem to have it working Topic continued in new thread http://forum.lazarus.freepascal.org/index.php/topic,22774.0.html for clarity, with link to download.