如果想查看?node mySql 實現數據的導入導出，以及導入批量插入的sql語句，連接如下

node mySql 實現數據的導入導出，以及導入批量插入的sql語句-CSDN博客https://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++) {// 插入圖片到Excelconst 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++) {// 插入圖片到Excelconst 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;