js-xlsx how to rearrange columns?

Question

I am using js-xlsx to create an excel file. After doing .json_to_sheet I am getting my sheet not with the wanted columns order. for example, I am getting the following order:

100 | 200 | 300| 400| a | b | c

when 100, 200, 300, 400, a, b, c are my columns header.

I what to get the following order:

a | b | c | 100 | 200 | 300| 400

when a | b | c are always constant and the number of the other columns are changing.

How can I rearrange the columns order?

NOTE: When used numbers as header the xlsx rearrange the columns such that the headers start whit numbers are first and then all the rest. When the headers don't start with numbers you can just order the columns in the JSON you save. One solution is to add a space ' ' before the number and it fixes the problem. but I don't want a space before the number.

The issue is with js not with xlsx. E.g. see here: https://stackoverflow.com/questions/5525795/does-javascript-guarantee-object-property-order/38218582#38218582. I think the answer below should work... — Robin Mackenzie, May 08 '21 at 11:34
that may explain the problem. I will stay with my solution - adding space before the numbers — Dean Taler, May 09 '21 at 07:30

score 0 · Answer 1 · edited May 02 '21 at 07:54

0

// by default generates 'a,b,c\n1,2,3\n'
XLSX.utils.sheet_to_csv(XLSX.utils.json_to_sheet([{a:1,b:2,c:3}]))

// pass header:['c','a','b'] to reorder: 'c,a,b\n3,1,2\n'
XLSX.utils.sheet_to_csv(XLSX.utils.json_to_sheet([{a:1,b:2,c:3}], {header:['c','a','b']}))

// pass header:['c','b','a'] to reorder: 'c,b,a\n3,2,1\n'
XLSX.utils.sheet_to_csv(XLSX.utils.json_to_sheet([{a:1,b:2,c:3}], {header:['c','b','a']}))

I found this on github issue #738 Check if you can make something out of this. (Something with the 'header' part)

edited May 02 '21 at 07:54

David Buck

3,439
29
24
31

answered May 02 '21 at 07:11

THE TECHIE HAND

106
5

it doesn't help, when used numbers as header the ```xlsx``` rearrange the columns such that the headers start whit numbers are first and then all the rest. when the headers don't start with numbers you can just order the columns in the ```JSON``` you save – Dean Taler May 02 '21 at 13:40
Can you expand on why it doesn't help ? Passing `["a", "b", "c", "100", "200", "300", "400"]` to the `header` option should do what you want. – Robin Mackenzie May 08 '21 at 11:34
I did the same, but when I open it with excel it is not in the same order - the numbers are first – Dean Taler May 09 '21 at 07:27

js-xlsx how to rearrange columns?

1 Answers1