Support for the default number formats in fpspreadsheet (BIFF8) lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- wp · 2013-12-14 00:35 BigChimp's recent addition of date/time support for BIFF8 xls files in fpspreadsheet served as a guideline for me to implement the most important ones of the 58 default number formats of the xls file structure. Some explanations: The type TsNumberFormat introduced by BigChimp was extended to allow for more specific formatting: nfGeneral - general number format used by defaultnfFixed - floating point representation with a given number of decimals.nfFixedTh - dto, but using the system's thousand separator nfExp - exponential representation with a given number of decimalsnfSci - similar to nfExp, but the exponent is a multiple of 3; there is a new function "SciFloat" in fpspreadsheet.pas for this special formatting, but maybe the same is buried somewhere in the fcl.nfCurrency - like nfFixed, but adding the system's currency symbolnfShortDateTime - shows a date/time as DateToStr and hours and minutes nfFmtDateTime - allows an additional formatstring. In principle, this could be any string accepted by the "FormatString" function, but since the current implementation supports only built-in formats, only the following one are accepted: - "d/mmm" (can be abbreviated by "dm") - "mmm/yy" (can be abbreviated by "my") - "nn:ss" (can be abbreviated by "ms") - "nn:ss.zzz" (can be abbreviated by "msz") nfShortTime - time as hours and minutesnfLongTime - time as hours, minutes and secondsnfShortTimeAM - like nfShortTime, but in 12-hour format with "AM/PM" addednfLongTimeAM - like nfLongTime, but in 12-hour format with "AM/PM" addedThe method TSpreadsheet.WriteNumber was extended by optional parameters to specify the number format and the number of decimals (I thought this would be more compact than calling WriteNumber and WriteNumberFormat separately). Please note that the built-in Excel formats allow only for no or 2 decimals; different values are currently not written to the xls file - reading is ok though. Similarly, the method TSpreadsheet.WriteDateTime was extended to specify the NumberFormat and the special format string for nfFmtDateTime mentioned above. I updated the "excel8write" and "excel8read" demos with examples for all formatting options. Excel2007 can read the created file (there are #VALUE! cells, though, which are due to rpn formula issues and should be fixed some time...). OpenOffice accepts the file without any issues as well (it even can deal with the rpn formulas). Using this demo to create also sample files in other spreadsheet formats I found out that TCustomSpreadWriter.WriteDateTime is abstract and therefore I implemented a corresponding method for the other file format in the package. Since full date/time support is only available for BIFF8 the date/time is written as a string to the file in these cases. So, in summary, quite a few modifications. The patch, therefore, is rather long, sorry for that. The attached zip file contains the patch together with the full package files that I modified (plus the excel8 demos). I'd be happy if this code would find its way into the fpspreadsheet package. ---------------------------------------------------------------------- BigChimp · 2013-12-14 11:07 Very interesting! I'm sorry to be a bit harsh, but if you want me to have a look at the patch, I do request you add test cases to the unit tests to test read/write functionality: - read functionality from existing xls files written by Excel (such as testbiff8.xls) - write out and read back to temporary xls files within fpspreadsheet - optionally: write out to xls files (see manualtests.pas); user can manually use LibreOffice/Excel etc to open the resulting file testmanual.xls and verify correctness similarly to the existing tests. If not, perhaps Felipe or somebody else is willing to look at the patch. A question: as I mentioned in the other thread: the document "OpenOffice.org's Documentation of the Microsoft Excel File Format" (included in the fpspreadsheet reference subdirectory), 5.49, FORMAT, states: QuoteThe following table shows which number formats are used by default in a US-English environment. In what language versions of Excel have the number formats you introduce been tested? ---------------------------------------------------------------------- wp · 2013-12-14 16:31 Never worked with test cases before - my impression had been that their preparation requires more work than is saved. But anyway, I'll give it a try and have a look at your examples. But be prepared to answer some questions... Code: [Select]In what language versions of Excel have the number formats you introduce been tested?I am working with a German Windows, therefore I should have seen differences if Microsoft would not use US/English format instructions within the xls file on which my code relies. To be more specific, language code comes in at three places: Detection of the number or date/time values stored: Excel stores them in binary format, therefore, there is no need for a format string to extract a stored value. For dates, there is an issue with different date origins between Excel and fpc, but this already has been treated by youExtracting of the format for displaying numbers or date/times: Here I must emphasize again that my implementation only covers the built-in formats. Therefore, the format is determined by the index in the built-in format list. However, I did not want to be too detailed in TsNumberFormat items and used the same value for several formats. My nfFixed format, for example, covers the built-in formats #1 (no decimals) and #2 (2 decimals). To distinguish between them, I added the number of decimals to the format description in TCell (TNumberDecimals). From these, the format string for fpc's FormatFloat is derived: '0' for built-in format #1, '0.00' for built-in format #2 etc. There are more possibilities for date/times. But again, most format instructions can be identified from their index in the format list. There are only a few built-in format items requiring an explicit formatting instruction: #16 ("D-MMM"), #17 ("MMM-YY"), #45 ("mm:ss") and #47 ("mm:ss.0"). These cases have the NumberFormat value nfFmtDateTime in my implementation, the four cases are distinguished by their NumberFormatStr. In any case, the format string is built-up to be compatible with fpc's FormatFloat and FormatDateTime. Converting a binary number or date/time to text: if the cell is a number the format string is passed to FormatFloat function using the TCell.NumberValue, in case of a date/time it is passed to FormatDateTime using the TCell.DateTimeValue. Since fpc functions are used, the text string is always created on the basis of the current DefaultFormatSettings of Lazarus.In essence, the language in which the Exel file was written does not matter. I am aware that the format documentation mentioned by you points to some language-specific items, but I am treating those only as generic date/time data displayed in the same way as nfShortDateTime. This is not perfect, but better than showing an empty cell. If we once will be able to read/write the user-defined format styles I think the concept of a format string attached to TCell is still valid. In a user-defined format, the Excel file contains the Excel format string. This is in US-English as you noted, and in most cases this is identical to the format string required by fpc. I am aware that the formatting string of Excel can be much more complex than that of fpc. Therefore it must be the task of fpspreadsheet to detect such too- complex format instructions and decide on what to do: Either use a parser that can understand Excel's formatting language, or detect the basic data type and formatting (nfPercentage of "%" is at the end of the format string, a date if "/" is contained in the format string, a time if ":" is in the format string, etc.). The latter case is implemented in the code uploaded. ---------------------------------------------------------------------- BigChimp · 2013-12-14 16:50 Well, the doc explicitly says different language versions of *Excel* (not Windows) have different numbers. The format index numbers listed are for US Excel; it doesn't hint or indicate what other language versions would use but yes, likely (but not certainly) that's limited to date display. Then again. who knows. Anyway, I've got a Dutch Excel XP/2002 that I can use to test things. I'll be ready for any questions on test coverage.... IMO they're very useful indeed to guard against regressions - thereby saving total amount of work. Concerning custom formats etc: personnally I don't care that much about supporting all these formats. It'll end up in a horribly complicated translation layer and I can't imagine too many people will really care enough to use it... IMO the existing functionality is enough to import/export data which probably is the most important purpose. Otherwise, it's easy enough to use external references within another Excel file that get the data in an fpspreadsheet-generated xls and format/present it however the user wants. Just my 2 cents. ---------------------------------------------------------------------- wp · 2013-12-21 17:12 In the attachment there's a new patch with some bug fixes and a complete test case for the formatting code. Since I don't know how the patch file behaves when it gets binary files, I removed the testbiff8.xls and testbiff8-1899.xls files from the patch and attached them separately in the zip file. Although I was a bit doubtful about the usefulness of unit tests I have to admit that the wealth of data combinations did lead me to a variety of bugs that still had existed in the first submission. BigChimp, you can call me convinced. A few words about the changes: - I removed the currency number format since it may lead to confusion when several currencies are contained in the same spreadsheet, but only the system currency is detected. - I renamed nfTimeDuration to nfTimeInterval to keep the wording of the function "EncodeTimeInterval" of DateUtils.pas which also deals with more than 24 hours. ---------------------------------------------------------------------- BigChimp · 2013-12-22 09:46 Great, thanks, I'll have a look! ---------------------------------------------------------------------- BigChimp · 2013-12-22 14:02 Thanks a lot for that great contribution! Implemented patch with some slight additional modifications in r2863. Some remarks: 1) Please keep cell contents of testbiff8_1899.xls and testbiff8.xls the same (i.e. added texts, numbers) so the test suite will work with either version 2) Love your well documented set of numbers & text including format description! Gives PM and AM in my Dutch Excel XP so I suppose the US formats described in the OpenOffice xls format document seem to be applicable "cross Excel UI language" 3) I reverted (the EConvertError exception) in examples/excel8demo/excel8read.lpi for debugging; probably left over from testing? 4) Reverted changes examples/excel8demo/excel8write.lpi which seem cosmetic 5) Likewise examples/ooxmldemo/ooxmlwrite.lpi 6) Reverted tests/internaltests.pas: it seems to me the datetests unit is not required here??? 7) In xlsbiff8.pas, have replaced see "Microsoft Excel Format", page 174f. to section number 5.49 similarly to how it's done elsewhere in fpspreadsheet Added formattests unit added to spreadtestcli.lpr Still going to have a look at the test cases; might want to add some invalid data and see what happens. Thanks again! ---------------------------------------------------------------------- wp · 2013-12-22 16:58 Thanks for applying. Your changes are fine, sorry for leaving some garbage here and there before submitting. There is one issue, though: excel8write does not compile because I had renamed nfPercent to nfPercentage and obviously had not recompiled before uploading. In the attachment, you can find a patch for that. ---------------------------------------------------------------------- BigChimp · 2013-12-22 18:12 Thanks, r2864 Meanwhile having some second thoughts about text format used in biff2..5, xlsx, ooxml for date/time output. OS locale is used for  date format which will mess up interoperability. This si worse as biff8 output *doe* output properly locale-independent data. Will probably have a look tomorrow. Not hugely important anyway... ---------------------------------------------------------------------- BigChimp · 2013-12-23 12:12 Quote from: BigChimp on December 22, 2013, 07:12:11 pmMeanwhile having some second thoughts about text format used in biff2..5, xlsx, ooxml for date/time output. OS locale is used for  date format which will mess up interoperability. This si worse as biff8 output *doe* output properly locale-independent data. Implemented in revision 2865. Looking at LibreOffice/OpenOffice format, it might be quite easy to support writedatetime correctly there... Will have a look after looking some more at test cases ---------------------------------------------------------------------- wp · 2013-12-23 12:36 Thank you. Working fine. There was a minor shock, though, when excel complained about the file being damaged after opening the tmp file of the format test in Excel. However, this is due to an incorrect sheet name of the date/time test: in the constant "FmtDateTimesSheet" of unit "formattests" don't use the '/' character. I wonder why I did not see this in the first place. ---------------------------------------------------------------------- BigChimp · 2013-12-23 13:37 Ok will fix it fixed. Probably officially would need some kind of validation routine in sheet naming, but.... well... not interested. Who knows what .xlsx/.ods support compared to .xls etc. (Edit: BTW, IIRC from my tests, LibreOffice opened the xls file with Date/Time sheet just fine...) Meanwhile got basic support for .ods/libreoffice/openoffice date/time output going. ---------------------------------------------------------------------- wp · 2013-12-23 15:23 Quotesheet naming, but.... well... not interestedI agree. QuoteOS locale is used for  date format which will mess up interoperabilityWhat do you mean by that ("interoperability")? In all non-biff8 formats, so far, date/times are stored as strings. They can be anything, Excel does not need to convert to date/times, our reading functions neither. So no need for any "interoperability" as I can see. From this point of view, the written non-biff8 xls files lose clarity if date/time is written as ISO-formatted strings instead of OS-locale. ---------------------------------------------------------------------- BigChimp · 2013-12-23 16:35 Quote from: wp on December 23, 2013, 04:23:53 pmQuoteOS locale is used for  date format which will mess up interoperabilityWhat do you mean by that ("interoperability")? E.g. a German computer generating a BIFF2 xls file to exchange with an American. Quote from: wp on December 23, 2013, 04:23:53 pmIn all non-biff8 formats, so far, date/times are stored as strings. They can be anything, Excel does not need to convert to date/times, our reading functions neither. So no need for any "interoperability" as I can see. Apart from the above: whenever you start to automatically generate spreadsheet files, people start writing macros etc to extract the data from them automatically. Seems to be some law of nature. Having the exact same unambiguous date format, preferably a well- defined international standard, works best for this. Quote from: wp on December 23, 2013, 04:23:53 pmFrom this point of view, the written non-biff8 xls files lose clarity if date/time is written as ISO-formatted strings instead of OS-locale. Well: 1. IMO the benefits given above outweigh the drawbacks 2. The real solution is not fiddling with text output but implementing proper date/time output. Interested people are welcome to submit patches; most of the work for BIFF2,BIFF5 is done in the BIFF8 implementation (IIRC, location of some fields in the OLE document are different which needs to be accounted for but date calculation etc is the same). As I just implemented basic date/time support for LibreOffice, that leaves MS xlsx support; I vaguely remember it'll needs the same 1899/1904 date support but once again that can be reused. 3. If people are not interested in 2, they can write out their dates formatted as text as *text* cells the way they want directly. ---------------------------------------------------------------------- wp · 2013-12-23 17:12 Thanks for the explanation. It is clear now. Just perfect! ---------------------------------------------------------------------- eldonfsr · 2014-05-06 02:13 Compile exel8read and send me this error fpspreadsheet.pas(904,23) Error: function header "WriteNumber" doesn't match forward : var name changes ANumberFormat => AFormat ---------------------------------------------------------------------- wp · 2014-05-06 07:16 excel8read is running fine with me. Are you sure that you are using the correct version? Number format support is currently only available in the svn version (http://sourceforge.net/p/lazarus- ccr/svn/HEAD/tree/components/fpspreadsheet/). See also the wiki for documentation of the recent changes (http://wiki.lazarus.freepascal.org/FPSpreadsheet). ---------------------------------------------------------------------- eldonfsr · 2014-05-07 04:24 Thanks i downloading this version and compile yea runs great thanks for your help.  I going to do another test if something happend i let you know. thanks.