用NodeparsingXLSX并创buildjson

好吧,所以我发现这个真正有据可查的node_module叫做js-xlsx

问题:如何parsingxlsx来输出json

以下是Excel工作表的样子:

在这里输入图像描述

最后,json应该是这样的:

 [ { "id": 1, "Headline": "Team: Sally Pearson", "Location": "Austrailia", "BodyText": "...", "Media: "..." }, { "id": 2, "Headline": "Team: Rebeca Andrade", "Location": "Brazil", "BodyText": "...", "Media: "..." } ] 

index.js:

 if(typeof require !== 'undefined') { console.log('hey'); XLSX = require('xlsx'); } var workbook = XLSX.readFile('./assets/visa.xlsx'); var sheet_name_list = workbook.SheetNames; sheet_name_list.forEach(function(y) { /* iterate through sheets */ var worksheet = workbook.Sheets[y]; for (z in worksheet) { /* all keys that do not begin with "!" correspond to cell addresses */ if(z[0] === '!') continue; // console.log(y + "!" + z + "=" + JSON.stringify(worksheet[z].v)); } }); XLSX.writeFile(workbook, 'out.xlsx'); 

       

网上收集的解决方案 "用NodeparsingXLSX并创buildjson"

“Josh Marinacci”的改进版本的答案,它将读取超出Z列(即AA1)。

 var XLSX = require('xlsx'); var workbook = XLSX.readFile('test.xlsx'); var sheet_name_list = workbook.SheetNames; sheet_name_list.forEach(function(y) { var worksheet = workbook.Sheets[y]; var headers = {}; var data = []; for(z in worksheet) { if(z[0] === '!') continue; //parse out the column, row, and value var tt = 0; for (var i = 0; i < z.length; i++) { if (!isNaN(z[i])) { tt = i; break; } }; var col = z.substring(0,tt); var row = parseInt(z.substring(tt)); var value = worksheet[z].v; //store header names if(row == 1 && value) { headers[col] = value; continue; } if(!data[row]) data[row]={}; data[row][headers[col]] = value; } //drop those first two rows which are empty data.shift(); data.shift(); console.log(data); }); 

你也可以使用

 var XLSX = require('xlsx'); var workbook = XLSX.readFile('Master.xlsx'); var sheet_name_list = workbook.SheetNames; console.log(XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]])) 

我认为这个代码会做你想做的。 它将第一行存储为一组标题,然后将其余的存储在数据对象中,您可以将其作为JSON写入磁盘。

 var XLSX = require('xlsx'); var workbook = XLSX.readFile('test.xlsx'); var sheet_name_list = workbook.SheetNames; sheet_name_list.forEach(function(y) { var worksheet = workbook.Sheets[y]; var headers = {}; var data = []; for(z in worksheet) { if(z[0] === '!') continue; //parse out the column, row, and value var col = z.substring(0,1); var row = parseInt(z.substring(1)); var value = worksheet[z].v; //store header names if(row == 1) { headers[col] = value; continue; } if(!data[row]) data[row]={}; data[row][headers[col]] = value; } //drop those first two rows which are empty data.shift(); data.shift(); console.log(data); }); 

打印出来

 [ { id: 1, headline: 'team: sally pearson', location: 'Australia', 'body text': 'majority have…', media: 'http://www.youtube.com/foo' }, { id: 2, headline: 'Team: rebecca', location: 'Brazil', 'body text': 'it is a long established…', media: 'http://s2.image.foo/' } ]