SortOrder has no effect lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- flaza · 2014-10-18 20:35 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 · 2014-10-19 23:00 QuoteAm 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 · 2014-10-20 17:47 Not the answer I hoped for, but an answer anyway. Thanks. ---------------------------------------------------------------------- wp · 2014-10-20 18:47 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 · 2014-10-20 22:51 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. ---------------------------------------------------------------------- wp · 2014-10-20 23:21 QuoteAny 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. ---------------------------------------------------------------------- rvk · 2014-10-21 07:39 Quote from: wp on October 21, 2014, 01:21:53 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 · 2014-10-21 08:39 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 · 2014-10-21 09:47 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 :-) ---------------------------------------------------------------------- wp · 2014-10-21 10:07 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 · 2014-10-21 11:01 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 · 2014-10-21 15:28 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 · 2014-10-21 16:00 Quote from: wp on October 21, 2014, 05:28:53 pm... my head is smoking...Oooo, that's not good...  Quote from: wp on October 21, 2014, 05:28:53 pmIn 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 ---------------------------------------------------------------------- wp · 2014-10-23 15:45 Sorting of the worksheet and worksheetgrid should be working now. Please see the wiki for details: http://wiki.lazarus.freepascal.org/FPSpreadsheet#Sorting