需求:electron环境下想要实现根据多个表生成多个Excel文件,打包存入文件夹内并压缩下载到本地。(实际场景描述:界面中有软件工程一班学生信息、软件工程二班学生信息、软件工程三班学生信息,上方有“一键生成”的按钮,点击时弹出文件存储位置选择框选择压缩包所要保存的位置,选择完成后点击保存后生成压缩包中存放一、二、三班三张对应班级学生信息Excel的文件。)注:生成的Excel文件需要按照对应模板进行显示(合并单元格、修改excel单元格的高度和宽度...)
思路:使用electron中的dialog选择保存文件的位置,判断所选地址是否存在;如果存在使用XLSX、XLSXStyle进行文件的生成;然后使用adm-zip将所有文件存放在zip文件夹中保存到本地。
1.下载xlsx xlsx-style adm-zip
| npm i xlsx |
| npm i xlsx-style |
| npm i adm-zip |
| 或 |
| yarn add xlsx |
| yarn add xlsx-style |
| yarn add adm-zip |
注意:使用xlsx-style插件的时候需要简单修改下源码,第一种情况下会出现报错,第二种是为了可以调整excel单元格的行高。
1)修改node_modules文件夹下的xlsx-style下的dist下的cpexcel.js文件
807行:var cpt = cptable;
2)修改xlsx-style文件夹下面的xlsx.js文件 替换write_ws_xml_data以下方法
| |
| |
| |
| |
| |
| var DEF_PPI = 96, PPI = DEF_PPI; |
| function px2pt(px) { return px * 96 / PPI; } |
| function pt2px(pt) { return pt * PPI / 96; } |
| function write_ws_xml_data(ws, opts, idx, wb) { |
| var o = [], r = [], range = safe_decode_range(ws['!ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows']; |
| var dense = Array.isArray(ws); |
| var params = ({r:rr}), row, height = -1; |
| for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C); |
| for(R = range.s.r; R <= range.e.r; ++R) { |
| r = []; |
| rr = encode_row(R); |
| for(C = range.s.c; C <= range.e.c; ++C) { |
| ref = cols[C] + rr; |
| var _cell = dense ? (ws[R]||[])[C]: ws[ref]; |
| if(_cell === undefined) continue; |
| if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell); |
| } |
| if(r.length > 0 || (rows && rows[R])) { |
| params = ({r:rr}); |
| if(rows && rows[R]) { |
| row = rows[R]; |
| if(row.hidden) params.hidden = 1; |
| height = -1; |
| if (row.hpx) height = px2pt(row.hpx); |
| else if (row.hpt) height = row.hpt; |
| if (height > -1) { params.ht = height; params.customHeight = 1; } |
| if (row.level) { params.outlineLevel = row.level; } |
| } |
| o[o.length] = (writextag('row', r.join(""), params)); |
| } |
| } |
| if(rows) for(; R < rows.length; ++R) { |
| if(rows && rows[R]) { |
| params = ({r:R+1}); |
| row = rows[R]; |
| if(row.hidden) params.hidden = 1; |
| height = -1; |
| if (row.hpx) height = px2pt(row.hpx); |
| else if (row.hpt) height = row.hpt; |
| if (height > -1) { params.ht = height; params.customHeight = 1; } |
| if (row.level) { params.outlineLevel = row.level; } |
| o[o.length] = (writextag('row', "", params)); |
| } |
| } |
| return o.join(""); |
| } |
2.可以在utils下新建一个js文件用于写入xlsx以及xlsx-style相关方法
| |
| import XLSX from 'xlsx' |
| import XLSXStyle from 'xlsx-style' |
| |
| export function exportExcel(filename, data) { |
| var sheet_name = 'Sheet1' |
| var work_book = XLSX.utils.book_new() |
| var sheet = XLSX.utils.aoa_to_sheet(data) |
| XLSX.utils.book_append_sheet(work_book, sheet, sheet_name) |
| XLSXStyle.writeFile(work_book, filename) |
| } |
| |
| export function workbook2ArrayBuffer(workbook) { |
| var wopts = { |
| bookType: 'xlsx', |
| bookSST: false, |
| type: 'binary', |
| } |
| var wbout = XLSXStyle.write(workbook, wopts) |
| |
| function s2ab(s) { |
| var buf = new ArrayBuffer(s.length) |
| var view = new Uint8Array(buf) |
| for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff |
| return buf |
| } |
| return s2ab(wbout) |
| } |
3.在要进行一键生成的界面进行相关逻辑的编写。
| <button @click='ClickGenerate'>一键生成</button> |
| ... |
| ... |
| methods: { |
| |
| ClickGenerate(){ |
| const fs = require('fs') |
| let zipName = '软件工程学生信息.zip' |
| |
| const { dialog } = require("electron").remote |
| let options = { |
| title: "软件工程学生信息", |
| defaultPath: zipName |
| } |
| dialog.showSaveDialog(options, (result) => { |
| if (!result) { |
| this.$message.warning('下载任务已取消') |
| return |
| } |
| let path = getDirectory(result) |
| if (!fs.existsSync(path)) { |
| this.$message.warning('所选路径不存在!') |
| return |
| } |
| |
| this.downloadZip(result) |
| }) |
| }, |
| |
| async downloadZip(savePath) { |
| |
| let AdmZip = require("adm-zip") |
| let zip = new AdmZip() |
| zip.writeZip(savePath) |
| |
| |
| let soft1buffer = this.creatSoftbuffer('1') |
| if (soft1buffer) { |
| zip.addFile('软件工程一班学生信息' + ".xlsx", soft1buffer, "") |
| } |
| |
| zip.writeZip(savePath, (error) => { |
| if (error) { |
| this.$message.error('[下载异常]' + error.message) |
| return |
| } |
| this.$message.success('下载已完成') |
| }) |
| }, |
| |
| creatSoftbuffer(name) { |
| let list = [] |
| let date = new Date() |
| let Y = date.getFullYear() + '-'; |
| let M = (date.getMonth() + 1 < 10 ? '0' + (date.getMonth() + 1) : date.getMonth() + 1) + '-'; |
| let D = date.getDate() + ' '; |
| let dqsj = Y + M + D |
| let soft1Datas = [ |
| { id: 1, name: "张三", age: '24', address: '哈尔滨市南岗区', grade: '软件一班' }, |
| { id: 2, name: "里斯", age: '22', address: '哈尔滨市呼兰区', grade: '软件一班' }, |
| { id: 3, name: "王二", age: '21', address: '哈尔滨市松北区', grade: '软件一班' }, |
| ... |
| ] |
| switch (name) { |
| case '1': |
| list.push(["软件工程一班学生信息"]) |
| list.push(["统计时间:", dqsj, "", "", ""]) |
| list.push(["序号", "姓名", "年龄", "地址", "年级"]) |
| let lock1 = 0 |
| for (let i in soft1Datas) { |
| lock1 = lock1 + 1 |
| let item = soft1Datas[i] |
| let column = [lock1, item["name"], item["age"], item["address"], item["grade"]] |
| list.push(column) |
| } |
| |
| var sheet_name = "软件工程一班学生信息" |
| var sum = 5 |
| break |
| default: |
| console.log(0) |
| } |
| var sheet = XLSX.utils.aoa_to_sheet(list) |
| |
| let counts = soft1Datas.length + 5 |
| sheet['!cols'] = [] |
| sheet['!rows'] = [] |
| for (let i = 1; i < sum; i++) { |
| sheet['!cols'].push({ wpx: 150 }) |
| } |
| for (let j = 1; j < counts; j++) { |
| sheet['!rows'].push({ hpx: 14 }) |
| } |
| sheet['!rows'][0].hpx = 35 |
| sheet['!rows'][1].hpx = 20 |
| sheet['!rows'][2].hpx = 20 |
| sheet['!rows'][3].hpx = 20 |
| |
| for (let key in sheet) { |
| if (sheet[key] instanceof Object) { |
| sheet[key].s = { |
| alignment: { |
| horizontal: 'center', |
| vertical: 'center' |
| }, |
| font: { |
| sz: 11, |
| name: '宋体' |
| }, |
| border: { |
| top: { |
| style: 'thin' |
| }, |
| bottom: { |
| style: 'thin' |
| }, |
| left: { |
| style: 'thin' |
| }, |
| right: { |
| style: 'thin' |
| } |
| } |
| } |
| } |
| } |
| |
| sheet.A1.s = { |
| font: { |
| name: '宋体', |
| sz: 16, |
| bold: true, |
| }, |
| alignment: { |
| horizontal: 'center', |
| vertical: 'center' |
| } |
| } |
| sheet['!merges'] = [ |
| { s: { r: 0, c: 0 }, e: { r: 0, c: 5 } } |
| ]; |
| |
| var work_book = XLSX.utils.book_new() |
| |
| XLSX.utils.book_append_sheet(work_book, sheet, sheet_name) |
| const workbookArrayBuffer = workbook2ArrayBuffer(work_book) |
| |
| var buf = new Buffer(workbookArrayBuffer.byteLength) |
| var view = new Uint8Array(workbookArrayBuffer) |
| for (var i = 0; i < buf.length; ++i) { |
| buf[i] = view[i] |
| } |
| return buf |
| } |
| } |
| |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」