Yes it has bug and fpSpreadsheet can not handle "wenn" formula from ODS-documents. ("wenn" is "if"-formula in german.)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":
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.
...Are you saying cell I1 to i41?
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?
..I selected cell formats in Excel as time, 13:30. (Kategorie:'uhrzeit', Type:13:30)
- 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).
Are you meaning with "negative time" the column "I" ("Stunden/Netto")? This column is formatted as decimal.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 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.)
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.
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".
...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.
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 tried to implent it for excel8-format, it's half solution.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!
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[ ...]
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".
fpspreadsheet does not unterstand linebreakThe 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!).
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.
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).
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.
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.