Excel 導出
Excel 的導入導出都是依賴于js-xlsx來實作的。
在
js-xlsx
的基礎上又封裝了Export2Excel.js來友善導出資料。
使用
由于
Export2Excel
不僅依賴
js-xlsx
還依賴
file-saver
和
script-loader
。
是以你先需要安裝如下指令：
npm install xlsx file-saver -Snpm install script-loader -S -D
由于
js-xlsx
體積還是很大的，導出功能也不是一個非常常用的功能，是以使用的時候建議使用懶加載。使用方法如下：
import('@/vendor/Export2Excel').then(excel => { excel.export_json_to_excel({ header: tHeader, //表頭 必填 data, //具體資料 必填 filename: 'excel-list', //非必填 autoWidth: true, //非必填 bookType: 'xlsx' //非必填 })})
參數
| 參數 | 說明 | 類型 | 可選值 | 預設值 |
|---|---|---|---|---|
| header | 導出資料的表頭 | Array | / | [] |
| data | 導出的具體資料 | Array | / | []] |
| filename | 導出檔案名 | String | / | excel-list |
| autoWidth | 單元格是否要自适應寬度 | Boolean | true / false | true |
| bookType | 導出檔案類型 | String | xlsx, csv, txt, more | xlsx |
示例
import('@/vendor/Export2Excel').then(excel => { const tHeader = ['Id', 'Title', 'Author', 'Readings', 'Date'] const data = this.list excel.export_json_to_excel({ header: tHeader, //表頭 必填 data, //具體資料 必填 filename: 'excel-list', //非必填 autoWidth: true, //非必填 bookType: 'xlsx' //非必填 })})
excel.vue檔案代碼
<template> <div class="app-container"> <div> <el-button @click="handleDownload"> Export Excel el-button> div> <el-table :data="list"> <el-table-column align="center" label="Id" width="95"> <template slot-scope="scope"> {{ scope.$index }} template> el-table-column> <el-table-column label="Title"> <template slot-scope="scope"> {{ scope.row.title }} template> el-table-column> <el-table-column label="Author" width="110" align="center"> <template slot-scope="scope"> <el-tag>{{ scope.row.author }}el-tag> template> el-table-column> <el-table-column label="Readings" width="115" align="center"> <template slot-scope="scope"> {{ scope.row.pageviews }} template> el-table-column> <el-table-column align="center" label="Date" width="220"> <template slot-scope="scope"> <i class="el-icon-time" /> <span>{{ scope.row.timestamp }}span> template> el-table-column> el-table> div>template><script>export default { name: "ExportExcel", data() { return { list: null, listLoading: true, filename: "", autoWidth: true, bookType: "xlsx", }; }, created() { this.fetchData(); }, methods: { fetchData() { /** * mock的假資料 */ this.list = [ { title: "test", author: "machangzhi", pageviews: "777", timestamp: "12345678910", }, ]; }, handleDownload() { import("@/excel/Export2Excel").then((excel) => { const tHeader = ["Id", "Title", "Author", "Readings", "Date"]; const filterVal = [ "id", "title", "author", "pageviews", "display_time", ]; const list = this.list; const data = this.formatJson(filterVal, list); excel.export_json_to_excel({ header: tHeader, data, filename: this.filename, autoWidth: this.autoWidth, bookType: this.bookType, }); }); }, formatJson(filterVal, jsonData) { return jsonData.map((v) => filterVal.map((j) => { return v[j]; }) ); }, },};script><style>.radio-label { font-size: 14px; color: #606266; line-height: 40px; padding: 0 12px 0 30px;}style>
Export2Excel.js檔案代碼
/* eslint-disable */import { saveAs } from "file-saver";import XLSX from "xlsx";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); var oo = generateArray(theTable); var ranges = oo[1]; /* original data */ var data = oo[0]; var ws_name = "SheetJS"; 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" );}export function export_json_to_excel({ multiHeader = [], header, data, filename, merges = [], autoWidth = true, bookType = "xlsx"} = {}) { /* original data */ filename = filename || "excel-list"; data = [...data]; data.unshift(header); for (let i = multiHeader.length - 1; i > -1; i--) { data.unshift(multiHeader[i]); } var ws_name = "SheetJS"; var wb = new Workbook(), ws = sheet_from_array_of_arrays(data); if (merges.length > 0) { if (!ws["!merges"]) ws["!merges"] = []; merges.forEach(item => { ws["!merges"].push(XLSX.utils.decode_range(item)); }); } if (autoWidth) { /*設定worksheet每列的最大寬度*/ const colWidth = data.map(row => row.map(val => { /*先判斷是否為null/undefined*/ if (val == null) { return { wch: 10 }; } else if (val.toString().charCodeAt(0) > 255) { /*再判斷是否為中文*/ return { wch: val.toString().length * 2 }; } else { return { wch: val.toString().length }; } }) ); /*以第一行為初始值*/ let result = colWidth[0]; for (let i = 1; i < colWidth.length; i++) { for (let j = 0; j < colWidth[i].length; j++) { if (result[j]["wch"] < colWidth[i][j]["wch"]) { result[j]["wch"] = colWidth[i][j]["wch"]; } } } ws["!cols"] = result; } /* add worksheet to workbook */ wb.SheetNames.push(ws_name); wb.Sheets[ws_name] = ws; var wbout = XLSX.write(wb, { bookType: bookType, bookSST: false, type: "binary" }); saveAs( new Blob([s2ab(wbout)], { type: "application/octet-stream" }), `${filename}.${bookType}` );}
![vue 導出excel表格[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)