* * *

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

wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #15 on: August 10, 2018, 10:57:40 am »
fpspreadsheet does not unterstand linebreak
The linebreak inside a formula has some special attribute within the biff structure (described in section 3.10.5 of "excelfileformat.pdf"). fpspreadsheet does not handle these attrbutes correctly. If it finds an unknown attribute then it stops working. This is also the reason why the IF functions did not work - because it has that attribute token at the beginning of the token sequence (which turns out to be unnecessary - but if Excel writes it we must consider it!).

These crashes must not happen - if fpspreadsheet does not support an attribute it must at least jump over them to stay in sync with the data structure. I am working at this issue at the momenet.

[EDIT]
Done in ccr-r6581. "Arbeitszeiten-Vorlage.xls" is now read correctly without error message; the shared formula issue, however, is not yet resolved.
« Last Edit: August 10, 2018, 11:44:34 am by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Soner

  • Jr. Member
  • **
  • Posts: 89
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #16 on: August 10, 2018, 12:14:24 pm »
There is one other issue with xlsx-Files. (office 2007 + files)
I put  it here if want look it.
When you save xlsx-Files with LibreOffice6 or Planmaker in same format as xlsx then Spready has display errors (draws border lines where there is no border lines or wrong cell widths).
And when you open same files with Excel 2011, LibreOffice6 or Planmaker 2018 then they show them without error.
I put all the files as zip-File here.
Look at file names, .._pm2018 for planmaker2018, .._lo6 for Libreoffice and with no endig excel 2011 file.


Soner

  • Jr. Member
  • **
  • Posts: 89
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #17 on: August 10, 2018, 12:50:37 pm »
You are real fast, latest svn-version understand line breaks.

I see you changed fpsTypes.pas and removed the variable :
UTF8FormatSettings: TFormatSettings;

Now spready can not compiled either you must change spready or add in  fpsTypes the variable again.
I created it in spreadyfiles sCSVParfamsform.pas and smain.pas as global variables made this:
Code: Pascal  [Select]
  1. //in both units
  2. implementation
  3. var
  4.   UTF8FormatSettings: TFormatSettings;
  5.  
  6. //at end of both units
  7. initialization
  8.   InitUTF8FormatSettings(UTF8FormatSettings);
  9. end.
  10.  

wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #18 on: August 10, 2018, 01:33:08 pm »
Ah, I forgot to commit the updated spready (ccr-r6582). I removed the UTF8FormatSettings because using the procedure, it is not needed as a global record within the library any more. And there are already too many FormatSettings around.

In r.6583, you find drag and drop of files from the Explorer to Spready.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #19 on: August 10, 2018, 01:54:32 pm »
Now when you open the file in tsspreadsheetgrid then you will see the cell from i15 to i40 have no formula only result.
This is a shared formula issue. Excel writes repeated formulas only once and tags the following formulas as "shared formulas" which means that the formula basically is the same but the cell references have to be adapted according to the new position of the formula.

This feature was broken by the new formula engine needed for the 3D cell references: the shared formulas were transfered to the worksheet correctly, but were erased again, when the formula result was read in the next FORMULA record. Another stupid mistake... (I guess there will be more of them).

Fixed in r6584.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Soner

  • Jr. Member
  • **
  • Posts: 89
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #20 on: August 10, 2018, 02:53:45 pm »
Woow what a speed, thanks.
I tried new version they are working.

One thing is missing,  but it is not important.
When you save xls-workbook with "shared formula" from spready then you get "range  check error" but you can save it as xlsx-format.
It is the file "Arbeitszeiten-vorlage.xls" from reply #12.



wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #21 on: August 10, 2018, 03:04:03 pm »
Cannot reproduce. You are loading "Arbeitszeiten-vorlage.xls" of reply #12 into Spready, save it as .xls again, ang get a "Range check error"?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #22 on: August 10, 2018, 05:56:29 pm »
When you save xlsx-Files with LibreOffice6 or Planmaker in same format as xlsx then Spready has display errors (draws border lines where there is no border lines or wrong cell widths).
Should be fixed now (ccr-r6588).
  • The spurious PlanMaker cell border lines were caused because PlanMaker defines a border style "none" but assignes a line color to it. FpSpreadsheet did not check for the "none" because in Excel these no-border nodes are not specified any further.
  • LibreOffice writes boolean attributes to xml as "true" or "false" while I've seen only "1" and "0" in Excel so far. Therefore, FpSpreadsheet failed to detect custom column widths etc.
  • LibreOffice writes the time format in uppercase letters, Excel in lowercase. While Fpspreadsheet should check for both cases, the uppercase 'H' was missing in the comparisons.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Soner

  • Jr. Member
  • **
  • Posts: 89
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #23 on: August 10, 2018, 11:24:17 pm »
woow you are really fast  :o I thought some things are not solveable.

Most problems are solved, I saw at testing testing two issues:
1) When you save in xls-Format from Spready then the comments are lost.
2) Some cells xlsx-Files from LibreOffice6 are false drawn. Look at screenshot.
I think more people will ask here about this problem, because LibreOffice is used
often for saving as xlsx-format.

Cannot reproduce. You are loading "Arbeitszeiten-vorlage.xls" of reply #12 into Spready, save it as .xls again, ang get a "Range check error"?
Yes, but now I see this is not the file with "range check eror". I can't find it on this computer. When I find it then i post it here.

We started here with ODS-format and now fpSpreadsheet and TsWorksheetGrid are supporting all formats from 3 office software big thanks.

wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #24 on: August 11, 2018, 02:47:39 pm »
1) When you save in xls-Format from Spready then the comments are lost.
Yes, unfortunately (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Progress_by_supported_cell_content) But I gave up to implement writing of comments to BIFF8 files which turned out to be too complicated for me. I was sure that everything is correct, but fpspreadsheet writes only unreadable files. Therefore, I added an "exit" immediately after entering TsSpreadBiff8Writer.WriteComments (in unit xlsBiff8). So, the old code is still there but currently not used. Maybe somebody would like to dive into it and submit a patch. But be warned: the exelfileformat.pdf is of little help, and you need a lot of reverse engineering using originally written xls files and BIFFExplorer.

2) Some cells xlsx-Files from LibreOffice6 are false drawn. Look at screenshot.
You mean the row heights and the truncated cells, like "Arbeitgeber" in F2 etc? The latter is due the wordwrap which was falsely detected for the way LibreOffice writes the xlsx files - should be fixed in ccr-r6589. As for the row height I tried to measure the row heights, it seems that they are correct. But the positioning of the text might be off by 1 or 2 pixels. Normally this does not matter, but since the row heights (and column widths as well) in this file are very tight, every pixel counts. Instead of trying to solve this for this file (and still seeing issues in other files) I activated the WorksheetGrid's RowHeight calculation routine in Spready. In the new version, check "Update row heights after loading" in the Settings menu, and the internal row height calculations will be used when a new file is loaded. The row heights may turn out to be a bit different, but the text positioning will probably be much better. One warning, though: This option should not be used when loading large files with inidividual character formatting within many cells: Because the height of every cell must be calculated here, the loading process can be slowed down considerably.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 4982
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #25 on: August 11, 2018, 10:59:16 pm »
Finally fixed also the detection of the time format 'H:MM;@' in ODS files (ccr-r6597) making also the ODS file of the very first post readable. Some of what I had written above (reply #5, #8) was nonsense...
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Soner

  • Jr. Member
  • **
  • Posts: 89
Re: Is the ODS-format(LibreOffice) not supported by fpspreadsheet?
« Reply #26 on: August 12, 2018, 05:05:24 pm »
Good news, thanks.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus