Is there a manner in which the statistics for each CALC sheet can be extracted?

[WhiteKnight]

Version: 7.0.6.2 (x64)

Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b

CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win

Locale: sv-SE (en_GB); UI: en-GB

Calc: threaded

Hi there,

The FILE menu function “properties” includes a tab for Statistics, presenting data for the entire file.

Is there a manner in which the statistics for individual sheets within that file can be ascertained?

Obviously, one can remove sheets one at a time and save copies for file size comparison but that’s not really optimised for a user-centric experience.

[JohnSUN]

Which of the Statistics indicators (for one sheet) are you interested in? Number of filled cells? (I don’t see anything more useful in Statistics). Just use the formula COUNTA() in the status bar

[WhiteKnight]

It was just a point of interest rather than a statistical requirement.
I do like to try to understand how things work and the impact of my actions.
Understanding how adding sheets and populating them with formulae or data affects the overall file size was on the list. Also, I’m uncertain what the definition of “Formula groups” could be and hoped that as my knowledge grew then so would my understanding.
Thanks for the response, much appreciated.

[JohnSUN]

I’m afraid the relationship between the contents of a spreadsheet and its size is hard to find. Do a simple experiment. In the new book, fill in A1:A200 with the value 1, and fill in column B with the formula =A1*A1, =A2*A2, and so on. You will get 400 cells filled with ones. Save this spreadsheet. Now change the formula, let each cell in column B contain =$A$1*$A$1. It’s still 400 cells filled with ones. Save this spreadsheet too. Compare the sizes. Compressing XML to ZIP is an unpredictable process …

[WhiteKnight]

Hmmmmm I tried five different methods for filling all the cells with 1 and the one with absolute references to A1 appears to be the most file size efficient. I’ve read that array processing is highly efficient and this seems to give a clear indication of the potential - it’s 7.5% more file size efficient in this small sample, is that also representative of the potential processing efficacy for array processing? I have surmised that the A2:A200 cells are redundant in terms of anything other than space-filling when the absolute reference is used so I emptied those and the file size saving was 11.9%.

A good answer always prompts another question - Is the file size saving directly correlated to the memory utilised when the file is loaded? You mentioned compression - is the saved file compressed to make saving and loading more efficient. Yeah I guess that was two.

It’s always a good day when you learn something new - twice.

Thanks

[LeroyG]

The content.xml (uncompress the .ods file) in the first case has less characters by cell (no “$”):

    <table:table-row table:style-name="ro1">
<table:table-cell/>
<table:table-cell table:formula="of:=[.A1]*[.A1]" office:value-type="float" office:value="0" calcext:value-type="float">
<text:p>0</text:p>
</table:table-cell>
</table:table-row>

…<table:table-cell table:formula=“of:=[.A2]*[.A2]”

In the second case I guess that it is easier to compress the same code 200 times:

    <table:table-row table:style-name="ro1">
<table:table-cell/>
<table:table-cell table:formula="of:=[.$A$1]*[.$A$1]" office:value-type="float" office:value="0" calcext:value-type="float">
<text:p>0</text:p>
</table:table-cell>
</table:table-row>

…<table:table-cell table:formula=“of:=[.$A$1]*[.$A$1]”

[WhiteKnight]

Leroy, thanks for your input.

I’m not sure what all the code does - is it a macro for measuring the data consumption for each case?

Certainly, your summary above each panel was quite self-explanatory and certainly within my comprehension - I also think it’s affirming my question concerning whether the calc file is compressed.

Thanks - another good day.

[LeroyG]

If you uncompress the .ods file (sorry, my image is about an .odt), and open the content.xml, you will see the file content code. I copied from there.

[WhiteKnight]

I just got a whole lot smarter - Thanks