Recent

Author Topic: Cell Order  (Read 1673 times)

sfeinst

  • Full Member
  • ***
  • Posts: 230
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: 12013
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
  20.     worksheet := workbook.AddWorksheet('Sheet 1');
  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');
  40.   ReadLn;
  41. end.

I cannot guarantee, however, that the default arrangement of cells by row implicitly isn't assumed somewhere internally. If you find an inconsistency, please report.

sfeinst

  • Full Member
  • ***
  • Posts: 230
Re: Cell Order
« Reply #2 on: July 18, 2023, 12:13:18 pm »
Thank you.  Just knowing it is row by row and in column order us helpful when traversing.  I don't need to change the sort at this time.

 

TinyPortal © 2005-2018