Date/time support, date/time formula, xls string corruption fix, tests
Original Reporter info from Mantis: BigChimp
-
Reporter name: Reinier Olislagers
Original Reporter info from Mantis: BigChimp
- Reporter name: Reinier Olislagers
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 formulas,numbers etc using cardinals increasing row/column limits for formats that support it.
Note: changes public signature from word to cardinal=> check: this shouldn't impact existing code, right? 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 (better than corrupting the xls file - e.g. try to open it with Excel 2002 - as happens now with xls format)
- Formats that use fpspreadsheet 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)
New files - in newfiles.zip:
tests*, namely
datetests.pas
internaltests.pas
manualtests.pas
numberstests.pas
readme.txt
spreadtestcli.lpi
spreadtestcli.lpr
spreadtestgui.lpi
spreadtestgui.lpr
spreadtestgui.res
stringtests.pas
testbiff8.xls
testbiff8_1899.xls
testdbwriter.ini
testdbwriter.pas
testdbwriter.rc
testdbwriter.res
testdbwriter_firebird.sql
testdbwriter_postgresql.sql
testsutility.pas
reference\MSODumper.txt
SVN diff in hugepatch.diff
Involves files:
fpsopendocument.pas => row/column to cardinal fix
fpspreadsheet.pas => most changes sse above
fpszipper.pp => use fpc builtin zipper for FPC2.7.1+; harden date/time routines (as is done in fpc now)
laz_fpspreadsheet.pas => use fpc builtin zipper for FPC2.7.1+
xlsbiff2.pas => date/time formula support,row/column to cardinal fix,string length fix
xlsbiff5.pas => same as xlsbiff2
xlsbiff8.pas => cosmetic, comment fixes, most changes
xlscommon.pas => date/time formula support,datemode support (needed for date/time cells), gerenic Excel date/time number<>TDateTime conversion code
xlsxooxml.pas => use fpc builtin zipper for FPC2.7.1+;string length fix
Steps to reproduce:
Test:
see testsbeforepatch.txt testsafterpatch.txt which basically show date/time cells work now.
You can also run the tests on the original fpspreadsheet (just copy the tests directory and compile the *.lpr files).
Try and run the string tests, then open the created temp file in your %temp% folder with Excel - it's corrupt for my Excel 2002 (though it works with LibreOffice).
Date/time reading writing is covered by tests and works.
Additional information:
Run/tested on
- FPC trunk x86&x64, Windows, Laz trunk
- fpc 2.6.2 x86, Windows, Laz 1.0.12
Supersedes patch in 24988 [Lazarus CCR] [Patch] Respect limit on number of characters in text cell; hardening
Mantis conversion info:
- Mantis ID: 25388
- OS: Windows
- OS Build: Windows 7
- Platform: x64 (with x86 compiler)
- Monitored by: » @wpam (wp)