Using nodejs to realize, JSON file automatically transferred to excel tool

Time:2020-8-1

During this period of time, I need to send the documents in JSON format to the business personnel for translation. Every time I translate them manually, it’s very troublesome. So I want to write a highly qualified automation tool to complete this task.

In terms of implementation, the initial idea is to use a command like “json2 excel start”, and then it will run on its own. Like Vue, react to run the command.

First of all, weNPM init creates a new project project and the core file json2 of our project excel.jsAnd run node json2 exce.js And then the console can print things.
Using nodejs to realize, JSON file automatically transferred to excel tool

To convert a file into another file, we need to know the path of the file and the location to which it is saved. Therefore, the command is designed as follows:

json2excel start inpath outpath

We use a very useful command line helper package “commander” to prompt for command input, json2 excel.js As follows:,

const program = require('commander')

//Defines the current version
program
  .version(require('../package').version)

//Define command method
program
  .usage('<command> [inPath] [toPath]')
  
program
  .command('start [paths...]')
  .description('Conversion from JSON to csv')
  .alias('-s')
  .action(paths => require('./command/j2c')(paths))
  
program.parse(process.argv)

if (!program.args.length) {
  program.help()
}

Then run node json2 excel.js You will see that (the bin command has not been installed, so node json2excel is used instead of json2excel),
Using nodejs to realize, JSON file automatically transferred to excel tool

You can see the command boot prompt with an operation.
. command () defines the command and the parameters after it. We define paths
. description () is the description
. alias() is the alias of the command
. action() is the operation to be executed when running the command, and paths is the parameter passed from the command

When we create.. / command / J2C. JS,. Action(), we accept command parameters

module.exports = (paths) => {
    //In this way, the path of input and output is obtained
    let [inPath, outPath] = paths
}

What if the command parameters are not attached?
For example:node json2excel startNo path, then enter

Then we’ll guide the user to type again, using the “CO” and “co prompt” tools

Upper Code: / / command / j2c.js

const co = require('co')
const prompt = require('co-prompt')


module.exports = (paths) => {
    co(function* () {
        let [inPath, outPath] = paths
        //Processing user input
        inPath = inPath ? inPath : yield prompt('Input file directory: ')
        outPath = outPath ? outPath : (yield prompt('Output file directory: ')) || inPath
    })
}

The generator function is accepted in CO, which is mainly used to write asynchronous operations for synchronous processing.

functionnode json2excel start

Using nodejs to realize, JSON file automatically transferred to excel tool

In this way, we can ensure that we can get the path of input and output, full score of user experience, great.

next step,Get the JSON file through the input pathUsing the “glob” tool, get all the JSON files under the inpath path through regular matching

Standing on the shoulders of giants, you can get twice the result with half the effort. The code is as follows:
Using nodejs to realize, JSON file automatically transferred to excel tool

When we get the JSON file, we start to convert it to excel. CSV is a data structure as simple as JSON. We convert JSON to CSV format.

The following is a comparison between JSON format and CSV format. In this way, the conversion is not difficult. On the left is the JSON data format, and on the right is the string.
Using nodejs to realize, JSON file automatically transferred to excel tool

We use the package “json2csv”, and we can transform and splice ourselves when we have time.

Read the JSON file and convert it to SCV:

const Json2csvParser = require('json2csv').Parser

for(let filename in files) {
    //Read files synchronously
    let jsonData = fs.readFileSync(files[filename])
    jsonData = JSON.parse(jsonData)

    //Json2csv conversion
    const fields = Object.keys(jsonData[0])
    const json2csvParser = new Json2csvParser({fields})
    const csvData = json2csvParser.parse(jsonData)

    //File name written
    const outputFileName = `${outPath}/${filename}.csv`

    //Write to file
    const err = fs.writeFileSync(outputFileName, csvData)
    if(err) {
        return console.log(err)
    } else {
        console.log(`- ${filename}.json Conversion successful!`)
    }
}

Using nodejs to realize, JSON file automatically transferred to excel tool
After running, you can get a. CSV file, a simple implementation.

Detail optimization, and achieve:

  • In office will display garbled code, so to define the storage format of UTF-8.
//Office Excel needs BOM header to define UTF-8 encoding format
const BOM = Buffer.from('\uFEFF')
const csvData = Buffer.concat([BOM, Buffer.from(csvData)])
  • If the output path does not exist and the store will not succeed, you must first create a directory
//Create without folder
if(!fs.existsSync(outPath)) {
    fs.mkdirSync(outPath)
}
  • Data in JSON format can be in the form of object or array. If it is an object, it will be converted into an array
//If it is an object, each key value pair of the object is converted into an array item of 'key' and 'value'
let jsonData, fields
if(Object.prototype.toString.call(jsonData) === '[object Object]') {
    jsonData = Object.keys(jsonData).map(key => ({
      key: key,
      value: jsonData[key]
    }))
    fields = ['key', 'value']
}
if(Object.prototype.toString.call(jsonData) === '[object Array]') {
    jsonData = jsonData
    fields = Object.keys(jsonData[0])
}
  • Operation plus prompt, and output text color
//Use a very convenient tool chalk
const chalk = require('chalk')

//For example, prompt before starting conversion
console.log(chalk.green('Start Conversion: '))
  • Store successfully displays the path of the file store and exits the process
//Prompt output file directory
console.log(chalk.blue(`- Please go to check the file: ${chalk.underline(path.join(process.cwd(), outPath))}`))

//Exit process
process.exit()

The complete code is as follows:

'use strict'
const fs = require('fs')
const path = require('path')
const chalk = require('chalk')
const glob  = require('glob')
const co = require('co')
const prompt = require('co-prompt')
const Json2csvParser = require('json2csv').Parser

//How to get multiple files
const getMultiEntry = function (globPath) {
    let entries = {}

    glob.sync(globPath).forEach(function (entry) {
        const basename = path.basename(entry, path.extname(entry))
        entries[basename] = entry
    })

    return entries
}

module.exports = (paths) => {
    co(function* () {
        let [inPath, outPath] = paths
        //Processing user input
        inPath = inPath ? inPath : yield prompt('Input file directory: ')
        outPath = outPath ? outPath : (yield prompt('Output file directory: ')) || inPath

        //Traverse to get JSON file
        const files = getMultiEntry(`${inPath}/*.json`)

        //If there is no JSON file in the specified directory, the prompt message will be output and the process will exit
        if (!Object.keys(files).length) {
            console.log(chalk.red('\n x There is no JSON file in the specified folder'))
            process.exit()
        }

        //Start converting files
        console.log('\n ')
        console.log(chalk.green('Start Conversion: '))

        for(let filename in files) {
            //Read files synchronously
            let jsonData = fs.readFileSync(files[filename])
            jsonData = JSON.parse(jsonData)

            /*
            *Judging the data structure acceptable to CSV
            *If it is a JSON object, key and value are taken as columns
            *If it is a JSON array, all keys in the first row are read
            * */
            let jData, fields
            if(Object.prototype.toString.call(jsonData) === '[object Object]') {
                jData = Object.keys(jsonData).map(key => ({
                    key: key,
                    value: jsonData[key]
                }))
                fields = ['key', 'value']
            }
            if(Object.prototype.toString.call(jsonData) === '[object Array]') {
                jData = jsonData
                fields = Object.keys(jsonData[0])
            }

            //JSON format = > CSV format
            const json2csvParser = new Json2csvParser({fields})
            const csvData = json2csvParser.parse(jData)

            //Office Excel needs BOM header to define UTF-8 encoding format
            const BOM = Buffer.from('\uFEFF')
            const bomCsv = Buffer.concat([BOM, Buffer.from(csvData)])

            //File name written
            const outputFileName = `${outPath}/${filename}.csv`

            //Create without folder
            if(!fs.existsSync(outPath)) {
                fs.mkdirSync(outPath)
            }

            //Write to file
            const err = fs.writeFileSync(outputFileName, bomCsv)
            if(err) {
                return console.log(err)
            } else {
                console.log(chalk.green(`- ${filename}.json Conversion successful!`))
            }
        }

        //Prompt output file directory
        console.log('\n ')
        console.log(chalk.blue(`- Please go to check the file: ${chalk.underline(path.join(process.cwd(), outPath))}`))
        process.exit()
    })
}

After that, the command is used. How to install the command?
package.json Bin command

Create a bin directory in the root directory of the project, package.json Define bin commands
Using nodejs to realize, JSON file automatically transferred to excel tool

In bin / json2 excel.js Write at the beginning of the file#!/usr/bin/env node
Using nodejs to realize, JSON file automatically transferred to excel tool

When the project package is installed, NPM will be in the/node_modules/.binInstall a bin command inside, so you can use the json2excel command, executejson2excel start *

If it is a global installation, it can be used anywhere.

So far, a JSON to CSV tool is perfect.


We convert JSON to CSV. If we get the CSV, how can we restore it to JSON?

Modify commandjson2excel start [paths]

Convert JSON to CSV and rename tojson2excel j2c [paths]
Add the command to convert CSV to JSONjson2excel c2j [paths]

The conversion from CSV to JSON is similar to the previous implementation, except for the difference in format conversion (using the “csvtojson” tool), which is no longer written here.
See the complete code https://github.com/zwzou/json2excel#readme

At this point, a complete JSON – Excel (CSV) format conversion tool is completed. Expect to expand other excel formats in the future

Recommended Today

ASP.NET Example of core MVC getting the parameters of the request

preface An HTTP request is a standard IO operation. The request is I, which is the input; the responsive o is the output. Any web development framework is actually doing these two things Accept the request and parse to get the parameters Render according to the parameters and output the response content So let’s learn […]