FPSpreadsheet: date support - download lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- BigChimp · 2013-11-27 08:38 Edit: for people finding this via a search: the relevant code is included in the released version, fpspreadsheet 1.2. I'm working on updating fpspreadsheet to support writing/reading date/times to the BIFF8/XLS format. I think I've got it working - you can download my version at https://bitbucket.org/reiniero/smalltools/downloads/fpspreadsheet_29No vember.zip and try for yourself. Comments/suggestions/patches welcome; see e.g. the program in tests directory. I'll continue working on adding tests to the test program so it's easier to keep track of regressions and upload it to the bugtracker when done. My work-in-progress notes: To do: add this to patch description * Added FPCUnit test suite to test reading and reading writing files. For now uses BIFF8 xls; contributions for other formats and more tests welcome. See the tests subdirectory. * XLS BIFF8: added DATE and TIME worksheet functions to RPN formula * Allow writing formula,* fields for formats that support it. Note: changes public signature from word to cardinal=> check: this shouldn't impact existing code, should it? Anyway, the same change was done for the label/text cell support earlier * Add date/time/datetime read/write support for BIFF8 xls format. No plans for earlier formats though the code could be adapted * Add checks for text cell size limits when writing xls, xml. If exceeded, cell text will be truncated and an exception will be raised you can use try..except to discard this exception in your application if wanted * Formats that use zip have extra checks to prevent crashes for invalid zip dates * Use FPC built-in zip format when using FPC 2.7.1+ as it has fixes/improvements (e.g. zip64 support) Update: added more tests, including the background color tests from the other tests that fail. Thanks a lot for an awesome library - I hope I can improve it! ---------------------------------------------------------------------- exdatis · 2013-11-27 08:53 Thanks, BigChimp! ---------------------------------------------------------------------- BigChimp · 2013-11-29 08:05 No problem Uploaded a new version with more tests. I'll run the tests on my version of fpspreadsheet & the original and compare to check for regressions, then review the diff, finally upload it to mantis/bugtracker. Might take a while ---------------------------------------------------------------------- BigChimp · 2013-11-30 13:07 Posted on mantis: http://bugs.freepascal.org/view.php?id=25388 ---------------------------------------------------------------------- wp · 2013-12-07 16:01 Thanks for applying the patch. Great work! ---------------------------------------------------------------------- BigChimp · 2013-12-08 09:55 My pleasure. Glad I could help improve fpspreadsheet ... and please post if there's problems... ---------------------------------------------------------------------- wp · 2013-12-08 14:07 There is a little issue with reading: I added a cell with the current date/time to the spreadsheet of "excel8write" formatted as nfShortDateTime: Code: [Select]  // Write current date/time to cell A2   MyWorksheet.WriteNumber(1, 0, now());   lCell := MyWorksheet.GetCell(1, 0);   lCell.NumberFormat:= nfShortDateTime;   lCell.UsedFormattingFields := [uffNumberFormat]; Excel can read this cell perfectly, but the Lazarus demo "exel8read" only displays the date part of that cell. The bug is in TsWorksheet.ReadAsUTF8Text, case cctDateTime of ACell^.ContentType, where the result is calculated only with DateToStr. With the following simple modification, the date/time cell is read by "excel8read" correctly: Code: [Select]function TsWorksheet.ReadAsUTF8Text(ARow, ACol: Cardinal): ansistring; ...   case ACell^.ContentType of     ...   cctDateTime:   begin                    Result := SysUtils.DateToStr(ACell^.DateTimeValue);                    if ACell^.NumberFormat = nfShortDateTime then                      Result := Result + ' ' + SysUtils.TimeToStr(ACell^.DateTimeValue)                  end;   ... ---------------------------------------------------------------------- BigChimp · 2013-12-09 11:27 Thanks, wp. Unfortunately, as there is no separate date/time cell format (as does exist with numbers and text), date+time values can not only be encoded in nfShortDateTime, but also in any other layout format that has time (in fact, storing date+time in a shortdate only format would work as well). Hadn't looked at that example; I'm going to expand the test suite instead so we can regression test fpspreadsheet more easily. Haven't looked at any code yet, but I'm wondering whether ReadAsUTF8Text should just return date and time always for contenttype cctDateTime. This will cover user custom date/time formats coming from Excel as well as the various built in versions that appear in Excel... ---------------------------------------------------------------------- BigChimp · 2013-12-10 12:25 Thanks wp, I ended up with this fix: QuoteIndex: fpspreadsheet.pas =================================================================== --- fpspreadsheet.pas   (revision 2857) +++ fpspreadsheet.pas   (working copy) @@ -689,11 +689,18 @@    end;    case ACell^.ContentType of -    //cctFormula    cctNumber:     Result := FloatToStrNoNaN(ACell^.NumberValue);    cctUTF8String: Result := ACell^.UTF8StringValue; -  cctDateTime:   Result := SysUtils.DateToStr(ACell^.DateTimeValue); +  cctDateTime: +  begin +    Result := SysUtils.DateToStr(ACell^.DateTimeValue); +    // User can have specified custom date/time format or one of the other buil t +    // in formats that include time. We can't parse all of them so just return +    // time as well unless absolutely sure we only want a date +    if ACell^.NumberFormat<>nfShortDate then +      Result := Result+' ' + SysUtils.TimeToStr(ACell^.DateTimeValue); +  end;    else      Result := ''; Revision 2861 Test included in TSpreadInternalTests.ReadDateAsUTF8. I know I'm asking even more from somebody who hans me working code on a plat, but.... If you hit more problems, I'd really appreciate extended test cases in the test suites - it makes it much easier to regression test... and helps me keep my confidence in committing to the repo ---------------------------------------------------------------------- wp · 2013-12-10 12:52 Thanks - that's exactly my modification. BTW, your great date/time code served as a guideline for me to introduce the most relevant ones of the 58 built-in formatting styles to BIFF8. Still need some tweaking, then I'll show this version here. Quotewho hans me working code on a platSorry - my English isn't good enough to understand this... ---------------------------------------------------------------------- BigChimp · 2013-12-10 13:02 Well, to reassure you, that wasn't English at all... my mind was faster than my fingers as usual I meant "who hands me working code on a [silver] platter" @the 58 styles: interesting! I got the impression from the OpenOffice specs that the styles differ between various versions of Excel though (see e.g. the Japanese examples - I wonder whether the ID numbers are reused in other locales for other settings). Still, very interested... ---------------------------------------------------------------------- wp · 2013-12-13 09:17 When checking whether the modifications that I introduced in the general units for reading/writing of the default formats with xlsbiff8 have an effect on other spreadsheet versions I came across the issue that only xlslbiff8 implements WriteDateTime; for the others, this method is abstract. This leads to the situation that saving the same workbook for biff8 works, but crashes for the other formats. Wouldn't it be better the use an empty method for WriteDateTime in these cases, or even call WriteNumber to store at least the numerical value instead of the datetime (although in the latter case, if the user reformats these cells to date/time in Excel, he may see a different date as the programmer when the cells were filled, due to different reference dates between fpc and Excel...)? Quote I got the impression from the OpenOffice specs that the styles differ between various versions of Excel thoughThat's my impression, too, but I guess it does not matter at the moment since we're working only with BIFF8. ---------------------------------------------------------------------- BigChimp · 2013-12-13 10:04 Quote from: wp on December 13, 2013, 10:17:57 amWhen checking whether the modifications that I introduced in the general units for reading/writing of the default formats with xlsbiff8 have an effect on other spreadsheet versions I came across the issue that only xlslbiff8 implements WriteDateTime; for the others, this method is abstract. This leads to the situation that saving the same workbook for biff8 works, but crashes for the other formats. Wouldn't it be better the use an empty method for WriteDateTime in these cases, or even call WriteNumber to store at least the numerical value instead of the datetime (although in the latter case, if the user reformats these cells to date/time in Excel, he may see a different date as the programmer when the cells were filled, due to different reference dates between fpc and Excel...)? Thanks. I wouldn't be in favour of writing out numbers due to the reference date issue you mentioned. Rather write out an empty cell... or perhaps a text cell with the ISO date (yyyymmdd hhmmss)? Quote from: wp on December 13, 2013, 10:17:57 amQuote I got the impression from the OpenOffice specs that the styles differ between various versions of Excel thoughThat's my impression, too, but I guess it does not matter at the moment since we're working only with BIFF8. No, I'm talking about different language versions of one version of Excel (say Excel 2003) that all write BIFF8. ---------------------------------------------------------------------- JD · 2014-01-02 17:06 @BigChimp I also have a need to write date values into a spreadsheet. I had been using WriteUTF8Text but my users (they never seem to stop asking for more features  ) now want to be able to sort the date columns in the Excel files I created using fpspreadsheet. I clicked on the link you provided earlier but it doesn't work again. Thanks for your help. JD ---------------------------------------------------------------------- BigChimp · 2014-01-04 13:09 @JD: the date writing support is now available in current fpspreadsheet development version/trunk. Please see the fpspreadsheet wiki page for installation instructions. Thanks. ---------------------------------------------------------------------- JD · 2014-01-06 21:48 Quote from: BigChimp on January 04, 2014, 02:09:41 pm@JD: the date writing support is now available in current fpspreadsheet development version/trunk. Please see the fpspreadsheet wiki page for installation instructions. Thanks. Thanks a lot. I'll look it up ASAP. ---------------------------------------------------------------------- JD · 2014-01-20 22:16 @BigChimp I just want to thank you for your good work. I downloaded & tested the date support & it's working fine. JD ---------------------------------------------------------------------- BigChimp · 2014-01-21 06:35 My pleasure, JD ...and the development itch I had is scratched..