A challenge file for fpspreadsheet lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Leledumbo · 2015-03-14 16:31 I have a big xlsx file that even WPS spreadsheet refuses to open. The file seems to be originally created under MS Office and is ~5 MB in size. I tried spready demo to open this file and it's stuck in minutes opening the file that I finally have to kill to stop it. I'm not in a rush or something, but if fpspreadsheet can open this file, then it will be just great : https://dl.dropboxusercontent.com/u/22124591/Docs/Voucher%20code%20- %20sign%20up%20bonus.zip ---------------------------------------------------------------------- Nitorami · 2015-03-14 17:53 Gnumeric portable opens it straightaway. ---------------------------------------------------------------------- Scoops · 2015-03-14 17:55 Opened the file with openoffice and the row count was 65536, I have encountered this with alot of spreedsheet software 2 to the power of 16 the limit for such software. OK this doesnt directly answer your request but it might be the problem. ---------------------------------------------------------------------- wp · 2015-03-14 17:57 Strange... In the fpsspeedtest (thanks to rvk) we test various file sizes, the smallest one (10.000x100 cells) which has about the same number of cells like yours (100.000x10 cells) is opened by fpspreadsheet within 6 seconds (without a gui, though). Let me investigate, and I'll come back when I have an idea or solution. ---------------------------------------------------------------------- Leledumbo · 2015-03-14 18:10 Quote from: Scoops on March 14, 2015, 06:55:52 pmOpened the file with openoffice and the row count was 65536, I have encountered this with alot of spreedsheet software 2 to the power of 16 the limit for such software. OK this doesnt directly answer your request but it might be the problem. 100,000 to be precise. Some spreadsheet software does have 64K limits in row/col count. Quote from: wp on March 14, 2015, 06:57:25 pmStrange... In the fpsspeedtest (thanks to rvk) we test various file sizes, the smallest one (10.000x100 cells) which has about the same number of cells like yours (100.000x10 cells) is opened by fpspreadsheet within 6 seconds (without a gui, though). Let me investigate, and I'll come back when I have an idea or solution. Definitely a good thing for fpspreadsheet stress test ---------------------------------------------------------------------- Scoops · 2015-03-14 18:18 @Leledumbo Openoffice which is in my opinion a decent bit of software, cant go past 65536 rows then ? Thx for the info ---------------------------------------------------------------------- wp · 2015-03-14 18:28 @Scoops: No, OpenOffice opens all 100.000 rows; its limit is 1.048.576 rows x 1024 cols (current version 4, older ones have less). It is an fpspreadsheet issue: After trimming the file down to 1000 rows fpspreadsheed opens it after 1.8 sec; after commenting the code which writes the cells to the TsWorksheet leaving only the code traversing the xml nodes of the xlsx file structure the time drops to about 0.08 sec. ---------------------------------------------------------------------- Scoops · 2015-03-14 18:38 @wp, OK my bad i'm still using V3.2.0  , thx ---------------------------------------------------------------------- wp · 2015-03-14 18:45 Is anybody familiar with AVLTrees here (class TAVLTree in unit AVL_Tree)? fpspreadsheet stores the cells in an AVLTree such that they are always sorted along row and then along column. In the spreadsheet files the cells are already arranged in this way. In fpspreadsheet, however, we are a bit too conservative and first seek the entire tree if there exists already a cell at the new position and, if not, creates one. Since during reading the worksheet is initially empty I am pretty sure that the existence check can be omitted. The next point is adding the new node. I think that the AVLTree, again, is conservative and seeks the correct insert position. But we know that the new cell must be immediately next to the previous one. Is there a way to add a node to the AVLTree in this way? ---------------------------------------------------------------------- wp · 2015-03-14 20:00 No, false track... This AVLTree is extremely fast. Dropped the "FindCell" which looks for existing cells - almost no change. Another candidate is the ChangeCell method which fires whenever a cell changes. Apart from updating the gui it checks whether the new cell belongs to an existing formula and triggers recalculation of this formula. I see now that this part offers a wide field of improvements. In the first test, just bypassing the formula thing, the effect is huge: the test file stripped down to 10.000 rows opens now in 8 seconds instead of 144. We will get there... ---------------------------------------------------------------------- wp · 2015-03-16 08:54 I see now that the basic fpspreadsheet library reads the file in 6 seconds. The reason why it takes so long in spready is due to unoptimized routines in TsWorksheetGrid. ---------------------------------------------------------------------- wp · 2015-03-16 17:47 The current fpspreadsheet revision in trunk applies various improvements. In the end, the file appears after 7-8 seconds in Spready (Excel2007 does the job in 3 seconds, while OpenOffice Calc needs almost 1 minute). ---------------------------------------------------------------------- rvk · 2015-03-17 08:28 For me LibreOffice loads the file in 2 seconds... but kodus for optimizing spready to load it so much faster than it did before. I do still have a GDB-crash after reading this file and closing spready. When I get some time I'll try the latest trunk of Lazarus/FPC and see where it crashes. (It's only the debugger that crashes. Outside the IDE it runs fine.) (Lazarus 1.5 r47591M FPC 3.1.1 i386-win32-win32/win64, fpspreadsheet 4039) ---------------------------------------------------------------------- wp · 2015-03-17 09:11 QuoteFor me LibreOffice loads the file in 2 secondsCorrect, I only tested OpenOffice before writing this - LibreOffice needs 4 seconds on my system. ---------------------------------------------------------------------- Leledumbo · 2015-03-17 09:22 I'll retest tonight ---------------------------------------------------------------------- rvk · 2015-03-17 10:45 The problems I'm having with GDB-crashing when closing spready has nothing to do with spready itself. It's a problem with TOpenDialog and TSaveDialog on my Lazarus installation (I also have it with the latest trunk). I'm not sure if it's a bug or something that I did wrong but I also encountered it some time ago. I never got around to debugging this but everytime I do OpenDialog1.Execute or SaveDialog1.Execute when closing my simple app (with just one button) I get this GDB-crash. (Outside the IDE it works fine) Either way... it's not due to fpspreadsheet ---------------------------------------------------------------------- marcov · 2015-03-17 12:39 Quote from: rvk on March 17, 2015, 11:45:59 am something that I did wrong Some shell plugins can cause problems in these dialogues because of their exception system.  64-bit FPC 3.x.y should be fixed for that, 32-bit needs to be recompiled with -dTEST_WIN32_SEH, but there is some discussion if the same issue as on 64-bit is also happening on 32-bit. (iow if it helps) ---------------------------------------------------------------------- rvk · 2015-03-17 13:04 Quote from: marcov on March 17, 2015, 01:39:42 pmSome shell plugins can cause problems in these dialogues because of their exception system.Ah. Ok. (so not something I did ) I set DisableLoadSymbolsForLibraries to true in the IDE options under Debugger/General and that fixes it for now. http://wiki.lazarus.freepascal.org/GDB_Debugger_Tips#gdb.exe_has_stopp ed_working (Will try the TEST_WIN32_SEH sometime later) ---------------------------------------------------------------------- marcov · 2015-03-17 13:24 Quote from: rvk on March 17, 2015, 02:04:58 pmQuote from: marcov on March 17, 2015, 01:39:42 pmSome shell plugins can cause problems in these dialogues because of their exception system.Ah. Ok. (so not something I did ) Well, those plugins don't come standard with Windows :-) ---------------------------------------------------------------------- rvk · 2015-03-17 13:35 Quote from: marcov on March 17, 2015, 02:24:03 pmQuote from: rvk on March 17, 2015, 02:04:58 pmQuote from: marcov on March 17, 2015, 01:39:42 pmSome shell plugins can cause problems in these dialogues because of their exception system.Ah. Ok. (so not something I did ) Well, those plugins don't come standard with Windows :-) Well, I don't have many (or exotic) shell-plugins. 7-Zip, TortoiseSVN and Teracopy (I take it, those are some of the plugins you mean). I'll try to find out with a "clean" VM which one could be the cause of this. ---------------------------------------------------------------------- Leledumbo · 2015-03-17 14:42 Retested. Good job! Now it opens around 7.5 secs on my laptop