Clear SortRange

Clear SortRange

Posted by: pyoh on 21 February 2024, 10:15 am EST

• 

  pyoh

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 February 2024, 10:15 am EST

  I want to implement a function that sorts by asc, desc, and default when a column header is clicked.
  
  so, I use SortRange API like below

  sheet.bind(GC.Spread.Sheets.Events.CellClick, (_sender: any, args: any) => {
        if (
          args.sheetArea === GC.Spread.Sheets.SheetArea.colHeader
        ) {
          sheet.sortRange(
            0,
            0,
            sheet.getRowCount(),
            sheet.getColumnCount(),
            true,
            [
              {
                index: args.col,
                ascending: true,
              },
            ]
          );
        }
      });

  ascending, descending is possible but can’t restore origin data order.
  
  how can be implement?

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 22 February 2024, 11:08 pm EST

  Hi,

  Currently, once the filter is applied, the underlying values are changed for the cells and the original order cannot be restored.

  However, you may do custom implementation and undo the changes to get back the original order. Kindly refer to the following code snippet and the sample:

      let Commands = GC.Spread.Sheets.Commands;
  
      let customSort = {
          canUndo: true,
          execute: function (spread, options, isUndo) {
              if (isUndo) {
                  Commands.undoTransaction(spread, options);
                  return true;
              } else {
                  Commands.startTransaction(spread, options);
  
                  let sheet = spread.getSheetFromName(options.sheetName);
                  const { rowIndex, colIndex, rowCount, colCount, ascending, index } = options;
                  console.log(sheet, rowIndex, colIndex, rowCount, colCount, ascending, index)
                  sheet.sortRange(
                      rowIndex, colIndex, rowCount, colCount,
                      true,
                      [
                          {
                              index: index,
                              ascending: ascending,
                          },
                      ]
                  );
                  Commands.endTransaction(spread, options);
                  return true;
              }
          }
      }
  
      spread.commandManager().register("customSorting", customSort, null, false, false, false, false);
  
      let clickCounts = {};
      sheet.bind(GC.Spread.Sheets.Events.CellClick, (_sender, args) => {
          if (args.sheetArea === GC.Spread.Sheets.SheetArea.colHeader) {
              const colIndex = args.col;
              clickCounts[colIndex] = clickCounts[colIndex] || 0;
  
              let rowCount = sheet.getRowCount();
              let colCount = sheet.getColumnCount();
  
              if (clickCounts[colIndex] === 0) {
                  spread.commandManager().execute({
                      cmd: "customSorting", sheetName: "Sheet1", rowIndex: 0, colIndex: 0,
                      rowCount: rowCount, colCount: colCount, ascending: true, index: args.col
                  });
  
              } else if (clickCounts[colIndex] === 1) {
                  spread.commandManager().execute({
                      cmd: "customSorting", sheetName: "Sheet1", rowIndex: 0, colIndex: 0,
                      rowCount: rowCount, colCount: colCount, ascending: false, index: args.col
                  });
              } else {
                  clickCounts[colIndex] = -1;
                  // Undo Two times;
                  spread.undoManager().undo();
                  spread.undoManager().undo();
              }
              clickCounts[colIndex]++;
          }
      });

  Sample: https://jscodemine.grapecity.com/share/WjP267n5tkSPdsdjwleh1Q/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  However, this is just a custom solution. If you apply the Row Filter on Excel also, you cannot revert back to original order. Similarly, you could do your custom implementation.

  References:

  Undo Manager Demo: https://developer.mescius.com/spreadjs/demos/features/worksheet/actions/undo-manager#demo_source_name

  Custom Actions: https://developer.mescius.com/spreadjs/demos/features/worksheet/actions/custom-action/purejs

  Regards,

  Ankit

• 

  pyoh

  • Post Options:
  • Link
    Copy

  Posted 24 February 2024, 11:12 pm EST

  Thanks for answer.
  
  I feel unfortunate that has no way to clear filters when after it applies.
  
  undo command that you suggested solution maybe not proper to me. when filter change and then cell value changed. your undo solution is possible to undo cell value either.

  but very creative solution. thx

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 26 February 2024, 10:34 pm EST

  Hi,

  You could add a hidden column that stores the initial row indexes, and when you click on the third time, it should short according to the hidden column index.

  Before sorting store the row indexes in a new hidden column:

  function preProcessSheet(sheet) {
  
      sheet.suspendPaint();
      sheet.suspendEvent();
      sheet.suspendDirty();
  
      // Add New Column
      sheet.addColumns(sheet.getColumnCount(), 1);
  
      // Store Indexes on the column
      for (let i = 0; i < sheet.getRowCount(); i++) {
          sheet.setValue(i, sheet.getColumnCount() - 1, i);
      }
  
      // Hide the newly added column
      sheet.setColumnVisible(sheet.getColumnCount() - 1 , false);
  
      sheet.resumeDirty();
      sheet.resumeEvent();
      sheet.resumePaint();
  
  }

  Kindly use the following code when col header is clicked:

      sheet.bind(GC.Spread.Sheets.Events.CellClick, (_sender, args) => {
          if (args.sheetArea === GC.Spread.Sheets.SheetArea.colHeader) {
              const colIndex = args.col;
              clickCounts[colIndex] = clickCounts[colIndex] || 0;
  
              if (clickCounts[colIndex] === 0) {
                  sheet.sortRange(
                      0,
                      0,
                      sheet.getRowCount(),
                      sheet.getColumnCount(),
                      true,
                      [
                          {
                              index: args.col,
                              ascending: true,
                          },
                      ]
                  );
  
              } else if (clickCounts[colIndex] === 1) {
                  sheet.sortRange(
                      0,
                      0,
                      sheet.getRowCount(),
                      sheet.getColumnCount(),
                      true,
                      [
                          {
                              index: args.col,
                              ascending: false,
                          },
                      ]
                  );
              } else {
                  clickCounts[colIndex] = -1;
                  sheet.sortRange(
                      0,
                      0,
                      sheet.getRowCount(),
                      sheet.getColumnCount(),
                      true,
                      [
                          {
                              index: sheet.getColumnCount() - 1,
                              ascending: true,
                          },
                      ]
                  );
              }
              clickCounts[colIndex]++;
          }
      });
  

  Sample: https://jscodemine.grapecity.com/share/hqhWZur5dEmGYEwwQLgxXQ/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  Let me know if you face any issues.

  Regards,

  Ankit