Recent

Author Topic: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?  (Read 10813 times)

Soner

  • Sr. Member
  • ****
  • Posts: 305
Is the ODS-format(LibreOffice) not supported by fpspreadsheet?

I can not open my ods-file with the example application spready.
I have 2 filetypes in attachment. One file is very simple and created with libreoffice 6.  Spready can not  open this file when it contains the formula "WENN".
And the second file has 4 different file formats, spready can open only XLSX-File that created with Excel 2011.

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #1 on: August 09, 2018, 11:33:50 am »
Yes it  has bug and fpSpreadsheet can not handle "wenn" formula from ODS-documents. ("wenn" is "if"-formula in german.)
The problem is LibreOffice(German Version) does not accept "if" as formula name.
fpSpreadsheet can only handle xlsx-files with this formula.
for xls-Files(office 2002) in some cells accepted.

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #2 on: August 09, 2018, 03:08:01 pm »
I tried to implent it for excel8-format, it's half solution.
I created in LibreOffice 6 excel97-2003-file with one formula in cell a1:
if(1>2;1;2)

It reads the formula "=if(1>2;1;2)" as "=IF(IF(1>2);1;2) "
Here is my changes in xlscommon.pas
Code: Pascal  [Select][+][-]
  1. function TsSpreadBIFFReader.ReadRPNTokenArray(..)
  2. //...
  3.  
  4.     case token of
  5.       INT_EXCEL_TOKEN_TATTR:
  6.         begin
  7.           b := AStream.ReadByte;
  8.           supported := ReadRPNAttr(AStream, b);
  9.           if supported then begin
  10.             case b of
  11.               $10:
  12.                 begin  // one-parameter sum
  13.                   rpnItem := RPNFunc('SUM', 1, rpnItem)
  14.                 end;
  15.               $2: rpnItem := RPNFunc('IF', 1, rpnItem); //soner add:
  16.               //soner: hier case fehlt option für $8. Es wird zweimal aufgerufen, für linke(1) und rechte(2) seite von if: if(4>5;1;2)
  17.               //trotzdem hat es geklappt es wird, "if"-Funktion wird gelesen,aber nicht ganz richtig.
  18.               //die funktion: =if(1>2;1;2) wird als =IF(IF(1>2);1;2) gelesen.
  19.             end;
  20.           end;
  21.         end;
  22. //...
  23. end;
  24.  
  25. // And here:
  26. function TsSpreadBIFFReader.ReadRPNAttr(AStream: TStream; AIdentifier: Byte): Boolean;
  27. begin
  28.   Result := false;
  29.   case AIdentifier of
  30.     $01: AStream.ReadWord;     // tAttrVolatile token, data not used
  31.     $02: AStream.ReadWord;     // soner add: Excelformat.pdf->page 42 (This is a tAttrIf token (IF function control))
  32.     $08: AStream.ReadWord;     // soner add: Excelformat.pdf->page 42 (This is a tAttrSkip token (skip part of token array))
  33.     $10: AStream.ReadWord;     // tAttrSum token, data not used
  34.     else exit;                 // others not supported by fps --> Result = false
  35.   end;
  36.   Result := true;
  37. end;
  38.  
  39.  


wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #3 on: August 09, 2018, 03:48:13 pm »
Yes it  has bug and fpSpreadsheet can not handle "wenn" formula from ODS-documents. ("wenn" is "if"-formula in german.)
The problem is LibreOffice(German Version) does not accept "if" as formula name.
fpSpreadsheet can only handle xlsx-files with this formula.
for xls-Files(office 2002) in some cells accepted.
No. Fpspreadsheet can handle IF formulas; the language does not matter: in xls the function is stored in the file as a number (Func ID on page 74 ff of "excelfileformat.pdf" which is in folder "reference" of the fpspreadsheet installation; in xlsx and ods the formula is always (well - I've never seen anything else) using its English name: unzip the ods file (after renaming to .zip) and scan the file contents.xlm for the <table:table-cell> nodes. This is from your "created-with-libreoffice6.ods":
Code: XML  [Select][+][-]
  1.  <table:table-cell table:formula="of:=IF([.A1]="";[.A1];"leer")" office:value-type="string" office:string-value="leer" calcext:value-type="string">
  2.    <text:p>leer</text:p>
  3. </table:table-cell>

The problem you are seeing is a left-over of bugs due to the last major rework of the formula engine which was required to introduce 3d references in formulas. Precisely, the trouble is caused by the semicolon used by LibreOffice to separate formula arguments - fpspreadsheet and Excel use a comma. I did not investigate the xlsx issue, yet. All this had been working in the past, and I already fixed the ods semilcolon, but came across other issues. All I can do is to ask you for some patience.

These bugs are very elemental and I wonder why they could slip through the fpspreadsheet unit tests...

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #4 on: August 09, 2018, 05:32:54 pm »
You have right. I have here old version, spready is compiled in october 2017 and it can open the example files from first posting.

LibreOffice/OpenOffice is worsest office that ever created. It can not save simple table  in xlsx format without format errors. And they supported from many companies and programmers.
Even the spready app or fpspreadsheet can do this better and you are working mostly alone on this library.

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #5 on: August 09, 2018, 06:04:13 pm »
The current version in trunk reads your ods files of the first post without crashing.

With "Arbeitszeiten-vorlage-libreoffice6.ods" there are several issues, though:
  • The IF formulas are always refering to row 1 or row 2, instead of to the same row containing the formula. I'll still have to check this. How did you create these formulas? By typing the first formula into the top-most cell and then dragging it down? In Excel this results in a "shared formula" which fpspreadsheet would not read for ods because I've never seen it there - but maybe they changed this in v6?
  • The work times in columns E to H seem to be missing. But if you look at the cell editor you see the values. If you change the cell color they come up as well. The problem here is that these cells have a very unusual time formatting: LibreOffice displays the number format as "H:MM;@". Normally this means than positive values (part before the semicolon) will be formatted as "H:MM", and negative values (after the semicolon) will be formatted as text. But what does a "negative time" mean? How does a clock display a negative time? I think this is just nonsense. If you remove the ";@" and apply only the format "H:MM" then spready does display the work times correctly - see attached modified file. (Note that in spite of its extension this is an ods file. After saving, just remove the .zip extension by renaming).

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #6 on: August 09, 2018, 08:13:38 pm »
...
With "Arbeitszeiten-vorlage-libreoffice6.ods" there are several issues, though:
  • The IF formulas are always refering to row 1 or row 2, instead of to the same row containing the formula. I'll still have to check this. How did you create these formulas? By typing the first formula into the top-most cell and then dragging it down? In Excel this results in a "shared formula" which fpspreadsheet would not read for ods because I've never seen it there - but maybe they changed this in v6?
Are you saying cell I1 to i41?
Yes I created it so, writing in the top most cell then dragging it with mouse to bottom or copy/paste.
The Arbeitszeiten-vorlage-libreoffice6 from first post and also yours have no problem with rows when I open it with spready*. It show (cell I10..I41) correct formulas and when i change values then spready* calculates correct.

..
  • The work times in columns E to H seem to be missing. But if you look at the cell editor you see the values. If you change the cell color they come up as well. The problem here is that these cells have a very unusual time formatting: LibreOffice displays the number format as "H:MM;@". Normally this means than positive values (part before the semicolon) will be formatted as "H:MM", and negative values (after the semicolon) will be formatted as text. But what does a "negative time" mean? How does a clock display a negative time? I think this is just nonsense. If you remove the ";@" and apply only the format "H:MM" then spready does display the work times correctly - see attached modified file. (Note that in spite of its extension this is an ods file. After saving, just remove the .zip extension by renaming).
I selected cell formats in Excel as time, 13:30. (Kategorie:'uhrzeit', Type:13:30)
In Spready* color changing has no effect. It is visible when i select a format other then custom format. (i:e. Menu>Cell>Number format>short time)

Are you meaning with "negative time" the column "I" ("Stunden/Netto")? This column is formatted as decimal.
I selected red color for negative number to show false enterings.
When you enter for in-time 8:00 and out-time 7:00 then you have -1 Hour, because: 7-8=-1.
(It is not time as hours and minutes. i.e. 7,5 is 7 Hours and 30 Minutes.)

I think we should complete the Excel8-format. Because this format is completed and MS does not change it more. Plus all office applications can read this and write this format. Better one format completed as to many half implemented formats.
ODS or XLSX-Format are changing with new office versions, you will get often this problems.

*When I say here spready then I mean compiled in octobre 5, 2017.


Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #7 on: August 09, 2018, 09:22:47 pm »
Oh I forget it, the Excel8-Format ist bad because it only supports up to 256 Columns and one year has 365 days. Last month I made with softmaker office holiday calender, columns for days and rows for employees.  After saving it as xls-format the file was corrupt. Softmaker Office did not warn that there is more columns as the file format supports . :D

Microsoft can nothing do good, spreadsheet format with only 256 columns  :D
First Excel 2007 can create more then 256 columns, exactly max. 16,384 columns.

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #8 on: August 09, 2018, 10:36:44 pm »
Are you meaning with "negative time" the column "I" ("Stunden/Netto")? This column is formatted as decimal.
I selected red color for negative number to show false enterings.
When you enter for in-time 8:00 and out-time 7:00 then you have -1 Hour, because: 7-8=-1.
(It is not time as hours and minutes. i.e. 7,5 is 7 Hours and 30 Minutes.)
This column is no problem. It has a number format "0,0;[RED ]0,0" which formats positive and negative values as a number with one decimal place, but negative values additionally are colored in red (the sign is dropped - is that intentional?)

No, the problem is in columns E to H which have the format "H:MM;@" where the second part is meaningless in my eyes because it refers to negative times but a watch can only display positive times. And this causes trouble for fpspreadsheet because the procedure for reading the number format expects only a single part, e.g. "H.MM". I don't know why you see these cells in the old spready, and I don't - they did not get a format, I should see them at least in the default format.

In the cell format dialog of LibreOffice Calc I selected the very first item of the time formats, and this one works. If you want a one-digit hour value you can edit the format code in the bottom of the dialog to "H:MM" - see screenshot.

ODS or XLSX-Format are changing with new office versions.
I don't think so - at least not at the level of the incomplete fpspreadsheet implementation. Of course, you must not use the newest super-duper features.

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #9 on: August 10, 2018, 12:05:45 am »
The IF formulas are always refering to row 1 or row 2, instead of to the same row containing the formula. I'll still have to check this. How did you create these formulas? By typing the first formula into the top-most cell and then dragging it down? In Excel this results in a "shared formula" which fpspreadsheet would not read for ods because I've never seen it there - but maybe they changed this in v6?
They did not add shared formulas to Calc; it was just an ordinary bug in the fpspreadsheet formula parser (the scanner of the ODS cell address considered only the highest digit of the row index,  :-[). The IF formulas should now work in the "Arbeitszeiten-vorlage-libreoffice6.ods".

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #10 on: August 10, 2018, 12:21:53 am »
...
This column is no problem. It has a number format "0,0;[RED ]0,0" which formats positive and negative values as a number with one decimal place, but negative values additionally are colored in red (the sign is dropped - is that intentional?)
..
I don't know, but I saw it today. I think this is the invention from stupid Microsoft programmer. I can't believe how stupid they are. How can user change positive from negative number without '-' sign. I must change the format for those cells.

I don't know about @ part in  "H:MM;@"  . I only used ExcelXp GUI for cell format.  I created the file first with Excel Xp then opened it with LibreOffice and saved it as ODS-Format.
I created the format in the same way like you with your screenshot, only I did it only with ExcelXp. I can make again tworrow and post. They are short time without negative part. I had same dialogue in Excel Xp except the edit field with  "H:MM;@" . Tomorrow I can post my old fpspreadsheet with old spreadsheet. Now I am not at computer.

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #11 on: August 10, 2018, 12:47:32 am »
I tried to implent it for excel8-format, it's half solution.
I created in LibreOffice 6 excel97-2003-file with one formula in cell a1:
if(1>2;1;2)

It reads the formula "=if(1>2;1;2)" as "=IF(IF(1>2);1;2) "
Here is my changes in xlscommon.pas
Code: Pascal  [Select][+][-]
  1. function TsSpreadBIFFReader.ReadRPNTokenArray(..)
  2. //...
  3.  
  4.     case token of
  5.       INT_EXCEL_TOKEN_TATTR:
  6.         begin
  7.           b := AStream.ReadByte;
  8.           supported := ReadRPNAttr(AStream, b);
  9.           if supported then begin
  10.             case b of
  11.               $10:
  12.                 begin  // one-parameter sum
  13.                   rpnItem := RPNFunc('SUM', 1, rpnItem)
  14.                 end;
  15.               $2: rpnItem := RPNFunc('IF', 1, rpnItem); //soner add:
  16.               //soner: hier case fehlt option für $8. Es wird zweimal aufgerufen, für linke(1) und rechte(2) seite von if: if(4>5;1;2)
  17.               //trotzdem hat es geklappt es wird, "if"-Funktion wird gelesen,aber nicht ganz richtig.
  18.               //die funktion: =if(1>2;1;2) wird als =IF(IF(1>2);1;2) gelesen.
  19.             end;
  20.           end;
  21.         end;
  22. //...
  23. end;
  24.  
[ ...]
Now I looked at this patch in more detail - yes it is working. To avoid the double IF you only must not do anything after the $2 tag. Obviously all the reading of the bytes following has been done correctly by the already implemented code, the only problem was that the tag $2 was not handled. Thank you for helping!

Code: Pascal  [Select][+][-]
  1.     case token of
  2.       INT_EXCEL_TOKEN_TATTR:
  3.         begin
  4.           b := AStream.ReadByte;
  5.           supported := ReadRPNAttr(AStream, b);
  6.           if supported then begin
  7.             case b of
  8.               $02: ;
  9.               $10: rpnItem := RPNFunc('SUM', 1, rpnItem);  
  10.             end;
  11.           end;
  12.         end;

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #12 on: August 10, 2018, 09:44:45 am »
Thank you wp, this is greatest news in this week.

The IF formulas are always refering to row 1 or row 2, instead of to the same row containing the formula. I'll still have to check this. How did you create these formulas? By typing the first formula into the top-most cell and then dragging it down? In Excel this results in a "shared formula" which fpspreadsheet would not read for ods because I've never seen it there - but maybe they changed this in v6?
They did not add shared formulas to Calc; it was just an ordinary bug in the fpspreadsheet formula parser (the scanner of the ODS cell address considered only the highest digit of the row index,  :-[). The IF formulas should now work in the "Arbeitszeiten-vorlage-libreoffice6.ods".

Maybe you have right with this or the parser for xl-files has same  bug like ods parser.
I have here one file that show this error.
The Zip-File contains 2 files:
1) Arbeitszeiten-vorlage.xls is the original file created with Excel Xp.
fpspreadsheet does not show formulas from row I13 to I40. I think i dragged this cell with mouse.
2) Arbeitszeiten-vorlage_if-formulas-dont-visible-in-fpsprsh-2.xls is cutted copy from Arbeitszeiten-vorlage.xls
3) Arbeitszeiten-vorlage-3.xls is copy from Arbeitszeiten-vorlage.xls
Then i copied the cell i10 to i11,i12 and i13 (copy & paste).
As next i selected cell i13 and dragged with mouse until i40.
(Mouse click on + sign on the right bottom corner of selected cell and drag)
Now when you open the file in tsspreadsheetgrid then you will see the cell from i15 to i40 have no formula only result.
I think excel copies in the first row formula and in the next rows reference to it.


Ignore the cell d41. fpspreadsheet does not unterstand linebreak and it is not possible to implent it because this signs are used for tokens tLE and tGT (Page 40). And maybe I am the only one in whole world that created formel in notepad++ and pasted it in Excel  :)
Softmaker and Libreoffice removes it and in Softmaker office, Libreoffice and Excel it is not possible to enter line break.




Thaddy

  • Hero Member
  • *****
  • Posts: 14197
  • Probably until I exterminate Putin.
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #13 on: August 10, 2018, 10:26:32 am »
Yes. I believe linebreaks should be considered whitespace for formulae (just like in pascal). Possibly soft linebreaks should be allowed for display purposes..
Specialize a type, not a var.

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #14 on: August 10, 2018, 10:50:03 am »
@Thaddy you have right with soft line breaks, but hard line breaks(char #13#10)   in formula is not possible. User cannot enter it by typing in libreoffice, softmaker office or excel.  I made it only with copy/paste from notepad++. In future we could implent this too(removing break instead of saying formula error), but now it is very difficult and other things have a higher priority.

@wp
Forget the file form post #12 with empty formulas.
Now, I created blank workbooks with Excel 2011 and Excel Xp and created  other cells with mouse drag, same way as in post #12, now there is no error.
I think the file Arbeitszeiten-vorlage-.... has some error. It is very old, I used, changed it too often.
Now, I can do my real work and look clooser to this component at weekend.

Thanks for your erffort and your and felipes great work in this components.

 

TinyPortal © 2005-2018