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

个人博客vue3 nodejs mysql http://36.151.145.67:30000/如果想查看 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列i2代表第i2行F${i2}:F${i2}代表第i2行第6列 worksheet.addImage(imageId, F${i 2}:F${i 2}); } } // buffer 返回给前端即可 const buffer await workbook.xlsx.writeBuffer();三、完整代码/* * Description: ------------ fileDescription ----------- * Author: snows_l snows_l163.com * Date: 2024-04-15 14:29:31 * LastEditors: snows_l snows_l163.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列i2代表第i2行F${i2}:F${i2}代表第i2行第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;