How to determine row/column extent of spreadsheet?

[mfleming]

Hi everyone,

For programming purposes, I'm trying to determine how large a given spreadsheet might be. I know that the columns range from A to ZZ, and I believe the number of rows can exceed 10,000. I would like to iterate through the cells of a spreadsheet within the range of populated cells, not within the maximum bounds of the sheet.

What I'd like to determine is the extent of a spreadsheet where you know it is rectangular and populated, i.e., no blank rows or columns or columns of different lengths. I know I can determine the number of rows under these conditions using the code

Code:


L1 := A:A(0);
nrows := length(L1);

How would I determine the number of populated columns? I can't find documentation of any application variable that might be set to the max row or column. Scanning backwards from the ZZ column looking for an non-empty cell also seems pretty unreliable given that a zero is a valid cell content in a spreadsheet interior.

Any thoughts or pointers from anyone?

Thanks as always,
~ Mark

[cyrille de brébisson]

Hello,

This is a hard one!
Obviously, the spreadsheet does keep that data internally, but as you noted, there is no user accessible variable that lets you access it...

I had a look at the source code and here is the trick.
When you extract a full row or column, it returns a list which size IS the max row/col count!

So, SIZE(A:A) gives you the number of rows and SIZE(1:1) the number of columns!

Not very efficient, but will work.

Regards,
Cyrille

[mfleming]

Perfect! I did not know you could extract a row, so your answer is a valuable lesson.

The fact that an arbitrarily extracted row or column is the same maximum row or column length brings up an obvious question; Are spreadsheets stored internally as lists of lists? The knowledge might be helpful when optimizing a program.

Regards,
~ Mark

[cyrille de brébisson]

Hello,

Nope, the spreadsheet has a sorted (by row/column) list of cells (binary search) that contain content... So only 'used cells' do take memory. It also maintains a max row/max col number which is just to avoid looking for something that does not exists.

When extracting a row, it will loop from 0 to max col, looking for each cell and fills the unknown by 0s.

These 0s are internally 'special' 0. They are special in that they have a special marker that allows the program to distinguish them from a 'normal' 0. This allows a function such as average to work as expected (ignoring the empty cells) while a function like sin will return sin(0) as also expected.
This is not accessible from the user realm at the moment... maybe they should be a IsSpecialZero function in the spreadsheet...

Cyrille

[Tyann]

Bonjour
Personnellement , j'ai créer une variable d'aplication avec Avars dans laquelle je stocke une valeur de fin.
Quand je rentre des données dans les cellules, je met le contenu de cette variable dans la cellule derrière la dernière donnée.
Pour connaître ensuite la longueur d'une colonne ou d'une ligne, je parcours celle-ci jusqu'à ce que je rencontre la valeur de cette variable .
J'adapte le contenu de la variable selon le type de données, une chaîne si les données sont numérique, ou sinon zéro si les données sont des chaînes.
Exemple :

Hello
Personally, I create an aplication variable with Avars in which I store an end value .
When I enter data into the cells, I put the contents of this variable in the cell behind the last data .
Then know the length of a column or row , I go through it until I meet the value of this variable.
I adapt the content of the variable depending on the data type, a string if the data is digital, or otherwise zero if the data are strings.
example:

Code:


EXPORT Dim_col(n)
BEGIN
LOCAL i;
 WHILE 1 DO
  i:=i+1;
  IF TYPE(Cell(i,n))==TYPE(Send)  THEN // Send is a variable Avars
   IF Cell(i,n)==Send THEN
    BREAK;
   END;
 END;
 i-1;
END;

[mfleming]

Thanks!

Cyrille, your description of the spreadsheet storage format sounds as though it is efficient for both space and element lookup. I was worried that iterating through the cells might be O(n^2) or something. You mention special zero; would that be the same as Cell(0,0)? Could I use that in a comparison to differentiate cell content in the same way as your example using average and sin?

Hi Tyann, I'm worried a user might alter the spreadsheet, and might even add a row or column. Unless I can lock the spreadsheet against changes, it seemed best to check the size each time.

A last question on spreadsheet access. As mentioned earlier, A:A(0) can access the content of column A and 1:1(0) can access the content of row 1. Is there a way to do this via a function? The help text mentions accessing a row via Cell(n, 0) or column via Cell(0, n) but that only seems valid for writing to an entire row or column. I also tried accessing an entire row by constructing a string like "9:9(0)" and passing it to EVAL. No luck there!

Accessing an entire row or column at once might be moot if cell access is O(1). I could always write my own function now that I know how to find the size of a sheet, but it would be interesting to know if a function is available.

Regards,
~ Mark

[Tyann]

Quote:Hi Tyann, I'm worried a user might alter the spreadsheet, and might even add a row or column. Unless I can lock the spreadsheet against changes, it seemed best to check the size each time.

Dans le programme associé à l'application vous pouvez interdire l'accés à la vue numérique :

In the program associated with the application you can prohibit access to the digital view :

Code:


Num
BEGIN
 MSGBOX("cette vue contient"+
CHAR(10)+"des données sensibles");
END;

[cyrille de brébisson]

Hello,

No, unfortunately, there is no way that I can think of that would let a user differentiate a normal from a special zero...

Although, you might use a function (like Average) to do the work for you!
OK, I just did a test. This is of course extremely inefficient! Place 0 in A1 and make sure that A2 is empty.
AVERAGE(A1,1,1) -> 0.666...
AVERAGE(A2,1,1) -> 1
This allows you to make the distinction!

Cyrille