From json to sheet.

[roccomuso]

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]

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]

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]

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

[SheetJSDev]

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

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

[SheetJSDev]

@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;
}

[jomel]

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

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

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

[sharatbaradia]

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]

@koolhuman the official npm name is xlsx

[sharatbaradia]

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

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

[sharatbaradia]

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

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

[reviewher]

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

[sharatbaradia]

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

[reviewher]

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

[sharatbaradia]

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

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

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