fpspreadsheet file writing issues lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- wp · 2012-04-22 21:11 When writing measurement data to an excel file using fpspreadsheet I can across two bugs: When more than a certain number of cells is written to the spreadsheet the file cannot be opened in Excel any more. The limit seems to depend on the file version (sfOpenDocuments does not seem to have this limitation though). See the attached demo for a demonstration. Change the constant N (the number of cells to be written) to a smaller value to get readable files.In the demo, the cell (0,2) is written by a datetime value (WriteDateTime). But after opening the file in Excel or LibreOffice, this cell is empty. I'm using a recent Lazarus snapshot with FPC2.6 on Win7 64-bit. The files were read with Excel2007 and LibreOffice 3.5 ---------------------------------------------------------------------- wp · 2012-04-23 20:05 I did some research on the corrupt file issue and found this interesting article: http://social.msdn.microsoft.com/Forums/hr/exceld ev/thread/69434b37-a3ed-4970-a929-89e03ee01aaf. "The main cause for this is because Windows 7 performs an additional "security scan" that Windows XP and Windows Vista did not enforce.  There is an internal Microsoft article that talks about changes to the OLE32 dlls, specifically how it reads the OLEFAT table.   Microsoft has NO PLANS to change this behavior.  Yay... thank you." As suggested in this article, I can successfully open the "defective" files using Open Office / LibreOffice and on a virtual machine with XP and the Excel Viewer. As for the datetime issue, I found in the source code of fpspreadsheet that the TCustomSpreadWriter does not yet support writing of Cell.ContentType = cctDateTime. Are there any plans to implement this? ---------------------------------------------------------------------- BigChimp · 2012-04-24 06:22 Haven't run the code or know much about fpspreadsheet but...at least some versions of Excel have a row limit of 65535 ---------------------------------------------------------------------- wp · 2012-04-24 06:47 I know, but the limit I am talking here is much lower, 3582. ---------------------------------------------------------------------- felipemdc · 2012-04-24 08:26 Quote from: wp on April 23, 2012, 10:05:37 pmAre there any plans to implement this? About the OLE Document issue, I didn't write our OLE doc writer, so I have no idea. You could try to contact the author of this part, or simply figure out yourself and make a patch with the necessary changes. I don't have Microsoft Office, so I cannot test this. And in general I am overloaded with Android, CustomDrawn and fpvectorial work at the moment and I also find OLE Document a overtly complex format and very boring to study. If you are willing to do the OLE doc fix then I could be compelled to implement the date writing. It shouldny be hard as I remember how I wrote the date reading. You are forgetting another solution, by the way, use the OOXML writer. I implemented it as my "solution" to the OLE Document issue. It doesn't have support for all features yet, however, and of course I would prefer if someone solves the OLE Document issue. ---------------------------------------------------------------------- wp · 2012-04-24 14:30 Thank you for your response. Well, I can have a look at the OLE issue, but I must confess that the OLE document scares me a lot, and I can't guarantee anything... Additionally, the documentation may not be up to date since the issues seem to have been introduced later with Win 7. Excel 2007 cannot read the OOXML version of the test file, Libre Office cannot either. Both can read the ODS version, though, even if the number of rows is set to be 100.000, but truncate after row 65536 - probably because TCustomSpreadWriter requires row and column indices to be of type WORD only. ---------------------------------------------------------------------- felipemdc · 2012-04-24 18:14 Try a minimal OOXML spreadsheet with fpspreadsheet. If it works (and it should work) then try to see what causes OOXML not to work in your case. That would also be useful. ---------------------------------------------------------------------- wp · 2012-04-24 22:43 I used your ooxmlwrite demo now - but same result: Excel 2007 (on Win7/64-bit) cannot read the file produced, the same for the current ExcelReader installed into a virtual machine with XP. LibreOffice and PlanMaker fail as well, PlanMaker even is not responding any more. OpenOffice, however, can read the xlsx file successfully. Then I removed some cells from the source code and suddenly got readable files. In particular, I had to remove the calls to WriteNumber and the adding of the second worksheet - see attached modified demo. ---------------------------------------------------------------------- felipemdc · 2012-04-25 06:28 Crap, I had modeled my OOXML writer after OpenOffice =( as I don't have MS Office. Also strange that it doesn't work in LibreOffice since it is the same thing as OpenOffice, only newer. It seams that older builds of OpenOffice had a buggy OOXML generation then. It should be really easy to fix this. Just compare the created file to what MS Office does. The xlsx is just a zip file. And then make appropriate changes in the writer to fix the file. ---------------------------------------------------------------------- wp · 2012-04-26 19:17 There's quite a lot of differences because Microsoft, as usual, had added more to the xml files than what would be absolutely necessary. Regarding the issues with writing floating point numbers your structure is correct. The problem was with locale settings -- I am using non-US language settings here. Therefore, the floating point numbers were written into the sheet1.xml file with a comma, not a period. In the writing code, I am using now FormatSettings with the correct decimal separator. I am attaching the modified xlsxooxml.pas  and a patch with the differences to the current svn version. If necessary I can also create a ticket for the Bug Tracker. BTW: How can I retrieve the standard US format settings? The DefaultFormatSettings are already localized. Regarding the issue with adding an additional worksheet, I am still seeking. Basically, all files needed seem to be present, and the relationships seem to be correct. I'll keep you updated. ---------------------------------------------------------------------- Ask · 2012-04-27 07:33 By the way, there is another interesting option for exporting files data to Excel -- .vbs (VB Script) file. Since vbs is just a plain text, it is much easier to create. It also has a very high level of compatibility between MS Office versions, does not have arbitrary limitations and does not depend on locale settings. Downsides are: 1) Low compatibility with LibreOffice 2) Some versions of Excel request user confirmation when opening scripts 3) Somewhat slow for large data sets. ---------------------------------------------------------------------- felipemdc · 2012-04-27 08:05 Quote from: wp on April 26, 2012, 09:17:42 pmIn the writing code, I am using now FormatSettings with the correct decimal separator. I am attaching the modified xlsxooxml.pas  and a patch with the differences to the current svn version. If necessary I can also create a ticket for the Bug Tracker. Great catch! =) There was already similar code for OpenDocument, so I made some changes to your patch and applied it at revision 2410. Please test. I think that persisting and fixing remaining bugs in fpSpreadsheet is the best solution for spreadsheet reading/writing. It is 100% Pascal so it works in all platforms and has no external dependencies =) QuoteBTW: How can I retrieve the standard US format settings? The DefaultFormatSettings are already localized. I think you cannot, except by going to a computer with US formatting and reading all the values and hard-coding them in our code. But only the decimal separator is relevant here, so the rest should be irrelevant. ---------------------------------------------------------------------- wp · 2012-04-27 22:21 Ok - found now also the bug which prevented adding sheets to the xlsx file. It was an incorrect tag - Excel allows only one sheets tag: Written by fpspreadsheet: Code: [Select]                Written by Excel, LibreOffice etc: Code: [Select]            That makes perfect sense, but I can't remember how often I have compared these sections and did not spot the difference... Enclosed is another patch which corrects this issue. Going back to the first posting of this thread: That demo now runs fine for xlsx files. Quoteonly the decimal separator is relevant here Only for the moment. If we'll once have datetimes in the file we will face the same problem... ---------------------------------------------------------------------- felipemdc · 2012-04-29 15:26 Thanks, applied the patch in rev 2412 Quote from: wp on April 28, 2012, 12:21:31 amOnly for the moment. If we'll once have datetimes in the file we will face the same problem... I don't know about XLSX, but in XLS (biff) dates are stored as numbers, not text. ---------------------------------------------------------------------- wp · 2012-04-29 15:45 QuoteI don't know about XLSX, but in XLS (biff) dates are stored as numbers, not text. You are right: I did some experiments with datetimes in XLSX in the meantime and found out that they are stored as numbers, like TDateTime. ---------------------------------------------------------------------- marcov · 2012-04-29 15:56 Quote from: felipemdc on April 27, 2012, 10:05:31 amI think you cannot, except by going to a computer with US formatting and reading all the values and hard-coding them in our code. But only the decimal separator is relevant here, so the rest should be irrelevant. On Windows you can use sysutils.getlocaleformatsettings. It is a standard Delphi call, but unfortunately it uses a Windows LCID as argument so it is windows only and marked with "platform;" ---------------------------------------------------------------------- José Mejuto · 2012-05-15 17:50 Quote from: wp on April 22, 2012, 11:11:34 pmWhen writing measurement data to an excel file using fpspreadsheet I can across two bugs: When more than a certain number of cells is written to the spreadsheet the file cannot be opened in Excel any more. The limit seems to depend on the file version (sfOpenDocuments does not seem to have this limitation though). See the attached demo for a demonstration. Change the constant N (the number of cells to be written) to a smaller value to get readable files.[...] Hello, The bug has been fixed in revision 2454. The bug happends when the file needs more than 2 FAT sectors as it suddenly jumps to 128 instead 3 (missing division). I land in this post by chance, I hope the fix arrives in time. ---------------------------------------------------------------------- BigChimp · 2012-05-15 18:16 Thanks, joshy. Just tested with wp's initial test program and fpspreadsheet r2434. Edit: Windows Vista x64 Test5.xls and Test8.xls can be opened by Excel 2000 (XP) The .xlsx file can't be opened as it is a newer format (don't intend to download compatibility pack...) LibreOffice 3.5.2.2: test5.xls, test8.xls open. On opening the .xlsx file, it asks for a filter/file type. Selected Microsoft 2007/2010 XML=> general error. Test.ods opens on libreoffice. ---------------------------------------------------------------------- José Mejuto · 2012-05-15 19:03 Quote from: BigChimp on May 15, 2012, 08:16:12 pmThanks, joshy. Just tested with wp's initial test program and fpspreadsheet r2434. Test5.xls and Test8.xls can be opened by Excel 2000 (XP) The .xlsx file can't be opened as it is a newer format (don't intend to download compatibility pack...) LibreOffice 3.5.2.2: test5.xls, test8.xls open. On opening the .xlsx file, it asks for a filter/file type. Selected Microsoft 2007/2010 XML=> general error. Test.ods opens on libreoffice. Hello, xlsx is a different beast, the fix only affects the .xls ones under Windows 7. ---------------------------------------------------------------------- BigChimp · 2012-05-15 19:20 Hi Joshy, Didn't know that or more correctly, I did know it was different, just didn't care: just blindly tested everything Thanks for the fix! ---------------------------------------------------------------------- José Mejuto · 2012-05-15 20:17 Quote from: BigChimp on May 15, 2012, 09:20:38 pmHi Joshy, Didn't know that or more correctly, I did know it was different, just didn't care: just blindly tested everything Thanks for the fix! Hi, basically the xls, doc, ppt, etc from Microsoft family are something like a big disk (up to 2 GB ?) which can have files inside (streams), folder, etc, while xlsx is a zip file with several .xml inside. So xls is a quite complex structured storage which works more or less like a disk, where you can write files, folders, delete, move, alter, etc without rewritting the whole file. ---------------------------------------------------------------------- wp · 2012-05-15 22:15 Thank you, JoshyFun. All test files open fine in Excel 2007 and OpenOffice now. I wasn't aware that the xls file format is so close to hardware. One small issue left, although not very important in practice: to my knowledge, xlsx and ods allow for more than 65536 rows. But when I use in my demo N=70000 rows the data vanish after 65536 rows. The reason seems to be that fpspreadsheet is not very consistent with data types for row and cell addresses. TsWorksheet uses cardinal for ARow and ACol indexes while the TSpreadWriter uses word only. ---------------------------------------------------------------------- José Mejuto · 2012-05-16 08:01 Quote from: wp on May 16, 2012, 12:15:20 amThank you, JoshyFun. All test files open fine in Excel 2007 and OpenOffice now. I wasn't aware that the xls file format is so close to hardware. One small issue left, although not very important in practice: to my knowledge, xlsx and ods allow for more than 65536 rows. But when I use in my demo N=70000 rows the data vanish after 65536 rows. The reason seems to be that fpspreadsheet is not very consistent with data types for row and cell addresses. TsWorksheet uses cardinal for ARow and ACol indexes while the TSpreadWriter uses word only. Yes, maybe the "word" is inherited by the very first format used to write. Maybe Felipe can say if the "words" can be changed to cardinal without side effects. ---------------------------------------------------------------------- felipemdc · 2012-05-16 08:23 Quote from: JoshyFun on May 16, 2012, 10:01:11 amYes, maybe the "word" is inherited by the very first format used to write. Yes, fpspreadsheet started in 2007 and at that time Excel was limited to a maximum of Word rows, so I saw no need to make more space. I also don't use more then that in my files. It can be changed to cardinal safely. ---------------------------------------------------------------------- wp · 2012-05-20 16:51 The attached patch replaces the WORD row/cell indices with CARDINALs. It would be fine if somebody with write-access to the svn could apply it. [EDIT] Added a check for the maximum row and column index in case of the old xls formats. See patch WordToCardinal1. [2nd EDIT] No! Not my best day today... The changes introduced in the new patch WordToCardinal1 don't compile.