Let's talk about the bidirectional conversion of JSON format and Excel format in Node.js

This article will show you how to use Node.js on the front end to convert JSON format to Excel files, and Excel format to JSON files. We will introduce the process of their conversion. I hope it will be helpful to everyone.

Usually, the data our boss/customer wants is displayed in an intuitive Excel format, but our front-end or back-end data is all in JSON format, so JSON -> Excel file format conversion is required; if we make a web page with a <table> component in it to implement a function: export to Excel/import to Excel, then we need to JSON Excel file format bidirectional conversion. [Recommended study: "<a href="//m.sbmmt.com/course/list/24.html" target="_blank" textvalue="nodejs 教程">nodejs Tutorial</a>"]<p>This is the work of back-end students. In fact, front-end students can also do it. The language required is <code>Node.js

Convert JSON to Excel file

Process

• Use fs module to read data And use the JSON.parse() method to convert the format

• Traverse the data and process it to obtain the Object

• Use the json2xls module to convert Object gets the variable and uses the fs module to write Excel

Example

ReferencenodeJS converts json data into excel (xlsx file) Example of output , with the help of its requirements: The data crawled by the website crawler is converted into an Excel file:

data.json

result.xlsx

Code

The npm packages that need to be introduced include fs, json2xls

const fs = require('fs')
const json2xls = require('json2xls');

fs.readFile('data.json','utf8',(err,data)=>{
  if (err) throw err;
  const json = JSON.parse(data);
  const jsonArray = [];
  json.forEach(function(item){
    let temp = {
      '类型' : item.type,
      '问题' : item.question,
      '选项' : item.answers,
      '答案' : item.trueAnswer
    }
    jsonArray.push(temp);
  });
  
  let xls = json2xls(jsonArray);
  
  fs.writeFileSync('result.xlsx', xls, 'binary');
})

Convert Excel to JSON file

Process

• Read Get local Excel files to variable temporary storage;

• Process variable data; (process according to respective needs)

• The processed data is written locally JSON file

Example

ReferenceNode.js excel to json article, with the help of its requirements: put data.xlsx file is converted into result.json

data.xlsx

Code

npm packages that need to be introduced include fs, node-xlsx, follow the above three steps

var xlsx = require("node-xlsx");
var fs = require('fs');
var list = xlsx.parse("raw-data.xlsx"); // 需要转换的excel文件

var data = list[0].data;  // 1.读取json数据到变量暂存
var len = data.length;
var outData_cn = {}; // 中文
var outData_us = {}; // 英文
for(let i = 0; i < len; i ++){  // 2. 数据处理
    let item = data[i];
    outData_cn[item[0]] = item[1];
    outData_us[item[0]] = item[2];
}
var outData = {
    cn: outData_cn,
    us: outData_us
}

fs.writeFile("result.json",JSON.stringify(outData),&#39;utf-8&#39;,complete);  //  3. 数据写入本地json文件
//           输出的json文件        数据          文件编码格式 完成事件
function complete(err) {
   if(!err) console.log("文件生成成功");
}

Introduction to Programming! !

The above is the detailed content of Let's talk about the bidirectional conversion of JSON format and Excel format in Node.js. For more information, please follow other related articles on the PHP Chinese website!