express实现excel附件上传和和下载

1.附件上传
这里只是模拟数据-非真实

express服务端代码

const express = require("express");
const router = express.Router();
const formidable = require("formidable");
const uuid = require("uuid");

// 上传excel附件
router.post("/fileUpload", fileUploadFunc);

 // 上传附件
  fileUploadFunc(req, res, next) {
   // 使用formidable库处理formData上传
    const form = new formidable.IncomingForm();
    form.parse(req, function (err, fields, file) {
      if (err != null) {
        console.log(err);
        return res.status(400).json({ message: err.message });
      }
      console.log(file.files);

      // 获取文件名和文件上传path
      const { originalFilename, filepath } = file.files;
      const read = fs.createReadStream(filepath);
      // const file_name = md5Code(originalFilename)

      //文件格式类型限制校验
      if (
        !originalFilename.endsWith("xls") &&
        !originalFilename.endsWith("xlsx")
      ) {
        return res.json({ text: "请上传xls或xlsx格式的文件" });
      }

      // 创建文件名为originalFilename的附件,并写入内容,存储到服务器static目录下面
      const write = fs.createWriteStream("./static/" + originalFilename);
      read.pipe(write);

      // 监听文件创建流完成
      write.on("finish", function () {
        console.log("fs.createWriteStream finish");

        // 获取上传的excel数据
        const workSheetsFromFile = xlsx.parse("./static/" + originalFilename);
        const excelDatas = workSheetsFromFile[0].data;
        // console.log("workSheetsFromFile",excelDatas)
        /* 
          打印输出 打印数据是excel上传的-这里是假数据
            workSheetsFromFile [
                [ '姓名', '年龄', '性别', '身份证标示', '职业', '贯籍', 'UUID' ],
                [ '老王1', 20, '男', 123456789012345000, '软件开发', '北京' ],
                [ '老王2', 21, '男', 123456789012345000, '前端开发', '上海' ],
                [ '大数据开发', 47, '女', 123456789012345000, '大数据开发', '天津' ],
                [ '测试开发', 59, '女', 123456789012345000, '测试开发', '重庆' ],
                [ '老王5', 43, '女', 123456789012345000, '软件开发', '四川' ],
                [ '老王6', 30, '男', 123456789012345000, '前端开发', '江苏' ],
                [ '老王7', 29, '男', 123456789012349000, '大数据开发', '浙江' ],
                [ '老王8', 26, '女', 123456789012345000, '测试开发', '江西' ],
                [ '老王9', 24, '男', 123456789012345000, '大数据开发', '广西' ],
                [ '老王10', 22, '男', 123456789012345000, '测试开发', '云南' ]
            ]
            */
            
	   // 写入指定文件
        const fsFile = 'user.txt'
        writeStreamFun(
          fsFile,
          excelDatas,
          res,
          () => {
            res.json({
              code: 0,
              data: null,
              msg: "sucess",
            });
          },
          true
        );
      });
    });
  }

简单封装一个写入流的函数writeStreamFun

// 为了每次导入数据或者添加数据直接追加,不需要重复清空内容和读写
let userFile = 'user.txt'
let writeStream = fs.createWriteStream(userFile )

function writeStreamFun(fileName, contextArr, res, callBack,isXlxs=false) {
  // 创建一个可以写入的流
  !isXlxs && (writeStream = fs.createWriteStream(fileName))
  isXlxs && contextArr.shift()

  // 数组内容存储处理
  while (contextArr && contextArr.length > 0) {
    let bodys = contextArr.shift();

    isXlxs && (bodys = {
      name: bodys[0],
      age: bodys[1],
      sex: bodys[2] === "男" ? "0" : "1",
      id: bodys[3],
      career: bodys[4],
      province: bodys[5],
      uuid: uuid.v4(),
    })

    // 重新写入内容
    writeStream.write(JSON.stringify(bodys) + "/n", "utf8");
  }

  // 标记流结束
  writeStream.end();

  // 写入完成触发finish函数
   writeStream.on("finish", () => {
   console.log("写入完成");
   
   callBack && callBack();
  });

  // 写入失败
  writeStream.on("error", (err) => {
    res.send({
      code: -1,
      msg: "写入内容失败",
      data: err,
    });
  });
}

前端代码

import React from 'react';
import { UploadOutlined } from '@ant-design/icons';
import { Button, Space, Upload, message } from 'antd';
import {fileUploadApi} from '../../apis/index'
import { Form } from 'antd';

const uploadc:any = (props:any) => {
    const [messageApi, contextHolder] = message.useMessage();
	
	// 上传附件点击   
    const onChangeFiles = async ({fileList}:any) => {
        console.log(fileList)
        
        const formData = new FormData();//创建formData对象
        fileList.forEach((item:any) => {
            formData.append('files', item.originFileObj);
        });

        // 调用接口
        const result = await fileUploadApi(formData)
        if(result.data.code === 0) {
            messageApi.open({
                type: 'success',
                content: '导入数据成功',
              });

            // 请求成功 刷新列表
            setTimeout(() => {
                props.addUserHandler()
            },200)
           
        }
    }

    return (
        <Space direction="vertical" style={{ width: '100%' }} size="large">
         {contextHolder}
         
          {/* <Form>
               <Form.Item className="item" name='files'>
                
               </Form.Item>
          </Form> */}
          <Upload
                        headers={{ 'content-type': 'multipart/form-data' }}
                        action=""
                        maxCount={1}
                        beforeUpload={()=> false}
                        onChange={(info:any) => onChangeFiles(info)}
                        showUploadList={false}
                    >
                        <Button icon={<UploadOutlined />}>导入excel数据</Button>
                    </Upload>
        </Space>
      );
}

export default uploadc;

2.附件下载

express服务端

const uuid = require("uuid");
const fs = require("fs");
const xlsx = require("node-xlsx");
const path = require("path");
const { md5Code } = require("v5w-utils");

 // 导出excel
  exportsExcelFunc(req, res, next) {
    // 查询数据-这里可以换成数据库查询
    readFileFunc(fsFile, res, (result) => {
      // 精准条件匹配
      const searchRes = exactLookup(req.query, result);
      // console.log(searchRes, "精准匹配最终数据");
		
	  // 数据源
      // 定义excel表格表头和内容
      const excelArr = searchRes.map((item) => [
        item.name,
        item.age,
        item.sex === "0" ? "男" : "女",
        item.id + "",
        item.career,
        item.province,
        item.uuid,
      ]);
      //  excel的title以及数据存放到data变量
      const data = [
        // 表头格式
        ["姓名", "年龄", "性别", "身份证标示", "职业", "贯籍", "UUID"],
        // 内容和表头格式一致
        ...excelArr,
      ];

      // 设置表格宽
      const sheetOptions = {
        "!cols": [
          { wch: 10 },
          { wch: 7 },
          { wch: 10 },
          { wch: 20 },
          { wch: 20 },
          { wch: 20 },
          { wch: 40 },
        ],
      };
	 
	 // 将数据data传入xlsx.build中,xlsx.build自动处理写入excel操作
      // 使用node-xlsx模块生成excel文件
      const buffer = xlsx.build([{ name: "基本信息表", data }], {
        sheetOptions,
      });

      // 设置返回的http header信息，告诉浏览器该文件是一个excel文件
      res.setHeader(
        "Content-Type",
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      );
      res.setHeader(
        "Content-Disposition",
        "attachment; filename=" + encodeURIComponent("导出数据.xlsx")
      );

      // 将生成的excel文件发送给客户端
      res.send(buffer);
    });
  }

附件导出前端下载代码

 // 导出excel
  const onExportExcel = async () => {
    console.log("props.searchParams",props.searchParams)
	// exportExcel是接口，props.searchParams这个是搜索条件，根据自己情况修改
    const response = await exportExcel(props.searchParams || {})
    console.log('导出excel',response.data)
    const url = window.URL.createObjectURL(new Blob([response.data]));
    const link = document.createElement('a');
    link.href = url;
    const date = new Date()
    const xlsxName =  date.getFullYear()+''+(date.getMonth()+1)+''+date.getUTCDate()+''+date.getHours()+''+date.getMinutes()
    // 表名前端可以控制自己定义
    link.setAttribute('download', xlsxName+'基本信息表.xlsx');
    document.body.appendChild(link);
    link.click();
  }