Columns in lowercase #3109

New Issue

Rednas83 · 2024-04-13T08:42:51Z

Rednas83 commented

2024-04-13 08:42:51 +00:00

Usually headers in excel starts with a capital letter and json headers are written in lowercase

Something like

S	H	E	E	T	J	S 
1	2			5	6	7
2	3			6	7	8
3	4			7	8	9
4	5	6	7	8	9	0

Returned as

[
  {s: 1, h: 2, t: 5, j: 6, s_1: 7}
  {s: 2, h: 3, t: 6, J: 7, s_1: 8}
  {s: 3, h: 4, t: 7, J: 8, s_1: 9}
  {s: 4, h: 5, e: 6, e_1: 7, t: 8, j: 9, s_1: 0}
]

Is there an easy performant way to return the header in lowercase with sheet_to_json?
And perhaps also add support for replacing whitespaces?

Something like
Name of the book
returned as
name_of_the_book

Usually headers in excel starts with a capital letter and json headers are written in lowercase **Something like** ``` S H E E T J S 1 2 5 6 7 2 3 6 7 8 3 4 7 8 9 4 5 6 7 8 9 0 ``` **Returned as** ``` [ {s: 1, h: 2, t: 5, j: 6, s_1: 7} {s: 2, h: 3, t: 6, J: 7, s_1: 8} {s: 3, h: 4, t: 7, J: 8, s_1: 9} {s: 4, h: 5, e: 6, e_1: 7, t: 8, j: 9, s_1: 0} ] ``` Is there an easy performant way to return the header in lowercase with `sheet_to_json`? And perhaps also add support for replacing whitespaces? **Something like** `Name of the book` **returned as** `name_of_the_book`

sheetjs commented

2024-04-13 09:36:48 +00:00

https://docs.sheetjs.com/docs/api/utilities/array#array-output

Using the example in the live codeblock:

  var ws = XLSX.utils.aoa_to_sheet([
    ["S", "h", "e", "e", "t", "J", "S"],
    [  1,   2,    ,    ,   5,   6,   7],
    [  2,   3,    ,    ,   6,   7,   8],
    [  3,   4,    ,    ,   7,   8,   9],
    [  4,   5,   6,   7,   8,   9,   0]
  ]);

If you want to override the headers, pass an array as the header option and pass range: 1 to skip the first row:

var data = XLSX.utils.sheet_to_json(ws, {header:["s","h","e","e_1","t","j","s_1"], range:1})

Note that you must disambiguate the headers directly.

If you want to do this dynamically, pull the header row using sheet_to_json after restricting the range:

var range = XLSX.utils.decode_range(ws["!ref"]);
range.e.r = range.s.r;
var header = XLSX.utils.sheet_to_json(ws, {header:1, range})[0].map(h => h.toLowerCase());
var data = XLSX.utils.sheet_to_json(ws, {header, range:1});

If you want to replace whitespace with underscores, just do it in the map:

var header = XLSX.utils.sheet_to_json(ws, {header:1, range})[0].map(h => h.toLowerCase().replace(/ /g, "_"));

https://docs.sheetjs.com/docs/api/utilities/array#array-output Using the example in the live codeblock: ```js var ws = XLSX.utils.aoa_to_sheet([ ["S", "h", "e", "e", "t", "J", "S"], [ 1, 2, , , 5, 6, 7], [ 2, 3, , , 6, 7, 8], [ 3, 4, , , 7, 8, 9], [ 4, 5, 6, 7, 8, 9, 0] ]); ``` If you want to override the headers, pass an array as the `header` option and pass `range: 1` to skip the first row: ```js var data = XLSX.utils.sheet_to_json(ws, {header:["s","h","e","e_1","t","j","s_1"], range:1}) ``` Note that you must disambiguate the headers directly. --- If you want to do this dynamically, pull the header row using `sheet_to_json` after restricting the range: ```js var range = XLSX.utils.decode_range(ws["!ref"]); range.e.r = range.s.r; var header = XLSX.utils.sheet_to_json(ws, {header:1, range})[0].map(h => h.toLowerCase()); var data = XLSX.utils.sheet_to_json(ws, {header, range:1}); ``` If you want to replace whitespace with underscores, just do it in the map: ```js var header = XLSX.utils.sheet_to_json(ws, {header:1, range})[0].map(h => h.toLowerCase().replace(/ /g, "_")); ```

sheetjs closed this issue

2024-04-13 09:36:49 +00:00

Sign in to join this conversation.