Data Sort

Open the demo in new window can open the designer

The sortOption interface is as follows:

type DataCellBase = {
    binding?: Binding;
    sortOptions?: SortOption[]; // can set multiple sort rule here.
};
type SortOption = ({
    asc: string;
} | {
    desc: string;
}) & {
    list?: string[]; // custom order list.
};

There are several uses for data sorting:

1. Sort data by column value:

   You can specify which data column the current cellNode is sorted according to, then the currentNode and child node will reorder within the currentNode parent groups.

   const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
   const ordersTable = spread.dataManager().addTable('Orders', {
       remote: {
           read: {
               url: 'https://demodata.mescius.io/northwind/api/v1/orders'
           }
       }
   });
   ordersTable.fetch().then(() => {
       const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet);
       const templateSheet = reportSheet.getTemplate();
       // set value and binding for the template
       templateSheet.setTemplateCell(1, 0, {
           type: 'List',
           binding: `Orders[customerId]`,
           sortOptions: [ // orderBy freight asc then by shipName asc.
               {
                   asc: "freight"
               },
               {
                   asc: "shipName"
               }
           ]
       });
       templateSheet.setTemplateCell(1, 1, {
           type: 'List',
           binding: `Orders[freight]`,
       });
       templateSheet.setTemplateCell(1, 2, {
           type: 'List',
           binding: `Orders[shipName]`,
       });
       reportSheet.refresh();
   });
   

2. Sort data by cell:

   You can specify which cellNode value the current cellNode is sorted according to; the cellNode value must be the current cellNode child. If there are multiple values in the referred cellNode, it will use the first value.

   If the referred cellNode is the current cellNode itself, it will sort by the current cell value.

   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: `Orders[customerId]`,
       sortOptions: [
           {
               desc: "=A2"
           }
       ]
   });
   

3. Sort data by formula:

   You can use the formula as a sorting option to sort data by the formula calculation result:

   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: 'Orders[orderDate]',
       sortOptions: [
           {
               desc: "=YEAR([@orderDate])"
           },
           {
               asc: "=YEAR([@orderDate])"
           }
       ]
   });
   templateSheet.setTemplateCell(1, 1, {
       type: "List",
       binding: "Orders[freight]",
   });
   

4. Sort data by a list:

   You can use a specified value array in each sort option, then the sort result will order by the list order first, then in ascending/descending order.

   templateSheet.setTemplateCell(1, 0, {
       type: 'List',
       binding: 'Orders[CustomerId]',
       sortOptions: [
           {
               asc: "customerId",
               list: ["VINET", "TOMSP"]
           }
       ]
   });
   templateSheet.setTemplateCell(1, 1, {
       type: "List",
       binding: "Orders[freight]",
   });