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

Myth丶恋晨 2022-12-09 02:31 206阅读 0赞

## 数据库导出为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;

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