* * *

Author Topic: [solved]fpSpreadsheet can't open simple sum-formula.  (Read 723 times)

SunyD

  • Guest
[solved]fpSpreadsheet can't open simple sum-formula.
« on: October 04, 2017, 12:39:22 pm »
I try to open xls-Document (excel xp) with TsWorksheetGrid+TsWorkbookSource.
When I open it without formulas everything looks good.(boReadFormulas not in TsWorkbookSource.Options)
But when i open it with formulas then some formulas are wrong, grid shows <FMLA?>. They are simple formulas like this:
=SUM(F10:K10).
I opened the Excel file with LibreOffice and saved it with other formats and opened with my testapp with some result.
Some of wrong cells with wrong formulas are showing now the formula correct but some others are showing now false.
What is wrong with this formula or file?

In the attached zip file is only the file "Stdtest-Formulas-from-E10-to-E40-Wrong-ORIGINAL.xls " original. all others are format variations from LibreOffice.
« Last Edit: October 05, 2017, 04:11:28 pm by Soner A. »

wp

  • Hero Member
  • *****
  • Posts: 3953
Re: fpSpreadsheet can't open simple sum-formula.
« Reply #1 on: October 04, 2017, 06:48:05 pm »
Well, what appears to be "simple" may be complex... I had to consult the "excelfileformat.pdf" (which is in the "reference" folder of the fpspreadsheet installation) again to learn that SUM formulas can be stored in the xls file in two different ways. Only the one with a standard function token for a variable number of arguments was implemented in fpspreadsheet. But your formula has only a single argument ("F10:K10"), and in this case Excel writes a different token. How mean!

The current trunk version now supports also the sum function with a single parameter. Please test and report back.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SunyD

  • Guest
Re: fpSpreadsheet can't open simple sum-formula.
« Reply #2 on: October 04, 2017, 10:23:14 pm »
You made it again, big thanks.

I don't not about excelfileformat. In summer i read some pages from included pdf-doc, but forgit it again. Also I don't use fpspreadsheet. Sometimes I open in spready some excelfile to look if it works. But I am thinking to use  fpSpreadsheet for calculations, vba s*cks, pascal is good.

Now I inspected other issue:
 -when I save the file in spready as "Excel 97-2003"-Format then comments are lost.
 - when I save it as "Excel 2007+"-Format then comments are there but formatting (font, borders) are lost.
 - when I save it as "ODS"-Format then I get error "At most 3 format sections allowed".
Is this normal?

I would use "Excel 97-2003"-Format, because most programs are supporting this format, maybe it is easiest format.

Thanks again.

wp

  • Hero Member
  • *****
  • Posts: 3953
Re: fpSpreadsheet can't open simple sum-formula.
« Reply #3 on: October 04, 2017, 11:42:09 pm »
-when I save the file in spready as "Excel 97-2003"-Format then comments are lost.
Yes, comments depend on several poorly documented binary records in the xls format. I finally gave up to implement writing of comments to xls (of course, patches are welcome).

- when I save it as "Excel 2007+"-Format then comments are there but formatting (font, borders) are lost.
Which formatting do you mean? Formatting of the cells? This should be there, for sure. Formatting of the comment? Yes, this is not supported.

- when I save it as "ODS"-Format then I get error "At most 3 format sections allowed".
In which context? Please post an example of the code you are using. Or, if you want to save a file with spready to ods, post the file.

BTW, fpspreadsheet supports only a subset of the features available in spreadsheet files. Therefore, loading of existing files into spready etc. and resaving them in another format may lead to loss of formatting, data and/or features -- better to use the original applications for file format conversion.

I would use "Excel 97-2003"-Format, because most programs are supporting this format, maybe it is easiest format.
As the (co-)author of fpspreadsheet I disagree - it is the most complex format. It is a binary format in which a single incorrect byte may destroy the file. I would prefer xlsx (Excel 2007+) or OpenDocument. These are zipped xml files which are human-readable and much easier to understand (although there are a lot of inconsistencies here, too).
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SunyD

  • Guest
Re: fpSpreadsheet can't open simple sum-formula.
« Reply #4 on: October 05, 2017, 12:08:59 am »
I used every time the file "Stdtest-Formulas-from-E10-to-E40-Wrong-ORIGINAL.xls" from my first post.
Quote from: wp
Which formatting do you mean? Formatting of the cells? This should be there, for sure. Formatting of the comment? Yes, this is not supported.
Yes I am formatting of cells, border lines and background colors from all cells are lost after saving it with spready in xlsx-format.
EDIT: Sorry, I used LIbreOffice to open xlsx-File, Maybe LibreOffice has problems. Spready shows it good. Tomorrow, I will look it with excel 2013 at work.

Quote from: wp
In which context? Please post an example of the code you are using. Or, if you want to save a file with spready to ods, post the file.
I opened again the file from first post with spready and tried to save it as "ods format"

Everytime, I get best results when I use Excel 97-2003 format  :)

« Last Edit: October 05, 2017, 12:17:00 am by Soner A. »

wp

  • Hero Member
  • *****
  • Posts: 3953
Re: fpSpreadsheet can't open simple sum-formula.
« Reply #5 on: October 05, 2017, 03:18:07 pm »
The problem originates in some peculiar number format strings used in "Stdtest-Formulas-from-E10-to-E40-Wrong-ORIGINAL.xls":

The currency values are formatted as _-* #,##0.00" €"_-;\-* #,##0.00" €"_-;_-* \-??" €"_-;_-@_-. Nice, isn't it? This format string consists of four sections separated by colons: the first one for positive value, the second one for negative values, the third one for zero values, and the last one for text display. OpenDocument number formats have only up to three sections, and in fact, I never understand what the forth sections is good for. Therefore the ods writer raised an exception if it meets such a format. But of course, this is too harsh - why should writing of the entire file fail because of such a minor detail? I removed the exception now and fixed the writer to truncate after three sections.

The values in cells F7:K7 are not multiplied by the factor 100 - they should be because of the percent notation. Obviously the percentage sign was not interpreted correctly in your format string "0\ %" (without quotes). This was due to the space that is added between the zero and the percent character - fpspreadsheet expects nothing to be between percent and number format. Again, there is no reason for this limitation, and I extended the number format parser to accept this case too.

After these fixes spready I still could not write valid xlsx and ods files. After carefully inspecting the xls file with biffexplorer I found a tiny difference - one bit to be exact - between the percent format shown above and the one in the file: in the file the character between the '\' and the '%' is #$A0, instead of #$20 (space), no idea how you were able to enter this value. This incorrectly set bit destroyed the xml file...

After fixing this format string I was able to write the original xls file to xlsx and ods, and these files can be opened by Excel/LibreOffice. No formatting is lost
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SunyD

  • Guest
Re: fpSpreadsheet can't open simple sum-formula.
« Reply #6 on: October 05, 2017, 04:11:16 pm »
The cells F7:K7 are text cells. I forgot to enter ' and excel made it number.
This file is only example, I found it on my hard drive.

In future I will try to use LibreOffice. It seems like fpSpreadsheet can handle ODS-Format good.

Big thanks!

 

Recent

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