From json to sheet. #610

New Issue

2017-03-29T22:37:01Z

roccomuso commented

2017-03-29 22:37:01 +00:00

(Migrated from github.com)

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

Some quick questions:

How to iterate on the sheet's rows? Right now i'm using the sheet_to_json utility...
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)

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)

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)

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)

@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)

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)

@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)

@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)

@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)

@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)

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)

@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)

@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)

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)

@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)

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)

@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)

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)

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)

@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)

@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)

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

Sign in to join this conversation.