- 安装 xlsx 和 xlsx-style
- src下创建文件夹utils , utils文件夹下创建index.js文件,index.js文件内容如下:
* Created by Anqi on 16/11/18. */ /** * Parse the time to string * @param {(Object|string|number)} time * @param {string} cFormat * @returns {string | null} */ // import XLSX from 'xlsx-style'; import XLSX from 'yxg-xlsx-style' export function parseTime(time, cFormat) { if (arguments.length === 0) { return null; } const format = cFormat || '{y}-{m}-{d} {h}:{i}:{s}'; let date; if (typeof time === 'object') { date = time; } else { if ((typeof time === 'string') && (/^[0-9]+$/.test(time))) { time = parseInt(time); } if ((typeof time === 'number') && (time.toString().length === 10)) { time = time * 1000; } date = new Date(time); } const formatObj = { y: date.getFullYear(), m: date.getMonth() + 1, d: date.getDate(), h: date.getHours(), i: date.getMinutes(), s: date.getSeconds(), a: date.getDay() }; const time_str = format.replace(/{([ymdhisa])+}/g, (result, key) => { const value = formatObj[key]; // Note: getDay() returns 0 on Sunday if (key === 'a') { return ['日', '一', '二', '三', '四', '五', '六'][value ]; } return value.toString().padStart(2, '0'); }); return time_str; } /** * @param {number} time * @param {string} option * @returns {string} */ export function formatTime(time, option) { if (('' + time).length === 10) { time = parseInt(time) * 1000; } else { time = +time; } const d = new Date(time); const now = Date.now(); const diff = (now - d) / 1000; if (diff < 30) { return '刚刚'; } else if (diff < 3600) { // less 1 hour return Math.ceil(diff / 60) + '分钟前'; } else if (diff < 3600 * 24) { return Math.ceil(diff / 3600) + '小时前'; } else if (diff < 3600 * 24 * 2) { return '1天前'; } if (option) { return parseTime(time, option); } else { return ( d.getMonth() + 1 + '月' + d.getDate() + '日' + d.getHours() + '时' + d.getMinutes() + '分' ); } } /** * @param {string} url * @returns {Object} */ export function param2Obj(url) { const search = url.split('?')[1]; if (!search) { return {}; } return JSON.parse( '{"' + decodeURIComponent(search) .replace(/"/g, '\\"') .replace(/&/g, '","') .replace(/=/g, '":"') .replace(/\+/g, ' ') + '"}' ); } export function downloadFile(fileName, res) { const blob = new Blob([res.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' }); const link = document.createElement('a'); link.href = window.URL.createObjectURL(blob); link.download = fileName; link.click(); URL.revokeObjectURL(link.href); } export function resizeListener(chart) { window.addEventListener('resize', function() { chart.resize(); }); } // 为合并项添加边框 export const addRangeBorder = (range, ws) => { const arr = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']; range.forEach(item => { const startColNumber = Number(item.s.r); const endColNumber = Number(item.e.r); // 0-0 const startRowNumber = Number(item.s.c); const endRowNumber = Number(item.e.c); // 0-16 const test = ws[arr[startRowNumber] + (startColNumber + 1)]; // 合并项第一个单元格中的内容 for (let col = startColNumber; col <= endColNumber; col++) { // 0-0 for (let row = startRowNumber; row <= endRowNumber; row++) { // 0-16 ws[arr[row] + (col + 1)] = test; // A1-P1 } } }); return ws; }; // 将一个sheet转成最终的excel文件的blob对象 export const sheet2blob = (sheet, sheetName) => { // console.log(sheet, sheetName, 'sheet, sheetName') sheetName = sheetName || 'sheet1'; console.log(sheetName, 'sheetName'); var workbook = { SheetNames: [sheetName], Sheets: {} }; workbook.Sheets[sheetName] = sheet; // 生成excel的配置项 var wopts = { bookType: 'xlsx', // 要生成的文件类型 bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性 type: 'binary' }; var wbout = XLSX.write(workbook, wopts); var blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' }); // 字符串转ArrayBuffer function s2ab(s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } return blob; }; export const openDownloadDialog = (url, saveName) => { if (typeof url === 'object' && url instanceof Blob) { url = URL.createObjectURL(url); // 创建blob地址 } var aLink = document.createElement('a'); aLink.href = url; aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效 var event; if (window.MouseEvent) event = new MouseEvent('click'); else { event = document.createEvent('MouseEvents'); event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null); } aLink.dispatchEvent(event); };
3. 注意!!!!!!!直接引用 import XLSX from 'xlsx-style' 会报错,报错信息如下图:
想要解决此错误需要修改model包里源码,需要修改源码:在\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;,修改之后问题解决可以解决,但是另一个问题就来了,
因为修改了xlsx-style源码所以我们npm i的时候不能获取修改后的包,但是其实可以将修改后的源码上传自己的npm再下载自己修改后的包。但是由于本人实在懒得可怕,找了一位博主上传好的直接下载了!!!!!!!!!
下载 npm install --save yxg-xlsx-style , 页面引入 :import XLSX from 'yxg-xlsx-style'
vue页面使用如下:
exportFn() { var wb = XLSX2.utils.table_to_sheet(document.querySelector('#table'));// mytable为表格的id名 console.log(wb); if (!wb['!fullref']) { this.$message.warning('无法导出:报表无数据'); return; } // 设置列宽(这里用到列的数量是用来设置不同列的不同宽度的) const sum = 8; // 列的数量,根据自身项目进行数据的更改 for (var i = 0; i < sum; i++) { wb['!cols'][i] = { wpx: 160 }; } for (const key in wb) { if (key.indexOf('!') === -1) { // 排除带!的字段,只要单元格字段 wb[key].s = { font: { // 字体设置 sz: 13, bold: false, color: { rgb: '000000'// 十六进制,不带# } }, border: { // 设置边框 top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }, alignment: { // 文字居中 //字体水平居中、垂直居中、自动换行 horizontal: 'center', vertical: 'center', wrap_text: true } }; } } // var data = addRangeBorder(wb['!merges'], wb); // 合并项添加边框 let a = dayjs(this.queryParams.time[0]).format('YYYYMMDDHHmmss'); let b = dayjs(this.queryParams.time[1]).format('YYYYMMDDHHmmss'); var filedata = sheet2blob(wb); // 将一个sheet转成最终的excel文件的blob对象 openDownloadDialog(filedata, `数据_${a}_${b}.xlsx`); // 下载报表 },
别忘记在el-table 上定义 ID !!!!!!!!!!!!!!!
标签:node,xlsx,style,const,报错,time,var,return From: https://www.cnblogs.com/anqiphp/p/18224375