xls size bigger than before [ANSWERED by wp]

[totya]

Hi wp master!

As I see, the created excel (.xls) files about 10-50% bigger than before. The size is okay about 2 months before (since this time I don't looking it). This isn't a big problem, only just I noticed for you. Thanks!

[wp]

No idea what you did. xls files don't grow by themselves like plants.

[Thaddy]

@wp well, they grow "by themselves" when there is an automatic string conversion.....YKWUM.
With UTF8 that can be pretty dramatic....

[totya]

wp master: Thanks for the joke

I often compare my app results, and I noticed the differents (I keep reference files for compare). I use always the latest FPSpreadsheet svn version.

So, when I save created .xls files, I save to csv too automatically. Well, the created csv files all equally with the old version created, only the .xls files differents (10-50% bigger the new). These workplace files, but I will create sample for you. Thanks

[wp]

One size-relevant change, for example, was the introduction of the SharedStringTable for xls files, but this certainly was more than two months ago.

It would be good if you could send the "same" file twice, the current version and the old one. Send me a PM and I give you my e-mail address if you don't want to publish the files.

[wp]

To the general audience: totya sent me the "same" old and new file per email.

Loading both files into the BIFFExplorer I see that the older one has a SharedStringTable (SST), the newer one not. This is some (I would say: pseudo-) optimization introduced by Microsoft - a central list for all strings in the workbook, and each text cell stores only the index of the displayed string into this table. In fpspreadsheet, this feature was added to the xls reader at least a year ago (I'm too lazy to look through the svn commit notes) which is way too long before these files were created. But on June 5 there was a very recent change because the fpspreadsheet speedtest showed a dramatic slow-down for loading large xls files with many strings. Therefore, the xls writer was modified to write an SST only when needed, i.e. for strings longer than 255 characters.

It is hard to tell in general whether an xls file with SST will be larger or smaller than a file without SST having the same content. On the one side, an SST stores mulitply-used strings only once which would make the SST version of the file smaller. But this is counter-acted by the larger memory requirement to store a string with SST because the cell record must additionally contain the index to the SST which is not present in the no-SST version.

[totya]

Thanks you for the detailed answer, wp master!


SST very needed, as I remember I wrote some bug reports with it (without it). So, now all clear. Now SST used only, if the text size bigger than 255 char. This is mean, if many repeated text available in the xls file (example for me files), the result file is bigger. But you say, the performance (speed) is much higher in this way.

Thanks again!

[wp]

But you say, the performance (speed) is much higher in this way.

Yes. In every access to a text cell the string must be searched in the SST, and this is very dramatic during reading or writing a file. Initially, the SST was implemented as a TStringList, but the linear search in a StringList can become painfully slow when there is a million strings in the workbook. Then I switched to a TStringHashTable which is much faster. But the xls Excel8 files were still written noticably slower than their Excel5 cousins which don't have an SST at all. Finally I decided to use the SST only for the long strings (the old issue was that strings are truncated at 255 characters if they are written to the cell directly).