json_to_sheet mutates header array #2139

New Issue

2020-10-11T20:09:52Z

SheetJSDev commented

2020-10-11 20:09:52 +00:00

(Migrated from github.com)

While I try to change header titles by passing array of titles to options like below it does not override the headers. Instead it writes new headers first and original data with old headers again from next cell.

I am experiencing the same problem.

In addition, sheets js is mutating the header array passed in, which is not something I would ever expect.

Example:

const header = ['field2', 'field3'];
const data = [{field1: 'foo', 'field2': 'bar', 'field3': 'baz'}]

const worksheet = XLSX.utils.json_to_sheet(data, {header});

console.log(header); // RESULTS:  ["field2", "field3", "field1"]

Originally posted by @paustint in https://github.com/SheetJS/sheetjs/issues/1487#issuecomment-706759470

>While I try to change header titles by passing array of titles to options like below it does not override the headers. Instead it writes new headers first and original data with old headers again from next cell. I am experiencing the same problem. In addition, sheets js is mutating the header array passed in, which is not something I would ever expect. Example: ```javascript const header = ['field2', 'field3']; const data = [{field1: 'foo', 'field2': 'bar', 'field3': 'baz'}] const worksheet = XLSX.utils.json_to_sheet(data, {header}); console.log(header); // RESULTS: ["field2", "field3", "field1"] ``` _Originally posted by @paustint in https://github.com/SheetJS/sheetjs/issues/1487#issuecomment-706759470_

SheetJSDev commented

2020-10-11 20:22:16 +00:00

(Migrated from github.com)

@paustint Suppose there were two missing fields:

const data = [
  {f1: 1, f2: 2, f3: 3, f4: 4}
];
const header = ['f1', 'f2'];

When the sheet is written, both f3 and f4 will be written after f1 and f2. The order is dependent on the order of presentation within the data itself. For example:

const data = [
  {f1: 1, f4: 4},
  {f2: 2, f3: 3},
];

The order will start with f1 then f2. The next column will be f4 and then the next column will be f3. If you flip the order in the array, like

const data = [
  {f2: 2, f3: 3},
  {f1: 1, f4: 4},
];

the write order will be f1, f2, f3, f4.

We needed a way to communicate that ordering back to the caller. Since elements are never removed (they are only appended if data objects have headers that are missing), mutating the array preserves the intent and lets you chain into subsequent calls of sheet_add_json:

const header = ['f1', 'f2'];
const worksheet = XLSX.utils.json_to_sheet([
  {f2: 2, f3: 3},
], {header});
XLSX.utils.sheet_add_json(worksheet, [
  {f1: 1, f4: 4},
], {header, origin: -1, skipHeader: true});

@paustint Suppose there were two missing fields: ```js const data = [ {f1: 1, f2: 2, f3: 3, f4: 4} ]; const header = ['f1', 'f2']; ``` When the sheet is written, both f3 and f4 will be written after f1 and f2. The order is dependent on the order of presentation within the data itself. For example: ```js const data = [ {f1: 1, f4: 4}, {f2: 2, f3: 3}, ]; ``` The order will start with `f1` then `f2`. The next column will be `f4` and then the next column will be `f3`. If you flip the order in the array, like ```js const data = [ {f2: 2, f3: 3}, {f1: 1, f4: 4}, ]; ``` the write order will be `f1`, `f2`, `f3`, `f4`. We needed a way to communicate that ordering back to the caller. Since elements are never removed (they are only appended if data objects have headers that are missing), mutating the array preserves the intent and lets you chain into subsequent calls of `sheet_add_json`: ```js const header = ['f1', 'f2']; const worksheet = XLSX.utils.json_to_sheet([ {f2: 2, f3: 3}, ], {header}); XLSX.utils.sheet_add_json(worksheet, [ {f1: 1, f4: 4}, ], {header, origin: -1, skipHeader: true}); ```

paustint commented

2020-10-11 21:03:13 +00:00

(Migrated from github.com)

I understand. Many libraries will ignore extra headers if they are not included in the first row of data if a headers array not explicitly specified and I think that sheetsjs has the better approach of not requiring all rows to be the exact same shape.

I now understand the tradeoffs much better, thank you for taking the time to explain.

I understand. Many libraries will ignore extra headers if they are not included in the first row of data if a headers array not explicitly specified and I think that sheetsjs has the better approach of not requiring all rows to be the exact same shape. I now understand the tradeoffs much better, thank you for taking the time to explain.

SheetJSDev commented

2020-10-11 21:06:16 +00:00

(Migrated from github.com)

Now that you understand this, maybe you can help improve the docs :) We'll accept a PR that clarifies the behavior.

👍 1

himanshusaini111 commented

2021-01-05 19:02:37 +00:00

(Migrated from github.com)

@SheetJSDev Hi, This issue is still open. So I raised a PR with suggested Changes

Now that you understand this, maybe you can help improve the docs :) We'll accept a PR that clarifies the behavior.

@SheetJSDev Hi, This issue is still open. So I raised a PR with suggested Changes > Now that you understand this, maybe you can help improve the docs :) We'll accept a PR that clarifies the behavior.

reviewher commented

2021-09-11 08:22:36 +00:00

(Migrated from github.com)

af34ae4178

af34ae4178390974977101221d9f48869d5a1228

Sign in to join this conversation.