node 实现导出, 在导出excel中包含图片（附件）,导出带图片的excel

个人博客（vue3 + nodejs + mysql ）​http://snows-l.sitehttp://snows-l.site

如果想查看 node mySql 实现数据的导入导出，以及导入批量插入的sql语句，连接如下

node mySql 实现数据的导入导出，以及导入批量插入的sql语句https://blog.csdn.net/snows_l/article/details/139998373https://blog.csdn.net/snows_l/article/details/139998373

一、效果如图：

二、实现方法

利用里 exceljs 插件的 addImage 方法进行插入， 关键代码如下：

const workbook = new Excel.Workbook();
const worksheet = workbook.addWorksheet('收入明细');
// 设置表头
// worksheet.addRow(['标题', '月份', '收入金额', '备注', '收入截图']);
let baseTableTitle = [
  { header: '标题', key: 'title', width: 20 },
  { header: '月份', key: 'date', width: 12 },
  { header: '收入金额（元）', key: 'money', width: 16 },
  { header: '就职于', key: 'source', width: 12 },
  { header: '备注', key: 'remark', width: 24 }
];
if (includePic == 'true') {
  baseTableTitle.push({ header: '收入截图', key: 'pic', width: 16 });
}
worksheet.columns = baseTableTitle;

// 循环写入数据
data.forEach(async (item, index) => {
  const rowData = worksheet.addRow([item.title, item.date, item.money, item.sourceStr, item.remark]);
  // 指定行高
  rowData.height = 50;
});

// 插入图片
if (includePic == 'true') {
  for (let i = 0; i < data.length; i++) {
    // 插入图片到Excel
    const imageId = workbook.addImage({
      filename: '../public' + data[i].pic, // 图片路径 不能出现中文名字
      extension: 'jpeg'
    });
    // E代表第5列，i+2代表第i+2行，F${i+2}:F${i+2}代表第i+2行第6列
    worksheet.addImage(imageId, `F${i + 2}:F${i + 2}`);
  }
}

// buffer 返回给前端即可
const buffer = await workbook.xlsx.writeBuffer();

三、完整代码

/*
 * @Description: ------------ fileDescription -----------
 * @Author: snows_l snows_l@163.com
 * @Date: 2024-04-15 14:29:31
 * @LastEditors: snows_l snows_l@163.com
 * @LastEditTime: 2024-06-24 22:34:26
 * @FilePath: /Website/Server/src/router/wages.js
 */
const express = require('express');
const db = require('../../utils/connDB');
const router = express.Router();
const Excel = require('exceljs');


// 导出
router.get('/wages/export', async (req, res) => {
  let { eDate, sDate, source, includePic } = req.query;
  let sql = `SELECT * FROM wages`;
  if (eDate && sDate) {
    sql += ` WHERE date >= '${sDate}-01' AND date <= '${eDate}-28'`;
  }
  if (source) {
    sql += ` ${eDate && sDate ? 'AND' : 'WHERE'}  source = '${source}'`;
  }
  sql += ` ${(eDate && sDate) || source ? 'and' : 'where'} del_flag = ? ORDER BY date DESC`;
  const params = [0];
  try {
    db.queryAsync(sql, params).then(ress => {
      const data = ress.results;
      dictSql = `select* from sys_dict where dictType = 'wages_source' and pid <> 0 order by sort ASC;`;
      db.queryAsync(dictSql).then(async dictRes => {
        const dictData = dictRes.results;
        // 处理就职于字典
        data.forEach(item => {
          item.sourceStr = dictData.find(dict => dict.value === item.source).label;
        });
        /**
         * 使用 XLSX 库导出excel文件 支持普通的xlsx格式
         */
        // 将数据转换为工作表
        // const ws = XLSX.utils.json_to_sheet(data);
        // // 创建工作簿并添加工作表
        // const wb = XLSX.utils.book_new();
        // XLSX.utils.book_append_sheet(wb, ws, '收入');
        // //生成Excel文件的二进制数据
        // const excelBuffer = XLSX.write(wb, {
        //   type: 'buffer',
        //   bookType: 'xlsx'
        // });
        // const realName = encodeURI('收入报表.xlsx', 'GBK').toString('iso8859-1');
        // // 设置响应头
        // res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        // res.setHeader('Content-Disposition', 'attachment; filename=' + realName);
        // // 发送Excel文件
        // res.send(excelBuffer);

        /**
         * 使用 exceljs 库导出excel文件
         */
        // 下载图片并保存到临时文件
        // const downloadImage = async (url, filePath) => {
        //   const response = await axios.get(url, { responseType: 'arraybuffer' });
        //   fs.writeFileSync(filePath, response.data);
        // };

        const workbook = new Excel.Workbook();
        const worksheet = workbook.addWorksheet('收入明细');
        // 设置表头
        // worksheet.addRow(['标题', '月份', '收入金额', '备注', '收入截图']);
        let baseTableTitle = [
          { header: '标题', key: 'title', width: 20 },
          { header: '月份', key: 'date', width: 12 },
          { header: '收入金额（元）', key: 'money', width: 16 },
          { header: '就职于', key: 'source', width: 12 },
          { header: '备注', key: 'remark', width: 24 }
        ];
        if (includePic == 'true') {
          baseTableTitle.push({ header: '收入截图', key: 'pic', width: 16 });
        }

        worksheet.columns = baseTableTitle;
        // 循环写入数据
        data.forEach(async (item, index) => {
          const rowData = worksheet.addRow([item.title, item.date, item.money, item.sourceStr, item.remark]);
          // 指定行高
          rowData.height = 50;
        });

        // 插入图片
        if (includePic == 'true') {
          for (let i = 0; i < data.length; i++) {
            // 插入图片到Excel
            const imageId = workbook.addImage({
              filename: '../public' + data[i].pic, // 图片路径 不能出现中文名字
              extension: 'jpeg'
            });
            // E代表第5列，i+2代表第i+2行，F${i+2}:F${i+2}代表第i+2行第6列
            worksheet.addImage(imageId, `F${i + 2}:F${i + 2}`);
          }
        }

        const buffer = await workbook.xlsx.writeBuffer();
        // 处理中文文件名
        const realName = encodeURI('收入报表.xlsx', 'GBK').toString('iso8859-1');
        // 设置响应头
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', 'attachment; filename=' + realName);
        // 发送Excel文件
        res.send(buffer);
      });
    });
  } catch (error) {
    return res.send({
      code: 500,
      data: null,
      msg: '导出失败'
    });
  }
});

module.exports = router;