Javascript 用 Node 解析 XLSX 并创建 json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30859901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-23 05:51:08 来源:igfitidea点击:
Parse XLSX with Node and create json
提问by Matthew Harwood
Ok so I found this really well documented node_modulecalled js-xlsx
好的，所以我发现这个node_module名为js-xlsx 的文档非常好
Question:How can I parse an xlsx to output json?
问题：如何解析 xlsx 以输出 json？
Here is what the excel sheet looks like:
这是excel表格的样子：
In the end the json should look like this:
最后 json 应该是这样的：
[
{
"id": 1,
"Headline": "Team: Sally Pearson",
"Location": "Austrailia",
"BodyText": "...",
"Media: "..."
},
{
"id": 2,
"Headline": "Team: Rebeca Andrade",
"Location": "Brazil",
"BodyText": "...",
"Media: "..."
}
]
index.js:
索引.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');
采纳答案by Parijat
Improved Version of "Josh Marinacci" answer , it will read beyond Z column (i.e. AA1).
“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);
});
回答by user3812377
You can also use
你也可以使用
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]]))
回答by Josh Marinacci
I think this code will do what you want. It stores the first row as a set of headers, then stores the rest in a data object which you can write to disk as JSON.
我认为这段代码会做你想做的。它将第一行存储为一组标题，然后将其余部分存储在一个数据对象中，您可以将其作为 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);
});
prints out
打印出来
[ { 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/' } ]
回答by godblessstrawberry
here's angular 5 method version of this with unminified syntax for those who struggling with that y, z, ttin accepted answer. usage: parseXlsx().subscribe((data)=> {...})
这里的这个角5方法版本unminified语法对于那些谁与挣扎y，z，tt在接受的答案。用法：parseXlsx().subscribe((data)=> {...})
parseXlsx() {
let self = this;
return Observable.create(observer => {
this.http.get('./assets/input.xlsx', { responseType: 'arraybuffer' }).subscribe((data: ArrayBuffer) => {
const XLSX = require('xlsx');
let file = new Uint8Array(data);
let workbook = XLSX.read(file, { type: 'array' });
let sheetNamesList = workbook.SheetNames;
let allLists = {};
sheetNamesList.forEach(function (sheetName) {
let worksheet = workbook.Sheets[sheetName];
let currentWorksheetHeaders: object = {};
let data: Array<any> = [];
for (let cellName in worksheet) {//cellNames example: !ref,!margins,A1,B1,C1
//skipping serviceCells !margins,!ref
if (cellName[0] === '!') {
continue
};
//parse colName, rowNumber, and getting cellValue
let numberPosition = self.getCellNumberPosition(cellName);
let colName = cellName.substring(0, numberPosition);
let rowNumber = parseInt(cellName.substring(numberPosition));
let cellValue = worksheet[cellName].w;// .w is XLSX property of parsed worksheet
//treating '-' cells as empty on Spot Indices worksheet
if (cellValue.trim() == "-") {
continue;
}
//storing header column names
if (rowNumber == 1 && cellValue) {
currentWorksheetHeaders[colName] = typeof (cellValue) == "string" ? cellValue.toCamelCase() : cellValue;
continue;
}
//creating empty object placeholder to store current row
if (!data[rowNumber]) {
data[rowNumber] = {}
};
//if header is date - for spot indices headers are dates
data[rowNumber][currentWorksheetHeaders[colName]] = cellValue;
}
//dropping first two empty rows
data.shift();
data.shift();
allLists[sheetName.toCamelCase()] = data;
});
this.parsed = allLists;
observer.next(allLists);
observer.complete();
})
});
}
回答by AN German
**podria ser algo asi en react y electron**
xslToJson = workbook => {
//var data = [];
var sheet_name_list = workbook.SheetNames[0];
return XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list], {
raw: false,
dateNF: "DD-MMM-YYYY",
header:1,
defval: ""
});
};
handleFile = (file /*:File*/) => {
/* Boilerplate to set up FileReader */
const reader = new FileReader();
const rABS = !!reader.readAsBinaryString;
reader.onload = e => {
/* Parse data */
const bstr = e.target.result;
const wb = XLSX.read(bstr, { type: rABS ? "binary" : "array" });
/* Get first worksheet */
let arr = this.xslToJson(wb);
console.log("arr ", arr)
var dataNueva = []
arr.forEach(data => {
console.log("data renaes ", data)
})
// this.setState({ DataEESSsend: dataNueva })
console.log("dataNueva ", dataNueva)
};
if (rABS) reader.readAsBinaryString(file);
else reader.readAsArrayBuffer(file);
};
handleChange = e => {
const files = e.target.files;
if (files && files[0]) {
this.handleFile(files[0]);
}
};
回答by mad Man
I found a better way of doing this
我找到了一个更好的方法来做到这一点
function genrateJSONEngine() {
var XLSX = require('xlsx');
var workbook = XLSX.readFile('test.xlsx');
var sheet_name_list = workbook.SheetNames;
sheet_name_list.forEach(function (y) {
var array = workbook.Sheets[y];
var first = array[0].join()
var headers = first.split(',');
var jsonData = [];
for (var i = 1, length = array.length; i < length; i++) {
var myRow = array[i].join();
var row = myRow.split(',');
var data = {};
for (var x = 0; x < row.length; x++) {
data[headers[x]] = row[x];
}
jsonData.push(data);
}
