Contents of sheet not being exported properly #2972

New Issue

SeanCullen11 · 2023-06-22T20:29:55Z

SeanCullen11 commented

2023-06-22 20:29:55 +00:00

I'm requesting a json response from an api and when I try and turn that response into a excel sheet I am only able to export the headers of my response and not the content

Here is my block of code:

const XLSX = require("xlsx")
let response = body.results
var ws = XLSX.utils.json_to_sheet(response)
var wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, ws, title)
XLSX.writeFile(wb, title + ".xlsx")

When looking at the ws object I can see it is populated with all the headers and content I want mapped to the right cells but when I actually export all I have is an excel sheet full of headers

I'm requesting a json response from an api and when I try and turn that response into a excel sheet I am only able to export the headers of my response and not the content Here is my block of code: const XLSX = require("xlsx") let response = body.results var ws = XLSX.utils.json_to_sheet(response) var wb = XLSX.utils.book_new() XLSX.utils.book_append_sheet(wb, ws, title) XLSX.writeFile(wb, title + ".xlsx") When looking at the ws object I can see it is populated with all the headers and content I want mapped to the right cells but when I actually export all I have is an excel sheet full of headers

sheetjs commented

2023-06-22 20:35:13 +00:00

Can you log the response and confirm it is an actual JS array of objects? If it is a string like '[{"a":1,"b":2},{"a":3,"b":4}] you will need something like:

var data = JSON.parse(response);
var ws = XLSX.utils.json_to_sheet(data);

If that doesn't work, please share the contents of response.

Can you log the `response` and confirm it is an actual JS array of objects? If it is a string like `'[{"a":1,"b":2},{"a":3,"b":4}]` you will need something like: ```js var data = JSON.parse(response); var ws = XLSX.utils.json_to_sheet(data); ``` If that doesn't work, please share the contents of `response`.

SeanCullen11 commented

2023-06-22 20:50:30 +00:00

I believe it should be since it is being processed correctly by json_to_sheet

I attached images below that show the value of both response and ws after the json_to_sheet function

I believe it should be since it is being processed correctly by json_to_sheet I attached images below that show the value of both response and ws after the json_to_sheet function

response.png

106 KiB

ws.png

83 KiB

sheetjs commented

2023-06-23 02:39:09 +00:00

When the property is an object that is not a date, it is assumed to be a cell object and will be passed thru. We can eventually add some validation.

To fix your export, since it looks like those fields are arrays, you can pull the first value of each array property:

var fixed_data = response.map(obj => Object.fromEntries(Object.entries(obj).map(row => ([row[0], Array.isArray(row[1]) ? row[1][0] : row[1]]))))

If you're generally curious, https://docs.sheetjs.com/docs/getting-started/example#raw-data discusses how you can look at your dataset and flatten to an array of simple objects.

When the property is an object that is not a date, it is assumed to be a cell object and will be passed thru. We can eventually add some validation. To fix your export, since it looks like those fields are arrays, you can pull the first value of each array property: ```js var fixed_data = response.map(obj => Object.fromEntries(Object.entries(obj).map(row => ([row[0], Array.isArray(row[1]) ? row[1][0] : row[1]])))) ``` If you're generally curious, https://docs.sheetjs.com/docs/getting-started/example#raw-data discusses how you can look at your dataset and flatten to an array of simple objects.

SeanCullen11 commented

2023-06-23 13:49:13 +00:00

Ahh okay. I see. That's good to know.

The code you provided did the trick, I'll read though the docs on flattening datasets.

Thanks for your help!

Ahh okay. I see. That's good to know. The code you provided did the trick, I'll read though the docs on flattening datasets. Thanks for your help!

sheetjs closed this issue

2023-06-23 13:51:39 +00:00

sheetjs referenced this issue

2023-06-26 01:51:40 +00:00

json_to_sheet not convert data as required #2888

Sign in to join this conversation.