Recent

Author Topic: SortOrder has no effect  (Read 7828 times)

flaza

  • Jr. Member
  • **
  • Posts: 57
SortOrder has no effect
« on: October 18, 2014, 10:35:13 pm »
Hi.

I have a fpspreadsheetgrid  and use the routine grid.LoadFromSpreadsheetFile to read a xls spreadhseet with 245 records. I set FrozenRows to 1. So fa so good. Now I want to sortorder the data. I use the routine grid.SortColRow(True,4): nothing happens. Then I created the routine grid.OnCompareCells. This routine is called and gives the right result each time: but the grid doesn't change. I change the routine grid.SortColRow(True,4,2,245) but nothing happens.
I've tried grid.Invalidate and grid.Repaint and grid.Refresh, but nothing works.

Am I forgetting something?

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #1 on: October 20, 2014, 01:00:50 am »
Quote
Am I forgetting something?
No. It's just that sorting is not yet supported. The TsSpreadsheetGrid looks like a StringGrid, but it is only a DrawGrid which gets the data from the underlying worksheet. Calling the grid method SortColRow is not yet propagated to the worksheet - and this does not support sorting anyway at the moment. So, you have to be patient for some time...

flaza

  • Jr. Member
  • **
  • Posts: 57
Re: SortOrder has no effect
« Reply #2 on: October 20, 2014, 07:47:46 pm »
Not the answer I hoped for, but an answer anyway.
Thanks.

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #3 on: October 20, 2014, 08:47:24 pm »
What about writing a patch? fpspreadsheet is open source, everybody can contribute. Providing a patch is the fastest way to get the feature that you need.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SortOrder has no effect
« Reply #4 on: October 21, 2014, 12:51:49 am »
Not exactly a patch yet but I looked at the possibility and come this far. (We just need to override ColRowExchanged and DoCompareCells)

But I can't seem to get cells to swap.
(I tried assigning cell^.row but that doesn't seem to work.)

Any ideas how I can swap 2 cells?

Code: [Select]
type
  TsCustomWorksheetGrid = class(TCustomDrawGrid)
  // ...
  protected
    procedure ColRowExchanged(IsColumn: boolean; index, WithIndex: integer); override;
    function DoCompareCells(Acol, ARow, Bcol, BRow: integer): integer; override;
  // ...
  end;

procedure TsCustomWorksheetGrid.ColRowExchanged(IsColumn: boolean; index, WithIndex: integer);
var
  iCol: integer;
  A, B: string;
  cell1, cell2: PCell;
begin
  for iCol := HeaderCount to ColCount - 1 do
  begin

    // grrrr. this doesn't work
    //cell1 := Worksheet.GetCell(index - HeaderCount, iCol - HeaderCount);
    //cell2 := Worksheet.GetCell(Withindex - HeaderCount, iCol - HeaderCount);
    //cell1^.Row := withindex - HeaderCount;
    //cell2^.Row := index - HeaderCount;

    // A, well, just swapping strings then ;)
    A := Worksheet.ReadAsUTF8Text(Index - HeaderCount, iCol - HeaderCount);
    B := Worksheet.ReadAsUTF8Text(WithIndex - HeaderCount, iCol - HeaderCount);
    Worksheet.WriteUTF8Text(Index - HeaderCount, iCol - HeaderCount, B);
    Worksheet.WriteUTF8Text(WithIndex - HeaderCount, iCol - HeaderCount, A);
  end;

end;

function TsCustomWorksheetGrid.DoCompareCells(Acol, ARow, Bcol, BRow: integer): integer;
var
  A, B: string;
begin
  if Assigned(OnCompareCells) then
    Result := inherited DoCompareCells(Acol, ARow, Bcol, BRow)
  else
  begin
    A := Worksheet.ReadAsUTF8Text(ARow - HeaderCount, ACol - HeaderCount);
    B := Worksheet.ReadAsUTF8Text(BRow - HeaderCount, BCol - HeaderCount);
    Result := UTF8CompareText(A, B);
    if SortOrder = soDescending then
      Result := -Result;
  end;
end;


Of course DoCompareCells should be a lot better to also compare numbers etc. and maybe include cells after the sort column in the compare but it's work in progress. Also, this example only sorts rows (and not columns) yet.
« Last Edit: October 21, 2014, 12:54:37 am by rvk »

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #5 on: October 21, 2014, 01:21:53 am »
Quote
Any ideas how I can swap 2 cells?
I'll look in detail tomorrow, but I could guess that this is due to the underlying balanced tree which stores the cells. This tree is sorted along the rows and then along the cols. Just changing the content of the cell does not force the tree to rearrange its nodes. You probably could remove the cells to be swapped and re-add them with the correct col/row indexes. That's essentially the same what you do with swapping the strings, but a bit more general.
« Last Edit: October 21, 2014, 01:24:44 am by wp »

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SortOrder has no effect
« Reply #6 on: October 21, 2014, 09:39:47 am »
... but I could guess that this is due to the underlying balanced tree which stores the cells.
...
You probably could remove the cells to be swapped and re-add them with the correct col/row indexes.
Yeah... I should have thought of that balanced tree ;)
I don't particularly like the idea of removing the cell and inserting it again because it involves a lot (and I mean a lot) of swapping. I think that won't be good for performance. Best is to leave the cells in the balanced tree and only swap the data (leaving the row-counter in the cell intact). I tried it now like this and it works. (Still needs a lot of testing plus some checking if the cells exists etc.)

Code: [Select]
    cell1 := Worksheet.GetCell(index - HeaderCount, iCol - HeaderCount);
    cell2 := Worksheet.GetCell(Withindex - HeaderCount, iCol - HeaderCount);
    New(cell0);
    FillChar(cell0^, SizeOf(TCell), #0);
    cell0^ := cell1^;
    cell1^ := cell2^;
    cell1^.Row:= index - Headercount; // row needs to be kept intact
    cell2^ := cell0^;
    cell2^.Row:= withindex - Headercount; // row needs to be kept intact

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #7 on: October 21, 2014, 10:39:34 am »
I believe that sorting the grid is not the correct way to go because you'll always need a grid to sort data I'd prefer to have sorting in the worksheet directly. Updating the sorted grid would just be a simple repaint.

I am also not sure about the cost of removing/deleting cells from the sorted tree. These are just pointer operations and should be faster than copying cell contents.

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1260
Re: SortOrder has no effect
« Reply #8 on: October 21, 2014, 11:47:30 am »
<suspends lurking...>

Seems to me you've got two entities.  An object representing the spreadsheet, and the grid that that object's data is being displayed in. 

One is easy to sort, one isn't. That's not important though. 

If I was calling grid.sort, then I wouldn't expect that to sort the underlying dataset.  Sort a DBGrid and you don't expect tables to be rewritten.

So, I think you've got two sort's to consider, and it seems to me only grid sorting is required for the OP.

Of course, I could be wrong and it's custard pie on face time :-)

<resumes lurking..>
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #9 on: October 21, 2014, 12:07:57 pm »
The DBGrid is a good example: What is sorted in the first place is the dataset, the grid just displays the dataset in its sorted state. Calling DBGrid.Sort passes the task of sorting over to the dataset. The same here: We have to sort the worksheet first, and then re-display it in the grid.

There are two programming tasks: first, implement sorting in the worksheet; for this much can be copied from TCustomGrid. Second, modify the sorting methods in the grid such that they do not attempt any sorting at all, but just trigger sorting by the worksheet, and redisplay the sorted data.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SortOrder has no effect
« Reply #10 on: October 21, 2014, 01:01:23 pm »
Ok, I understand that sorting in the underlying sheet directly would be preferred. It would involve a lot more work. Because TCustomDrawGrid already does the most work I did create the two functions needed to do this in the grid itself. The grid would always have to sort the underlying sheet because otherwise you would need two sheet in memory. StringGrid also sorts its underlying data. Comparing it to something like a dataset is not fair because in that case you would need to create a real database engine which could sort "on the fly" (and leave the memory structure intact).

A sort routine in the spreadsheet itself would also need to receive a complete range (from/to column/rows) to sort and an index in that range (like you select a range in Excel and sort according to the 3th selected column or row). In TCustomDrawGrid.SortColRow the complete row (or column) gets sorted. I don't think you can adjust the range to sort (columns when sorting rows and rows when sorting columns).

So something like this:
TsWorksheet.SortSheet("E", "D10:J28") sorting range D10:J28 taking values from column E or
TsWorksheet.SortSheet("12", "D10:J28") sorting range D10:J28 taking values from row 12 or
TsWorksheet.SortSheet("12", "D:J") sorting range D:J taking values from row 12 or
TsWorksheet.SortSheet("A", "") sorting complete sheet taking values from column A


Here is my implementation for TCustomDrawGrid.SortColRow so far: (which we can throw in the bin because we need to implement this in TsWorksheet itself)
Code: [Select]
  TsWorksheetGrid = class(TsCustomWorksheetGrid)
    // ...
  protected
    // ...
    procedure ColRowExchanged(IsColumn: boolean; index, WithIndex: integer); override;
    function DoCompareCells(Acol, ARow, Bcol, BRow: integer): integer; override;
    // ...
  end;

procedure TsWorksheetGrid.ColRowExchanged(IsColumn: boolean; index, WithIndex: integer);
var
  iCol, iRow: integer;
  cell1: PCell;
  cell2: PCell;

  procedure SwapCell(C1, C2: PCell);
  var
    cell0: PCell;
    Row1, Col1: integer;
    Row2, Col2: integer;
  begin
    Row1 := C1^.Row;
    Col1 := C1^.Col;
    Row2 := C2^.Row;
    Col2 := C2^.Col;
    New(cell0);
    cell0^ := cell1^;
    cell1^ := cell2^;
    cell2^ := cell0^;
    cell1^.Row := Row1;
    cell1^.Col := Col1;
    cell2^.Row := Row2;
    cell2^.Col := Col2;
    Dispose(cell0);
  end;

begin
  if IsColumn then
  begin
    for iRow := HeaderCount to RowCount - 1 do
    begin
      cell1 := Worksheet.GetCell(iRow - HeaderCount, index - HeaderCount);
      cell2 := Worksheet.GetCell(iRow - HeaderCount, Withindex - HeaderCount);
      SwapCell(cell1, cell2);
    end;
  end
  else { IsColumn }
  begin
    for iCol := HeaderCount to ColCount - 1 do
    begin
      cell1 := Worksheet.GetCell(index - HeaderCount, iCol - HeaderCount);
      cell2 := Worksheet.GetCell(Withindex - HeaderCount, iCol - HeaderCount);
      SwapCell(cell1, cell2);
    end;
  end;
end;

function TsWorksheetGrid.DoCompareCells(Acol, ARow, Bcol, BRow: integer): integer;
var
  A, B: string;
  cell1: PCell;
  cell2: PCell;
begin
  if Assigned(OnCompareCells) then
    Result := inherited DoCompareCells(Acol, ARow, Bcol, BRow)
  else
  begin
    cell1 := Worksheet.GetCell(ARow - HeaderCount, ACol - HeaderCount);
    cell2 := Worksheet.GetCell(BRow - HeaderCount, BCol - HeaderCount);
    A := Worksheet.ReadAsUTF8Text(cell1);
    B := Worksheet.ReadAsUTF8Text(cell2);
    Result := UTF8CompareText(A,B);
    if SortOrder = soDescending then
      Result := -Result;
  end;
end;

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #11 on: October 21, 2014, 05:28:53 pm »
I just uploaded an initial version of fpspreadsheet that supports sorting of the worksheet. The worksheet method SortCols sorts columns in a given cell range, and SortRows does the same with rows. Multiple sorting criteria can be selected (but not working yet).

See the spready project for a demonstration. (Menu "Edit" / "Sort" or "Sort column only") The latter demo sorts only the column containing the active cell while the former one sorts the cells contained in the selection rectangle according to criteria defined in a new dialog, similar to Excel's. As noted, multiple sorting criteria are not yet working at the moment, this can cause spready to hang. Also different sorting directions for each column or row are not yet implemented.

Moreover, the grid's sorting methods are still available, they wll be changed to call the worksheet's sorting methods.

In essence, it is more a proof of concept than a solid implementation. But I have to stop for the moment, my head is smoking...

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SortOrder has no effect
« Reply #12 on: October 21, 2014, 06:00:54 pm »
... my head is smoking...
Oooo, that's not good...  :o

In essence, it is more a proof of concept than a solid implementation.
The concept (and framework) works nice. (also with the dialog for selecting the sort-column in spready)
I did however had a hang when selecting something other then the first column to sort.

But I suggest you take a breather. Bugs are to be worked out later. I think flaza (OP) can look at the new functionality (we all can) and if we find something we'll try to find out what's wrong and report back.

On behalf of all: thanks for the hard work  :D

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: SortOrder has no effect
« Reply #13 on: October 23, 2014, 05:45:06 pm »
Sorting of the worksheet and worksheetgrid should be working now. Please see the wiki for details: http://wiki.lazarus.freepascal.org/FPSpreadsheet#Sorting

 

TinyPortal © 2005-2018