node excell导入导出以及下载

所需依赖安装方式

npm install excel-export
npm install xlsx
npm install moment
npm install fs

进入node项目中执行即可

node

const express = require('express');
const router = express.Router();
const nodeExcel = require('excel-export')
const xlsxs = require('xlsx')
const moment = require('moment')
const fs = require('fs')
const connection = require('../mysql/mysqlUtil');
// 导出到本地
router.get('/export', (req, res) => {
    let arrData = [
        ['name', 'age'],
        ['张三', 20],
        ['李四', 21],
        ['王武', 22],
        ['赵六', 23],
        ['孙琪', 24],];

    let jsonData = [
        {
            name: '张三',
            age: 30
        }, {
            name: "李四",
            age: 31
        }, {
            name: "王武",
            age: 32
        }, {
            name: "赵六",
            age: 33
        }, {
            name: "孙琪",
            age: 34
        }];

// 将数据转成workSheet
//1.arrayData
    let arrayWorkSheet = xlsxs.utils.aoa_to_sheet(arrData);
//2.jsonData
    let jsonWorkSheet = xlsxs.utils.json_to_sheet(jsonData);
//构建workBook
    let workBook = {
        SheetNames: ['arrayWorkSheet', 'jsonWorkSheet'],
        Sheets: {
            'arrayWorkSheet': arrayWorkSheet,
            'jsonWorkSheet': jsonWorkSheet,
        }
    };
    xlsxs.writeFile(workBook, "./excell/works.xlsx");
    // res.send({code: 200, msg: '导出成功', data: xslx.write(workBok, 'userBook.xlsx')});
// xlsx.write(workBok, './excell/works.xlsx');
})
// 导入
router.post('/import', (req, res) => {
    let workbook = xlsxs.readFile('./excell/works.xlsx');
    let sheetNames = workbook.SheetNames;
// 获取第一个workSheet
    let sheet1 = workbook.Sheets[sheetNames[0]];
    console.log(sheet1);

    let range = xlsxs.utils.decode_range(sheet1['!ref']);

//循环获取单元格值
    for (let R = range.s.r; R <= range.e.r; ++R) {
        let row_value = '';
        for (let C = range.s.c; C <= range.e.c; ++C) {
            let cell_address = {c: C, r: R}; //获取单元格地址
            let cell = xlsxs.utils.encode_cell(cell_address); //根据单元格地址获取单元格
            //获取单元格值
            if (sheet1[cell]) {
                // 如果出现乱码可以使用iconv-lite进行转码
                // row_value += iconv.decode(sheet1[cell].v, 'gbk') + ", ";
                row_value += sheet1[cell].v + ", ";
            } else {
                row_value += ", ";
            }
        }
        // console.log(row_value);
    }
});
// 下载
router.get('/download/excel', function (req, res) {
    const conf = {};//创建一个写入格式map，其中cols(表头)，rows(每一行的数据);
    const cols = ['编号', '姓名', '密码', '角色'];//手动创建表头中的内容
    conf.cols = [];//在conf中添加cols

    for (var i = 0; i < cols.length; i++) {
        var tits = {};//创建表头数据所对应的类型,其中包括 caption内容 type类型
        tits.caption = cols[i];//添加内容
        tits.type = 'string';//添加对应类型，这类型对应数据库中的类型，入number，data但一般导出的都是转换为string类型的
        conf.cols.push(tits);//将每一个表头加入cols中
    }
    let fields = {
        id: '',
        username: '',
        password: '',
        realname: ''
    }
    //根据需求查询想要的字段
    let sql = "select id,username,password,realname from user"
    let data = connection.connectionMysql(sql);
    let r=[];
    r=data;
    let tows = ['id', 'username', 'password', 'realname'];//创建一个和表头对应且名称与数据库字段对应数据，便于循环取出数据
    let datas = [];//用于承载数据库中的数据
    let towsLen = tows.length
    let dataLen = r.length
    for (let i = 0; i < dataLen; i++) {//循环数据库得到的数据，因为取出的数据格式为
        //[{"id" : "1","username" : "张三","password" : "123456","realname" : "123"},{…………},{…………}]
        let row = [];//用来装载每次得到的数据
        for (let j = 0; j < towsLen; j++) {//内循环取出每个
            row.push(data[i][tows[j]].toString());//row.push((data[i].tows[j]).toString());两种形式都是相同的
        }
        datas.push(row);//将每一个{ }中的数据添加到承载中
    }
    conf.rows = datas;//将所有行加入rows中
    let result = nodeExcel.execute(conf);//将所有数据写入nodeExcel中
    res.setHeader('Content-Type', 'application/vnd.openxmlformats');//设置响应头
    //设置下载文件命名 支持的excel文件类有.xlsx .xls .xlsm .xltx .xltm .xlsb .xlam等
    res.setHeader("Content-Disposition", "attachment; filename=user" + moment(new Date().getTime()).format('YYYYMMDDhhmmss') + ".xlsx");
    res.end(result, 'binary');//将文件内容传入

});

module.exports = router;

html

<button  onclick="downloadExcelOper()">测试下载excel</button>

js

function downloadExcelOper() {
     var url =  "http://localhost:8088/excel/download/excel";
     window.location.href = url;
  }