Formatting

[straetch]

In my application, I read a spreadsheet file using fpspreadsheet, I modify the text content of a number of cells and write the spreadsheet to another file.
I do not change the formatting of any cell, so I assumed the format in the output file would be consistent with the input.
Some formatting, such as word wrap, is handled as expected. But foreground and background colors are not consistent. For some cells these are ok; for others, I get white letters on a black background. Same for vertical alignment. I assume this may be somehow related to the way the input spreadsheet was formatted before (by cell, by column, etc).
Since this is a rather simple (though long) spreadsheet I can set the format in my application cell by cell, but this blows up the output file size and makes the application very slow.
Do I miss something here?
Thanks,

[wp]

It would be helpful if you could post here the input and output files as well as the code you used. If the input file is too big I am sure you can extract some cells into another sheet which shows the same issue.

I can set the format in my application cell by cell

After loading the file you could iterate through all cells and empty their UsedFormattingFields - this should reset all formatting to default values. Do the issues disappear then?

but this blows up the output file size

That's a problem with the current implementation of fpspreadsheet: every cell carries only its own formatting attributes. It would be more memory efficient to collect all formatting styles in a list and give the cells only an index into that list. This feature will come sooner or later, but I am afraid I won't make many friends with it because it will break existing code.

makes the application very slow

How big is the spreadsheet? I cannot believe that.

[straetch]

Many thanks for the speedy response!

1. I have left three files on a server:
a. ArchiefIndex-V5-17-Part-Excel97-XP.xls   =   input spreadsheet processed by OpenOffice 4.1
         this file contains the first 100 rows of a spreadsheet with 14 columns and some 7000 rows.
b. ArchiefIndex-V5-17-Part-Excel97-XP.xls-DELTA.xls   =   output of the program
c. main-stripped.pas   =    essential part of the program

The program reads the spreadsheet file, writes one cell (in this example) with WriteUTF8Text(arow,acol,w) and writes the spreadsheet file.

Three files

2. Emptying UsedFormattingFields for all cells gives the expected result, but does not yield the format I want (e.g. vertical alignment should be top based).

3. I must correct myself: if I set the formatting for each cell, the subsequent reading of the spreadsheet by OpenOffice becomes very slow. The processing in the program is meant to correct errors in the spreadsheet contents and is made only once for each correction. The reading of the resulting file is made many times by the members of our history circle. My mistake, sorry.

Thanks, Chris

[wp]

Sorry I cannot access the server (Password required)

[straetch]

Three files in attached zip file.

Sorry for the inconvenience.
Chris

[wp]

Which version of fpspreadsheet had you been using when doing this? It looks as if you have an out-dated version, I roughly remember that once I fixed the issue where the color indexes of font and cell background color were not assigned correctly. Try the recently released version (see http://forum.lazarus.freepascal.org/index.php/topic,25007.0.html). I am pretty sure that the issues will be gone: I can load your raw file into "spready" (the "monster" demo of TsWorksheetGrid, see screenshot) and save it from there back to a new file which in turn loads perfectly into Excel or LibreOffice. Also, your sample code of loading a file, changing a cell text and storing it back works fine, no formatting seems to be lost when re-opening the saved file.

[straetch]

I use the rc version that was most recent on 22 june 2014. I did not note the version number (can I find the version number somewhere?). So I use a very recent version of fpspreadsheet. The name of the package in the overview in Lazarus is laz_fpspreadsheet 0.0 however.
I notice another strange thing: if I open the output file (ArchiefIndex-V5-17-Part-Excel97-XP.xls-DELTA.xls, as sent to you), I get a different behavour in OpenOffice 4.1 and LibreOffice 4.2: the spreadsheet loads succesfully in both, the cell colors are the same, but the wordwrap is present in OO but lost in LO. This is not the place to discuss the difference between both, but it may possibly help to explain the formatting problem.

My interpretation from your posts is that fpspreadsheet should indeed handle the formatting correctly (no bug or missing function). So, in my case where only the text content of some cells are changed, I should not touch the formatting in my program. Correct?

I appreciate very much the quick and thorough support you give me and the patience with a newbie.
fpspreadsheet is great!

Chris

[wp]

I use the rc version

As far as I know there is no "release-candidate" for fpspreadsheet. We just have "trunk" which changes quite rapidly (download via http from http://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/, or via svn from svn://svn.code.sf.net/p/lazarus-ccr/svn/components/fpspreadsheet), and the released zip-file (http://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/). So, I am a bit confused...

I get a different behavour in OpenOffice 4.1 and LibreOffice 4.2

I can confirm this, LibreOffice seems to be missing code for automatic calculation of row heights.
You can fix this manually by selecting the entire sheet (Ctrl-A), right-click on any row title, and select "Optimum row height".

in my case where only the text content of some cells are changed, I should not touch the formatting in my program. Correct?

If you mean loading and resaving without touching the formatting: Yes, this should leave the formats intact. But of course, formats can be very complex, and we do not read and write every detail. Therefore, there's is always a chance that something may be lost when re-saving the file. In this case, drop a note here, and I'll see what I can do.

[straetch]

I installed version 3271 of fpspreadsheet through svn.
Now everything works fine. I suspect something went wrong during the installation of the previous version.
Many thanks for your help!

Once fpspreadsheet is installed, is there a way to see which version is in use?

Chris

[wp]

Once fpspreadsheet is installed, is there a way to see which version is in use?

Good point - currently no...