Sort and filter columns with XLSX js after json_to_sheet

Question

I'm using this xlsx js library to generate an Excel file from a list of TypeScript objects in an Angular5 application.

I don't need all attributes of the TypeScript objects and I want to sort the other attributes in a specific way.

A simple TypeScript object list as an example :

[
  { 
    "id":"one",
    "location":"New York",
    "metadata":"just other infos",
    "name":"John",
  },
  { 
    "id":"two",
    "location":"Boston",
    "metadata":"just other infos",
    "name":"Mark",
  },
  { 
    "id":"three",
    "location":"Portland",
    "metadata":"just other infos",
    "name":"Samy",
  }
]

Wanted Excel output :

|id    |name |location |
|one   |John |New York |
|two   |Mark |Boston   |
|three |Samy |Portland |

What I have so far (sorting is ok):

const workbook = XLSX.utils.book_new();
const myHeader = ["id","name","location"];
const worksheet = XLSX.utils.json_to_sheet(this.myListOfObjects(), {header: myHeader});
XLSX.utils.book_append_sheet(workbook, worksheet, 'tab1');
XLSX.writeFile(workbook, 'excel_export.xlsb');

But this generate the following excel file:

|id    |name |location |metadata        |
|one   |John |New York |just other infos|
|two   |Mark |Boston   |just other infos|
|three |Samy |Portland |just other infos|

My problem is that all non listed attributes are just append at the end.

I don't/can't change my TypeScript object. I don't want to convert the worksheet back to an array.

Answer 1

It took me a while to find this easy solution. There is other possibilities but this is the simplest one.

It's possible to reduce the scope of the excel file with the worksheet['!ref']

So I reduced the scope from 'A1:D4' to 'A1:B3'. To do so I took the length of the myHeader list (this list should comes from a config file).

const range = XLSX.utils.decode_range(worksheet['!ref']);
range.e['c'] = myHeader.length - 1;
worksheet['!ref'] = XLSX.utils.encode_range(range);

Complete code snippet:

const workbook = XLSX.utils.book_new();
const myHeader = ["id","name","location"];
const worksheet = XLSX.utils.json_to_sheet(this.myListOfObjects(), {header: myHeader});

const range = XLSX.utils.decode_range(worksheet['!ref']);
range.e['c'] = myHeader.length - 1;
worksheet['!ref'] = XLSX.utils.encode_range(range);

XLSX.utils.book_append_sheet(workbook, worksheet, 'tab1');
XLSX.writeFile(workbook, 'excel_export.xlsb');

Et voila, this will generate the following excel file:

|id    |name |location |
|one   |John |New York |
|two   |Mark |Boston   |
|three |Samy |Portland |

Do you have a better solution ? Please share :)