nodejs模块之 xlsx

nodejs模块之 xlsx

xlsx 模块文档
node-xlsx 模块文档

当然node 还有一个模块叫 node-xlsx，也可以对excel文件进行读写，但是个人感觉本文主题的这个模块更加简单实用，主要针对excel。node-xlsx 还可以处理除excel之外的 其他文件（比如：csv，等）

xlsx-style 模块文档
可以使用这个模块对 excel 文档的样式进行设置

const xlsx = require('xlsx');
// const nxlsx = require('node-xlsx');
const fs = require('fs');

let result = [];
// let obj = nxlsx.parse(filename); //arr [{name:sheet1,data:[]}] 
function getInfo(filename, sheetIndex) {
    let obj = xlsx.readFile(filename); //arr [{name:sheet1,data:[]}]
    let sheetname = obj.SheetNames;
    let sheetObj = obj.Sheets[sheetname[sheetIndex]];
    let data1 = xlsx.utils.sheet_to_json(sheetObj)
        // console.log(data1);
    return data1;
}
// 形成新表数据
function check(arr1, arr2) {
    // console.log(arr1.length)
    for (let i = 0; i < arr1.length; i++) {
        let status = true;
        for (let o = 0; o < arr2.length; o++) {
            if (arr2[o].original_image_uuid.indexOf(arr1[i].uid) != -1) {
                // result.push({ name: arr1[i].name, id: arr2[o].id });
                arr1[i]['id'] = arr2[o].id;
                // arr1.splice(i, 1);
                // i--;
                status = false;
            }
        }
        if (status) {
            // console.log(arr1[i].name)
            result.push({ nouid: arr1[i].uid });
            arr1.splice(i, 1);
            i--;
        }

    }
    // console.log(result)
}
// 表内数据对比
function contract(arr1) {
    for (let i = 0; i < arr1.length; i++) {
        for (let o = i + 1; o < arr1.length; o++) {
            if (arr1[i].uid == arr1[o].uid) {
                arr1.splice(i, 1)
                i--;
            }

        }
    }

}

function transform(arr1, arr2) {
    for (let i = 0; i < arr1.length; i++) {
        for (let o = 0; o < arr2.length; o++) {
            if (arr2[o].original_image_uuid.indexOf(arr1[i].uid) != -1) {
                let w = arr2[o].image_resolution.split('x')[0];
                let h = arr2[o].image_resolution.split('x')[1];
                if (arr2[o].image_orientation == '90') {
                    // 21
                    arr1[i]['fix_special_text'] = trCoor(arr2[o].special_text, arr2[o].image_orientation, h);
                    // 17
                    // arr1[i]['fix_special_text'] = trCoor(arr2[o].special_text, arr2[o].image_orientation, w);
                } else if (arr2[o].image_orientation == '270') {
                    // 21
                    arr1[i]['fix_special_text'] = trCoor(arr2[o].special_text, arr2[o].image_orientation, w);
                    // 17
                    // arr1[i]['fix_special_text'] = trCoor(arr2[o].special_text, arr2[o].image_orientation, h);
                } else {
                    console.log('specialId', arr1[i].id, i);
                    arr1[i]['fix_special_text'] = trCoor(arr2[o].special_text, arr2[o].image_orientation, w, h);
                }
            }
        }
    }
}

function transformSingle(arr1) {
    for (let i = 0; i < arr1.length; i++) {

        if (arr1[i].original_image_uuid) {
            let w = arr1[i].image_resolution.split('x')[0];
            let h = arr1[i].image_resolution.split('x')[1];
            if (arr1[i].image_orientation == '90') {
                arr1[i]['fix_special_text'] = trCoor(arr1[i].special_text, arr1[i].image_orientation, h);
            } else if (arr1[i].image_orientation == '270') {
                arr1[i]['fix_special_text'] = trCoor(arr1[i].special_text, arr1[i].image_orientation, w);
            } else {
                console.log("specialId", arr1[i].id);
                arr1[i]['fix_special_text'] = trCoor(arr1[i].special_text, arr1[i].image_orientation, w, h);

            }
        }

    }
}

function trCoor(str, ro, val, h) {
    let arr = JSON.parse(str);
    for (let i = 0; i < arr.length; i++) {
        for (let o = 0; o < arr[i].length; o++) {
            if (ro == '90') {
                let t = arr[i][o][1];
                arr[i][o][1] = val - arr[i][o][0]; // [[[],[],[],[]]]
                arr[i][o][0] = t;
                // 4.17
                // let t = val - arr[i][o][0];
                // arr[i][o][0] = arr[i][o][1];
                // arr[i][o][1] = t;
            } else if (ro == '270') {
                let t = arr[i][o][0];
                arr[i][o][0] = val - arr[i][o][1];
                arr[i][o][1] = t;
                // 4.17
                // let t = val - arr[i][o][1];
                // arr[i][o][1] = arr[i][o][0];
                // arr[i][o][0] = t;
            } else {
                arr[i][o][0] = val - arr[i][o][0];
                arr[i][o][1] = h - arr[i][o][1];
                // 4.17
                // arr[i][o][0] = val - arr[i][o][0];
                // arr[i][o][1] = h - arr[i][o][1];
            }
        }
    }
    return JSON.stringify(arr);
}

// limit coordinate
function limitCdn(dataArr) {
    // console.log(dataArr[0])
    for (let i = 0; i < dataArr.length; i++) {
        let coorArr;
        if (typeof dataArr[i].fix_special_text == 'string') {
            coorArr = JSON.parse(dataArr[i].fix_special_text);
        } else {
            coorArr = dataArr[i].fix_special_text;
        }
        // console.log(i, coorArr)
        for (let o = 0; o < coorArr.length; o++) {
            coorArr[o].forEach((v) => {
                v[0] = spliceCdn(v[0]);
                v[1] = spliceCdn(v[1]);
            })
        }
        dataArr[i].fix_special_text = JSON.stringify(coorArr);
        // result.push({ coordinate: JSON.stringify(coorArr) });
    }
    // console.log(dataArr);
    // return dataArr;
}
// 截取 & 取正值
function spliceCdn(num) {
    num = num.toString();
    // console.log(num > 0)
    if (num.indexOf('.') != -1) {
        num = Number(num.slice(0, num.indexOf('.') + 3));
    }
    if (num < 0) {
        return -Number(num);
    } else {
        return Number(num);
    }
}

// let json = [
//     { "大标题": null },
//     { null: "大标题" },
//     { null: "大标题" },
//     { null: "大标题" },
//     { Name: 'name_01', Age: 21, Address: 'address_01' },
//     { Name: 'name_02', Age: 22, Address: 'address_02' },
//     { Name: 'name_03', Age: 23, Address: 'address_03' },
//     { Name: 'name_04', Age: 24, Address: 'address_04' },
//     { Name: 'name_05', Age: 25, Address: 'address_05' },
// ];
function write(json) {
    let ss = xlsx.utils.json_to_sheet(json); //通过工具将json转表对象
    let keys = Object.keys(ss).sort(); //排序 [需要注意，必须从A1开始]
    let lkeys = keys.map(v => v.slice(1))
    lkeys.sort(function(a, b) { return a - b });
    let ref = keys[1] + ':' + keys[keys.length - 1].slice(0, 1) + lkeys[keys.length - 1]; //这个是定义一个字符串 也就是表的范围[A1:C5] 
    // console.log(lkeys[keys.length - 1])

    let workbook = { //定义操作文档
        SheetNames: ['sheet'], //定义表明
        Sheets: {
            'sheet': Object.assign({}, ss, { '!ref': ref }) //表对象[注意表明]
        },
    }

    // xlsx.writeFile(workbook, './result/list_image.xlsx'); //将数据写入文件
    xlsx.writeFile(workbook, filename); //将数据写入文件
    console.log('finish write file')
}

function writeExistFile(wb, sheet, json, filename, position) {
    let setObj = {
        origin: position, // 输出位置
        skipHeader: false, // 是否不包含标题行
        // header:[]
    }
    xlsx.utils.sheet_add_json(sheet, json, setObj);
    xlsx.writeFile(wb, filename);
    console.log('finish add data to exist file')
}

function addNewSheet(wb, wsjson, sheetname, filename) {
    let ws = xlsx.utils.json_to_sheet(wsjson);
    // console.log(ws);
    xlsx.utils.book_append_sheet(wb, ws, sheetname);
    xlsx.writeFile(wb, filename);
    console.log('finish add new sheet');
}

// === 执行 ===

// let filename1 = 'C:\\Users\\Lenovo\\Downloads\\Issue images.xlsx';
let filename1 = 'C:\\Users\\Lenovo\\Downloads\\issue images_0508_306.xlsx';
// let filename2 = './result/specialcharactor_0429_1865.xlsx';
let filename2 = 'C:\\Users\\Lenovo\\Downloads\\qatooldatabaseprod.image_special_tex_0421_24.xlsx';
// let filename2 = 'C:\\Users\\Lenovo\\Downloads\\specialcharactor_0417_1997.xlsx';
// let filename = './result/list1.xlsx';

let arr1 = getInfo(filename1, 0); // 输出表
// let arr2 = getInfo(filename2, 0); // 大表
// console.log(arr1.length, arr2.length);
// check(arr1, arr2); // 对比 是否存在
// contract(arr1);
// transform(arr1, arr2); // 对比两表 修改
transformSingle(arr1); // 单表修改
limitCdn(arr1);
// console.log(arr1);
arr1.push({ uid: '=====', id: '===分割线===', fix_special_text: '======' })
console.log(arr1[0]);
// console.log(result.length);
let obj = xlsx.readFile(filename1);
let sheetObj = obj.Sheets[obj.SheetNames[0]];
writeExistFile(obj, sheetObj, arr1, filename1, 'AB1'); // workbook , sheetObj , json , filename
// addNewSheet(obj, result, 'noquery', filename1);