vue+xlsx實(shí)現(xiàn)表格的導(dǎo)入導(dǎo)出功能
官網(wǎng)地址:https://www.npmjs.com/package/xlsx
一、vue前端使用xlsx和 xlsx-style 導(dǎo)出excel,并修改樣式:
文章鏈接:http://t.csdn.cn/11OTd
1、改造后效果:
可控制大表頭顯示已隱藏;新增根據(jù)內(nèi)容,列寬自適應(yīng)


2、實(shí)現(xiàn):
<template>
<div class="HomeMain">
<el-button-group style="margin-bottom: 18px">
<el-button type="primary" icon="el-icon-download" @click="handleExport('車道配置')">導(dǎo)出</el-button>
</el-button-group>
<div class="section">
<el-table :data="tableData" ref="multipleTable" @selection-change="handleSelectionChange"
:row-key="getRowKey" :header-cell-style="{ background: '#fafafa', color: '#333' }" border>
<el-table-column type="index" label="序號(hào)" align="center" width="100" />
<el-table-column prop="laneNumber" label="車道編號(hào)" align="center" :show-overflow-tooltip="true" />
<el-table-column prop="laneName" label="車道名稱" align="center" :show-overflow-tooltip="true" />
<el-table-column prop="areaName" label="所屬區(qū)域" align="center" :show-overflow-tooltip="true" />
<el-table-column label="操作" align="center" width="200px">
<template slot-scope="scope">
<el-button type="text" @click="handleEdit(scope.row)">編輯</el-button>
<el-button type="text" @click="handleDelete(scope.row)">刪除</el-button>
</template>
</el-table-column>
</el-table>
</div>
</div>
</template>
<script>
import { laneList } from "@/api/indexAPI.js";
import excelUtil from "@/utils/excelUtil";
export default {
name: "Home",
data() {
return {
formData: {
pageNum: 1, //string true 查詢的頁(yè)數(shù)
pageSize: 10, //string true 每頁(yè)顯示的數(shù)量
pageTotal: 0,
},
tableData: [],
tableCheckList: [],
};
},
created() {
this.init();
},
methods: {
// 獲取頁(yè)面數(shù)據(jù)
init() {
let param = { ...this.formData };
laneList(param).then((res) => {
if (res.data.code == 0) {
this.tableData = res.data.data.list;
this.formData.pageTotal = res.data.data.total;
} else {
this.$message.error(res.data.msg);
}
});
},
handleExport(filename) {
/*
1、npm i xlsx 和npm i xlsx-style
2、引入excelUtil文件:import excelUtil from "@/utils/excelUtil";
3、使用
*/
if (this.tableData.length > 0) {
// 前兩行表頭設(shè)置
const excelContent = [["固收指標(biāo)表"], [""]];
// const excelContent = [];
// 列表頭設(shè)置
excelContent.push(["車道編號(hào)", "車道名稱", "所屬區(qū)域"]);
let objTableData = [];
this.tableData.forEach((item) => {
excelContent.push([item.laneNumber,item.laneName + "7665323144642124",item.areaName,]);
objTableData.push({"車道編號(hào)": item.laneNumber,"車道名稱": item.laneName + "7665323144642124","所屬區(qū)域": item.areaName,});
});
// excelContent:為表格內(nèi)容;"固收指標(biāo)表":為excel的前兩行頭,沒(méi)有就傳空數(shù)組;objTableData:為數(shù)組對(duì)象=>aotuWidth設(shè)置單元格列寬
const sheet = excelUtil.xlsxAddStyle( excelContent,"固收指標(biāo)表",objTableData );
// const sheet = excelUtil.xlsxAddStyle(excelContent, "", objTableData);
excelUtil.openDownloadDialog(excelUtil.sheet2blob(sheet),filename + `.xlsx`);
} else {
this.$message.warning("沒(méi)有數(shù)據(jù)無(wú)法導(dǎo)出");
}
},
},
};
</script>3、引入庫(kù)xlsx-style
# 第一步 修改nod_modules 里面xlsx-style文件夾下面dist文件夾下的cpexcel.js文件 這里會(huì)報(bào)錯(cuò)cpexcel.js
807: var cpt = cptable;
# 第二步 修改xlsx-style文件夾下面ods.js文件
10: return require('./' + 'xlsx').utils;
12: try { return require('./' + 'xlsx').utils; }
# 第三步 修改xlsx-style文件夾下面的xlsx.js文件 替換write_ws_xml_data以下方法
var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function pt2px(pt) { return pt * PPI / 96; }
function write_ws_xml_data(ws, opts, idx, wb) {
var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows'];
for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
for(R = range.s.r; R <= range.e.r; ++R) {
r = [];
rr = encode_row(R);
for(C = range.s.c; C <= range.e.c; ++C) {
ref = cols[C] + rr;
if(ws[ref] === undefined) continue;
if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell);
}
if(r.length > 0){
params = ({r:rr});
if(rows && rows[R]) {
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
}
o[o.length] = (writextag('row', r.join(""), params));
}
}
if(rows) for(; R < rows.length; ++R) {
if(rows && rows[R]) {
params = ({r:R+1});
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
o[o.length] = (writextag('row', "", params));
}
}
return o.join("");
}4、excelUtil.js文件
import * as XLSX from 'xlsx'
import XLSXStyle from "xlsx-style";
/**
* 通用的打開下載對(duì)話框方法,沒(méi)有測(cè)試過(guò)具體兼容性
* @param url 下載地址,也可以是一個(gè)blob對(duì)象,必選
* @param saveName 保存文件名,可選
*/
function openDownloadDialog(url, saveName) {
var urlA;
if (typeof url === "object" && url instanceof Blob) {
urlA = URL.createObjectURL(url); // 創(chuàng)建blob地址
}
const aLink = document.createElement("a");
aLink.href = urlA;
// HTML5新增的屬性,指定保存文件名,可以不要后綴,注意,file:///模式下不會(huì)生效
aLink.download = saveName || "";
let 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);
}
// 將一個(gè)sheet轉(zhuǎn)成最終的excel文件的blob對(duì)象,然后利用URL.createObjectURL下載
function sheet2blob(sheet, sheetName) {
var sheetNameS = sheetName || "sheet1";
var workbook = {
SheetNames: [sheetNameS],
Sheets: {},
};
workbook.Sheets[sheetNameS] = sheet;
// 生成excel的配置項(xiàng)
var wopts = {
bookType: "xlsx", // 要生成的文件類型
bookSST: false, // 是否生成Shared String Table,官方解釋是,如果開啟生成速度會(huì)下降,但在低版本IOS設(shè)備上有更好的兼容性
type: "binary",
};
var wbout = XLSXStyle.write(workbook, wopts);
// XLSXStyle.write(wb, { bookType: bookType, bookSST: false, type: 'binary' });
var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });
// 字符串轉(zhuǎn)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;
}
// 表格樣式的設(shè)置
function xlsxAddStyle(xlsx, title, objTableData) {
const sheet = XLSX.utils.aoa_to_sheet(xlsx);
const mergeArr = []; // 合并的單元格
const rowH = []; // 表格每列高度
// 單元格外側(cè)有框線
const borderAll = {
top: { style: "thin" },
bottom: { style: "thin" },
left: { style: "thin" },
right: { style: "thin" },
};
// 單元格外側(cè)無(wú)框線
const noBorder = {
top: { style: "" },
bottom: { style: "" },
left: { style: "" },
right: { style: "" },
};
for (const key in sheet) {
if (Object.hasOwnProperty.call(sheet, key)) {
const element = sheet[key];
if (typeof element === "object") {
const index = Number(key.slice(1)) - 1;
rowH[index] = { hpx: 24 };
element.s = {
alignment: {
horizontal: "left", // 所有單元格右對(duì)齊
vertical: "center", // 所有單元格垂直居中
},
font: {
name: "宋體",
sz: 12,
italic: false,
underline: false,
},
border: borderAll,
fill: {
fgColor: { rgb: "FFFFFFFF" },
},
};
// 表頭處理合并單元格數(shù)組 s 開始 e 結(jié)束 c 列 r行
if (title && title != "" && title.length > 0 && element.v === title) {
mergeArr.push({
s: { c: 0, r: 0 },
e: { c: 2, r: 1 },
});
}
// 列標(biāo)題的樣式
if (title && title != "" && title.length > 0 && index === 2) {
element.s.font.bold = true;
element.s.font.underline = false;
element.s.fill.fgColor = { rgb: "FFFFFFFF" };
element.s.alignment.horizontal = "center";
} else if ((!title || title != "" || title.length > 0) && index === 0) {
// 當(dāng)大表頭不存再,只有列表頭
element.s.font.bold = true;
element.s.font.underline = false;
element.s.fill.fgColor = { rgb: "FFFFFFFF" };
element.s.alignment.horizontal = "center";
}
// 設(shè)置C列表格的樣式
// if (key.indexOf("C") > -1) {
// element.s.alignment.horizontal = "right";
// }
}
}
}
// 表頭的樣式設(shè)置
if (title && title != "" && title.length > 0) {
sheet["A1"].s.font.sz = 18;
sheet["A1"].s.font.bold = true;
sheet["A1"].s.border = borderAll;
sheet["A1"].s.font.underline = false;
sheet["A1"].s.alignment.horizontal = "center";
}
// 單元格的列寬
// data["!cols"] = [{ wch: 50 }, { wch: 20 }, { wch: 40 }];
// 1.所有表頭的寬度
const headsWidth = Object.keys(objTableData[0]).map((value) => {
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
return parseFloat(5 + value.toString().length * 1.5); //設(shè)置得到的列寬
} else {
return parseFloat(5 + value.toString().length * 1.5)
}
});
// console.log("所有表頭的寬度:", headsWidth);
// 2.所有表體值的寬度
const rowsWidth = objTableData.map((item) => {
// 每行數(shù)據(jù)中值的寬度
const maxValue = Object.values(item).map((value, index) => {
let valueWidth;
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
valueWidth = parseFloat(5 + value.toString().length * 1.5);
} else {
valueWidth = parseFloat(5 + value.toString().length * 1.5);
}
// console.log("每行數(shù)據(jù)中值的寬度:", valueWidth);
// 對(duì)比出表頭和表體值的最大數(shù)
return Math.max(valueWidth, headsWidth[index]);
});
// console.log("本行值中最大寬度:", maxValue);
return maxValue;
});
// console.log("每行數(shù)據(jù)對(duì)比出的最大寬度:", rowsWidth);
// 3.對(duì)比每列最大值
let aotuWidth = [];
rowsWidth.map((row, index) => {
let maxWidth = [];
row.map((value, i) => {
if (index === 0) {
maxWidth.push({ wch: value, });
} else {
maxWidth.push({ wch: Math.max(value, aotuWidth[i].wch), });
}
});
aotuWidth = maxWidth;
});
sheet["!cols"] = aotuWidth;
sheet["!rows"] = rowH;
// 合并單元格 s: 起始位置, e: 結(jié)束位置, r: 行, c: 列
// data["!merges"] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 10 } }];
if (mergeArr && mergeArr.length > 0) {
sheet["!merges"] = mergeArr;
}
return sheet;
}
export default { openDownloadDialog, sheet2blob, xlsxAddStyle }二、前端xlsx插件怎么設(shè)置導(dǎo)出的excel列寬自適應(yīng)?
文章鏈接:https://ask.csdn.net/questions/7839639?weChatOA=weChatOA1
2-1、效果

2-2、效果
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
<title>json to excel</title>
</head>
<body>
<button onclick="toExcel()">導(dǎo)出</button>
<script>
function toExcel() {
const excel = XLSX.utils.book_new();
const demo = [{
"IMEI(設(shè)備編號(hào))": "86482351421321111",
設(shè)備名稱: "飯少吃",
設(shè)備類型: "空開",
設(shè)備型號(hào): "ML-200",
NB卡號(hào): "32113213",
批次號(hào): "11113333111",
出廠編號(hào): "4213231231215431",
出廠日期是發(fā)哈是開放的粉紅色: "2020-01-22 12:44:10",
產(chǎn)品標(biāo)識(shí): "7665323144642124",
設(shè)備密鑰: "cc76w454321a2674j3g65",
},
{
"IMEI(設(shè)備編號(hào))": "86482351422131231321111",
設(shè)備名稱: "上點(diǎn)飯",
設(shè)備類型: "電能表",
設(shè)備型號(hào): "ML-2100",
NB卡號(hào): "323213",
批次號(hào): "111133763433444441153531",
出廠編號(hào): "215431",
出廠日期是發(fā)哈是開放的粉紅色: "2020-01-22 12:44:10",
產(chǎn)品標(biāo)識(shí): "7665323144642124",
設(shè)備密鑰: "cc76w45432142312312312312312312a2674j3g65",
},
{
"IMEI(設(shè)備編號(hào))": "1231321111",
設(shè)備名稱: "粉絲地方撒",
設(shè)備類型: "空開",
設(shè)備型號(hào): "ML-200",
NB卡號(hào): "3213213213",
批次號(hào): "1111333344444111",
出廠編號(hào): "21543881",
出廠日期是發(fā)哈是開放的粉紅色: "2020-01-22 12:44:10",
產(chǎn)品標(biāo)識(shí): "766534642124",
設(shè)備密鑰: "cc76w45432142312312312a2674j3g65",
},
];
let data = XLSX.utils.json_to_sheet(demo, {
// origin: "A2", // 設(shè)置插入位置
});
// 表頭的樣式
data["A1"].s = {
font: {
bold: true,
},
alignment: {
horizontal: "center",
vertical: "center",
},
};
// 合并單元格 s: 起始位置, e: 結(jié)束位置, r: 行, c: 列
// data["!merges"] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 10 } }];
// 設(shè)置列寬
// data["!cols"] = [{ wch: 50 }, { wch: 20 }, { wch: 40 }];
// 1.所有表頭的寬度
const headsWidth = Object.keys(demo[0]).map((value) => {
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
return parseFloat(value.toString().length * 2.1);
} else {
return parseFloat(value.toString().length * 1.1)
}
});
// console.log("所有表頭的寬度:", headsWidth);
// 2.所有表體值的寬度
const rowsWidth = demo.map((item) => {
// 每行數(shù)據(jù)中值的寬度
const maxValue = Object.values(item).map((value, index) => {
let valueWidth;
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
valueWidth = parseFloat(value.toString().length * 2.1);
} else {
valueWidth = parseFloat(value.toString().length * 1.1);
}
// console.log("每行數(shù)據(jù)中值的寬度:", valueWidth);
// 對(duì)比出表頭和表體值的最大數(shù)
return Math.max(valueWidth, headsWidth[index]);
});
// console.log("本行值中最大寬度:", maxValue);
return maxValue;
})
// console.log("每行數(shù)據(jù)對(duì)比出的最大寬度:", rowsWidth);
// 3.對(duì)比每列最大值
let aotuWidth = []
rowsWidth.map((row, index) => {
let maxWidth = [];
row.map((value, i) => {
if (index === 0) {
maxWidth.push({
wch: value
});
} else {
maxWidth.push({
wch: Math.max(value, aotuWidth[i].wch)
})
}
})
console.log("最大值:", maxWidth);
aotuWidth = maxWidth;
});
// console.log("每列最大寬度:", aotuWidth);
// 4.給excel設(shè)置自適應(yīng)寬度
data["!cols"] = aotuWidth;
XLSX.utils.book_append_sheet(excel, data);
XLSX.writeFile(excel, "空開填寫模板.xlsx");
}
</script>
</body>
</html>三、xlsx插件,導(dǎo)入導(dǎo)出Excel (vue中導(dǎo)入導(dǎo)出表格)
文章鏈接:https://www.cnblogs.com/webSnow/p/15765468.html
3-1、效果:

3-2、實(shí)現(xiàn):
import * as XLSX from "xlsx";//npm i xlsx
import dealExcel from "@/utils/dealExcel";
//導(dǎo)出方法
handleExport(filename) {
let sheet1data = []
this.tableData.forEach(item => {
sheet1data.push({ '車道編號(hào)': item.laneNumber, '車道名稱': item.laneName, '所屬區(qū)域': item.areaName })
})
let sheet2data = [{ name: '張三', do: '整理文件' }, { name: '李四', do: '打印' }]
let sheet3data = [{ name: '王五', do: 'Vue' }, { name: '二楞', do: 'react' }]
var sheet1 = XLSX.utils.json_to_sheet(sheet1data)
var sheet2 = XLSX.utils.json_to_sheet(sheet2data)
var sheet3 = XLSX.utils.json_to_sheet(sheet3data)
// console.log(sheet1, sheet2, sheet3, 'sheet名稱')
// 創(chuàng)建一個(gè)新的空的workbook
var wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, sheet1, 'sheet1')
XLSX.utils.book_append_sheet(wb, sheet2, '行政部')
XLSX.utils.book_append_sheet(wb, sheet3, '前端部')
const workbookBlob = dealExcel.workbook2blob(wb)
dealExcel.openDownloadDialog(workbookBlob, filename + '.xlsx')
},3-3、dealExcel.js文件
// import XLSX from "xlsx";
import * as XLSX from 'xlsx'
function workbook2blob(workbook) {
// 生成excel的配置項(xiàng)
var wopts = {
// 要生成的文件類型
bookType: 'xlsx',
// // 是否生成Shared String Table,官方解釋是,如果開啟生成速度會(huì)下降,但在低版本IOS設(shè)備上有更好的兼容性
bookSST: false,
type: 'binary'
}
var wbout = XLSX.write(workbook, wopts)
// 將字符串轉(zhuǎn)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
}
let buf = s2ab(wbout)
var blob = new Blob([buf], {
type: 'application/octet-stream'
})
return blob
}
// 將blob對(duì)象 創(chuàng)建bloburl,然后用a標(biāo)簽實(shí)現(xiàn)彈出下載框
function openDownloadDialog(blob, fileName) {
if (typeof blob === 'object' && blob instanceof Blob) {
blob = URL.createObjectURL(blob) // 創(chuàng)建blob地址
}
var aLink = document.createElement('a')
aLink.href = blob
// HTML5新增的屬性,指定保存文件名,可以不要后綴,注意,有時(shí)候 file:///模式下不會(huì)生效
aLink.download = fileName || ''
var event
if (window.MouseEvent) event = new MouseEvent('click')
// 移動(dòng)端
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)
}
export default { workbook2blob, openDownloadDialog }四、vue+xlsx實(shí)現(xiàn)表格的導(dǎo)入導(dǎo)出
文章鏈接:http://t.csdn.cn/u3ndd
4-1、效果:


到此這篇關(guān)于vue+xlsx實(shí)現(xiàn)表格的導(dǎo)入導(dǎo)出功能的文章就介紹到這了,更多相關(guān)vue xlsx表格導(dǎo)入導(dǎo)出內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
深入了解vue-router原理并實(shí)現(xiàn)一個(gè)小demo
這篇文章主要為大家詳細(xì)介紹了vue-router原理并實(shí)現(xiàn)一個(gè)小demo,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下,希望能夠給你帶來(lái)幫助2022-03-03
初學(xué)vue出現(xiàn)空格警告的原因及其解決方案
今天小編就為大家分享一篇初學(xué)vue出現(xiàn)空格警告的原因及其解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2019-10-10
Vue?數(shù)據(jù)綁定事件綁定樣式綁定語(yǔ)法示例
這篇文章主要為大家介紹了Vue?數(shù)據(jù)綁定事件綁定樣式綁定語(yǔ)法示例,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-07-07
vue中this.$refs有值,但無(wú)法獲取ref的值問(wèn)題及解決
這篇文章主要介紹了vue中this.$refs有值,但無(wú)法獲取ref的值問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-01-01
詳解vuex 中的 state 在組件中如何監(jiān)聽(tīng)
本篇文章主要介紹了詳解vuex 中的 state 在組件中如何監(jiān)聽(tīng),小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-05-05
以v-model與promise兩種方式實(shí)現(xiàn)vue彈窗組件
這篇文章主要介紹了vue彈窗組件之兩種方式v-model與promise,每種方式給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2018-05-05

