Nodejs + Express + Mysql 实现 Excel 文件上传功能

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第4天，点击查看活动详情

概述

Excel 文件上传功能，在日常的项目开发中是很常见的功能，今天我们就来说说使用 Nodejs + Express + Mysql 来实现 Excel 文件上传至 Mysql 数据库功能。

项目介绍

这里我们准备一个 Excel 文件,在文件里面输入如下数据,如图所示：

截屏2022-05-31 下午8.56.24.png

这里我们要实现的功能有：

Excel 文件上传至 Nodejs + Express服务器并将数据存储在 Mysql 数据库中
从 Mysql 数据库中获取数据
下载 Mysql数据库中数据为 Excel 文件

文件上传 Mysql 数据库后效果如下：

截屏2022-05-31 下午9.06.27.png

列表接口

截屏2022-05-31 下午9.24.52.png

文件下载接口

截屏2022-05-31 下午9.43.26.png

当前版本

"nodejs" :"17.5.0",
"exceljs": "^4.0.1",
"express": "^4.17.1",
"multer": "^1.4.2",
"mysql2": "^2.1.0",
"read-excel-file": "^4.0.6",
"sequelize": "^5.21.13"

文件目录结构

├── README.md
├── node_modules
├── package-lock.json
├── package.json
├── resources
│   └── static
│       └── assets
│           └── uploads
└── src
    ├── app.js
    ├── config
    │   └── db.config.js 
    ├── controllers
    │   └── ExcelController.js
    ├── middlewares
    │   └── upload.js
    ├── models
    │   ├── ExcelModel.js
    │   └── index.js
    └── routes
        └── index.js

目录说明

resources/static/assets/uploads/    // 存储上传的excel文件

src/config/db.config.js  // 导出 MySQL 连接和 Sequelize 的配置参数。

src/controllers/ExcelController.js 
//  1. 用于`read-excel-file`读取文件夹中的 Excel 文件`uploads`，然后使用 Sequelize Model 将数据保存到 MySQL 数据库中。
//  2. 用于检索数据库表中所有教程的导出函数

middleware/upload.js：初始化 Multer Storage 引擎并定义中间件函数以将 Excel 文件保存在`uploads`文件夹中。

`routes/index.js`：定义从 数据请求的路由，使用控制器（连同中间件）来处理请求。

`app.js`：初始化路线，运行 Express 应用程序

项目开始

1. 初始化项目

打开终端，输入命令 mkdir nodejs-upload-file 建一个文件夹，cd 如文件中，执行命令 npm init --yes

安装项目需要的插件，执行命令

npm install express multer sequelize mysql2 read-excel-file

2. Mysql 数据库相关配置

在 src 下我们新建一个 db.config.js 文件

src/config/db.config.js

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

HOST, USER, PASSWORD, DB, dialect 参数是用于 Mysql 数据库连接，pool 是可选的，用于 Sequelize 连接池配置

max: 池中的最大连接数
min：池中的最小连接数
idle: 连接被释放前可以空闲的最长时间，以毫秒为单位
acquire：最大时间，以毫秒为单位，该池将在抛出错误之前尝试获取连接

具体参数的作用可以查看文档Sequelize 构造函数的 API 参考。

3. Sequelize 初始化

我们在 src/models下新建 index.js, 用于初始化 Sequelize，

src/models/index.js

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,
  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize; 

db.excel = require("./ExcelModel.js")(sequelize, Sequelize);

(async () => {
    try {
        await sequelize.authenticate()
        console.log('已成功建立连接');
    } catch (error) {
        console.error('连接失败', error);
    }
})();
module.exports = db;

我们在 src/models/ExcelModel.js 中建立模型

module.exports = (sequelize, Sequelize) => {
  const Excel = sequelize.define("excel", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING,
      allowNull: false
    },
    published: {
      type: Sequelize.BOOLEAN,
    }
  });

  return Excel;
};

此 Sequelize 模型表示 MySQL 数据库中的 excel 表。这些列将自动生成：id、title、description、published、createdAt、updatedAt。

初始化 Sequelize 后，我们不需要编写 CRUD 函数，Sequelize 都支持：

创建一个新 Excel：create(object)
创建多个 Excel：bulkCreate(objects)
通过 id 查找 Excel：findByPk(id)
获取所有 Excel：findAll()

4. 创建 Excel 文件上传的中间件

我们在中间件文件夹中创建 upload.js 中间件

const multer = require("multer");

const excelFilter = (req, file, cb) => {
  if (
    file.mimetype.includes("excel") ||
    file.mimetype.includes("spreadsheetml")
  ) {
    cb(null, true);
  } else {
    cb("Please upload only excel file.", false);
  }
};

var storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, __basedir + "/resources/static/assets/uploads/");
  },
  filename: (req, file, cb) => {
    console.log(file.originalname);
    cb(null, `${Date.now()}-zhijianqiu-${file.originalname}`);
  },
});

var uploadFile = multer({ storage: storage, fileFilter: excelFilter });
module.exports = uploadFile;

中间件文件夹中我们导入了 multer, 并做了相关限制 excel 的配置，destination 选项主要作用是确定文件上传后存储的文件夹，filename 我们将[timestamp]-zhijianqiu-前缀添加到文件的原始名称中，以确保不会出现重复项。

5. 创建文件上传的控制器

我们新建一个文件夹用于存储控制器相关的文件

src/controllers/ExcelControlls.js

const db = require("../models");
const ExcelDB = db.excel; 

const readXlsxFile = require("read-excel-file/node");
const excel = require("exceljs");

const upload = async (req, res) => {
  try {
    if (req.file == undefined) {
      return res.status(400).send("Please upload an excel file!");
    }

    let path =
      __basedir + "/resources/static/assets/uploads/" + req.file.filename;

    readXlsxFile(path).then((rows) => {
      // skip header
      rows.shift();

      let excelData = [];

      rows.forEach((row) => {
        let item = {
          id: row[0],
          title: row[1],
          description: row[2],
          published: row[3],
        };

        excelData.push(item);
      });

      ExcelDB.bulkCreate(excelData)
        .then(() => {
          res.status(200).send({
            message: "Uploaded the file successfully: " + req.file.originalname,
          });
        })
        .catch((error) => {
          res.status(500).send({
            message: "Fail to import data into database!",
            error: error.message,
          });
        });
    });
  } catch (error) {
    console.log(error);
    res.status(500).send({
      message: "Could not upload the file: " + req.file.originalname,
    });
  }
};

const getExcelData = (req, res) => {
  ExcelDB.findAll()
    .then((data) => {
      res.send(data);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error.",
      });
    });
};

const download = (req, res) => {
  ExcelDB.findAll().then((objs) => {
    let excelData = [];

    objs.forEach((obj) => {
      excelData.push({
        id: obj.id,
        title: obj.title,
        description: obj.description,
        published: obj.published,
      });
    });

    let workbook = new excel.Workbook();
    let worksheet = workbook.addWorksheet("ExcelData");

    worksheet.columns = [
      { header: "Id", key: "id", width: 5 },
      { header: "Title", key: "title", width: 25 },
      { header: "Description", key: "description", width: 25 },
      { header: "Published", key: "published", width: 10 },
    ];

    // Add Array Rows
    worksheet.addRows(excelData);

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader(
      "Content-Disposition",
      "attachment; filename=" + "zhijianqiu.xlsx"
    );

    return workbook.xlsx.write(res).then(function () {
      res.status(200).end();
    });
  });
};

module.exports = {
  upload,
  getExcelData,
  download,
};

我们简要说明一下 upload.js 函数，现在看一下upload函数：

首先我们从req.file 读取文件数据
接下来我们使用读取上传文件夹中的read-excel-file Excel 文件，将返回的数据更改为数组。
然后我们使用 Sequelize 模型方法将数组（id、title、description、published）保存到 MySQL 数据库。rows excel bulkCreate()``excel

该getExcelData()函数使用findAll()方法返回存储在数据库excel表中的所有数据。

5. 定义文件上传的接口

接下来我们来定义下请求的接口路径

/api/excel/upload：邮政
/api/excel/list：得到

在routes文件夹中创建一个index.js，其内容如下：

const express = require("express");
const router = express.Router();
const excelController = require("../controllers/ExcelController");
const upload = require("../middlewares/upload");

let routes = (app) => {
  router.post("/upload", upload.single("file"), excelController.upload);
  router.get("/list", excelController.getExcelData);

  router.get("/download", excelController.download);

  return app.use("/api/excel", router);
};

module.exports = routes;

这里可以看到我们使用 ExcelController.js。

6. 定义项目入口文件

在src 下我们新建一个 app.js 文件, 并初始化 Express 服务器

src/app.js,

const express = require("express");
const app = express();
const db = require("./models");
const initRoutes = require("./routes");

global.__basedir = __dirname + "/..";

app.use(express.urlencoded({ extended: true }));
initRoutes(app);

db.sequelize.sync();

// db.sequelize.sync({ force: true }).then(() => {
//   console.log("Drop and re-sync db.");
// });

let port = 8080;
app.listen(port, () => {
  console.log(`Running at localhost:${port}`);
});

这里我们调用了 db.sequelize.sync();

如果我们需要删除表并重新同步数据，可以调用以下代码

db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

到这里，大部分功能以及基本完成了，当然最后一步，我们建立 resources/static/assets 文件夹用于存储上传的文件

7. 运行项目

进入到项目的根目录中，运行命令 nodejs src/app.js, 项目正常启动，使用 postman 测试，可以看到项目运行ok

总结

不积跬步，无以至千里；不积小流，无以成江海