数据库导出为excel表格的封装

数据库导出为excel表格的封装

/**
 * @name ExcelUtil
 * @description 数据库导出Excel或Excel导入数据库的操作
 * @requires node-xlsx 
 * @requires DBUtil
 * @author 斌
 */
const xlsx = require("node-xlsx");
const fs = require("fs");
const os = require("os");
const path = require("path");
const DBUtil = require("./DBUtil");   //导入数据库操作详情见数据库封装篇

class ExcelUtil {
    /**
     * 数据库数据导出为Excel
     * @param {Array} results  数据库查询的结果对象 ， 是个数组，里面包含了对象
     * @param {string} outputPath 要保存Excel文件的路径，如果不传默认在临时目录
     * @returns {boolean|string} 如果不成功返回false,如果成功则返回excel的路径
     */
    static dbToExcel(results, outputPath) {
        if (Array.isArray(results)) {
            if (results.length == 0) {
                return false;
            }
            let excelData = [];
            //建立表头数据
            let headerRow = Object.keys(results[0]);
            excelData.push(headerRow);
            //建立表的数据
            for (let item of results) {
                excelData.push(Object.values(item));
            }
            let buffer = xlsx.build([{
                name: "sheet1",
                data: excelData
            }]);

            if (!outputPath) {
                outputPath = path.join(os.tmpdir(), `${Date.now()}.xlsx`);
            }

            fs.writeFileSync(outputPath, buffer);
            return outputPath;
        }
        else {
            throw new Error("要导出的数据必须是一个数组");
        }
    }

    /**
     * excel的数据导出为数据库
     * @param {string} excelPath 要读取的Excel路径 
     * @param {string} tableName 要插入数据库中的数据表的名称，没有则创建
     * @returns {boolean} true代表成功,false代表失败
     */
    static async excelToDB(excelPath, tableName) {
        if (!excelPath) {
            return false;
        }
        if (!fs.existsSync(excelPath)) {
            return false;
        }
        let extName = path.extname(excelPath);     
        if (!/^\.xlsx?$/.test(extName)) {
            return false;
        }
        let buffer = fs.readFileSync(excelPath);
        let workSheetBook = xlsx.parse(buffer);
        let excelData = workSheetBook[0].data;
        if (excelData.length == 0) {
            return false;
        }
        let headerRow = excelData.shift();     
        try {
            let ddlSql = `
                create table if not exists ${tableName} (
                    id int primary key auto_increment,
                    ${headerRow.map(item => item + " varchar(255) ").toString()}
                ) engine = innodb default charset = utf8;
            `;
            let result1 = await DBUtil.executeSql(ddlSql);
            let strSql = "";
            for (let item of excelData) {
                strSql += ` insert into ${tableName} (${headerRow.toString()}) values (${item.map(_item => "'" + _item + "'")}) ; `;
            }
            let result2 = await DBUtil.executeSql(strSql);
            return true;
        } catch (error) {
            throw error;
        }
    }
}

module.exports = ExcelUtil;

本文封装基于数据库封装详情见封装数据库的篇章