构建html5博客网站/品牌网站建设解决方案

如果想查看 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 [e-mail]* @Date: 2024-04-15 14:29:31* @LastEditors: snows_l [e-mail]* @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;