导出带图片的Excel——OOXML文件分析
需求：
普通js导出文件excel具有兼容性问题，通过js-xsl导出文件API未找到导出图片的方案，实例过少，因此针对07年后以.xlsx后缀的excel文件，通过修改后缀.zip参考文件模板来实现完整OOXML文档下载。
OOXML分析xl目录下的文件，如图：http://officeopenxml.com/SScontentOverview.php
主要数据文件在worksheet目录下面
styles.xml里面存放着excel的样式数据:cellStyle
很容易看出包含，字体，边框，单元格样式信息。
sharedStrings.xml存储的excel中的字符串，excel中的字符串都是放在这个里面，共享字符串，所以往往excel的文件会比txt小。
worksheets里面是excel的sheet文件
展开单元格cols、数据sheetData:
其中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.charCodeAt(i) & 0xFF; return buf; } export function export_table_to_excel(id) { var theTable = document.getElementById(id); console.log('a') var oo = generateArray(theTable); var ranges = oo[1]; /* original data */ var data = oo[0]; var ws_name = "SheetJS"; console.log(data); var wb = new Workbook(), ws = sheet_from_array_of_arrays(data); /* add ranges to worksheet */ // ws['!cols'] = ['apple', 'banan']; ws['!merges'] = ranges; /* add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'}); saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx") } function formatJson(jsonData) { console.log(jsonData) } export function export_json_to_excel(th, jsonData, defaultTitle) { /* original data */ var data = jsonData; data.unshift(th); var ws_name = "SheetJS"; var wb = new Workbook(), ws = sheet_from_array_of_arrays(data); /* add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'}); var title = defaultTitle || '列表' saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), title + ".xlsx") }
demo：
<template>
<div>
<el-button
style="margin-bottom:10px"
id="kp_but_1745"
type="primary"
@click="exportExcelChange"
>导出Excel</el-button>
</div>
</template>
<script>
import XLSX from "xlsx";
import _ from "lodash";
import moment from 'moment';
export default {
data () {
return {
tableData: [
{ "功能模块与路径": "\\APP接口测试示例\\登录接口", "用例编号": "s_001", "前置条件": "第一个为前置条件，最后一个为预期结果，需要带相应关键字", "用例名称": "用例编写示例，用例必须有叶子结节", "操作步骤": "操作步骤直接填写，每个步骤一行", "预期结果": "用例必须有叶子结点", "标记": "", "优先级": "3", "用例属性": "", "编制人员": "", "用例设计时间": "2019-02-15", "备注": "" },
{ "功能模块与路径": "", "用例编号": "", "前置条件": "", "用例名称": "", "操作步骤": "操作步骤需要按顺序排序", "预期结果": "", "标记": "", "优先级": "", "用例属性": "", "编制人员": "", "用例设计时间": "", "备注": "" }, { "功能模块与路径": "\\APP接口测试示例\\登录接口\\身份证类型登录", "用例编号": "s_002", "前置条件": "我是前置条件，需要“前置条件”为关键字", "用例名称": "身份证登录", "操作步骤": "1，操作步骤1，按次序排列", "预期结果": "我是预期结果", "标记": "", "优先级": "3", "用例属性": "", "编制人员": "", "用例设计时间": "2019-02-15", "备注": "" },
{ "功能模块与路径": "", "用例编号": "", "前置条件": "", "用例名称": "", "操作步骤": "2，操作步骤2", "预期结果": "", "标记": "", "优先级": "", "用例属性": "", "编制人员": "", "用例设计时间": "", "备注": "" }, { "功能模块与路径": "", "用例编号": "", "前置条件": "", "用例名称": "", "操作步骤": "3，我是操作步骤3，按次序排列", "预期结果": "", "标记": "", "优先级": "", "用例属性": "", "编制人员": "", "用例设计时间": "", "备注": "" },
{ "功能模块与路径": "\\APP接口测试示例\\登录接口\\身份证类型登录", "用例编号": "s_003", "用例名称": "用例名称一定要长-登录失败", "操作步骤": "我是操作步骤", "预期结果": "如果这个是预期结果，则上层为用例", "标记": "", "优先级": "3", "用例属性": "", "编制人员": "", "用例设计时间": "2019-02-15", "备注": "" }, { "功能模块与路径": "\\APP接口测试示例\\登录接口", "用例编号": "s_004", "用例名称": "账户类型登录", "预期结果": "账户为空3", "标记": "", "优先级": "3", "用例属性": "", "编制人员": "", "用例设计时间": "2019-02-15", "备注": "" }
],
column01: ['类型', '名称', '描述'],
table01: [
{ type: "string", name: "hell", desc: "你好" },
{ type: "number", name: "111", desc: "数字111" },
{ type: "string", name: "qq", desc: "QQ号" }
],
column02: ['姓名', '年龄', '性别'],
table02: [
{ name: "z3", age: 16, sex: "男" },
{ name: "l4", age: 20, sex: "女" },
{ name: "w5", age: 24, sex: "男" },
{ name: "z6", age: 23, sex: "女" },
{ name: "ss", age: 10, sex: "不男不女" }
]
}
},
methods: {
exportExcelChange () {
const table01 = this.outputXlsxFile(this.column01, this.table01)
const table02 = this.outputXlsxFile(this.column02, this.table02)
// console.log(table01, "数据1")
// console.log(table02, "数据2")
// const worksheet = XLSX.utils.aoa_to_sheet([this.column02, this.table02]);//数组映射A1:A2
// console.log(worksheet, "数据3")
let new_workbook = XLSX.utils.book_new();
// const fileName = "接口协议"+moment().format('YYYY-MM-DD,HH:mm:ss');
XLSX.utils.book_append_sheet(new_workbook, table01, "数据信息");
XLSX.utils.book_append_sheet(new_workbook, table02, "人物信息");
console.log(new_workbook, "输出数据格式")
// 模拟数据
this.downloadExl(new_workbook)
},
outputXlsxFile (columns, data) {
let headObj = {},//生成表头
indexObj = {},//生成表内数据
renge = {};//计算出范围A1:F4
//********************************01：headObj */
for (let i = 0; i < columns.length; i++) {
let str = String.fromCharCode(65 + i) + 1;
if (i > 26) {
str = String.fromCharCode(65) + String.fromCharCode(65 + i - 26) + 1;
}
if (i > 52) {
this.$message.warning("表头太长，请做适当删减！")
}
headObj[str] = { v: columns[i] };
}
//********************************02:indexObj */
const indexArr = [];
for (let e = 0; e < data.length; e++) {
for (let j = 0; j < columns.length; j++) {
let index = String.fromCharCode(65 + j) + (e + 2);
indexArr.push(index);
}
}
for (let i = 0; i < indexArr.length; i++) {
indexObj[indexArr[i]] = { v: "" };
}
const excelArr = [];
for (let r = 0; r < data.length; r++) {
let rec = data[r];
for (let ii in rec) {
excelArr.push(rec[ii]);
}
}
for (let ind = 0; ind < indexArr.length; ind++) {
let j = 0;
for (let jj in indexObj) {
if (indexArr[ind] == jj) {
indexObj[jj].v = excelArr[j];
break;
}
j++;
}
}
//********************************03:renge */
let refS = 'A1', refE = String.fromCharCode(65 + columns.length - 1) + (data.length + 1);//范围{!ref:min:max}
renge = { '!ref': refS + ':' + refE };
//1.Lodash和Underscore是两款javaScript工具库：输出excal格式的数据
// let _data=underscore.extend(headObj,indexObj,renge);
// let _data=_.assignIn(headObj,indexObj,renge);
//2.ES6直接合并
let _data = Object.assign(headObj, indexObj, renge);
return _data
},
downloadExl (json, type,fileName,mySheet) {
// 配置文件类型
const wopts = { bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true }
// var tmpdata = json[0];
// json.unshift({});
// var keyMap = []; //获取keys
// for (var k in tmpdata) {
// keyMap.push(k);
// json[0][k] = k;
// }
// var tmpdata = [];//用来保存转换好的json
// json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
// v: v[k],
// position: (j > 25 ? this.getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
// }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
// v: v.v
// });
// var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
// tmpdata["B1"].s = { font: { sz: 14, bold: true, color: { rgb: "FFFFAA00" } }, fill: { bgColor: { indexed: 64 }, fgColor: { rgb: "FFFF00" } } };//<====设置xlsx单元格样式
// tmpdata["!merges"] = [{
// s: { c: 1, r: 0 },
// e: { c: 4, r: 0 }
// }];//<====合并单元格
// const sheet1 = mySheet || 'sheet1'
// var tmpWB = {
// SheetNames: [sheet1], //保存的表标题
// Sheets: {
// sheet1: Object.assign({},
// tmpdata, //内容
// {
// '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
// })
// }
// };
const tmpWB = json
const tmpDown = new Blob([this.s2ab(XLSX.write(tmpWB,
{ bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
))], {
type: ""
});
this.saveAs(tmpDown, fileName || "未命名" + '.' + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
},
// 1.下载功能
saveAs (obj, fileName) {
var tmpa = document.createElement("a");
tmpa.download = fileName || "未命名";
// 兼容ie
if ("msSaveOrOpenBlob" in navigator) {
window.navigator.msSaveOrOpenBlob(obj, fileName || "未命名" + ".xlsx");
} else {
tmpa.href = URL.createObjectURL(obj);
}
tmpa.click();
setTimeout(function () {
URL.revokeObjectURL(obj);
}, 100);
},
// 2.获取26个英文字母用来表示excel的列
getCharCol (n) {
let temCol = '',
s = '',
m = 0
while (n > 0) {
m = n % 26 + 1
s = String.fromCharCode(m + 64) + s
n = (n - m) / 26
}
return s
},
//3.
s2ab (s) {
if (typeof ArrayBuffer !== 'undefined') {
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;
} else {
var buf = new Array(s.length);
for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
}
}
}
</script>
<style scoped>
</style>
手写OOXML模板
1.静态内容模板：
const Content_Types = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="png" ContentType="image/png" />
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" />
<Default Extension="xml" ContentType="application/xml" />
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" />
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet3.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet4.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet5.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/worksheets/sheet6.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml" />
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" />
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" />
<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing2.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing3.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing4.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing5.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/xl/drawings/drawing6.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml" />
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml" />
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" />
</Types>
`;
const relsData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"
Target="docProps/app.xml" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"
Target="docProps/core.xml" />
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
Target="xl/workbook.xml" />
</Relationships>
`;//2
const appData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>工作表</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>6</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="6" baseType="lpstr">
<vt:lpstr>k=3</vt:lpstr>
<vt:lpstr>k=4</vt:lpstr>
<vt:lpstr>k=5</vt:lpstr>
<vt:lpstr>k=6</vt:lpstr>
<vt:lpstr>k=7</vt:lpstr>
<vt:lpstr>k=8</vt:lpstr>
</vt:vector>
</TitlesOfParts>
<Company></Company>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>16.0300</AppVersion>
</Properties>
`;
const coreData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:creator>v_dbzhao(赵东波)</dc:creator>
<cp:lastModifiedBy>v_dbzhao(赵东波)</cp:lastModifiedBy>
<dcterms:created xsi:type="dcterms:W3CDTF">2019-10-22T06:23:04Z</dcterms:created>
<dcterms:modified xsi:type="dcterms:W3CDTF">2019-10-22T09:08:15Z</dcterms:modified>
</cp:coreProperties>
`;//4
const stylesData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="x14ac x16r2 xr" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main"
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision">
<fonts count="2" x14ac:knownFonts="1">
<font>
<sz val="11" />
<color theme="1" />
<name val="等线" />
<family val="2" />
<charset val="134" />
<scheme val="minor" />
</font>
<font>
<sz val="9" />
<name val="等线" />
<family val="2" />
<charset val="134" />
<scheme val="minor" />
</font>
</fonts>
<fills count="2">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="gray125" />
</fill>
</fills>
<borders count="2">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
<border>
<left style="thin">
<color auto="1" />
</left>
<right style="thin">
<color auto="1" />
</right>
<top style="thin">
<color auto="1" />
</top>
<bottom style="thin">
<color auto="1" />
</bottom>
<diagonal />
</border>
</borders>
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0">
<alignment vertical="center" />
</xf>
</cellStyleXfs>
<cellXfs count="3">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
<alignment vertical="center" />
</xf>
<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1">
<alignment vertical="center" />
</xf>
<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1">
<alignment vertical="center" />
</xf>
</cellXfs>
<cellStyles count="1">
<cellStyle name="常规" xfId="0" builtinId="0" />
</cellStyles>
<dxfs count="0" />
<tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16" />
<extLst>
<ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1" />
</ext>
<ext uri="{9260A510-F301-46a8-8635-F512D64BE5F5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">
<x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1" />
</ext>
</extLst>
</styleSheet>
`;
const workbookData = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6"
xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2">
<fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="21328" />
<workbookPr defaultThemeVersion="166925" />
<mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
<mc:Choice Requires="x15">
<x15ac:absPath url="F:\桌面文档\Desktop\" xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac" />
</mc:Choice>
</mc:AlternateContent>
<xr:revisionPtr revIDLastSave="0" documentId="13_ncr:1_{D386BEC6-9E8D-4A6C-9BC7-09EF2C7D6400}" xr6:coauthVersionLast="41"
xr6:coauthVersionMax="41" xr10:uidLastSave="{00000000-0000-0000-0000-000000000000}" />
<bookViews>
<workbookView xWindow="3675" yWindow="660" windowWidth="21600" windowHeight="11385" xr2:uid="{AB6A0709-83A4-4152-867C-37AB087503A3}" />
</bookViews>
<sheets>
<sheet name="k=3" sheetId="1" r:id="rId1" />
<sheet name="k=4" sheetId="2" r:id="rId2" />
<sheet name="k=5" sheetId="3" r:id="rId3" />
<sheet name="k=6" sheetId="4" r:id="rId4" />
<sheet name="k=7" sheetId="5" r:id="rId5" />
<sheet name="k=8" sheetId="6" r:id="rId6" />
</sheets>
<calcPr calcId="191029" />
<extLst>
<ext uri="{140A7094-0E35-4892-8432-C4D2E57EDEB5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main">
<x15:workbookPr chartTrackingRefBase="1" />
</ext>
<ext uri="{B58B0392-4F1F-4190-BB64-5DF3571DCE5F}" xmlns:xcalcf="http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures">
<xcalcf:calcFeatures>
<xcalcf:feature name="microsoft.com:RD" />
<xcalcf:feature name="microsoft.com:FV" />
</xcalcf:calcFeatures>
</ext>
</extLst>
</workbook>
`;//8
const workbookXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId8" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" />
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet3.xml" />
<Relationship Id="rId7" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" />
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml" />
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml" />
<Relationship Id="rId6" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet6.xml" />
<Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet5.xml" />
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet4.xml" />
<Relationship Id="rId9" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
Target="sharedStrings.xml" />
</Relationships>
`;
const drawing1Data = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
<xdr:twoCellAnchor editAs="oneCell">
<xdr:from>
<xdr:col>9</xdr:col>
<xdr:colOff>0</xdr:colOff>
<xdr:row>0</xdr:row>
<xdr:rowOff>161925</xdr:rowOff>
</xdr:from>
<xdr:to>
<xdr:col>15</xdr:col>
<xdr:colOff>542925</xdr:colOff>
<xdr:row>16</xdr:row>
<xdr:rowOff>38100</xdr:rowOff>
</xdr:to>
<xdr:pic>
<xdr:nvPicPr>
<xdr:cNvPr id="2" name="图片 1" descr="图1">
<a:extLst>
<a:ext uri="{FF2B5EF4-FFF2-40B4-BE49-F238E27FC236}">
<a16:creationId xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main" id="{B3254D43-B0F9-40B2-8D35-540241F52832}" />
</a:ext>
<a:ext uri="{00000000-0001-0000-0000-000000000000}">
<a16:creationId xmlns="" xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main" id="{00000000-0000-0000-0000-000000000000}" />
</a:ext>
</a:extLst>
</xdr:cNvPr>
<xdr:cNvPicPr>
<a:picLocks noChangeAspect="1" />
</xdr:cNvPicPr>
</xdr:nvPicPr>
<xdr:blipFill>
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId1" />
<a:stretch>
<a:fillRect />
</a:stretch>
</xdr:blipFill>
<xdr:spPr>
<a:xfrm>
<a:off x="6172200" y="161925" />
<a:ext cx="4657725" cy="2771775" />
</a:xfrm>
<a:prstGeom prst="rect">
<a:avLst />
</a:prstGeom>
</xdr:spPr>
</xdr:pic>
<xdr:clientData />
</xdr:twoCellAnchor>
<xdr:twoCellAnchor editAs="oneCell">
<xdr:from>
<xdr:col>9</xdr:col>
<xdr:colOff>9525</xdr:colOff>
<xdr:row>18</xdr:row>
<xdr:rowOff>9525</xdr:rowOff>
</xdr:from>
<xdr:to>
<xdr:col>15</xdr:col>
<xdr:colOff>542925</xdr:colOff>
<xdr:row>33</xdr:row>
<xdr:rowOff>114300</xdr:rowOff>
</xdr:to>
<xdr:pic>
<xdr:nvPicPr>
<xdr:cNvPr id="3" name="图片 2" descr="图2">
<a:extLst>
<a:ext uri="{FF2B5EF4-FFF2-40B4-BE49-F238E27FC236}">
<a16:creationId xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main" id="{0E01A452-F190-4032-B5E1-45D5788F7B24}" />
</a:ext>
<a:ext uri="{00000000-0001-0000-0000-000000000000}">
<a16:creationId xmlns="" xmlns:a16="http://schemas.microsoft.com/office/drawing/2014/main" id="{00000000-0001-0000-0000-000000000000}" />
</a:ext>
</a:extLst>
</xdr:cNvPr>
<xdr:cNvPicPr>
<a:picLocks noChangeAspect="1" />
</xdr:cNvPicPr>
</xdr:nvPicPr>
<xdr:blipFill>
<a:blip xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:embed="rId2" />
<a:stretch>
<a:fillRect />
</a:stretch>
</xdr:blipFill>
<xdr:spPr>
<a:xfrm>
<a:off x="6181725" y="3267075" />
<a:ext cx="4648200" cy="2819400" />
</a:xfrm>
<a:prstGeom prst="rect">
<a:avLst />
</a:prstGeom>
</xdr:spPr>
</xdr:pic>
<xdr:clientData />
</xdr:twoCellAnchor>
</xdr:wsDr>
`;
const theme1Data = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office 主题">
<a:themeElements>
<a:clrScheme name="Office">
<a:dk1>
<a:sysClr val="windowText" lastClr="000000" />
</a:dk1>
<a:lt1>
<a:sysClr val="window" lastClr="FFFFFF" />
</a:lt1>
<a:dk2>
<a:srgbClr val="44546A" />
</a:dk2>
<a:lt2>
<a:srgbClr val="E7E6E6" />
</a:lt2>
<a:accent1>
<a:srgbClr val="4472C4" />
</a:accent1>
<a:accent2>
<a:srgbClr val="ED7D31" />
</a:accent2>
<a:accent3>
<a:srgbClr val="A5A5A5" />
</a:accent3>
<a:accent4>
<a:srgbClr val="FFC000" />
</a:accent4>
<a:accent5>
<a:srgbClr val="5B9BD5" />
</a:accent5>
<a:accent6>
<a:srgbClr val="70AD47" />
</a:accent6>
<a:hlink>
<a:srgbClr val="0563C1" />
</a:hlink>
<a:folHlink>
<a:srgbClr val="954F72" />
</a:folHlink>
</a:clrScheme>
<a:fontScheme name="Office">
<a:majorFont>
<a:latin typeface="Calibri Light" panose="020F0302020204030204" />
<a:ea typeface="" />
<a:cs typeface="" />
<a:font script="Jpan" typeface="游ゴシック Light" />
<a:font script="Hang" typeface="맑은 고딕" />
<a:font script="Hans" typeface="等线 Light" />
<a:font script="Hant" typeface="新細明體" />
<a:font script="Arab" typeface="Times New Roman" />
<a:font script="Hebr" typeface="Times New Roman" />
<a:font script="Thai" typeface="Tahoma" />
<a:font script="Ethi" typeface="Nyala" />
<a:font script="Beng" typeface="Vrinda" />
<a:font script="Gujr" typeface="Shruti" />
<a:font script="Khmr" typeface="MoolBoran" />
<a:font script="Knda" typeface="Tunga" />
<a:font script="Guru" typeface="Raavi" />
<a:font script="Cans" typeface="Euphemia" />
<a:font script="Cher" typeface="Plantagenet Cherokee" />
<a:font script="Yiii" typeface="Microsoft Yi Baiti" />
<a:font script="Tibt" typeface="Microsoft Himalaya" />
<a:font script="Thaa" typeface="MV Boli" />
<a:font script="Deva" typeface="Mangal" />
<a:font script="Telu" typeface="Gautami" />
<a:font script="Taml" typeface="Latha" />
<a:font script="Syrc" typeface="Estrangelo Edessa" />
<a:font script="Orya" typeface="Kalinga" />
<a:font script="Mlym" typeface="Kartika" />
<a:font script="Laoo" typeface="DokChampa" />
<a:font script="Sinh" typeface="Iskoola Pota" />
<a:font script="Mong" typeface="Mongolian Baiti" />
<a:font script="Viet" typeface="Times New Roman" />
<a:font script="Uigh" typeface="Microsoft Uighur" />
<a:font script="Geor" typeface="Sylfaen" />
<a:font script="Armn" typeface="Arial" />
<a:font script="Bugi" typeface="Leelawadee UI" />
<a:font script="Bopo" typeface="Microsoft JhengHei" />
<a:font script="Java" typeface="Javanese Text" />
<a:font script="Lisu" typeface="Segoe UI" />
<a:font script="Mymr" typeface="Myanmar Text" />
<a:font script="Nkoo" typeface="Ebrima" />
<a:font script="Olck" typeface="Nirmala UI" />
<a:font script="Osma" typeface="Ebrima" />
<a:font script="Phag" typeface="Phagspa" />
<a:font script="Syrn" typeface="Estrangelo Edessa" />
<a:font script="Syrj" typeface="Estrangelo Edessa" />
<a:font script="Syre" typeface="Estrangelo Edessa" />
<a:font script="Sora" typeface="Nirmala UI" />
<a:font script="Tale" typeface="Microsoft Tai Le" />
<a:font script="Talu" typeface="Microsoft New Tai Lue" />
<a:font script="Tfng" typeface="Ebrima" />
</a:majorFont>
<a:minorFont>
<a:latin typeface="Calibri" panose="020F0502020204030204" />
<a:ea typeface="" />
<a:cs typeface="" />
<a:font script="Jpan" typeface="游ゴシック" />
<a:font script="Hang" typeface="맑은 고딕" />
<a:font script="Hans" typeface="等线" />
<a:font script="Hant" typeface="新細明體" />
<a:font script="Arab" typeface="Arial" />
<a:font script="Hebr" typeface="Arial" />
<a:font script="Thai" typeface="Tahoma" />
<a:font script="Ethi" typeface="Nyala" />
<a:font script="Beng" typeface="Vrinda" />
<a:font script="Gujr" typeface="Shruti" />
<a:font script="Khmr" typeface="DaunPenh" />
<a:font script="Knda" typeface="Tunga" />
<a:font script="Guru" typeface="Raavi" />
<a:font script="Cans" typeface="Euphemia" />
<a:font script="Cher" typeface="Plantagenet Cherokee" />
<a:font script="Yiii" typeface="Microsoft Yi Baiti" />
<a:font script="Tibt" typeface="Microsoft Himalaya" />
<a:font script="Thaa" typeface="MV Boli" />
<a:font script="Deva" typeface="Mangal" />
<a:font script="Telu" typeface="Gautami" />
<a:font script="Taml" typeface="Latha" />
<a:font script="Syrc" typeface="Estrangelo Edessa" />
<a:font script="Orya" typeface="Kalinga" />
<a:font script="Mlym" typeface="Kartika" />
<a:font script="Laoo" typeface="DokChampa" />
<a:font script="Sinh" typeface="Iskoola Pota" />
<a:font script="Mong" typeface="Mongolian Baiti" />
<a:font script="Viet" typeface="Arial" />
<a:font script="Uigh" typeface="Microsoft Uighur" />
<a:font script="Geor" typeface="Sylfaen" />
<a:font script="Armn" typeface="Arial" />
<a:font script="Bugi" typeface="Leelawadee UI" />
<a:font script="Bopo" typeface="Microsoft JhengHei" />
<a:font script="Java" typeface="Javanese Text" />
<a:font script="Lisu" typeface="Segoe UI" />
<a:font script="Mymr" typeface="Myanmar Text" />
<a:font script="Nkoo" typeface="Ebrima" />
<a:font script="Olck" typeface="Nirmala UI" />
<a:font script="Osma" typeface="Ebrima" />
<a:font script="Phag" typeface="Phagspa" />
<a:font script="Syrn" typeface="Estrangelo Edessa" />
<a:font script="Syrj" typeface="Estrangelo Edessa" />
<a:font script="Syre" typeface="Estrangelo Edessa" />
<a:font script="Sora" typeface="Nirmala UI" />
<a:font script="Tale" typeface="Microsoft Tai Le" />
<a:font script="Talu" typeface="Microsoft New Tai Lue" />
<a:font script="Tfng" typeface="Ebrima" />
</a:minorFont>
</a:fontScheme>
<a:fmtScheme name="Office">
<a:fillStyleLst>
<a:solidFill>
<a:schemeClr val="phClr" />
</a:solidFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:lumMod val="110000" />
<a:satMod val="105000" />
<a:tint val="67000" />
</a:schemeClr>
</a:gs>
<a:gs pos="50000">
<a:schemeClr val="phClr">
<a:lumMod val="105000" />
<a:satMod val="103000" />
<a:tint val="73000" />
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:lumMod val="105000" />
<a:satMod val="109000" />
<a:tint val="81000" />
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="5400000" scaled="0" />
</a:gradFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:satMod val="103000" />
<a:lumMod val="102000" />
<a:tint val="94000" />
</a:schemeClr>
</a:gs>
<a:gs pos="50000">
<a:schemeClr val="phClr">
<a:satMod val="110000" />
<a:lumMod val="100000" />
<a:shade val="100000" />
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:lumMod val="99000" />
<a:satMod val="120000" />
<a:shade val="78000" />
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="5400000" scaled="0" />
</a:gradFill>
</a:fillStyleLst>
<a:lnStyleLst>
<a:ln w="6350" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr" />
</a:solidFill>
<a:prstDash val="solid" />
<a:miter lim="800000" />
</a:ln>
<a:ln w="12700" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr" />
</a:solidFill>
<a:prstDash val="solid" />
<a:miter lim="800000" />
</a:ln>
<a:ln w="19050" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr" />
</a:solidFill>
<a:prstDash val="solid" />
<a:miter lim="800000" />
</a:ln>
</a:lnStyleLst>
<a:effectStyleLst>
<a:effectStyle>
<a:effectLst />
</a:effectStyle>
<a:effectStyle>
<a:effectLst />
</a:effectStyle>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="57150" dist="19050" dir="5400000" algn="ctr" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="63000" />
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
</a:effectStyle>
</a:effectStyleLst>
<a:bgFillStyleLst>
<a:solidFill>
<a:schemeClr val="phClr" />
</a:solidFill>
<a:solidFill>
<a:schemeClr val="phClr">
<a:tint val="95000" />
<a:satMod val="170000" />
</a:schemeClr>
</a:solidFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="93000" />
<a:satMod val="150000" />
<a:shade val="98000" />
<a:lumMod val="102000" />
</a:schemeClr>
</a:gs>
<a:gs pos="50000">
<a:schemeClr val="phClr">
<a:tint val="98000" />
<a:satMod val="130000" />
<a:shade val="90000" />
<a:lumMod val="103000" />
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="63000" />
<a:satMod val="120000" />
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="5400000" scaled="0" />
</a:gradFill>
</a:bgFillStyleLst>
</a:fmtScheme>
</a:themeElements>
<a:objectDefaults />
<a:extraClrSchemeLst />
<a:extLst>
<a:ext uri="{05A4C25C-085E-4340-85A3-A5531E510DB2}">
<thm15:themeFamily xmlns:thm15="http://schemas.microsoft.com/office/thememl/2012/main" name="Office Theme" id="{62F939B6-93AF-4DB8-9C6B-D6C7DFDC589F}"
vid="{4A3C46E8-61CC-4603-A589-7422A47A8E4A}" />
</a:ext>
</a:extLst>
</a:theme>
`;
const sheet1Data = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"
xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{21003895-7A4C-4232-A74B-BA9CB37E5275}">
`;
export {
Content_Types,
relsData,
appData,
coreData,
stylesData,
workbookData,
workbookXml,
drawing1Data,
theme1Data,
sheet1Data,
}
2.导出文件：
/* eslint-disable */ require('file-saver'); import JSZip from 'jszip' import { Content_Types, relsData, appData, coreData, stylesData, workbookData, workbookXml, drawing1Data, theme1Data, sheet1Data, } from './xlsxStyles' function make_app_data(chartData) { let sheetCount = chartData.length; let titleVtItems = ''; chartData.forEach((item,index)=>{ titleVtItems += `<vt:lpstr>`+item.name+`</vt:lpstr>` }); return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"> <Application>Microsoft Excel</Application> <DocSecurity>0</DocSecurity> <ScaleCrop>false</ScaleCrop> <HeadingPairs> <vt:vector size="2" baseType="variant"> <vt:variant> <vt:lpstr>工作表</vt:lpstr> </vt:variant> <vt:variant> <vt:i4>`+sheetCount+`</vt:i4> </vt:variant> </vt:vector> </HeadingPairs> <TitlesOfParts> <vt:vector size="`+sheetCount+`" baseType="lpstr"> ` + titleVtItems + ` </vt:vector> </TitlesOfParts> <Company></Company> <LinksUpToDate>false</LinksUpToDate> <SharedDoc>false</SharedDoc> <HyperlinksChanged>false</HyperlinksChanged> <AppVersion>16.0300</AppVersion> </Properties> `; } function make_workbook_data(chartData) { let sheetItems = ''; chartData.forEach((item,index)=>{ let id = index + 1 sheetItems += `<sheet name="`+item.name+`" sheetId="`+id+`" r:id="rId`+id+`" />`; }); return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6" xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"> <fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="21328" /> <workbookPr defaultThemeVersion="166925" /> <mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"> <mc:Choice Requires="x15"> <x15ac:absPath url="F:\桌面文档\Desktop\" xmlns:x15ac="http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac" /> </mc:Choice> </mc:AlternateContent> <xr:revisionPtr revIDLastSave="0" documentId="13_ncr:1_{D386BEC6-9E8D-4A6C-9BC7-09EF2C7D6400}" xr6:coauthVersionLast="41" xr6:coauthVersionMax="41" xr10:uidLastSave="{00000000-0000-0000-0000-000000000000}" /> <bookViews> <workbookView xWindow="3675" yWindow="660" windowWidth="21600" windowHeight="11385" xr2:uid="{AB6A0709-83A4-4152-867C-37AB087503A3}" /> </bookViews> <sheets> ` +sheetItems+ ` </sheets> <calcPr calcId="191029" /> <extLst> <ext uri="{140A7094-0E35-4892-8432-C4D2E57EDEB5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"> <x15:workbookPr chartTrackingRefBase="1" /> </ext> <ext uri="{B58B0392-4F1F-4190-BB64-5DF3571DCE5F}" xmlns:xcalcf="http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures"> <xcalcf:calcFeatures> <xcalcf:feature name="microsoft.com:RD" /> <xcalcf:feature name="microsoft.com:FV" /> </xcalcf:calcFeatures> </ext> </extLst> </workbook> `; } function make_workbook_xml(chartData) { let relationships = ''; chartData.forEach((item,index)=>{ let id = index + 1 relationships += `<Relationship Id="rId`+id+`" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet`+id+`.xml" />`; }); return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> ` +relationships+ ` <Relationship Id="rId`+(chartData.length+1)+`" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" /> <Relationship Id="rId`+(chartData.length+2)+`" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" /> <Relationship Id="rId`+(chartData.length+3)+`" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml" /> </Relationships> `; } export function export_txt_to_zip(imgData, chartData ,xlsxName) { // console.log(imgData,"图形"); // console.log(chartData ,"数据"); const zip = new JSZip(); const zip_name = xlsxName || 'excel文档'; // 第一层文件夹： const _rels = zip.folder("_rels"); const docProps = zip.folder("docProps");//app.xml core.xml custom.xml const xl = zip.folder("xl"); // sharedStrings.xml styles.xml workbook.xml // 第二层文件夹： const xl_rels = xl.folder("_rels"); const media = xl.folder("media"); //图片2*6=12 const theme = xl.folder("theme");// theme1.xml const drawings = xl.folder("drawings"); // 【1个关系文件夹+数据文件6个】 const worksheets = xl.folder("worksheets"); // 【1个关系文件夹+数据文件6个】 // 第三层文件夹： const drawings_xl_rels = drawings.folder("_rels"); const sheets_xl_rels = worksheets.folder("_rels");//可以不需要关系文档 // 文件内容 //【1】 zip.file(`[Content_Types].xml`, Content_Types); //【2】 _rels.file(`.rels`, relsData); //【3】 docProps.file(`app.xml`, make_app_data(chartData)); //【4】 docProps.file(`core.xml`, coreData); //【5】 // docProps.file(`custom.xml`, customData); //【6】 xl_rels.file(`workbook.xml.rels`, make_workbook_xml(chartData)); //【7】 xl.file(`workbook.xml`, make_workbook_data(chartData)); //【8】样式 xl.file(`styles.xml`, stylesData); //【9】主题 theme.file(`theme1.xml`, theme1Data); //【10】数据 let sharedStringsData = `` //共享数据 //参数 let num = 0 // 图片文件标记 let rowNum = 0;//共享数据标记 chartData.forEach((item,index)=>{ let sheetData = sheet1Data //模板 // 计算参数 const maxLenArr = item.data.map(v=>v.length) const maxLen = Math.max(...maxLenArr) const dataLen = item.data.length const dimension = String.fromCharCode(65 + maxLen-1) + String(dataLen) // 遍历数据 sheetData += ` <dimension ref="A1:${dimension}" /> <sheetViews> <sheetView ${index===0?'tabSelected="1"':''} workbookViewId="0" /> </sheetViews> <sheetFormatPr defaultColWidth="9" defaultRowHeight="14.25" x14ac:dyDescent="0.2" /> <sheetData>`; let row = 0; item.data.forEach((val)=>{//字符串区分：纯数字与其它 row++ sheetData += `<row r="${row}" spans="1:${maxLen}" x14ac:dyDescent="0.2">`;//第1行开始,每行条数 val.forEach((v,i)=>{ const newVal = String(v).replace(/-/g, '').replace(/</g, '(').replace(/>/g, ')') //去除横杆(减号)+转换尖括号（大于小于号） const abc = String.fromCharCode(65+i)+String(row) var reg = /^[0-9]+.?[0-9]*$/ if (reg.test(v)) { sheetData += `<c r="${abc}" s="1"><v>${+v}</v></c>`;//数字 }else{ // 非纯数字：需要单独存储 sheetData += `<c r="${abc}" s="2" t="s"><v>${rowNum}</v></c>`; sharedStringsData += `<si><t>${newVal}</t></si>`; rowNum++ } }) sheetData += `</row>`; }) sheetData += `</sheetData> <phoneticPr fontId="1" type="noConversion" /> <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" /> <drawing r:id="rId1" /> </worksheet>` worksheets.file(`sheet${index+1}.xml`, sheetData);//1. //2.动态模板 let sheet_xml_rels = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing${index+1}.xml" /> </Relationships>` sheets_xl_rels.file(`sheet${index+1}.xml.rels`, sheet_xml_rels); //3.固定模板 let drawingData = drawing1Data drawings.file(`drawing${index+1}.xml`, drawingData); //4.动态模板 let drawing_xml_rels = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image${2*index+2}.png" /> <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" Target="../media/image${2*index+1}.png" /> </Relationships> ` drawings_xl_rels.file(`drawing${index+1}.xml.rels`, drawing_xml_rels); //图片 imgData[index][0].forEach((v,i)=>{ num++ media.file(`image${num}.png`, v, {base64: true}); }) }) //共享数据 let sharedStrings = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="${rowNum}" uniqueCount="${rowNum}"> `; sharedStrings += sharedStringsData; sharedStrings +=`</sst>`; xl.file(`sharedStrings.xml`, sharedStrings); // 导出文件 zip.generateAsync({type:"blob"}).then((blob) => { saveAs(blob, `${zip_name}.xlsx`) }, (err) => { alert('导出失败') }) }
-end-
浙公网安备 33010602011771号