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.
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.
- 1 The issue is with js not with xlsx. E.g. see here: stackoverflow./questions/5525795/…. I think the answer below should work... – Robin Mackenzie Commented May 8, 2021 at 11:34
- that may explain the problem. I will stay with my solution - adding space before the numbers – Dean Taler Commented May 9, 2021 at 7:30
2 Answers
Reset to default 6 +50// 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)
const header=['a','b','c',100,..] const ws = XLSX.utils.json_to_sheet(Data, { header: header, }); remember the name of column headers should be same as the key in the data