Can't read LibreOffice files .ods lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- franzala · 2020-05-21 21:00 With Lazarus 1.8.2    FPC 3.0.4 and FPSpreadsheet 1.10.1 I can't read .ods files neither with the unit fpsopendocument in the uses clause, which gives the message unsupported file format nor with the unit fpsallformats which tries to open an xlsx file and finds a defective structure. xlsx files are opened without problem. Did I miss something? ---------------------------------------------------------------------- wp · 2020-05-21 21:25 Please show your code. And is it really an ODS file? Rename the ODS file to ZIP and extract its contents to some temp directory, you should see xml files such as "content.xml", "styles.xml" etc. If you cannot do this or if you do not get these files it is not an ODS file. If everything works you should upload the file to the forum (or to some cloud space and send me the link by PM) so that I can have a closer look. ---------------------------------------------------------------------- franzala · 2020-05-22 12:18 My ods file was created with LibreOffice, consequently I consider it as a genuine ods file (but maybe it's wrong). While preparing my answer,I tried to continue despite the error message and was surprised to see that the few lines of my test code seem to work properly: the different variables contain the correct value. I attached the complete unit of my code and an ods file created with LibreOffice which generates the error message when I debug. When I save that file with LibreOffice (under Ubuntu 18.04.4 LTS) as xlsx file and change the uses clause, I do not have any error message. PS: due to the restrictions in allowed formats, I zipped both file ---------------------------------------------------------------------- wp · 2020-05-22 12:30 Sorry - the attachment contains only empty directories. ---------------------------------------------------------------------- straetch · 2020-05-22 14:27 I have the same problem. When I open a .ods file, created by Libreoffice, I get a Debugger Exception Notification with the message: Project ... raised exception class 'EFpSpreadsheetReader' with message Defective internal structure of xlsx file in file 'source\common\xlsxooml.pas' at line 2538. As per your earlier post, I changed the extension of the .ods file to .zip and I see the files content.xml, settings.xml , styles.xml etc. Using Lazarus 2.0.6 and fpspreadsheet 1.10.1 ---------------------------------------------------------------------- franzala · 2020-05-22 15:03 My mistake, sorry.  Attached is a hopefully better zip file. ---------------------------------------------------------------------- franzala · 2020-05-22 15:28 Just a detail, with the unit fpsopendocument in the uses clause, the EFPSpreadsheetReader error message is slightly different: tried to read spreadsheet using unsupported format, but the result seems to be the same. ---------------------------------------------------------------------- straetch · 2020-05-22 15:49 As an addition: With the version of fpspreadsheet I used before (I dont'n know anymore which version number), I did not have this problem. It is only with recompiling the unchanged program with version 1.10.1 that the problem came up. ---------------------------------------------------------------------- wp · 2020-05-22 16:29 Ok I understand now. This is an exception which occurs during format detection only when the IDE is running, it does not occur when you run your exe outside the IDE (or with "Run" > "Run without debugger"). Let me explain: Your code "wb1.ReadFromFile(OD1.FileName)" does not contain a specification of the file format. Therefore fpspreadsheet tries to detect the format automatically. In a first step it reads the first few bytes from the file and tries to identify the format from the file header. File formats which cannot be identified from the header are opened for testing, and an exception is raised when the format does not match. This exception is what you are seeing. It is handled immediately and silently by the reader so that testing can proceed with the next format. This reading procedure has been changed in the current trunk version of fpspreadsheet already some time ago, and the exception does not occur here. Unfortunately the new features have not been released yet (because I had began adding chart support, which maybe was not a good idea...). So, in your version, you can safely ignore this exception. I am not 100% sure but you could try to add the exception type EFpSpreadSheetReader to the ignore list of the project (in v1.8.2 this is in "Tools" > "Options" > "Debugger > "Language Exceptions"", now it is in "Project" > "Options" > "Language Exceptions"). Alternatively you can avoid the exception when you specify the format in the ReadFromFile call: "wb1.ReadFromFile(OD1.FileName, sfOpenDocument)". ------------ In very old fpspreadsheet versions this exception did not occur because file formats were handled differently. This was changed to allow for user-defined spreadsheet format. ---------------------------------------------------------------------- straetch · 2020-05-23 06:59 Thanks for the prompt reply. Let me see if I get it right. In the error message "Debugger Exception Notification" form, there is a checkbox "Ignore this exception type". I could check this and click on Continue. Will the automatic format detection then still work, since the reader uses the exception to work correctly? Knowing this, it is a minor nuisance that I can live with for some time until you make a new official release of fpspreadsheet. Any idea when? Is there a patch I can use to overwrite the reader? ---------------------------------------------------------------------- wp · 2020-05-23 08:28 Quote from: straetch on May 23, 2020, 08:59:01 amIn the error message "Debugger Exception Notification" form, there is a checkbox "Ignore this exception type". I could check this and click on Continue. Will the automatic format detection then still work, since the reader uses the exception to work correctly?Yes this should work. It certainly will not affect automatic format detection. Quote from: straetch on May 23, 2020, 08:59:01 amI can live with for some time until you make a new official release of fpspreadsheet. Any idea when? I don't know. I had begun implementing chart support already some time ago, but gut stuck due to the immense amount of data needed in the files for them, and I lost interest in the subject. I don't want to have an incomplete feature in the library. So, maybe I should undo the related changes? Quote from: straetch on May 23, 2020, 08:59:01 amIs there a patch I can use to overwrite the reader?IIRC there were too many related changes. ---------------------------------------------------------------------- lucamar · 2020-05-23 08:52 Quote from: straetch on May 23, 2020, 08:59:01 amIn the error message "Debugger Exception Notification" form, there is a checkbox "Ignore this exception type". I could check this and click on Continue. Will the automatic format detection then still work, since the reader uses the exception to work correctly? Yes, if you check "Ignore this exception type" you'll not be bothered by them anymore, but if you don't want to ever see them again go to "Tools->Options->Debugger" and add it to the list of ignored exceptions (see atached image). ---------------------------------------------------------------------- wp · 2020-05-23 08:55 Quote from: lucamar on May 23, 2020, 10:52:54 amQuote from: straetch on May 23, 2020, 08:59:01 amIn the error message "Debugger Exception Notification" form, there is a checkbox "Ignore this exception type". I could check this and click on Continue. Will the automatic format detection then still work, since the reader uses the exception to work correctly? Yes, if you check "Ignore this exception type" you'll not be bothered by them anymore, but if you don't want to ever see them again go to "Tools->Options->Debugger" and add it to the list of ignored exceptions (see atached image). To my knowledge it is project-specific. Having this option in the IDE options was misleading and this is why it was moved to the project options in trunk. ---------------------------------------------------------------------- lucamar · 2020-05-23 09:11 Quote from: wp on May 23, 2020, 10:55:31 amTo my knowledge it is project-specific. Having this option in the IDE options was misleading and this is why it was moved to the project options in trunk. Yes, it says it right there: "These settings are stored with the project". I wrote: "(for the current project)" in my answer but it has somehow dissapeared (a "cut/forgot-to-paste" problem) ---------------------------------------------------------------------- franzala · 2020-05-23 10:26 Thanks for your replies and help. Since Libre and OpenOffice are able to save and read files under xlsx format, which has apparently no problem with FRSpreadsheet, I think I will consider to use that file format instead of ods (provided there are no problematical format conversion restrictions, which I will have to check). I don't like too much to ignore an error message which eventually could be raised by other problems than the one we try to solve. I'd preferred use open source solutions, but I hope it will be a temporary exception. As far as I'm concerned, this topic could be considered as solved. ---------------------------------------------------------------------- wp · 2020-05-23 10:58 Quote from: franzala on May 23, 2020, 12:26:54 pmThanks for your replies and help. Since Libre and OpenOffice are able to save and read files under xlsx format, which has apparently no problem with FRSpreadsheet, I think I will consider to use that file format instead of ods (provided there are no problematical format conversion restrictions, which I will have to check). I don't like too much to ignore an error message which eventually could be raised by other problems than the one we try to solve.But for the other cases which are not handled by the library you will still see the error message displayed by the application (the one you see is displayed by the debugger). In this (rare) case you still can reactivate the debugger exception to make the program stop when the exception occurs. Quote from: franzala on May 23, 2020, 12:26:54 pmI hope it will be a temporary exception.I don't think so. Raising an exception when a file cannot be read is the ultimate notification of the user, and it will always be there. But let me say again: You can avoid the exception when you specify the format in the ReadFromFile call. So when you know that your files are .ods, then why don't you specify the format directly instead of (ab)using automatic format detection? And another solution which has not been mentioned before: You could also switch your fpspreadsheet to the trunk version in which format detection is a bit more sophisticated. Just download the snapshot from https://sourceforge.net/p/lazarus- ccr/svn/HEAD/tree/components/fpspreadsheet/. ---------------------------------------------------------------------- straetch · 2020-05-24 08:51 In my case the automatic format detection is necessary. My program collects spreadsheets from different authors that can provide their data in different formats (xls, xlsx, ods, csv). I have set "ignore this exception type" at project level. I don't worry about this because the exception class 'EFpSpreadsheetReader' is defined in the fpspreadsheet package. Since the author of the package says there is no problem with this, why should I doubt this? I am happy with the temporary solution. Many thanks. ---------------------------------------------------------------------- wp · 2020-06-03 09:21 Quote from: straetch on May 23, 2020, 08:59:01 amKnowing this, it is a minor nuisance that I can live with for some time until you make a new official release of fpspreadsheet. Any idea when? A new version 1.12 has been released yesterday, it is available via OPM. ---------------------------------------------------------------------- franzala · 2020-06-05 17:34 I have tried the version 1.12 with a short test code;that new version works fine and and  is able to read the Libre/OpenOffice ods fileformat with and without specifying the fileformat in the procedure ReadFromFile. But I have now a problem with an older application which I used with xlsx files; I obtain now the following error message on debuging : operator is not overloaded : - ''TSWorkbook'' Any idea about what could cause that error message? ---------------------------------------------------------------------- wp · 2020-06-05 17:41 Please show more code ---------------------------------------------------------------------- franzala · 2020-06-05 20:03 I have cut everything except the procedure where the error arrises and checked that the error message remains; it is the case with the following code: . procedure TFocpta1g.MFiOuvClick(Sender: TObject);       //M Fichier Ouvrir begin   OD1.Title:='fichier ... format ods  ';   OD1.Filter:='fichier ods|*.ods';   if OD1.Execute then     ficpta:=OD1.FileName;-   wb1:=TsWorkbook.Create;   wb1.ReadFromFile(ficpta,sfOpenDocument);   ws1:=wb1.GetWorksheetByName('Param'); end; ---------------------------------------------------------------------- wp · 2020-06-05 20:32 Probably the '-' at the end of the line "ficpta := OD1.FileName;-". Another issue is that a workbook is created although the OpenDialog has been aborted; a begin-end block is missing after the "if" downto the "end": Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TFocpta1g.MFiOuvClick(Sender: TObject);       //M Fichier Ouvrirbegin  OD1.Title:='fichier ... format ods  ';  OD1.Filter:='fichier ods|*.ods';  if OD1.Execute then  begin                       // <------ "begin" added to execute everything when the OpenDialog has been closed successfully.    ficpta:=OD1.FileName;     // <---- remove the '-' at the end of the line    wb1:=TsWorkbook.Create;    wb1.ReadFromFile(ficpta,sfOpenDocument);    ws1:=wb1.GetWorksheetByName('Param');  end;                        // <-------- "end" addedend;