Newbie to js-xlsx - JSON to xlsx conversion with using angular #666

New Issue

2017-05-24T10:14:41Z

samuelkavin commented

2017-05-24 10:14:41 +00:00

(Migrated from github.com)

Anyone can guide me how to convert json to xlsx with using angular?

Here is sample json format:
[ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM", "Location":"Singapore", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM", "Location":"Malaysia", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"2342234", "subName":"30, Jul 2013 09:24 AM", "Location":"India", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" } ]

Here is xlsx sample that need to be generated:
screen shot 2017-05-24 at 6 16 18 pm

Thanks in advance

Anyone can guide me how to convert json to xlsx with using angular? Here is sample json format: `[ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM", "Location":"Singapore", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM", "Location":"Malaysia", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"2342234", "subName":"30, Jul 2013 09:24 AM", "Location":"India", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" } ]` Here is xlsx sample that need to be generated: <img width="1003" alt="screen shot 2017-05-24 at 6 16 18 pm" src="https://cloud.githubusercontent.com/assets/5300968/26399488/b2cc8ab8-40af-11e7-9302-d710cbd2cce0.png"> Thanks in advance

jomel commented

2017-05-26 10:18:05 +00:00

(Migrated from github.com)

@samuelkavin see my answer to your comment on #610

❤️ 1

samuelkavin commented

2017-05-26 11:52:33 +00:00

(Migrated from github.com)

@jomel, thank you for response. All done. Now, I stuck with settting up the title of the excel file which is Member Listing, Company Name and Report Date on A1, A2 and A3 cell. Any idea?

samuelkavin commented

2017-05-28 01:20:18 +00:00

(Migrated from github.com)

@martinnov92, is it possible insert custom 3 row before header? something like this:
screen shot 2017-05-24 at 6 16 18 pm

@martinnov92, is it possible insert custom 3 row before header? something like this: <img width="1003" alt="screen shot 2017-05-24 at 6 16 18 pm" src="https://cloud.githubusercontent.com/assets/5300968/26525313/caefd116-4386-11e7-9d55-a49c846adc0f.png">

martinnov92 commented

2017-05-29 04:36:18 +00:00

(Migrated from github.com)

Hello @samuelkavin I really don´t know this, sorry

👍 1 👎 1

bogdancar commented

2017-06-21 04:22:10 +00:00

(Migrated from github.com)

I added an example on exporting a xlsx file from an array of jsons on Angular2/4:

https://github.com/bogdancar/xlsx-json-to-xlsx-demo-Angular2

I added an example on exporting a xlsx file from an array of jsons on Angular2/4: [https://github.com/bogdancar/xlsx-json-to-xlsx-demo-Angular2](https://github.com/bogdancar/xlsx-json-to-xlsx-demo-Angular2)

indunilw commented

2017-07-12 11:14:42 +00:00

(Migrated from github.com)

@bogdancar your demo isnt avaialble

bogdancar commented

2017-07-12 11:55:05 +00:00

(Migrated from github.com)

@indunilw Sorry, fixed the link.

[@indunilw](https://github.com/indunilw) Sorry, fixed the link.

gnasit commented

2017-11-15 23:29:25 +00:00

(Migrated from github.com)

@samuelkavin @jomel did you solve the custom header portion of the sheet, where you want common data for the table in a top section, is this possible? the memberListing portion in the OPs example

SheetJSDev commented

2018-01-09 08:10:30 +00:00

(Migrated from github.com)

The new sheet_add_json function can add to an existing worksheet:

var ws = XLSX.utils.aoa_to_sheet([
  ["Member Listing"],
  ["Company Name:", "<Master Policy Holder Name>"],
  ["Report Date:", "<DD/MM/YYYY>"]
]);
XLSX.utils.sheet_add_json(ws, [ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM", "Location":"Singapore", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM", "Location":"Malaysia", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"2342234", "subName":"30, Jul 2013 09:24 AM", "Location":"India", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" } ],{origin:-1});

To control the order of the fields, pass a header option to sheet_add_json:

XLSX.utils.sheet_add_json(ws, [ ... ], {header: ["agentNo", "subName", "memberName", "certNo", "Gender", "dob"], origin:-1});

And to rewrite the column headers, if the objects don't have the same name as the titles you want, go back and rewrite a new header array in the fourth row (index 3):

XLSX.utils.sheet_add_aoa(ws, [["Agent no", "Subsidiary name", "Member Name", "Cert No", "Gender", "Date of Birth"]], {origin:3});

The new `sheet_add_json` function can add to an existing worksheet: ```js var ws = XLSX.utils.aoa_to_sheet([ ["Member Listing"], ["Company Name:", "<Master Policy Holder Name>"], ["Report Date:", "<DD/MM/YYYY>"] ]); XLSX.utils.sheet_add_json(ws, [ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM", "Location":"Singapore", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM", "Location":"Malaysia", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" }, { "agentNo":"2342234", "subName":"30, Jul 2013 09:24 AM", "Location":"India", "memberName":42, "certNo": "2342234", "Gender":"Male", "dob":"1 may 1987" } ],{origin:-1}); ``` To control the order of the fields, pass a header option to sheet_add_json: ```js XLSX.utils.sheet_add_json(ws, [ ... ], {header: ["agentNo", "subName", "memberName", "certNo", "Gender", "dob"], origin:-1}); ``` And to rewrite the column headers, if the objects don't have the same name as the titles you want, go back and rewrite a new header array in the fourth row (index 3): ```js XLSX.utils.sheet_add_aoa(ws, [["Agent no", "Subsidiary name", "Member Name", "Cert No", "Gender", "Date of Birth"]], {origin:3}); ```

👍 4 🎉 1

jomel commented

2018-01-10 14:07:28 +00:00

(Migrated from github.com)

@SheetJSDev OMG! Amazing! This makes it SO much easier. Thank you!

Sign in to join this conversation.