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

语言: CN / TW / HK

theme: orange

持续创作,加速成长!这是我参与「掘金日新计划 · 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

当前版本

js "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" 文件目录结构

js ├── 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

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

```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 中建立模型

```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 表。这些列将自动生成:idtitledescriptionpublishedcreatedAtupdatedAt

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

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

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

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

```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

```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,其内容如下:

```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,

```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

## 总结

不积跬步,无以至千里;不积小流,无以成江海