sortUndefined and Excel like sorting #6061

Activity

1. baskvava commented on Aug 5, 2025

   

   baskvava

   on Aug 5, 2025

   More actions

   Hello @philipp985

   After further investigation and experimentation, I've found a solution that achieves the desired "Excel-like" sorting while respecting all the constraints. I'm posting it here and hope it can solve your problem.

   1. Add accessorFn and cell under column definition

   accessorFn: (originalRow) => {
     const value = originalRow.name;
     if (value === null || value === "" || value === undefined) {
       return undefined;
     }
     return value;
   },
   cell: (info) => {
     const originalValue = info.row.original.name;
     return String(originalValue);
   },

   2. Replace cell getValue() as cell getContent() and render into flexRender() into the table

   This is final version for App.tsx

   // App.tsx
   
   import React from "react";
   import {
     useReactTable,
     getCoreRowModel,
     getSortedRowModel,
     ColumnDef,
     flexRender,
   } from "@tanstack/react-table";
   import { excelSortingFn } from "./excelSortingFn";
   
   type RowData = { name: string | null | undefined };
   
   const data: RowData[] = [
     { name: "Apple" },
     { name: "" },
     { name: null },
     { name: "banana" },
     { name: "Zebra" },
     { name: undefined },
   ];
   
   const columns: ColumnDef<RowData>[] = [
     {
       header: "Name",
       accessorKey: "name",
       // add accessorFn
       accessorFn: (originalRow) => {
         const value = originalRow.name;
         if (value === null || value === "" || value === undefined) {
           return undefined;
         }
         return value;
       },
       // add cell
       cell: (info) => {
         const originalValue = info.row.original.name;
         return String(originalValue);
       },
       sortingFn: excelSortingFn,
       sortUndefined: "last",
     },
   ];
   
   export default function App() {
     const [sorting, setSorting] = React.useState([]);
     const table = useReactTable({
       data,
       columns,
       state: { sorting },
       onSortingChange: setSorting,
       getCoreRowModel: getCoreRowModel(),
       getSortedRowModel: getSortedRowModel(),
     });
   
     return (
       <table style={{ borderCollapse: "collapse", width: "100%" }}>
         <thead>
           {table.getHeaderGroups().map((hg) => (
             <tr key={hg.id}>
               {hg.headers.map((h) => (
                 <th
                   key={h.id}
                   onClick={h.column.getToggleSortingHandler()}
                   style={{ cursor: "pointer", borderBottom: "1px solid black" }}
                 >
                   {h.column.id}{" "}
                   {{ asc: "🔼", desc: "🔽" }[h.column.getIsSorted() as string] ??
                     null}
                 </th>
               ))}
             </tr>
           ))}
         </thead>
         <tbody>
           {table.getRowModel().rows.map((r) => (
             <tr key={r.id}>
               {r.getVisibleCells().map((c) => (
                 <td
                   key={c.id}
                   style={{ padding: "4px", borderBottom: "1px solid lightgray" }}
                 >
                    {/* replace here*/}
                   {flexRender(c.column.columnDef.cell, c.getContext())}
                 </td>
               ))}
             </tr>
           ))}
         </tbody>
       </table>
     );
   }
2. jiji-hoon96

   added a commit that references this issue on Sep 2, 2025

   docs: add Excel-like sorting guide for null/undefined handling

   ...

   ba25124
3. ippeiukai commented on Jun 22, 2026

   

   ippeiukai

   on Jun 22, 2026

   More actions

   I'm trying to migrate existing tables to v8. In v7, sorting functions received desc flag that can be used to keep some rows always at the bottom.

   For ease of migration, it would still be nice to have more generalized option than sortUndefined, like this:

     // `sortUndefined: 'last'` equivalent
     sortFixed: { match: undefined, position: 'last' }
   
     // Pin null to always appear last
     sortFixed: { match: null, position: 'last' }
   
     // Pin empty values to always appear last
     sortFixed: { match: (v) => v == null || v === '', position: 'last' }