Fpspreadsheet patch set: date,time functions, overflow protection

[BigChimp]

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

=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]

Oh, the complete modified fpspreadsheet is used in my bankconvert tool:
https://bitbucket.org/reiniero/smalltools/src
directory bankconvert

[Bart]

Did you post your fixes in bugtracker (Lazarus CCR section)?
(I'm too lazy to search the bugtracker myself )

Bart

[BigChimp]

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]

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]

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]

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.