nodejs express使用node-xlsx实现文件的上传下载导入导出

在express项目中使用node-xlsx实现文件导入导出

npm install node-xlsx --save

1. 上传

app.js

const multer  = require('multer')
app.use(multer({dest: './uploads'}).any())

后端接口：

const path = require("path")
const fs = require('fs');
const xlsx = require('node-xlsx');
// 接口
router.post(
  '/importFile',
  passport.authenticate('jwt', { session: false }),
  (req, res) => {
    let excelContent =xlsx.parse(req.files[0].path);
    let map = {
      '科室名称': 'name',
      '所属院区': 'partName',
      '上级科室': 'equal',
      '科室位置': 'address',
      '状态': 'stateName',
    }

    let partMap = {
      '东院': 'EASTPART',
      '北院': 'NORTHPART',
      '湘南院': 'XIANGNANPART'
    };
    let stateMap = {
      '正常': 0,
      '异常': 1
    }
    let columnName = excelContent[0].data[0];
    let data = excelContent[0].data.slice(1);
    data.forEach(list => {
      let json = {
        name: '',
        part: '',
        partName: '',
        equal: '',
        state: '',
        stateName: '',
      };
      list.forEach((item, index) => {
        let key = map[columnName[index]];
        if (key === 'partName') {
          json.part = partMap[item];
        }
        if (key === 'stateName') {
          json.state = stateMap[item];
        }
        json[key] = item;
        
      })
      json.type =  !json.equal ? '0' : '1'; // 0 一级  1 二级
      Department.findOne({name: json.name}).then(data => {
        if (data) {
          let j = {...json};
          Department.findOneAndUpdate(
            { name: j.name },
            { $set: j},
            { new: true }
          ).then(r => {
            console.log('444444');
          })
        } else {
          new Department(json).save();
        }
        
      })
    })

    res.json({
      code: 0,
      msg: '导入成功',
      data: ''
    })
  }
)

2. 下载

后端接口：http:localhost:3000

const path = require("path")
const fs = require('fs');
const xlsx = require('node-xlsx');
// 接口
router.get('/exportFile',
 (req,res) => {
  Department.find().exec(function(err,data){
      var result=[];
      var title = ['科室','所属院区','上级科室', '科室位置',]//这是第一行 俗称列名
      result.push(title);
      let excelPath = path.resolve(__dirname,'../../exportFiles');
      data.forEach((item) => {
          var itemArr = [];
          itemArr.push(item.name);
          itemArr.push(item.part);
          itemArr.push(item.equal);
          itemArr.push(item.address);
          
          result.push(itemArr);
      });
      var name='科室列表.xlsx';    
      var buffer=xlsx.build([{name:'sheet1',data:result}]);
      fs.writeFile(excelPath+'/' + name,buffer,{'flag':'w'}, err => {
        res.download(excelPath+ '/' +name, downerr => {
          console.log(downerr, '990909')
        }); 
      })
      
  });
});

前端调用：

window.location.href = 'http://localhost:3000/api/department/exportFile'

效果：