load broken excel file lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- vk_barnaul · 2015-09-17 03:37 stack trace: #0 INTERNALREADFROMSTREAM(0x8b92100, 0x8d64cd0) at source\xlscommon.pas:2359 #1 READFROMSTREAM(0x8b92100, 0x8ba4b20) at source\xlsbiff5.pas:810 #2 READFROMFILE(0x8b92100, 0x8cd30d8 '1.XLS') at source\fpsreaderwriter.pas:307 #3 READFROMFILE(0x8bf0a30, 0x8cd30d8 '1.XLS', SFEXCEL5) at source\fpspreadsheet.pas:6653 point #0 -       raise Exception.Create('[TsSpreadBIFFReader.InternalReadFromStream] Reading of OLE document failed'); return to try finally block in point  #1 clear exception and return to point #2 and to point #3 but in this place our reader don't status error and continue run with broken data. wp, please, help me. brkoken excel file url https://drive.google.com/file/d/0B0YrxFV- r1slMHJOU2JWM2dTdXc/view?usp=sharing ---------------------------------------------------------------------- derek.john.evans · 2015-09-17 05:25 What were you hoping to achieve? Loading a 9 meg XLS which contains VB scripting, might be wishful thinking. You may have better luck automating office yourself (which is what fpspreadsheet already does), but you may be able to locate the reason why you are getting "Reading of OLE document failed". http://wiki.lazarus.freepascal.org/Office_Automation http://forum.lazarus.freepascal.org/index.php?topic=16910.0 Maybe, better explain what you are trying to achieve. ie: Data viewer, data extract, etc. It might not be possible, or at least, it might be easier to export the data using Excel. ---------------------------------------------------------------------- rvk · 2015-09-17 09:14 Quote from: Geepster on September 17, 2015, 07:25:55 amYou may have better luck automating office yourself (which is what fpspreadsheet already does), ... fpspreadsheet definitely doesn't use Office automation. It read the raw Excel file and interprets it. No need for Office on your PC at all. Quote from: Geepster on September 17, 2015, 07:25:55 amLoading a 9 meg XLS which contains VB scripting, might be wishful thinking. Actually it's not wishful thinking. VB scripts might not work but during reading they can (and should) be skipped. LibreOffice does read the 9MB XLS file correctly so I'm not sure what exactly is "broken" in the file. (It does complain about links to external files) It just might be that fpspreadsheet encounters something unexpected during reading and that could be fixed (by wp  ) ---------------------------------------------------------------------- wp · 2015-09-17 13:07 A tough one... This file uses a huge shared string table which, because a biff8 file record can only contain 8224 bytes, contains more CONTINUE records than I've ever seen. The problem is that the last CONTINUE record seems to be defective, it contains a string which is longer than the record itself. Simultaneously this string contains also additional asian-phonetic information which was not heavily tested due to lacking correct data files. Therefore, I am not sure whether the crash that you observe is due to a bug in fpspreadsheet or a faulty file. An argument against the latter case would be that Excel2007 and LibreOffice open the file without any problems. At to moment, I am a bit lost... Do you really need fpspreadsheet for your task, or can you convert the file to xlsx which will be successfully loaded by fpspreadsheet (please use the current trunc versions - there was a bug with font size reading). ods runs out of memory on my system (did not do any optimization though). You can use LibreOfficeCalc for the conversion. Also consider that your file contains a lot features that are not supported by fpspreadsheet. They will be lost when you re-save the modified file! ---------------------------------------------------------------------- vk_barnaul · 2015-09-17 14:26 i use fpspreadsheet only for read data from files. every day 40-50 files. I can not read only some. I always use current trunc versions with litle dirty patch: 1. xlsxooxml.pas                                                  function TsSpreadOOXMLReader.ReadFont(ANode: TDOMNode): Integer;     if nodename = 'sz' then     begin       s := GetAttrValue(node, 'val'); >>      if s <> '' then fntSize := StrToFloat(s); << if s <> '' then fntSize := StrToFloat(UTF8StringReplace(s, '.', ',', [rfReplaceAll]));     end 2. procedure TsSpreadOOXMLReader.ReadSheetViews(ANode: TDOMNode; AWorksheet: TsWorksheet);  >>         if s <> '' then AWorksheet.TopPaneHeight := StrToInt(s); <<            if s <> '' then AWorksheet.TopPaneHeight := StrToInt(UTF8StringReplace(s, '.0', '', [rfReplaceAll])); thanks to your last patch. I redid my corrections to a beautiful version. It's all simple cases. much more difficult to catch falling parsing xml (while reading xmls). ---------------------------------------------------------------------- wp · 2015-09-17 14:55 Aah, I missed your second item... The new uploaded version of the xlsx reader takes care of non-integer values of the fixed pane sizes. (There may be more of these cases where the xml accepts floating point values instead of integers). If you are forced to patch fpspreadsheet to overcome bugs or issues, please feel free to post this here or in the bug tracker. Otherwise it is not possible to fix these issues for other users. And for yourself it is very difficult to keep in sync with the rapidly changing trunk version. ---------------------------------------------------------------------- derek.john.evans · 2015-09-17 21:46 Quote from: rvk on September 17, 2015, 11:14:17 amQuote from: Geepster on September 17, 2015, 07:25:55 amYou may have better luck automating office yourself (which is what fpspreadsheet already does), ... fpspreadsheet definitely doesn't use Office automation. It read the raw Excel file and interprets it. No need for Office on your PC at all. Quote from: Geepster on September 17, 2015, 07:25:55 amLoading a 9 meg XLS which contains VB scripting, might be wishful thinking. Actually it's not wishful thinking. VB scripts might not work but during reading they can (and should) be skipped. LibreOffice does read the 9MB XLS file correctly so I'm not sure what exactly is "broken" in the file. (It does complain about links to external files) It just might be that fpspreadsheet encounters something unexpected during reading and that could be fixed (by wp  ) Mmm. Maybe I have a weird version. All I did was search my component packages for the string "Reading of OLE document failed". It found 2 locations: Units xlsbiff8 and xlsbiff5. The error relates to TOLEStorage.ReadOLEFile() which seems to use uvirtuallayer_ole, which has this at the top "Creates a virtual layer over a stream to access an Microsoft OLE document". I'm now unsure what "Microsoft OLE document" is? Is that not part of Office? I might have todo some reading. ---------------------------------------------------------------------- wp · 2015-09-17 22:13 Your version is fine, but fpspreadsheet definitely does not use Office automation, one of its main advantages. Yes, there is a TOLEStorage, but this just models the internal file structure of an excel file, look at the compdocfileformat.pdf file in the "reference" folder of the fpspreadsheet installation for more information, it does not depend of MS Office being installed.