### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Cell Order  (Read 946 times)

#### sfeinst

• Full Member
• Posts: 220
##### Cell Order
« on: July 18, 2023, 02:48:20 am »
If I read the docs correctly, it appears the quickest way to get data from cells is to use a for in loop against the TsWorkSheet.Cells.  Something like:
for Cell in Sheet.Cells do

My question is, is there a guarantee ordering?  For example will this return cells by row by col?  In other words, row 0 cols 0 thru n in order then row 1, cells 0 thru n and so on.

Or, is there no guarantee of ordering?  It depends on the spreadsheet?

#### wp

• Hero Member
• Posts: 11482
##### Re: Cell Order
« Reply #1 on: July 18, 2023, 09:39:25 am »
The pointers to the cell records are stored in an AVLTree which heavily depends on sorting. The cells are arranged from left to right and then from top to bottom, and this is the order in which a worksheet is traversed when you call "for cell in Worksheet.Cells".

As a descendant of TAVLTree, the TsCells class has a property OnCompare which you can replace to arrange the cells by column:

Code: Pascal  [Select][+][-]
1. program project1;
2.
3. uses
4.   fpSpreadsheet, fpsTypes, fpsClasses, xlsBIFF8, fpsOpenDocument;
5. var
6.   workbook: TsWorkbook;
7.   worksheet: TsWorksheet;
8.   cell: PCell;
9.
10. function CompareColRow(Item1, Item2: Pointer): Integer;
11. begin
12.   Result := Int64(PsRowCol(Item1)^.Col) - Int64(PsRowCol(Item2)^.Col);
13.   if Result = 0 then
14.     Result := Int64(PsRowCol(Item1)^.Row) - Int64(PsRowCol(Item2)^.Row);
15. end;
16.
17. begin
18.   workbook := TsWorkbook.Create;
19.   try
21.     worksheet.Cells.OnCompare := @CompareColRow;
22.     worksheet.WriteText(5, 3, 'D6');
23.     worksheet.WriteText(0, 0, 'A1');
24.     worksheet.WriteText(0, 1, 'B1');
25.     worksheet.WriteText(1, 0, 'A2');
26.     worksheet.WriteText(0, 2, 'C1');
27.     worksheet.WriteText(1, 1, 'B2');
28.
29.     for cell in worksheet.Cells do
30.       WriteLn('row ', cell^.Row, ', col ', cell^.Col, ' ---> ', worksheet.ReadAsText(cell));
31.
32.     workbook.WriteToFile('test.ods', sfOpenDocument, true);
33.     workbook.WriteToFile('test.xls', sfExcel8, true);
34.   finally
35.     workbook.Free;
36.   end;
37.
38.   WriteLn;
39.   Write('Press ENTER to close');