From json to sheet. #610

New Issue

Closed

opened 2017-03-29 22:37:01 +00:00 by roccomuso · 21 comments

roccomuso commented 2017-03-29 22:37:01 +00:00 (Migrated from github.com)

Copy Link

Hi guys,
thanks for the wonderful work on this module.

Some quick questions:

1. How to iterate on the sheet's rows? Right now i'm using the sheet_to_json utility...
2. Is there a way to get a sheet from the JSON? Like a json_to_sheet utility. To be able to write back the changed xls to the file.

Thanks

Hi guys, thanks for the wonderful work on this module. Some quick questions: 1. How to iterate on the sheet's rows? Right now i'm using the `sheet_to_json` utility... 2. Is there a way to get a sheet from the JSON? Like a `json_to_sheet` utility. To be able to write back the changed xls to the file. Thanks

reviewher commented 2017-03-29 22:42:58 +00:00 (Migrated from github.com)

Copy Link

If you pass header:1 and raw:true to sheet_to_json, you get an array of arrays of the original values. You can iterate across those fairly easily. It's explained in the docs: https://sheetjs.gitbooks.io/docs/#json

To convert the array of arrays back to a worksheet, you can use the aoa_to_sheet utility. It's explained in the docs: https://sheetjs.gitbooks.io/docs/#array-of-arrays-input.

Please skim those sections and let us know how we can improve them.

If you pass `header:1` and `raw:true` to `sheet_to_json`, you get an array of arrays of the original values. You can iterate across those fairly easily. It's explained in the docs: https://sheetjs.gitbooks.io/docs/#json To convert the array of arrays back to a worksheet, you can use the `aoa_to_sheet` utility. It's explained in the docs: https://sheetjs.gitbooks.io/docs/#array-of-arrays-input. Please skim those sections and let us know how we can improve them.

roccomuso commented 2017-03-29 22:58:44 +00:00 (Migrated from github.com)

Copy Link

Thanks for your reply!
As first thing I'd say, highlight the documentation, I didn't even see that documentation! Is it even linked to the README?

Thanks for your reply! As first thing I'd say, highlight the documentation, I didn't even see that documentation! Is it even linked to the README?

reviewher commented 2017-03-29 23:01:41 +00:00 (Migrated from github.com)

Copy Link

It's actually all in the readme, the gitbook links are basically re-rendering the README.

https://github.com/SheetJS/js-xlsx#json

https://github.com/SheetJS/js-xlsx#array-of-arrays-input

These are pretty undiscoverable because the README is huge. I like the gitbook style with the links on the left sidebar

It's actually all in the readme, the gitbook links are basically re-rendering the README. https://github.com/SheetJS/js-xlsx#json https://github.com/SheetJS/js-xlsx#array-of-arrays-input These are pretty undiscoverable because the README is huge. I like the gitbook style with the links on the left sidebar

👍 4

SheetJSDev commented 2017-03-30 03:38:59 +00:00 (Migrated from github.com)

Copy Link

@roccomuso this was as good a time as any to play with the RunKit service. Here's a little notebook that demonstrates how to change data using the JSON form: https://runkit.com/sheetjs/58dc6ea1eae8b800146359eb

@roccomuso this was as good a time as any to play with the RunKit service. Here's a little notebook that demonstrates how to change data using the JSON form: https://runkit.com/sheetjs/58dc6ea1eae8b800146359eb

roccomuso commented 2017-03-30 15:42:56 +00:00 (Migrated from github.com)

Copy Link

Btw a json_to_sheet utility wouldn't be bad for those who are more familiar with JSON.

Btw a `json_to_sheet` utility wouldn't be bad for those who are more familiar with JSON.

SheetJSDev commented 2017-03-30 16:07:38 +00:00 (Migrated from github.com)

Copy Link

@roccomuso Here's a simple function, we should add a more refined version of this to the library in the next release:

function json_to_sheet(js/*:Array<any>*/, opts)/*:Worksheet*/ {
	var o = opts || {};
	var ws = ({}/*:any*/);
	var range/*:Range*/ = ({s: {c:0, r:0}, e: {c:0, r:js.length}}/*:any*/);
	var hdr = o.header || [], C = 0;

	for(var R = 0; R != js.length; ++R) {
		Object.keys(js[R]).filter(function(x) { return js[R].hasOwnProperty(x); }).forEach(function(k) {
			if((C=hdr.indexOf(k)) == -1) hdr[C=hdr.length] = k;
			var v = js[R][k];
			var t = 'z';
			if(typeof v == 'number') t = 'n';
			else if(typeof v == 'boolean') t = 'b';
			ws[XLSX.utils.encode_cell({c:C,r:R+1})] = {t:t, v:v};
		});
	}
	range.e.c = hdr.length - 1;
	for(C = 0; C < hdr.length; ++C) ws[XLSX.utils.encode_col(C) + "1"] = {t:'s', v:hdr[C]};
	ws['!ref'] = XLSX.utils.encode_range(range);
	return ws;
}

@roccomuso Here's a simple function, we should add a more refined version of this to the library in the next release: ```js function json_to_sheet(js/*:Array<any>*/, opts)/*:Worksheet*/ { var o = opts || {}; var ws = ({}/*:any*/); var range/*:Range*/ = ({s: {c:0, r:0}, e: {c:0, r:js.length}}/*:any*/); var hdr = o.header || [], C = 0; for(var R = 0; R != js.length; ++R) { Object.keys(js[R]).filter(function(x) { return js[R].hasOwnProperty(x); }).forEach(function(k) { if((C=hdr.indexOf(k)) == -1) hdr[C=hdr.length] = k; var v = js[R][k]; var t = 'z'; if(typeof v == 'number') t = 'n'; else if(typeof v == 'boolean') t = 'b'; ws[XLSX.utils.encode_cell({c:C,r:R+1})] = {t:t, v:v}; }); } range.e.c = hdr.length - 1; for(C = 0; C < hdr.length; ++C) ws[XLSX.utils.encode_col(C) + "1"] = {t:'s', v:hdr[C]}; ws['!ref'] = XLSX.utils.encode_range(range); return ws; } ```

👍 4

jomel commented 2017-05-11 08:20:08 +00:00 (Migrated from github.com)

Copy Link

@SheetJSDev Thank you!! This is EXACTLY what I needed. I am looking forward to finding it in a future release!
In my case, I wanted to convert a result of a mongoDB query ( an array of objects) to excel, and this function does exactly that :)

@SheetJSDev Thank you!! This is EXACTLY what I needed. I am looking forward to finding it in a future release! In my case, I wanted to convert a result of a mongoDB query ( an array of objects) to excel, and this function does exactly that :)

samuelkavin commented 2017-05-25 08:40:29 +00:00 (Migrated from github.com)

Copy Link

@SheetJSDev Can you share sample code to generate json to xlsx with this sample data?
[ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM" } ]

I'm stucked with this part

@SheetJSDev Can you share sample code to generate json to xlsx with this sample data? `[ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM" } ]` I'm stucked with this part

jomel commented 2017-05-26 10:13:05 +00:00 (Migrated from github.com)

Copy Link

@samuelkavin
I use something like this to create a xlsx file from json and send it as a response to a web request:

const XLSX = require('xlsx');
data = [ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM" } ];

/* create workbook & set props*/
   const wb = { SheetNames: [], Sheets: {} };
   wb.Props = {
      Title: "Stats from app",
      Author: "John Doe"
   };

/*create sheet data & add to workbook*/
var ws = XLSX.utils.json_to_sheet(data);
var ws_name = "DataSheet 1";
XLSX.utils.book_append_sheet(wb, ws, ws_name);

/* create file 'in memory' */
var wbout = new Buffer(XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' }));

/* send it by web request - where app is express()*/
app.get('/api/jobs/download', (req, res) => {
   var filename = "myDataFile.xlsx";
   res.setHeader('Content-Disposition', 'attachment; filename=' + filename);
   res.type('application/octet-stream');
   res.send(wbout);
}

@samuelkavin I use something like this to create a xlsx file from json and send it as a response to a web request: ``` const XLSX = require('xlsx'); data = [ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM" } ]; /* create workbook & set props*/ const wb = { SheetNames: [], Sheets: {} }; wb.Props = { Title: "Stats from app", Author: "John Doe" }; /*create sheet data & add to workbook*/ var ws = XLSX.utils.json_to_sheet(data); var ws_name = "DataSheet 1"; XLSX.utils.book_append_sheet(wb, ws, ws_name); /* create file 'in memory' */ var wbout = new Buffer(XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' })); /* send it by web request - where app is express()*/ app.get('/api/jobs/download', (req, res) => { var filename = "myDataFile.xlsx"; res.setHeader('Content-Disposition', 'attachment; filename=' + filename); res.type('application/octet-stream'); res.send(wbout); } ```

👍 15 ❤️ 5 🎉 1

sharatbaradia commented 2017-11-16 18:54:15 +00:00 (Migrated from github.com)

Copy Link

how to use json_to_sheet for angular2? when i install the package npm js-xlsx, in the dist folder json_to_sheet function is not present in any file xlsx.full.min.js, xlsx.core.min.js

how to use json_to_sheet for angular2? when i install the package npm js-xlsx, in the dist folder json_to_sheet function is not present in any file xlsx.full.min.js, xlsx.core.min.js

reviewher commented 2017-11-16 21:35:11 +00:00 (Migrated from github.com)

Copy Link

@koolhuman the official npm name is xlsx

@koolhuman the official npm name is [`xlsx`](https://www.npmjs.com/package/xlsx)

sharatbaradia commented 2017-11-16 22:32:47 +00:00 (Migrated from github.com)

Copy Link

@reviewher do i need to import any typings for xlsx? I installed xlsx package version 0.11.9, but it does not compile and shows error-

error TS2339: Property 'json_to_sheet' does not exist on type 'IUtils'.

I imported the package in my component.
import * as XLSX from 'xlsx';

public exportAsExcelFile(json: any[], excelFileName: string): void {
const worksheet: any = XLSX.utils.json_to_sheet(json);
const workbook: any = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'binary' });
this.saveAsExcelFile(excelBuffer, excelFileName);
}

Cannot resolve symbol utils.

@reviewher do i need to import any typings for xlsx? I installed xlsx package version 0.11.9, but it does not compile and shows error- error TS2339: Property 'json_to_sheet' does not exist on type 'IUtils'. I imported the package in my component. import * as XLSX from 'xlsx'; public exportAsExcelFile(json: any[], excelFileName: string): void { const worksheet: any = XLSX.utils.json_to_sheet(json); const workbook: any = { Sheets: { 'data': worksheet }, SheetNames: ['data'] }; const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'binary' }); this.saveAsExcelFile(excelBuffer, excelFileName); } Cannot resolve symbol utils.

reviewher commented 2017-11-17 14:51:29 +00:00 (Migrated from github.com)

Copy Link

The typings are included with the library now. Remove @types/xlsx and ts-xlsx if you have those installed

The typings are included with the library now. Remove `@types/xlsx` and `ts-xlsx` if you have those installed

sharatbaradia commented 2017-11-17 18:42:04 +00:00 (Migrated from github.com)

Copy Link

@reviewher - I removed @types/xlsx and I did not had ts-xlsx installed. After removing now the compile error shows
-error TS7016: Could not find a declaration file for module 'xlsx'.

How do i import xlsx module in my angular 2 component?

Currently my import looks like - import * as XLSX from 'xlsx';
And then i use the XLSX in function as below.

public exportAsExcelFile(json: any[], excelFileName: string): void {
        const worksheet: any = XLSX.utils.json_to_sheet(json);
        const workbook: any = { Sheets: { 'data': worksheet }, SheetNames: ['data'] };
        const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'binary' });
        this.saveAsExcelFile(excelBuffer, excelFileName);
    }

@reviewher - I removed `@types/xlsx` and I did not had `ts-xlsx` installed. After removing now the compile error shows -error TS7016: Could not find a declaration file for module 'xlsx'. How do i import xlsx module in my angular 2 component? Currently my import looks like - `import * as XLSX from 'xlsx'`; And then i use the XLSX in function as below. ``` public exportAsExcelFile(json: any[], excelFileName: string): void { const worksheet: any = XLSX.utils.json_to_sheet(json); const workbook: any = { Sheets: { 'data': worksheet }, SheetNames: ['data'] }; const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'binary' }); this.saveAsExcelFile(excelBuffer, excelFileName); } ```

sharatbaradia commented 2017-11-17 18:46:31 +00:00 (Migrated from github.com)

Copy Link

After changing import * as XLSX from 'xlsx'; to const XLSX = require('xlsx'); Everything worked fine. Thanks again @reviewher for helping me out.

After changing `import * as XLSX from 'xlsx';` to `const XLSX = require('xlsx');` Everything worked fine. Thanks again @reviewher for helping me out.

reviewher commented 2017-11-17 18:54:10 +00:00 (Migrated from github.com)

Copy Link

@koolhuman The angular 2+ demo in demos/angular2 uses the import * as XLSX from 'xlsx' form.

I just checked and it still seems to work. To verify on angular 2:

$ git clone https://github.com/SheetJS/js-xlsx/
$ cd js-xlsx
$ npm install
$ cd demos/angular2
$ cp package.json-angular2 package.json
$ npm install
$ npm install xlsx
$ ng build
$ ng serve

Can you check if that works for you?

@koolhuman The [angular 2+ demo in `demos/angular2`](https://github.com/SheetJS/js-xlsx/blob/master/demos/angular2/src/app/sheetjs.component.ts#L5) uses the `import * as XLSX from 'xlsx'` form. I just checked and it still seems to work. To verify on angular 2: ```bash $ git clone https://github.com/SheetJS/js-xlsx/ $ cd js-xlsx $ npm install $ cd demos/angular2 $ cp package.json-angular2 package.json $ npm install $ npm install xlsx $ ng build $ ng serve ``` Can you check if that works for you?

sharatbaradia commented 2017-11-17 20:06:06 +00:00 (Migrated from github.com)

Copy Link

I am using Angular 4.0 and webpack. should that be an issue?

I am using Angular 4.0 and webpack. should that be an issue?

reviewher commented 2017-11-17 20:51:28 +00:00 (Migrated from github.com)

Copy Link

Repeat the same steps above except use the angular4 configuration:

$ cp package.json-angular4 package.json

The demo uses the angular CLI to generate a new project. Ejecting generates a web pack config!

$ ng eject
$ npm install
$ npm run build

Here is the generated webpack.config.js. Compare this webpack config as well as the tsconfig.json to see if you have any different options

Repeat the same steps above except use the angular4 configuration: ``` bash $ cp package.json-angular4 package.json ``` The demo uses the angular CLI to generate a new project. Ejecting generates a web pack config! ```bash $ ng eject $ npm install $ npm run build ``` [Here is the generated `webpack.config.js`](https://gist.github.com/reviewher/e7fe973fa90b2e3f9f120f5f9d6a3e9b). Compare this webpack config as well as the [`tsconfig.json`](https://github.com/SheetJS/js-xlsx/blob/master/demos/angular2/tsconfig.json) to see if you have any different options

sharatbaradia commented 2017-11-17 21:33:32 +00:00 (Migrated from github.com)

Copy Link

@reviewher - I can see in below link, there is some configuration done for webpack, do you think i might be missing something like that.

https://github.com/SheetJS/js-xlsx/tree/master/demos/webpack#reflecting-the-xlsx-variable

@reviewher - I can see in below link, there is some configuration done for webpack, do you think i might be missing something like that. https://github.com/SheetJS/js-xlsx/tree/master/demos/webpack#reflecting-the-xlsx-variable

sharatbaradia commented 2017-11-17 22:15:33 +00:00 (Migrated from github.com)

Copy Link

@reviewher - Thanks again, after changing my tsconfig.js file to the one you referred in your post the application compiles fine. Now i am able to use import * as XLSX from 'xlsx';

My earlier tsconfig.js looked like below

{
  "compileOnSave": false,
  "bundleOnSave":  false,
  "compilerOptions": {
    "target": "es5",
    "module": "commonjs",
    "types" : ["node"],
    "moduleResolution": "node",
    "sourceMap": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "lib": [ "es2016", "dom" ],
    "noImplicitAny": true,
    "suppressImplicitAnyIndexErrors": true,
    "skipLibCheck": true
  }

}

Changed to

{
  "compileOnSave": false,
  "compilerOptions": {
    "outDir": "./dist/out-tsc",
    "baseUrl": "src",
    "sourceMap": true,
    "declaration": false,
    "moduleResolution": "node",
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "skipLibCheck": true,
    "target": "es5",
    "typeRoots": [
      "node_modules/@types"
    ],
    "lib": [
      "es2016",
      "dom"
    ]
  }
}

@reviewher - Thanks again, after changing my tsconfig.js file to the one you referred in your post the application compiles fine. Now i am able to use `import * as XLSX from 'xlsx';` My earlier tsconfig.js looked like below ``` { "compileOnSave": false, "bundleOnSave": false, "compilerOptions": { "target": "es5", "module": "commonjs", "types" : ["node"], "moduleResolution": "node", "sourceMap": true, "emitDecoratorMetadata": true, "experimentalDecorators": true, "lib": [ "es2016", "dom" ], "noImplicitAny": true, "suppressImplicitAnyIndexErrors": true, "skipLibCheck": true } } ``` Changed to ``` { "compileOnSave": false, "compilerOptions": { "outDir": "./dist/out-tsc", "baseUrl": "src", "sourceMap": true, "declaration": false, "moduleResolution": "node", "emitDecoratorMetadata": true, "experimentalDecorators": true, "skipLibCheck": true, "target": "es5", "typeRoots": [ "node_modules/@types" ], "lib": [ "es2016", "dom" ] } } ```

uiforchange commented 2018-07-04 11:22:23 +00:00 (Migrated from github.com)

Copy Link

Is it possible to convert json from the excel which should be parse with column wise?

Is it possible to convert json from the excel which should be parse with column wise?

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

master

sankhavaramsaitulasiram/feat-fix-2752

maybeswapnil/issue2737

gh-pages

scottysseus/2560_2

ivan-trusov/fix-basedate

nandanv2702/issue_1300

protobi/master

ThomasChan/master

grantfayvor/master

tom-groves/bug-1105/rounding-error

mgreter/master

v0.20.3

v0.20.2

v0.20.1

v0.20.0

v0.19.3

v0.19.2

v0.19.1

v0.19.0

v0.18.12

v0.18.11

v0.18.10

v0.18.9

v0.18.8

v0.18.7

v0.18.5

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0

v0.17.5

v0.17.4

v0.17.3

v0.17.2

v0.17.1

v0.17.0

v0.16.8

v0.16.7

v0.16.6

v0.16.5

v0.16.3

v0.16.2

v0.16.1

v0.16.0

v0.15.6

v0.15.5

v0.15.2

v0.14.0

v0.13.5

v0.13.4

v0.13.3

v0.13.1

v0.13.0

v0.12.13

v0.12.12

v0.12.11

v0.12.10

v0.12.9

v0.12.8

v0.12.7

v0.12.6

v0.12.5

v0.12.4

v0.12.3

v0.12.2

v0.12.1

v0.12.0

v0.11.19

v0.11.18

v0.11.17

v0.11.16

v0.11.15

v0.11.14

v0.11.13

v0.11.12

v0.11.11

v0.11.10

v0.11.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

v0.11.1

v0.11.0

v0.10.9

v0.10.8

v0.10.7

v0.10.6

v0.10.5

v0.10.4

v0.10.3

v0.10.2

v0.10.1

v0.10.0

v0.9.13

v0.9.12

v0.9.11

v0.9.10

v0.9.9

v0.9.8

v0.9.7

v0.9.6

v0.9.5

v0.9.4

v0.9.3

v0.9.2

v0.9.1

v0.9.0

v0.8.8

v0.8.7

v0.8.6

v0.8.5

v0.8.4

v0.8.3

v0.8.2

v0.8.1

v0.8.0

v0.7.11

v0.7.10

v0.7.9

v0.7.7

v0.7.6-i

v0.7.6-h

v0.7.6-a

v0.7.6

v0.7.5

v0.7.4

v0.7.3

v0.7.2

v0.7.1

v0.5.10

v0.5.9

v0.5.8

v0.5.7

v0.5.0

v0.4.3

Labels

Clear labels   

DBF

  

Dates

  

Defined Names

  

Features

  

Formula

  

HTML

  

Images

  

Infrastructure

  

Integration

  

International

  

ODS

  

Operations

  

Performance

  

PivotTables

  

Pro

  

Protection

  

Read Bug

  

SSF

  

SYLK

  

Style

  

Write Bug

  

good first issue

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#610

No description provided.