导出带图片的 Excel - OOXML 文件分析
最编程
2024-07-06 10:22:33
...
需求:
普通js导出文件excel具有兼容性问题,通过js-xsl导出文件API未找到导出图片的方案,实例过少,因此针对07年后以.xlsx后缀的excel文件,通过修改后缀.zip参考文件模板来实现完整OOXML文档下载。
OOXML分析xl目录下的文件,如图:
主要数据文件在xl目录下面
styles.xml里面存放着excel的样式数据
很容易看出包含,字体,边框,单元格样式信息。
sharedStrings.xml存储的excel中的字符串,excel中的字符串都是放在这个里面,共享字符串,所以往往excel的文件会比txt小。
worksheets里面是excel的sheet文件
打开xml
其中r="C2"表示excel的C2这个单元格,t="s"表示该单元格是字符串,val是2表示在sharedStrings.xml里面第二个共享的字符串。
其中r="N2"表示excel的N2这个单元格,s="2"表示该单元格有样式,在styles.xml里面的cellXfs里面的第3个样式(0是第一个)。
一、简单的excel导出下载:
<template> <div> <p style="font-size: 20px;color: red;">使用table标签方式将json导出xls文件</p> <button @click='tableToExcel'>导出</button> </div> </template> <script> export default { methods:{ tableToExcel(){ //要导出的json数据 const jsonData = [ { name:'路人甲', phone:'123456', email:'123@123456.com' }, { name:'炮灰乙', phone:'123456', email:'123@123456.com' }, { name:'土匪丙', phone:'123456', email:'123@123456.com' }, { name:'流氓丁', phone:'123456', email:'123@123456.com' }, ] //列标题 let str = '<tr><td>姓名</td><td>电话</td><td>邮箱</td></tr>'; //循环遍历,每行加入tr标签,每个单元格加td标签 for(let i = 0 ; i < jsonData.length ; i++ ){ str+='<tr>'; for(let item in jsonData[i]){ //增加\t为了不让表格显示科学计数法或者其他格式 str+=`<td>${ jsonData[i][item] + '\t'}</td>`; } str+='</tr>'; } //Worksheet名 let worksheet = 'Sheet1' let uri = 'data:application/vnd.ms-excel;base64,'; //下载的表格模板数据 let template = `<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet> <x:Name>${worksheet}</x:Name> <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet> </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--> </head><body><table>${str}</table></body></html>`; //下载模板 window.location.href = uri + this.base64(template) }, //输出base64编码 base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) } } } </script>
这种导出更适合普通文本文件的输出:txt、doc、csv
<template> <div> <p style="font-size: 20px;color: red;">使用table标签方式将json导出csv文件</p> <button @click='tableToExcel'>导出</button> </div> </template> <script> export default { methods:{ tableToExcel(){ //要导出的json数据 const jsonData = [ { name:'路人甲', phone:'123456789', email:'000@123456.com' }, { name:'炮灰乙', phone:'123456789', email:'000@123456.com' }, { name:'土匪丙', phone:'123456789', email:'000@123456.com' }, { name:'流氓丁', phone:'123456789', email:'000@123456.com' }, ] //列标题,逗号隔开,每一个逗号就是隔开一个单元格 let str = `姓名,电话,邮箱\n`; //增加\t为了不让表格显示科学计数法或者其他格式 for(let i = 0 ; i < jsonData.length ; i++ ){ for(let item in jsonData[i]){ str+=`${jsonData[i][item] + '\t'},`; } str+='\n'; } //encodeURIComponent解决中文乱码 let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str); //通过创建a标签实现 let link = document.createElement("a"); link.href = uri; //对下载的文件命名 link.download = "json数据表.csv"; document.body.appendChild(link); link.click(); document.body.removeChild(link); } } } </script>
<template> <div> <a href="javascript:;" id="download" @click="download()" download="download.doc">导出数据</a> </div> </template> <script> export default { data() { return { tableData:[ { "name": "测试", "type": "审判系统", "proj_id": 333 } ], }; }, methods: { download () { var obj = document.getElementById('download'); var str = "姓名,出生日期,地址\n";//标题 for (var i = 0; i < this.tableData.length; i++) { var item = this.tableData[i]; str += item.name + ',' + item.type + ',' + item.proj_id; str += "\n"; } str = encodeURIComponent(str); obj.href = "data:text/csv;charset=utf-8,\ufeff" + str; obj.download = "download.doc";//文件名称+格式 }, } } </script>
<template> <div> <button @click="download()">导出数据</button> </div> </template> <script> export default { data() { return { tableData:[ { "name": "测试", "type": "审判系统", "proj_id": 333 } ], }; }, methods: { download () { var str = "姓名,出生日期,地址\n";//标题 for (var i = 0; i < this.tableData.length; i++) { var item = this.tableData[i]; str += item.name + ',' + item.type + ',' + item.proj_id; str += "\n"; } console.log(str,555) let blob = new Blob([str]);//内容 let link = document.createElement("a"); link.href = window.URL.createObjectURL(blob); link.download = '20181114.csv';//文件名称 link.click(); }, } } </script>
手写一个导出方法:export2Excel ()
/* eslint-disable */ let idTmr; const getExplorer = () => { let explorer = window.navigator.userAgent; //ie if (explorer.indexOf("MSIE") >= 0) { return 'ie'; } //firefox else if (explorer.indexOf("Firefox") >= 0) { return 'Firefox'; } //Chrome else if (explorer.indexOf("Chrome") >= 0) { return 'Chrome'; } //Opera else if (explorer.indexOf("Opera") >= 0) { return 'Opera'; } //Safari else if (explorer.indexOf("Safari") >= 0) { return 'Safari'; } } // 判断浏览器是否为IE const exportToExcel = (data,name) => { // 判断是否为IE if (getExplorer() == 'ie') { tableToIE(data, name) } else { tableToNotIE(data,name) } } const Cleanup = () => { window.clearInterval(idTmr); } // ie浏览器下执行 const tableToIE = (data, name) => { let curTbl = data; let oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel let oWB = oXL.Workbooks.Add(); //获取workbook对象 let xlsheet = oWB.Worksheets(1); //激活当前sheet let sel = document.body.createTextRange(); sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中 sel.select; //全选TextRange中内容 sel.execCommand("Copy"); //复制TextRange中内容 xlsheet.Paste(); //粘贴到活动的EXCEL中 oXL.Visible = true; //设置excel可见属性 try { let fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls"); } catch (e) { print("Nested catch caught " + e); } finally { oWB.SaveAs(fname); oWB.Close(savechanges = false); //xls.visible = false; oXL.Quit(); oXL = null; // 结束excel进程,退出完成 window.setInterval("Cleanup();", 1); idTmr = window.setInterval("Cleanup();", 1); } } // 非ie浏览器下执行 const tableToNotIE = (function() { // 编码要用utf-8不然默认gbk会出现中文乱码 let uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>', base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))); }, format = (s, c) => { return s.replace(/{(\w+)}/g, (m, p) => { return c[p]; }) } return (table, name) => { let ctx = { worksheet: name, table } if (navigator.userAgent.indexOf("Firefox") > -1){ window.location.href = uri + base64(format(template, ctx)) } else { //创建下载 let link = document.createElement('a'); link.setAttribute('href', uri + base64(format(template, ctx))); link.setAttribute('download', name); // window.location.href = uri + base64(format(template, ctx)) link.click(); } } })() // 导出函数 export const export2Excel = (theadData, tbodyData, dataname) => { let re = /http/ // 字符串中包含http,则默认为图片地址 let th_len = theadData.length // 表头的长度 let tb_len = tbodyData.length // 记录条数 let width = 40 // 设置图片大小 let height = 60 // 添加表头信息 let thead = '<thead><tr>' for (let i = 0; i < th_len; i++) { thead += '<th>' + theadData[i] + '</th>' } thead += '</tr></thead>' // 添加每一行数据 let tbody = '<tbody>' for (let i = 0; i < tb_len; i++) { tbody += '<tr>' let row = tbodyData[i] // 获取每一行数据 for (let key in row) { if (re.test(row[key])) { // 如果为图片,则需要加div包住图片 tbody += '<td style="width:' + width + 'px; height:' + height + 'px; text-align: center; vertical-align: middle"><div style="display:inline"><img src=\'' + row[key] + '\' ' + ' ' + 'width=' + '\"' + width + '\"' + ' ' + 'height=' + '\"' + height + '\"' + '></div></td>' } else { tbody += '<td style="text-align:center">' + row[key] + '</td>' } } tbody += '</tr>' } tbody += '</tbody>' let table = thead + tbody // 导出表格 exportToExcel(table, dataname) }
需要导出图片:
// 获取sheet名称 const getSheetsName = (dataList)=> { let sheetsName = [] dataList.data.forEach((item) => { sheetsName.push(item.order.order_id) }) return sheetsName } // 获取文件名 const getFileName = ()=> { let fileName = '' let day = new Date().toLocaleDateString().split('/') if (parseInt(day[1]) < 10) { day[1] = '0' + day[1] } if (parseInt(day[2]) < 10) { day[2] = '0' + day[2] } let time = new Date().toTimeString().split(' ')[0].replace(':', '').replace(':', '') fileName = '送货单-' + day[1] + day[2] + time + '.xls' return fileName } // 创建文件流 const base64ToBlob =(base64Data)=> { let arr = base64Data.split(',') let mime = arr[0].match(/:(.*?)/)[1] let bstr = atob(arr[1]) let n = bstr.length let u8arr = new Uint8ClampedArray(n) while (n--) { u8arr[n] = bstr.charCodeAt(n) } return new Blob([u8arr], { type: mime }) } // 获取所有xml代码 const getMainXml = (sheetsName_g,sheetHtml_g,qrCodes_g)=> { let mainHtml = '' let sheets = '' let printFormula = '' // 打印固定头部 let codesXml = '' if (sheetsName_g.length > 0) { for (let i = 0; i < sheetsName_g.length; i++) { let name = sheetsName_g[i] let sheetItem = ` <x:ExcelWorksheet> <x:Name>${name}</x:Name> <x:WorksheetSource HRef=3D"dbzhao/sheet${name}.xml"/> </x:ExcelWorksheet>` sheets += sheetItem printFormula += ` <x:ExcelName> <x:Name>Print_Titles</x:Name> <x:SheetIndex>${i + 1}</x:SheetIndex> <x:Formula>=3D'${name}'!$1:$7</x:Formula> </x:ExcelName> <x:ExcelName> <x:Name>Print_Titles</x:Name> <x:SheetIndex>${i + 1}</x:SheetIndex> <x:Formula>=3D'${name}'!$1:7</x:Formula> </x:ExcelName>` let code = qrCodes_g[i] // 头部二维码 let codeXml = ` ------BOUNDARY_0008---- Content-Location: file:///C:/0E8D990C/dbzhao/code${name}.xml Content-Transfer-Encoding: base64 Content-Type: image/jpeg ${code}` codesXml += codeXml } } mainHtml = `MIME-Version: 1.0 X-Document-Type: Workbook Content-Type: multipart/related; boundary="----BOUNDARY_0008----" ------BOUNDARY_0008---- Content-Location: file:///C:/0E8D990C/dbzhao.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns=3D"http://www.w3.org/TR/REC-html40"> <head> <xml> <o:DocumentProperties> <o:Author>dbzhao</o:Author> <o:LastAuthor>dbzhao</o:LastAuthor> <o:Company>dbzhao</o:Company> <o:Version>1.0</o:Version> </o:DocumentProperties> </xml> <!--[if gte mso 9]> <xml> <x:ExcelWorkbook> <x:ExcelWorksheets>${sheets} </x:ExcelWorksheets> </x:ExcelWorkbook> ${printFormula} </xml> <![endif]--> </head> </html>` + sheetHtml_g + ` ${codesXml} ------BOUNDARY_0008------` return mainHtml } // 获取每个sheet的xml代码 const getSheetXml = (dataList)=> { let sheetHtml = '' let sheets = '' for (let i = 0; i < dataList.data.length; i++) { let name = dataList.data[i].order.order_id // MIME要求格式必须顶格……所以这里排版比较乱…… let sheetItem = ` ------BOUNDARY_0008---- Content-Location: file:///C:/0E8D990C/dbzhao/sheet${name}.xml Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="us-ascii" <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head> <xml> <x:WorksheetOptions> <x:ProtectContents>False</x:ProtectContents> <x:ProtectObjects>False</x:ProtectObjects> <x:ProtectScenarios>False</x:ProtectScenarios> </x:WorksheetOptions> </xml> <style> <!-- @page {mso-footer-data:"&C\\7B2C &P \\9875\\FF0C\\5171 &N \\9875"; margin:0.748in 0.195in 0.748in 0.195in; mso-header-margin:0.51in; mso-footer-margin:0.51in;} --> </style> </head> <body>` let table = ` <table border="1" width="100%" rull="all" style="border-collapse:collapse;font-family:宋体;font-size:12px;">` table += ` <tr> <td colspan="12" height="26" style="border-bottom:1px solid #fff;border-right:1px solid #fff;"></td> </tr> <tr> <td colspan="11" height="110" style="text-align:center;font-size:28px;font-weight:bold;background:#fff;vertical-align:middle;border-left:1px solid #fff;border-right:1px solid #fff;"> 重庆农业科技发展有限公司<br style='mso-data-placement:same-cell;'/>送货单 </td> <td style="border-right:1px solid #fff;"> <img src=3D'code${dataList.data[i].order.order_id}.xml' /> </td> </tr> <tr> <td colspan="5" style="font-size:13px;">客户:${dataList.data[i].order.user_name}</td> <td colspan="4" style="font-size:13px;">联系人:${dataList.data[i].order.shipping_man}</td> <td colspan="3" style="font-size:13px;">订单号:${dataList.data[i].order.order_id}</td> </tr> <tr> <td colspan="5" style="font-size:13px;">地址:${dataList.data[i].order.address}</td> <td colspan="4" style="font-size:13px;">固定电话:${dataList.data[i].order.flPhone}</td> <td colspan="3" style="font-size:13px;">手机:${dataList.data[i].order.shipping_phone}</td> </tr> <tr> <td colspan="5" style="font-size:13px;">下单日期:${dataList.data[i].order.create_time}</td> <td colspan="4" style="font-size:13px;">送货时间:${dataList.data[i].order.distribut_require}</td> <td colspan="3" style="font-size:13px;">结算方式:${dataList.data[i].order.sales_model}</td> </tr> <tr> <td colspan="12" height="5"></td> </tr> <tr> <td width="46" height="42" style="text-align:center;font-weight:bold;">序号</td> <td width="120" style="text-align:center;font-weight:bold;">产品名称</td> <td width="53" style="text-align:center;font-weight:bold;">规格</td> <td width="40" style="text-align:center;font-weight:bold;">单位</td> <td width="44" style="text-align:center;font-weight:bold;">订单量</td> <td width="72" style="text-align:center;font-weight:bold;">单价</td> <td width="56" style="text-align:center;font-weight:bold;">发货量1</td> <td width="56" style="text-align:center;font-weight:bold;">发货量2</td> <td width="54" style="text-align:center;font-weight:bold;">签收量</td> <td width="63" style="text-align:center;font-weight:bold;">退货量</td> <td width="95" style="text-align:center;font-weight:bold;">金额(元)</td> <td width="108" style="text-align:center;font-weight:bold;">备注</td> </tr>` if (dataList.data[i].products.length > 0) { for (let k = 0; k < dataList.data[i].products.length; k++) { let pro = dataList.data[i].products let receive = parseFloat(pro[k].big_deliver_num) + parseFloat(pro[k].small_deliver_num) let total = parseFloat(pro[k].price.split('/')[0].replace('元', '')) * receive let tr = ` <tr>` tr += ` <td min-height="20" style="text-align:center;font-size:13px;">${k + 1}</td> <td style="text-align:left;font-size:13px;">${pro[k].title}</td> <td style="text-align:center;font-size:13px;">${pro[k].spec}</td> <td style="text-align:center;font-size:13px;">${pro[k].product_unit}</td> <td style="text-align:center;font-size:13px;">${pro[k].buy_num}</td> <td style="text-align:center;font-size:13px;">${pro[k].price}</td> <td style="text-align:center;font-size:13px;">${pro[k].big_deliver_num}</td> <td style="text-align:center;font-size:13px;">${pro[k].small_deliver_num}</td> <td style="text-align:center;font-size:13px;">${receive}</td> <td style="text-align:center;font-size:13px;"></td> <td style="text-align:center;font-size:13px;">${total}</td> <td style="text-align:left;font-size:13px;">${pro[k].remark}</td> </tr>` table += tr } } table += ` <tr> <td colspan="10" height="20" style="text-align:center;font-size:13px;font-weight:bold;">应 收:</td> <td style="text-align:center;font-size:13px;">${dataList.data[i].order.total}</td> <td></td> </tr> <tr> <td colspan="10" height="20" style="text-align:center;font-size:13px;font-weight:bold;">实 收:</td> <td></td> <td></td> </tr> <tr> <td colspan="6" height="80" style="font-size:13px;vertical-align:middle;"> 公司地址:重庆市江津区双福国际农贸市场22A-004 <br style='mso-data-placement:same-cell;'/> 联系电话:13996198545 <br style='mso-data-placement:same-cell;'/> 备注:商品数量和计量单位按实际发货为准 </td> <td colspan="4" style="font-size:13px;vertical-align:middle;"> 送货人: <br style='mso-data-placement:same-cell;'/> <br style='mso-data-placement:same-cell;'/> 日 期 : </td> <td colspan="2" style="font-size:13px;vertical-align:middle;"> 签收人: </td> </tr> </table>` sheetItem += table + ` </body> </html>` sheets += sheetItem } sheetHtml = sheets return sheetHtml } export { getSheetsName, getFileName, base64ToBlob, getMainXml, getSheetXml, }
demo:
<template> <div> <el-button @click="exportExcel">导出Excel</el-button> <el-button @click="exportImg">导出Img</el-button> </div> </template> <script> import { export2Excel } from './ExportExcel.js' import { export_json_to_excel } from './Export2Excel.js' export default { data () { return { qrCodes_g: [ // 开头:data:application/vnd.ms-excelbase64, `data:application/vnd.ms-excelbase64,iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAYAAACtWK6eAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAyNpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADw/eHBhY2tldCBiZWdpbj0i77u/IiBpZD0iVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkIj8+IDx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IkFkb2JlIFhNUCBDb3JlIDUuNi1jMDE0IDc5LjE1Njc5NywgMjAxNC8wOC8yMC0wOTo1MzowMiAgICAgICAgIj4gPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4gPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wTU09Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9tbS8iIHhtbG5zOnN0UmVmPSJodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvc1R5cGUvUmVzb3VyY2VSZWYjIiB4bWxuczp4bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iIHhtcE1NOkRvY3VtZW50SUQ9InhtcC5kaWQ6OTk2QkI4RkE3NjE2MTFFNUE4NEU4RkIxNjQ5MTYyRDgiIHhtcE1NOkluc3RhbmNlSUQ9InhtcC5paWQ6OTk2QkI4Rjk3NjE2MTFFNUE4NEU4RkIxNjQ5MTYyRDgiIHhtcDpDcmVhdG9yVG9vbD0iQWRvYmUgUGhvdG9zaG9wIENDIChNYWNpbnRvc2gpIj4gPHhtcE1NOkRlcml2ZWRGcm9tIHN0UmVmOmluc3RhbmNlSUQ9InhtcC5paWQ6NjU2QTEyNzk3NjkyMTFFMzkxODk4RDkwQkY4Q0U0NzYiIHN0UmVmOmRvY3VtZW50SUQ9InhtcC5kaWQ6NjU2QTEyN0E3NjkyMTFFMzkxODk4RDkwQkY4Q0U0NzYiLz4gPC9yZGY6RGVzY3JpcHRpb24+IDwvcmRmOlJERj4gPC94OnhtcG1ldGE+IDw/eHBhY2tldCBlbmQ9InIiPz5WHowqAAAXNElEQVR42uxda4xd1XVe53XvvD2eGQ/lXQcKuDwc2eFlCAGnUn7kT6T86J/+aNTgsWPchJJYciEOCQ8hF+G0hFCIHRSEqAuJBCqRaUEIEbmBppAIBGnESwZje8COZ+y587j3PLq+ffadGJix53HvPevcuz60xPjec89ZZ+39nf04+9vLSZKEFArFzHA1BAqFEkShUIIoFEoQhUIJolAoQRQKJYhCoQRRKJQgCoUSRKFQKEEUCiWIQrFo+Gv/8/YH+f/nsMWSHHMChyhxqPTTdyncWyJ3ScD/ztipiB3wXSqu6P17avN+TyFC5ggv4tRnmoxWTP1+5F+Mz17GPvPl49EKBWd3UsfXllPiso8VcYtmPba3fNuKrBVXrGFCbrdPwXndFL49ltI367roOpSUI4pGypv9s7q+ltj6JxqOQ07Bo/DgxGb2/a8cX0CnAWXJ5etz2TqdHiXHKlKj9w6i9XX8Ic41DmI8FVHhmmXk85MmRhCzJoiTWnig9LfJRHihgydxzAxJhBr7Bh/hK3yu+p9568FliTJF2aKMZfVd/kQOcKP6OBmS9+Rjm4zJ6faoeN0gOUn61MncLX4CJ+MRhe+P/dRxhfew2Df4CF/hs4jWg8vQYUKYMuWyRRkLjeHQ8YP0Z9mekVjA8Qj3VVcuoeDiXu63lkUE0ym6FA5PXBaNVr7qtPumGyPR4Bt8hK/wWUR5chn6XJYoU5StUHL8l+XEx2axhkS6yk+chJuP4rXLyOkIKJkS0B67adcqfL/0Y4pixxSysK6V8Yl9Mz7i3272NRFlhzJsu24Z5l9E9Ahmwfrpoj7uw3fZtktsRZKjIXnndlLxin7+W8ZTBwPf6I+Tg9HwxK2Ob8citbCoBoaxBxMCvsFH+CqjHCtUvLzflKWUcpwB91gupG5f9/Rtx39ZZBtmWyJtphKzHTQW0diP36b4aJmcLj/zGaSkHJPb4SWFi/tOJd8bTqd9s48VBRh4RKeUX/vjgXg8cpyCmz05xkJylxSoa8M5RF0eJaVIIkGOsg2yTc3UgpD94psiWxEOqDNYoOIXuHnGwE5AXUTFi46FTnRw4l/dwEm7/pSxcYnCF/gE3zInh52RRJkVP7/MlKFQcgCbjifHTAQBfsb2qsgBO3e1Cpf3UXBej3nRJKKrxU/rcH/pKzz4vNIQuRJTEmZklbg6EL4SPsE3GQPzinmfhbJDGQolB+r8w58abs5y8DqRt4ABeptLRR7koY9NleybEYw/MPisvF/ayT1/SvDewcnIcG32wfiCAbEvoCZyGaGsitdyz6XdTctQJq6fcT5mloNfYvu5yFZkpEz+RT0UrFoqpxVBV+vQxIrkaPnrbqdvXs6hcjbU+Jq4Nvvwd/BFRNeq2npwWfkX95iyE9p6PM72P/MhCPANTBSKu5WITHcC074Y9CUTkYglKBgcV/aVtlM5Kpp/RHFjDdfka7MP/2wG6m72661QNigjlBXKTGBtsjWKNs5atCf44Uds3xc5YD8Wknd2BxWuGjCzIxLWQzlFj+IjU108OL7bafM5sm5DDdfka/8T+9AJXyTMpqFsUEYoK5SZ0NbjVlvX500Q4Ha2A+JuCcEvhVS8qp/8MzspHhMSfO7mVPaP35BMRp9JsCQldbX+hmvxNfnamzJfqVvtWnGZoGxQRigroYs6UbfvOGHn4ORVkTaIbEWwtqg3MNO+Zql0JGCdVuCayhDuG9uJB7vp+oR17FbZc+NauCauLWLmKkqXr6NsUEYoK6GtxwY6CXXnEs0n2faIHLCPhhR8bikFKwRN+xZddHWu5a7Ol9yCZ2ZwHKdOxufGNeKRqS/hmnLWW1VMmQSrl5oyEkqOPbZu02IJAsic9sU7B+5uF9cOmqUfeLOdOaAZYb/CA+M/Ic9NxUoYMNfD/PT84f7xB807EAnrrbgMUBZt1w1SEpCIqfjF1Om5EuQNth0iu1r8tPLP76LCpX2yWpHDk2dGH018p6brtD5hOHf04cR3okOTZ0lqPVAW3gVdlMhdrfsTW6drRhDgRrYJcbeKZQxTkenvegNt6YBQwrQvOxG+P3ZHEia9TuClS9Br1XKge8XnxLlxjelzZ/2w4tijDMxyoHIsVQg1zvYPcy7KeZx4jG2zyFakFJF7Whu1XT2QvhfJeryeVNdplYPo4Pi9hKd7VVxVC8O5cH4+N65hXgoKuGfEHmWAskjGxI49Ntu6XHOCAD9ie1PcLSepjDNY00fB8m6KpSyJx/jgg9LfJEfLK40818w+LXY5e5zKaMfKl+DcIlSCZp0cd3U59igDI4+WOa2LunvfvDoD9RrcNLqAjDy3yzfrtKqbAkggSDIZmSlYxzz9a8BaJ101zF2rh3BuSTJaCKGMDEGujHbedXch0X2ebbdEkkDC6a9cQoWVguS53P0JP5xcHY1W/tppD9KxgrdAw5QxnwPn4nOukrPeqkzBJb0m9oJltLtt3a07QYD1IkMAeS7/hw0BXMhzJwXJc/eV7kuiyIN8OOGuUhLP06JUeoxz4FxiZLRouTsDM9WO2OdBRtsIgrzHtk3kgH00JO+cTipc2S9jqyCaluf2xwcnfuB6LndHuEsSzdP4N/gtzoFzSZHRIsaQQiPmidyXgttsnW0YQYDvsh2ROGBPxkMqXjNA/qlCFsnZ8UdlX+kfk0pymlnMWH2JOBfz0sWI+C3OMS1dzPphhPVWHOPC5wdMzIUOzFFHb1lwB2ARF+ZOPt0gshWBPLe/wCRZlu6CIkSei/cE0fD4g2ZbVWceyxH5WPwGvzXrrSTJaDnG7oBoGS3qaCULggCPsv1W5IAd8tzLllJwvpx1WthMIfyg9OVotHy1WVQ4V37wsfgNfkuSZLQcW8Q4lruU/RVbRykrggDXiwwN3uQWnXTa1xMkz2W/on2lndNajpNtAGePw2/MOicBMlqs+8K7GBNbjrFgGe2iX0nUgiAvs+0S2YpgndaFPVRc3SdmVanZlfGjifOiw5PrT/oGvPpG/vDkEH4jZ70Vt86rl5rYimmdP41/s3Uzc4Isup9XNxwvz+0tyNAlONPrtO6hctR+QnluKqNt52O3pxvtClhvxTH0egtmEwbBMlrUxU21OFGtCHKYbavIATv3j90z26kIea4QZRtahfhIuT0anrjH7O3rpjNVHzPIaLG3Lh8Tj5TbRQihjlNyehxTwTLarbZOiiEIcBfbPnGhMtroChXW9JN/VqeYdyPEY4nwwPj6ZCL8C1T+T61JhDqRv8MxZgwlJG2BxzEsrBmgeEzseqt9ti6SNIIA8t6wm901eFDZ66d7M4UkQ56LVgTTvvtKaRqFqoTWymjxGb6LpUzrImYcuzaOIWKJmAptPWpaB2sd+V+yvSB1wB6s7qXgwiUyBpbJdBqFq6MjU18mKCKhRsTyEbx558/wnRmYJzLiV+DYBat6JQ/MX7B1UCxBAKHy3IQrH6W7MhY9MWkUMNAN948/8Mm35/jMDIKlpC3gmBWQtsAjifkE61b36kGQP7DdL7KrVZXnXiYpjYKZxj09Gh7f4kB4yIa/8ZmU1brIIYiYIXaJ3Nbjflv3xBME+DZbSVwIzfIIK89dJkSea18Ihu+XflD9yPztCJnW5Ri5VRntpNh8giVb5ygvBIHu9yaRrchYRO6fFU0CSTPQlDLte6zshx9O3g3D3yJajySd4EDaAsQMsRPaetxk61zty+YTCXRqjf9jO19cOLnyYV+p8QffpcreMXJ7BeRgh77Ds6SIYhGbMBgB2tld1DW0nGL4VxbZfKBbdUHdhol1dl7mOi0MOjttGgWT11lAwU9r1mMSsX0oxwSxgYyWOvKXtiAvBPkV239I7GqZdVqX9FDw2V5+UoYipn2nt/WRMK3LMQlW9poYCZ7WfcrWsdwSBNggMrRYdcLdhjas0+q28lzJOc8bOU7jWLh2AwzEyLxclYm6Z2ZuBEE+YLtTZEVA9tzPdBh5biJ3q5rGD8yRjXbNAPkcm0RuyjTUqf3NQBDge2yHJFaGeDyi4tUD5J3WIXmzs8Y9NDgG3un80OCYIDZCHxqHbJ2iZiEIGmnB8twgzYIkd7vMxiBON59GLJyBQLKMdiM1qOPXyMn2f2f7X5EDdshzkUbhAtED0oZMXCAGiIXgtAW/YXusURdr9NsoufLcgmP20zKy2ErrNSNGRuunMUAshL7zABq61q/RBPkd2yNSn57+X3ZTQZA8t7H3H5p7RwwEt6KP2DrUtAQBIIUsiwt99Kf+tydFntuocVhVRltNWyBTRlumGslopRNkhO1mkRVlLCT3jHYzqyU48WSN+1ZWRou0BZDRyp3Ju9nWnaYnCHA3216JlQWy0gKy557dJSaNQn0nKNL1VrhnwTLavbbOUKsQBBApzzVpFHqsPFdIGoW6AfeG7cMwrcv3TC0io80LQZ5me07kU3WkYqSlhYvkpFGoz8C8bO7RyGjlpi14ztaVliMIIFOeizQKbpI+WdsDGfLcWvcmsaK53b4gdUW3lENZXjxrgrzNdq/IAftohbzzOql4eV/zjUUcu96K7w33KFhGi7rxVisTBEBSxWPiiqYqz71mGfmDQuS5tSIHstHyPZnd7+XKaI+RgKSxEggySWmKaXkVaSwi5xSbRmGiSdZpxVZGy/eEexMso73R1o2WJwiwk+11kQNZrNO6oo+Cc7vz39Wy07q4l+CKfnNvQu/ndVsnSAkifcCOAXq7R8W1y9JdRvI87QvfnTRtgdPeujLavBLkv9meEPnUHS2Tf1EPFT67lOKRnE77munrsrkH/+IeydPXqAO/VoLMDMhz5T2irTzXpFHoKeRPnluV0XYX0mlduTLamIRJtKUR5CDbbSIrGPfX/eUdVFyTQ3luku6OaNIW/HmH5LQFt9k6oAQ5Ab7PNiyxkmGndUhRvTNyJM9F1wrZaM9IZbQmG63MocewxIejRIKg+DaKbEXGI3KWBtT2hUFKyonUZeEfB3xkX4vsM3wXvIx/IwmMqCu0WH/B9qLIpzG6Wp/rpWBFj/x1WnaCAb4G7LPgad0XbZmTEmTukDnti0yzgZvKcwNPtDzXyGjZR5ONFincVEbbVAR5je0hkU/lkTL5F3TZzQ2EvjysJr1hH/0LuiVPTz9ky1oJsgB8iwQsN5hplISns5Hn9hXl9eurMlr2zUzrVsQuk5m0ZUxKkIXhKNsWkQN2yHNPhzx3WbqQMRZGYCOjXWZ8FDzjtsWWsRJkEfgh2zvyOvhWnovsucu75GTPtdlo4RN8i+W+s3nHli0pQRaPIXEeVeW53V46YJciz2Uf4IvxiX0juW/9h/JQ8fJCkGfZnpE5YK9QsHIJBZcIkOdW141d3Gt8EiyjfcaWqRKk6Z84kOc6duODjmzluUZGyz4g6Q18UhltaxHkXbbtIgfsRyvknQt5bobZc6dltP3Gl0SudmW7LUslSJ1mPUbFeWVUepDnDpB3SgazRtW0BXxt+ABfhE7rypyVbCKCTLF9U2QrgjQKg3b7zskGv3eI0+XsuDZ8EJy2YJMtQyVIHfEztldFDtghz728j4LzGphGoZq2gK9ZMDuwiH3ngTJ7OG+VLY8EAeTKc9ts9lwk42zEOi2st+JrYZIA1xYso12Xx4qWV4K8xPZzka3ISCrPDVY1YJ1WtfVYZWW0ctdbPW7LTAnSQHyDJCoykEYhTNdpuUsK6YDZqQ85cG5cw6y3CsWmLYBXG/NayfJMkI8oVR/KG7AfC8k7u4MKVw2kM1r1eB2RpDNXuAauJVhGe6stKyVIBrid7YA4r6o5N5BG4cxOI3mtaeWtymj53LiG4FwmKJs78lzB8k4QVIsN4ryqynN7AzP1ShXIc2tYg3GuSpJO6/aKltHK3KWmhQgCPMm2R+SAfTSkANlzV9Rw2rc6MDcyWtHZaPfYsiElSPaQOYVYiSnxiIprB8kpeGn+v8U2mZD8FjxzTpybKjqtqwQ5Od5g2yGyq4Xsued3UeHSvsW3IlUZLZ8L5xSctmCHLRMliCBgN/AJcV7F6SpbjBe8gUWkUaimLeBzmOUsU2JltOMkcbd+JQiNkYB8ErNVbPe0Nmq72i4kXMiwNUnfe+AcOJfgfCWbbVkoQQTiR2xvivPKynODNX0ULF9AGoVq2gL+Lc4hWEaL2N/XTBWq2Qgic3BYled2+ekeVfOV51az0WKNF59DsIx2XbNVpmYkyPNsuyWSBBJYf+USKsxHnlvNRsu/8WXLaHfb2CtBcoD1Ir2CPJf/wxSt2xmkupGT9c6QtoCPNdO66FfJldGub8aK1KwEeY9tm8gB+2hI3jmdVLii/+RbBdktfHAsfpPIfSm4zcZcCZIjfJftiMQBO1IQQBrrn3qCRYZ20SOOMTLacbHrrRDjW5q1EjUzQbiTTzeIbEUgz+232XNne59RfX+CbLT9omW0iHFFCZJPPMr2W5EDdshzL1tKwfkzrNOqrrfi73CMYBntKzbGpATJL64X6RXWZRVtxlnP+VgaBZO2wEu/wzGatkAJUk+8zLZLZCuCdVoXciux+rhVuXYVMD7Dd7Hc9Va7bGyVIE0Amf3kaXnuIHm9qTwXhr/xmWAZbUXk+E4JsmAcZtsqcsAOee6Z7VS08lwY/sZngmW0W21MlSBNhLvY9onzCqtIxipUuKqf3L6iMfyNz4RO6+6zsWwJ+NRawNvep8S1IhMxucie+8VT0o+6PIqPiB17rG+lCtNqBPkl2wts14gbsCONwqVLzT8Fr7d6wcawZeBS60Hm1GSSTu+a6d5EY6cEyQ5/YLtf4oCd4iQ1ma3H/TZ2SpAWwLfZSqSYK0o2ZqQEaQ1AN32T1vs54yYbMyVIC+GBVuwyLLBL+kCr3rzb4oV/vdZ/jZESZHb8iqS9F5GFp2yMlCAtjCENgcZGCTI79rPdqWH4FO60sVGCKOh7bIc0DNM4ZGNCShAFEFKOsyDVARttTJQgGoJpPMb2Gw2DicFjGgYlyExYpyHQGChBZsfv2B5p4ft/xMZAoQSZFZso3TKo1VC2965QgpwQI2w3t+B932zvXaEEOSnuZtvbQve7196zQgkyZ6zXe1UoQWbH02zPtcB9PmfvVaEEmTeG9B6VIIrZ8RbbvU18f/fae1QoQRYMJKU81oT3dYwkJj1VguQOk9REaY2Pw4323hRKkEVjJ9vrTXQ/r9t7UihBaobr9V6UIIrZ8Wu2J5rgPp6w96JQgtQcG2jmhGl5QWzvQaEEqQsOst2WY/9vs/egUILUtZIN59Dv4ZyTWwmSEyDnUx7luRtJar4qJUjT4RdsL+bI3xetzwolSMOwTn1Vgihmx2tsD+XAz4esrwolSMPxLZK9XGPS+qhQgmSCo2xbBPu3xfqoUIJkhh+yvSPQr3esbwolSOYYUp+UIIrZ8SzbM4L8ecb6pFCC6BNbWw8lSB7wLtt2AX5st74olCDikPWskfRZNSVIi2OKst2+c5P1QaEEEYuH2V7N4Lqv2msrlCDisa5FrqkEUSwIL7E93sDrPW6vqVCC5AaN0l/kVZ+iBGlxfMR2awOuc6u9lkIJkjvcwXagjuc/YK+hUILkEgnVdxeRDfYaCiVIbvEk2546nHePPbdCCZJ7rMvJORVKkEzwBtuOGp5vhz2nQgnSNMBu6uM1OM84Nedu80qQFscY1SYfx2Z7LoUSpOlwH9ubi/j9m/YcCiWIDth1YK4EaUU8z7Z7Ab/bbX+rUII0PdY36DcKJUgu8R7btnkcv83+RqEEaRncwnZkDscdsccqlCAthQrbDXM47gZ7rEIJ0nJ4lO2VE3z/ij1GoQRpWaxb4HcKJUhL4GW2XTN8vst+p1CCtDw+Oc6Y6/hEoQRpCRxm23rcv7fazxRKEIXFXZRuwBDZvxUC4GsIREHflguDkyQqaVYotIulUChBFAoliEKhBFEolCAKhRJEoVCCKBRKEIVCCaJQKJQgCoUSRKFQgigUShCFIhP8vwADACog5YM65zugAAAAAElFTkSuQmCC`, `iVBORw0KGgoAAAANSUhEUgAAAGQAAABkCAYAAABw4pVUAAAEFUlEQVR4Xu2b0Y6rMAxEL///0V3pvkGlHh2NQ8Pu9NXg2DP2OAF6vF6v17/+tkHgKCHbcPE/kBKyFx8lZDM+SkgJ2Q2BzeLpDCkhmyGwWTjYIcdx3BoyHYuu8aTXb5cfnUO2C/hSICVkcb+kAF/Do47aruDaIZtJsiWEKtg2kK1gWp/82Q6y+aT+9VAnQGwCBCDZYwDkTJrO7y3+dshZsu4uuBJyQcB24PYdYncp1wq0gNjrCUDyd3t+qWTdHvCw5peQywtKAiQd2u0QQKCS9fmNebztXS1ZqzuI/K/Ob3yXtTpgAiyVNPK/Or8SIre9JeTmod8OCQEniaJdld1UtEPkd3urAVvt/3EzhCp+NWCr/ZcQKZmPI4QqmOzfHqq0PsVPdus/PhhSQGSngFdXKK1P8ZPd+i8hww8raRdI71tKyNMJoRZN7ek5Ib0/jZ/uH+8QWjC1p4Cm96fx0/0lBCTJbhoIcLKXkN9GCDH+bbutcKrQr+dD79S/HSCtX0IIoZvtJeRmwGm5P0eITZgApG0p3X+100yg+CmeaTvlp0/q5JDslCDdX0KG/0FVQj6XXDskfD9CBUaS+vYwMt320uPl1RpOEkbx2aezlA9JLhGEHUILUMKUgK2waX8l5IJACTkD0g65FEja8aQosWTZAHHB4V2bBYDyIQmjmUXxkB07hBIgu02QArZ2K4k2XipAG28JgQ/z7CbCEqC3vdQBZLcVlyZEkjId7+0dQoBSQLbCUsAoHiJ8Ol6djz0Y6gVgiE9rfAkZ1uCU8BJSQk4qGHe8lax0ptj7SdNpJsQAScmleMiO2150EH75RxJVQogB+aiB3JWQm59llRBCYJgQt5y/enUH0UGSZp7NiHaB8QyxAdnrS4hFbPH1JWQxwNZ9CYFdlAXUXk/nhlTzU/9UIDbftxlFB8P0HGADTAGjIZz6LyHhSZkApIIjAmnXZAsSd1kUsF2QricAKlnhoxEigCqYJIj8W7uteBs/xaM7xAaMAUjCV3esza+ELP5qpYS0Q04iEkuWlRAa2mQnCaT7qQNIglI7xV9C4CBsCSbCSkgoiSUE/q9BFWYBpG219ffnOySdCSkh9qBKBfX4GVJCbpYU2sWVkBLyUXWoQH6dZNGQJU23Q5euJzsREL8PsYCkAFHCVtLIXzrk2yHyfUkJuZQcdVhqTzvy8R1iNTJNmNaLJWNxx1H88TmEFiA7SQjNCOoIWp8KhPxT/Hp9+5FDWoEEAEkWJZjGRwVA8cXrl5AzxY8jhCo0tdsKXA0gSZJdn/DRM4QcpvYSAqJHFZASkA5Nis8SPD3j7Exph8j/SBLBVGBUwEgIOah9FoESMotn7K2ExBDOOighs3jG3kpIDOGsgxIyi2fsrYTEEM46KCGzeMbefgB5wi8+XtSt2wAAAABJRU5ErkJggg==` ], tableData: [ { 'index': '0', "nickName": "沙滩搁浅我们的旧时光", "name": "小明" }, { 'index': '1', "nickName": "女人天生高贵", "name": "小红" }, { 'index': '2', "nickName": "海是彩色的灰尘", "name": "小兰" } ] } }, methods: { exportImg () { // tHeader和tbody的数据需要一一对应 let tHeader = [ '鲜花', '颜色', '照片' ] let tbody = [ { name: '玫瑰花', color: '红色', pic: 'https://ss1.bdstatic.com/70cFuXSh_Q1YnxGkpoWK1HF6hhy/it/u=2801998497,4036145562&fm=27&gp=0.jpg' }, { name: '菊花', color: '黄色', pic: 'https://ss1.bdstatic.com/70cFuXSh_Q1YnxGkpoWK1HF6hhy/it/u=1506844670,1837003941&fm=200&gp=0.jpg' }, { name: '牵牛花', color: '紫色', pic: 'https://ss1.bdstatic.com/70cFuXSh_Q1YnxGkpoWK1HF6hhy/it/u=3056120770,1115785765&fm=27&gp=0.jpg' }, { name: '梅花', color: '白色', pic: 'https://ss1.bdstatic.com/70cFvXSh_Q1YnxGkpoWK1HF6hhy/it/u=2700343322,3431874915&fm=27&gp=0.jpg' }, { name: '桃花花', color: '粉色', pic: 'https://ss1.bdstatic.com/70cFvXSh_Q1YnxGkpoWK1HF6hhy/it/u=602076004,4209938077&fm=27&gp=0.jpg' } ] export2Excel(tHeader, tbody, '我爱花朵') }, //导出的方法 exportExcel () { require.ensure([], () => { const tHeader = ['序号', '昵称', '姓名']; // 上面设置Excel的表格第一行的标题 const filterVal = ['index', 'nickName', 'name']; // 上面的index、nickName、name是tableData里对象的属性 const list = this.tableData; //把data里的tableData存到list const data = this.formatJson(filterVal, list); export_json_to_excel(tHeader, data, '列表excel'); }) }, formatJson (filterVal, jsonData) { return jsonData.map(v => filterVal.map(j => v[j])) } } } </script>
二、通过插件生成excel:
import XLSX from "xlsx"; require('file-saver'); require('./Blob'); function generateArray(table) { var out = []; var rows = table.querySelectorAll('tr'); var ranges = []; for (var R = 0; R < rows.length; ++R) { var outRow = []; var row = rows[R]; var columns = row.querySelectorAll('td'); for (var C = 0; C < columns.length; ++C) { var cell = columns[C]; var colspan = cell.getAttribute('colspan'); var rowspan = cell.getAttribute('rowspan'); var cellValue = cell.innerText; if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue; //Skip ranges ranges.forEach(function (range) { if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) { for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null); } }); //Handle Row Span if (rowspan || colspan) { rowspan = rowspan || 1; colspan = colspan || 1; ranges.push({s: {r: R, c: outRow.length}, e: {r: R + rowspan - 1, c: outRow.length + colspan - 1}}); } ; //Handle Value outRow.push(cellValue !== "" ? cellValue : null); //Handle Colspan if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null); } out.push(outRow); } return [out, ranges]; }; function datenum(v, date1904) { if (date1904) v += 1462; var epoch = Date.parse(v); return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000); } function sheet_from_array_of_arrays(data, opts) { var ws = {}; var range = {s: {c: 10000000, r: 10000000}, e: {c: 0, r: 0}}; for (var R = 0; R != data.length; ++R) { for (var C = 0; C != data[R].length; ++C) { if (range.s.r > R) range.s.r = R; if (range.s.c > C) range.s.c = C; if (range.e.r < R) range.e.r = R; if (range.e.c < C) range.e.c = C; var cell = {v: data[R][C]}; if (cell.v == null) continue; var cell_ref = XLSX.utils.encode_cell({c: C, r: R}); if (typeof cell.v === 'number') cell.t = 'n'; else if (typeof cell.v === 'boolean') cell.t = 'b'; else if (cell.v instanceof Date) { cell.t = 'n'; cell.z = XLSX.SSF._table[14]; cell.v = datenum(cell.v); } else cell.t = 's'; ws[cell_ref] = cell; } } if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range); return ws; } function Workbook() { if (!(this instanceof Workbook)) return new Workbook(); this.SheetNames = []; this.Sheets = {}; } 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.cha
上一篇: 创建React项目