企业官网建设流程全解析

个人博客（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列，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;