Vue用Export2Excel導(dǎo)出excel,多級(jí)表頭數(shù)據(jù)方式
使用Export2Excel導(dǎo)出數(shù)據(jù),以及多級(jí)表頭
最近公司做項(xiàng)目,需要導(dǎo)出數(shù)據(jù)到excel表格
使用到了 element UI ,Export2Excel
特此記錄,畢竟好記性不如爛筆頭
不包含修改樣式(菜鳥(niǎo)一枚,希望大佬指教點(diǎn)名)
1.安裝依賴,三個(gè)依賴包
npm install -S file-saver npm install -S xlsx npm install -D script-loader
2.創(chuàng)建文件
在項(xiàng)目src 下創(chuàng)建 vendor 文件夾
然后創(chuàng)建 Export2Excel.js 文件
- Export2Excel.js 文件內(nèi)容
/* 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) { /*設(shè)置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 dataInfo = wb.Sheets[wb.SheetNames[0]]; let arr = [ "A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1", "K1", "L1", "M1", "N1", "O1", "P1", "Q1", "R1", "S1", "T1", "U1", "V1", "W1", "X1", "Y1", "Z1" ]; let style = { font: { color: { rgb: "000000" }, bold: true }, alignment: { horizontal: "center", vertical: "center" } }; for (var i = 0; i < header.length; i++) { dataInfo[arr[i]].s = style; } var wbout = XLSX.write(wb, { bookType: bookType, bookSST: false, type: 'binary' }); saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), `${filename}.${bookType}`); }
補(bǔ)充:
所用到的數(shù)據(jù)格式
tableData: [{ date: '2016-05-02', name: '王小虎', address: '上海市普陀區(qū)金沙江路 1518 弄' }, { date: '2016-05-04', name: '王小虎', address: '上海市普陀區(qū)金沙江路 1517 弄' }, { date: '2016-05-01', name: '王小虎', address: '上海市普陀區(qū)金沙江路 1519 弄' }, { date: '2016-05-03', name: '王小虎', address: '上海市普陀區(qū)金沙江路 1516 弄' }]
3.使用方法(一級(jí)表頭)
導(dǎo)出單級(jí)表頭
- Html部分:
<el-button @click="handleDownload" class="addButton-class">批量導(dǎo)出</el-button>
- js部分:
// 導(dǎo)出數(shù)據(jù) handleDownload() { import('@/vendor/Export2Excel').then(excel => { // 表頭數(shù)據(jù) const header = ['日期', '條形碼', '所屬區(qū)域', '所屬部門(mén)', '商場(chǎng)名稱', '兌獎(jiǎng)員工'] // 數(shù)據(jù)來(lái)源中,代替表頭的變量 const filterVal = ['create_time', 'bar_code', 'aname', 'dname', 'warehouse_name', 'sell_name'] // 數(shù)據(jù)來(lái)源 const list = this.tableData // 拼接數(shù)據(jù) const data = this.formatJson(filterVal, list) excel.export_json_to_excel({ header, data, // 導(dǎo)出表格的文件名字 filename:'銷售產(chǎn)品清單', }) }) }, // 拼接數(shù)據(jù) formatJson(filterVal, jsonData) { return jsonData.map(v => filterVal.map(j => { return v[j] })) },
示例效果
4.多級(jí)表頭
導(dǎo)出多級(jí)表頭
導(dǎo)出多級(jí)表頭首先要理解,單元格合并
- 日期 所占 單元格為 A1:A3
- 配送信息 所占 單元格為 B1:F1
- 姓名 所占 單元格為 B2:B3
依次類推
理解這一點(diǎn),下面代碼就好理解了
- Html部分:
<el-button @click="handleDownload" class="addButton-class">批量導(dǎo)出</el-button>
// 導(dǎo)出單個(gè)表頭 handleDownload() { import('@/vendor/Export2Excel').then(excel => { const multiHeader = [ ['對(duì)賬清單','', '', '', '', '','', '', '', '', ''],// 業(yè)務(wù)需求可刪除 ['結(jié)算單位:',this.name, '', '', '', '','', '結(jié)算日期:', this.brginTime, '至', this.endTime], // 業(yè)務(wù)需求可刪除 ['日期', '上期結(jié)存', '', '本期進(jìn)貨', '', '本期退貨','', '本期銷售', '', '本期庫(kù)存', ''], ] // 前兩行的表頭數(shù)據(jù),二維數(shù)組,不夠的用空白補(bǔ)全 const header = ['', '數(shù)量', '金額', '數(shù)量', '金額', '數(shù)量', '金額', '數(shù)量', '金額', '數(shù)量', '金額'] // 最后一行的表頭數(shù)據(jù) const filterVal= ['goods_brand', 'cq_count', 'cq_total', 'bqrk_count', 'bqrk_total', 'bqck_count','bqck_total','bqxs_count','bqxs_total','bqkc_count','bqkc_total'] // 這里要注意 header 和 filterVal 的數(shù)組長(zhǎng)度一定要相等,否則會(huì)報(bào)錯(cuò) const list = this.tableData const data = this.formatJson(filterVal, list) const merges = ['A1:K1','A3:A4','B3:C3','D3:E3','F3:G3','H3:I3','J3:K3'] // 合并單元格的數(shù)據(jù),如何合并參考上面圖片講解 excel.export_json_to_excel({ multiHeader, header, merges, data, filename:'對(duì)賬清單', }) }) }, // 拼接數(shù)據(jù) formatJson(filterVal, jsonData) { return jsonData.map(v => filterVal.map(j => { return v[j] })) },
大致就是這樣,就是沒(méi)有居中效果(菜鳥(niǎo),勿噴)
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
- Vue3如何利用xlsx、xlsx-js-style導(dǎo)出Excel表格使用(適合新手)
- vue使用luckyexcel實(shí)現(xiàn)在線表格及導(dǎo)出導(dǎo)入方式
- 前端實(shí)現(xiàn)Excel文件導(dǎo)出功能的完整代碼解析(vue實(shí)現(xiàn)excel文件導(dǎo)出)
- vue2項(xiàng)目使用exceljs多表頭導(dǎo)出功能詳解
- vue使用Luckysheet插件實(shí)現(xiàn)excel導(dǎo)入導(dǎo)出
- vue使用xlsx庫(kù)和xlsx-style庫(kù)導(dǎo)入導(dǎo)出excel、設(shè)置單元格背景色、文字居中、合并單元格、設(shè)置列寬
- 在Vue中使用xlsx組件實(shí)現(xiàn)Excel導(dǎo)出功能的步驟詳解
- 在Vue中實(shí)現(xiàn)Excel導(dǎo)出功能(數(shù)據(jù)導(dǎo)出)
相關(guān)文章
詳解用vue-cli來(lái)搭建vue項(xiàng)目和webpack
本篇文章主要介紹了詳解用vue-cli來(lái)搭建vue項(xiàng)目和webpack,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-04-04vue項(xiàng)目及axios請(qǐng)求獲取數(shù)據(jù)方式
這篇文章主要介紹了vue項(xiàng)目及axios請(qǐng)求獲取數(shù)據(jù)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-08-08vue 彈框產(chǎn)生的滾動(dòng)穿透問(wèn)題的解決
這篇文章主要介紹了vue 彈框產(chǎn)生的滾動(dòng)穿透問(wèn)題,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-09-09ant design vue嵌套表格及表格內(nèi)部編輯的用法說(shuō)明
這篇文章主要介紹了ant design vue嵌套表格及表格內(nèi)部編輯的用法說(shuō)明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-10-10Vue中的methods、watch、computed的區(qū)別
這篇文章主要介紹了Vue中的methods、watch、computed的區(qū)別,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-11-11Vue動(dòng)態(tài)組件表格的實(shí)現(xiàn)代碼
這篇文章主要介紹了Vue動(dòng)態(tài)組件表格的實(shí)現(xiàn)代碼,包括框架結(jié)構(gòu)組件,文中還給大家封裝了幾個(gè)組件,有按鈕組件、圖片組件、滑動(dòng)開(kāi)關(guān),結(jié)合示例代碼給大家詳細(xì)講解,需要的朋友可以參考下2022-10-10