Sort excel file given column using nodejs

3.7k views Asked by At

I have a range in excel that I need to sort excel file according to ID column. The data will always range from Column A1 to Column A[row_count]. I tried to use exceljs module for this operation but I didn't find any sort function. How to implement sorting programmatically?

Here is my excel file content.

  • ID | Applicant | Status | Comment
  • 3224 |Armin Barrywater |In Review |Underwriter is out until next week.
  • 3244 |Georgi Angelchov |New |
  • 3257 |Imelda Sanchez |New |
  • 3223 |Jack Banner |Approved |
  • 3226 |Perry Kane |On Hold |Waiting on paperwork from customer.
  • 3225 |Shiela Donahue |In Review |
  • 3235 |Xavier Fannello |New |
1

There are 1 answers

0
Naive Developer On

It is not my answer (I have not tested), I found one in the following link:

https://github.com/guyonroche/exceljs/issues/363#issuecomment-435692059

const sortByColumn = columnNum => {
  if (worksheet) {
    columnNum--;
    const sortFunction = (a, b) => {
      if (a[columnNum] === b[columnNum]) {
        return 0;
      }
      else {
        return (a[columnNum] < b[columnNum]) ? -1 : 1;
      }
    }
    let rows = [];
    for (let i = 1; i <= worksheet.actualRowCount; i++) {
      let row = [];
      for (let j = 1; j <= worksheet.columnCount; j++) {
        row.push(worksheet.getRow(i).getCell(j).value);
      }
      rows.push(row); 
    }
    rows.sort(sortFunction);
    // Remove all rows from worksheet then add all back in sorted order
    worksheet.spliceRows(1, worksheet.actualRowCount);
    // Note worksheet.addRows() may add them to the end of empty rows so loop through and add to beginnning
    for (let i = rows.length; i >= 0; i--) {
      worksheet.spliceRows(1, 0, rows[i]);
    }
  }
}