[Node.js] Write a script that automatically organizes data into tables

foreword

After the data is buried in the enterprise project, the buried event names need to be sorted into an Excel table for easy statistics. The goal is to convert the data on the left side of the figure below into the Excel table on the right side of the figure below:

Considering that the buried point data on the left increases with the iteration of the project, every time the buried point data increases, I have to copy and paste the data one by one with Ctrl+C/V into the Excel table.

I'm lazy and don't want to play like this, so I wrote a script that automatically organizes it into a table for me.

Script implementation

Implementation process

  • Node.js generates Excel table tool library technology selection
  • Copy a copy of the buried point data separately to ensure that its changes will not affect the business-related buried point logic
  • Organize buried point data into the data structure we need

in three steps

Technology selection

The Node.js tool library for manipulating Excel tables includes:

Just list the above four.

The selected angles are as follows:

  • The learning cost is low, and the document API is simple and easy to use. It only needs to generate tables, and other functions are not needed, so the simpler the API, the better.
  • The data structure that needs to be provided to generate an Excel table is simple and easy to implement
  • Can export xlsx table to meet the most basic requirements

node-xlsx It is the closest to the above requirements, and it is preferred to use it.

node-xlsx The code block given by the official generated Excel table:

import xlsx from 'node-xlsx';
// Or var xlsx = require('node-xlsx').default;

const data = [
  [1, 2, 3],
  [true, false, null, 'sheetjs'],
  ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'],
  ['baz', null, 'qux'],
];
var buffer = xlsx.build([{name: 'mySheetName', data: data}]); // Returns a buffer

Generate table data data is a two-dimensional array, corresponding to the rows and columns of the table. data.length is the number of rows in the table, data[0].length is the number of columns in the table; data[0][0] corresponds to the value of the first row and first column of the table, data[0][1] corresponds to the value of the table The value of the first row and second column of the .

Therefore, it is enough to arrange the buried point data into a two-dimensional array, and the data structure arrangement of the two-dimensional array is easy to implement.

Copy buried point data

The buried point data is uniformly placed in the buryData.js file, but it cannot be changed at will, so copy the file separately.

buryData.js

export default {
    version1: 'v1.5.3',
    bury1: 'ding remind',
    bury2: 'approval-filter',
    bury3: 'Task-Click on the task title to open the task details',
    bury4: 'Task details popup-Click for details tab',
    bury5: 'Task details popup-Click Logging tab',
    bury6: 'Task details popup-Click on work summary tab',
    bury7: 'Task details popup-click dynamic tab',
    //...
}

The file copied from buryData.js is named bury.js, and there is another problem: bury.js needs to execute it to get the data object it exports, and the exported data uses ES6 modular syntax. Here, ES6 modularization needs to be transformed into CommonJs modularization can be done by replacing export default {} with module.exports ={}.

Node.js fs module + regular replacement can achieve the above goals, but in order to be faster, I choose to use the tool library magic-string

magic-string It is an operation string library, which can help me remove the steps of writing regular replacement strings.

const path = require('path');
const magicString = require('magic-string')
const fs = require('fs');

//buryData.js file path
const buryFile = path.join(__dirname, '../src/lib/buryData.js')

const getBuryContent = (filePath) => {
    const content = fs.readFileSync(filePath, 'utf8')
    //Replace export default with module.exports =
    const s = new magicString(content)
    s.replace('export default', 'module.exports = ')
    return s.toString()
}

(async () => {
    const str = getBuryContent(buryFile)
    //Write the replaced content to the bury.js file
    const copyFilePath = path.join(__dirname, '/bury.js')
    fs.writeFileSync(copyFilePath, str)
    //Dynamically import bury.js to get buried point data
    const { default: data } = await import(copyFilePath)
})()

Generate a 2D array

mentioned above, node-xlsx Generating a table requires organizing the data into a two-dimensional array first.

export default {
    version1: 'v1.5.3',
    bury1: 'ding remind',
    /...
    version2: 'v1.5.4',
    bury21: 'Contact list',
    //..
}

The above data is organized into:

[
  ['v1.5.3','v1.5.4'],
  ['ding remind','Contact list'],
  //...
]

First, store all the data in a Map object. Because the buried point data is an object, where version1 and version2 represent the version number, the version number will increase with the iteration of the project, version3, version4... Divide the Map value by version.

const _ = require('lodash');
//...

const getFormatDataMap = (data) => {
    let version
    const map = new Map();
    _.forIn(data, (value, key) => {
        if (key.includes('version')) {
            version = value
            !map.has(version) && map.set(version, [value])
            return
        }
        const mapValue = map.get(version)
        mapValue.push(value)
    })
    return map
}

(async () => {
    const str = getBuryContent(buryFile)
    const copyFilePath = path.join(__dirname, '/bury.js')
    fs.writeFileSync(copyFilePath, str)
    const { default: data } = await import(copyFilePath)
    
    //add
    const map = getFormatDataMap(data)
})()

After the getFormatDataMap function is executed, the returned data is:

{
'v1.5.3'=>['v1.5.3','ding remind' //...]
'v1.5.4'=>['v1.5.4','Contact list' //...]
}


Then, you need to know the maximum number of rows in the table, the number of table columns is map.size(), the maximum number of rows is obtained by obtaining Map.values() to obtain all the values ​​values, and traversing the values ​​to obtain the length and length of each array stored in the values Uniformly use another array lens to temporarily record, and compare the values ​​in the lens to get the maximum value after the traversal.

MAX_LEN is the maximum number of rows in the table, and it is also the value with the maximum length among all the arrays stored in values.

const _ = require('lodash');
//...
const getMergeArr = (map) => {
    const values = _.toArray(map.values())
    const lens = []
    //Get the length, the length value is uniformly stored in the lens array
    values.forEach((value) => { lens.push(value.length) })
    //Compare
    const MAX_LEN = _.max(lens)
    
    return getTargetItems({ mapValue: values, forNum: MAX_LEN })
}

(async () => {
    const str = getBuryContent(buryFile)
    const copyFilePath = path.join(__dirname, '/bury.js')
    fs.writeFileSync(copyFilePath, str)
    const { default: data } = await import(copyFilePath)
    const map = getFormatDataMap(data)
    //add
    const table = getMergeArr(map)
})()

Finally, double loop with values, MAX_LEN. The number of table columns map.size() can be obtained, but for the convenience of direct mapValue.length, the two are equal.

With the number of table columns, you can create the second layer array of the two-dimensional array. new Array(len).fill(' ') the length of the second layer array is mapValue.length. When creating, the values ​​in the array are first uniformly filled for' '.

const getTargetItems = ({ mapValue, forNum }) => {
    const len = mapValue.length
    const targetItems = []
    mapValue.forEach((v, i) => {
        for (let index = 0; index < forNum; index++) {
            const element = v[index];
            let targetItem = targetItems[index]
            if (!targetItem) {
            //To create an array, the values ​​are first uniformly filled with ' '
                targetItem = new Array(len).fill(' ')
            }
            /**
            If the current index is greater than the length of the array v, then the obtained value v[index] is undefined.
            If it is undefined, skip directly and keep targetItem[i] as ' '
            */
            targetItem[i] = element ? element : ' '
            targetItems[index] = targetItem
        }
    })
    return targetItems
}

Complete the transformation of the two-dimensional array, the data structure is as follows:

generate form

The data has been completed, and what is left is to write the data to generate the table and copy it directly node-xlsx Demo code down.

//...

(async () => {
    const str = getBuryContent(buryFile)
    const copyFilePath = path.join(__dirname, '/bury.js')
    fs.writeFileSync(copyFilePath, str)
    const { default: data } = await import(copyFilePath)
    const map = getFormatDataMap(data)
    const table = getMergeArr(map)

    //Write data, generate tables, and return buffer data
    const buffer = xlsx.build([{ name: 'Buried', data: table }])
    
    const outPath = path.join(__dirname, '/bury.xlsx')
    
    //The bury.js file can be deleted. If bury.xlsx already exists, delete it first
    fs.existsSync(outPath) && fs.unlinkSync(outPath)
    fs.existsSync(copyFilePath) && fs.unlinkSync(copyFilePath)
    
    //Create a bury.xlsx file and write the obtained buffer into
    fs.writeFileSync(outPath, buffer)
})()

Script finished.

Complete source code:

const path = require('path');
const fs = require('fs');
const xlsx = require('node-xlsx');
const magicString = require('magic-string')
const _ = require('lodash');
const buryFile = path.join(__dirname, '../src/lib/buryData.js')
const getBuryContent = (filePath) => {
    const content = fs.readFileSync(filePath, 'utf8')
    const s = new magicString(content)
    s.replace('export default', 'module.exports = ')
    return s.toString()
}
const getFormatDataMap = (data) => {
    let version
    const map = new Map();
    _.forIn(data, (value, key) => {
        if (key.includes('version')) {
            version = value
            !map.has(version) && map.set(version, [value])
            return
        }
        const mapValue = map.get(version)
        mapValue.push(value)
    })
    return map
}
const getTargetItems = ({ mapValue, forNum }) => {
    const len = mapValue.length
    const targetItems = []
    mapValue.forEach((v, i) => {
        for (let index = 0; index < forNum; index++) {
            const element = v[index];
            let targetItem = targetItems[index]
            if (!targetItem) {
                targetItem = new Array(len).fill(' ')
            }
            targetItem[i] = element ? element : ' '
            targetItems[index] = targetItem
        }
    })
    return targetItems
}
const getMergeArr = (map) => {
    const values = _.toArray(map.values())
    const lens = []
    values.forEach((value) => { lens.push(value.length) })
    const MAX_LEN = _.max(lens)
    return getTargetItems({ mapValue: values, forNum: MAX_LEN })
}
(async () => {
    const str = getBuryContent(buryFile)
    const copyFilePath = path.join(__dirname, '/bury.js')
    fs.writeFileSync(copyFilePath, str)
    const { default: data } = await import(copyFilePath)
    const map = getFormatDataMap(data)
    const table = getMergeArr(map)
    debugger
    const buffer = xlsx.build([{ name: 'Buried', data: table }])
    const outPath = path.join(__dirname, '/bury.xlsx')
    fs.existsSync(outPath) && fs.unlinkSync(outPath)
    fs.existsSync(copyFilePath) && fs.unlinkSync(copyFilePath)
    fs.writeFileSync(outPath, buffer)
})()

Remove blank lines, within one hundred lines.

Summarize

Node.js can be used in many scenarios. It is not only used for the development of server interface, but we can also solve repetitive tasks in life by writing scripts. With the simple syntax and powerful ecology of js, the front-end does not need to learn shell, python, etc. Scenarios such as crawlers and automated scripts can be handled using only js.

If my article is helpful to you, your 👍 is my greatest support^_^.

Tags: node.js Front-end

Posted by plaztic on Sat, 14 Jan 2023 10:08:33 +0300