How to move the empty cells to the top of a column when sorting it in React Spreadsheet

This knowledge base explains you how to move the empty cells to the top of a column when sorting it in React Spreadsheet. This requirement can be achieved by providing the column name in which the sort must be performed using the custom sortComparer. In the custom sortComparer function, compare each value within the provided range and sort it.

[index.js]

import * as React from 'react';
import { createRoot } from 'react-dom/client';
import {
 SpreadsheetComponent,
 SheetsDirective,
 SheetDirective,
 ColumnsDirective,
 RangesDirective,
 RangeDirective,
 ColumnDirective,
 isNumber,
} from '@syncfusion/ej2-react-spreadsheet';
import { isNullOrUndefined } from '@syncfusion/ej2-base';
import { defaultData } from './data';
import { DataUtil } from '@syncfusion/ej2-data';
import './index.css';

function App() {
 let spreadsheet;
 const onCreated = () => {
   spreadsheet.cellFormat(
     { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
     'A1:F1'
   );
 };
 const mySortComparer = (compareData, comparingData) => {
   // Assinging the sorting direction statically.
   const direction = 'Ascending';
   const comparer = DataUtil.fnSort(direction);
   let compareStringValue = false;
   let comparingStringValue = false;
   // Check whether the compareData is empty or not.
   const isCompareDataEmpty =
     isNullOrUndefined(compareData) ||
     (compareData &&
       (isNullOrUndefined(compareData.value) || compareData.value === ''));
   // Check whether the comparingData is empty or not.
   const isComparingDataEmpty =
     isNullOrUndefined(comparingData) ||
     (comparingData &&
       (isNullOrUndefined(comparingData.value) || comparingData.value === ''));
   // Condition to check the empty value and return the number accordingly.
   if (isCompareDataEmpty && isComparingDataEmpty) {
     return 1;
   }
   if (isCompareDataEmpty) {
     return -1;
   }
   if (isComparingDataEmpty) {
     return 1;
   }
   // Check the whether the data is number or not.
   if (
     compareData &&
     comparingData &&
     (typeof compareData.value === 'string' ||
       typeof comparingData.value === 'string') &&
     compareData.value !== '' &&
     comparingData.value !== ''
   ) {
     if (isNumber(compareData.value)) {
       compareData.value = parseIntValue(compareData.value);
       compareStringValue = true;
     }
     if (isNumber(comparingData.value)) {
       comparingData.value = parseIntValue(comparingData.value);
       comparingStringValue = true;
     }
     // Data comparison on the provided column.
     if (!compareStringValue && !comparingStringValue) {
       const caseOptions = { sensitivity: 'case' };
       const collator = new Intl.Collator(spreadsheet.locale, caseOptions);
       if (!direction || direction.toLowerCase() === 'ascending') {
         return collator.compare(compareData.value, comparingData.value);
       } else {
         return collator.compare(compareData.value, comparingData.value) * -1;
       }
     }
   }
   // Return the sorted data of the provided column.
   return comparer(
     compareData ? compareData.value : compareData,
     comparingData ? comparingData.value : comparingData
   );
 };
 const sortData = () => {
   if (spreadsheet.activeSheetIndex === 0) {
     spreadsheet.sort(
       {
         // Provide the column name in field property for which the sort has to be performed.
         sortDescriptors: { field: 'A', sortComparer: mySortComparer },
         containsHeader: true,
       },
       'A1:F9'
     );
   }
 };

 return (
   <div className="control-pane">
     <button onClick={sortData}> Custom Sort </button>
     <div className="control-section spreadsheet-control">
       <SpreadsheetComponent
         ref={(ssObj) => {
           spreadsheet = ssObj;
         }}
         created={onCreated}
       >
         <SheetsDirective>
           <SheetDirective name="Car Sales Report">
             <RangesDirective>
               <RangeDirective dataSource={defaultData}></RangeDirective>
             </RangesDirective>
             <ColumnsDirective>
               <ColumnDirective width={180} />
               <ColumnDirective width={130} />
               <ColumnDirective width={130} />
               <ColumnDirective width={180} />
               <ColumnDirective width={130} />
               <ColumnDirective width={120} />
             </ColumnsDirective>
           </SheetDirective>
         </SheetsDirective>
       </SpreadsheetComponent>
     </div>
   </div>
 );
}
export default App;

const root = createRoot(document.getElementById('sample'));
root.render(<App />);

Sample Link:

https://stackblitz.com/edit/react-eqxcrv-u75nwq?file=index.js

Output:

Documentation link:

https://ej2.syncfusion.com/react/documentation/spreadsheet/how-to/sort-a-range-by-custom-list

https://ej2.syncfusion.com/react/documentation/spreadsheet/sort