---

官网地址：https://www.npmjs.com/package/xlsx

一、vue前端使用xlsx和 xlsx-style 导出excel，并修改样式:

文章链接：http://t.csdn.cn/11OTd

1、改造后效果：

可控制大表头显示已隐藏；新增根据内容，列宽自适应

2、实现：

<template><div class="HomeMain"><el-button-group style="margin-bottom: 18px"><el-button type="primary" icon="el-icon-download" @click="handleExport('车道配置')">导出</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="序号" align="center" width="100" /><el-table-column prop="laneNumber" label="车道编号" align="center" :show-overflow-tooltip="true" /><el-table-column prop="laneName" label="车道名称" align="center" :show-overflow-tooltip="true" /><el-table-column prop="areaName" label="所属区域" 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 查询的页数pageSize: 10, //string true 每页显示的数量pageTotal: 0,},tableData: [],tableCheckList: [],};},created() {this.init();},methods: {// 获取页面数据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) {// 前两行表头设置const excelContent = [["固收指标表"], [""]];// const excelContent = [];// 列表头设置excelContent.push(["车道编号", "车道名称", "所属区域"]);let objTableData = [];this.tableData.forEach((item) => {excelContent.push([item.laneNumber,item.laneName + "7665323144642124",item.areaName,]);objTableData.push({"车道编号": item.laneNumber,"车道名称": item.laneName + "7665323144642124","所属区域": item.areaName,});});// excelContent:为表格内容;"固收指标表":为excel的前两行头,没有就传空数组;objTableData:为数组对象=>aotuWidth设置单元格列宽const sheet = excelUtil.xlsxAddStyle( excelContent,"固收指标表",objTableData );// const sheet = excelUtil.xlsxAddStyle(excelContent, "", objTableData);excelUtil.openDownloadDialog(excelUtil.sheet2blob(sheet),filename + `.xlsx`);} else {this.$message.warning("没有数据无法导出");}},},
};
</script>

3、引入库xlsx-style

# 第一步 修改nod_modules 里面xlsx-style文件夹下面dist文件夹下的cpexcel.js文件  这里会报错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";/**
* 通用的打开下载对话框方法，没有测试过具体兼容性
* @param url 下载地址，也可以是一个blob对象，必选
* @param saveName 保存文件名，可选
*/
function openDownloadDialog(url, saveName) {var urlA;if (typeof url === "object" && url instanceof Blob) {urlA = URL.createObjectURL(url); // 创建blob地址}const aLink = document.createElement("a");aLink.href = urlA;// HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效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);
}
// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {var sheetNameS = sheetName || "sheet1";var workbook = {SheetNames: [sheetNameS],Sheets: {},};workbook.Sheets[sheetNameS] = sheet;// 生成excel的配置项var wopts = {bookType: "xlsx", // 要生成的文件类型bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性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" });// 字符串转ArrayBufferfunction 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;
}// 表格样式的设置
function xlsxAddStyle(xlsx, title, objTableData) {const sheet = XLSX.utils.aoa_to_sheet(xlsx);const mergeArr = []; // 合并的单元格const rowH = []; // 表格每列高度// 单元格外侧有框线const borderAll = {top: { style: "thin" },bottom: { style: "thin" },left: { style: "thin" },right: { style: "thin" },};// 单元格外侧无框线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", // 所有单元格右对齐vertical: "center", // 所有单元格垂直居中},font: {name: "宋体",sz: 12,italic: false,underline: false,},border: borderAll,fill: {fgColor: { rgb: "FFFFFFFF" },},};// 表头处理合并单元格数组 s 开始 e 结束  c 列 r行if (title && title != "" && title.length > 0 && element.v === title) {mergeArr.push({s: { c: 0, r: 0 },e: { c: 2, r: 1 },});}// 列标题的样式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) {// 当大表头不存再，只有列表头element.s.font.bold = true;element.s.font.underline = false;element.s.fill.fgColor = { rgb: "FFFFFFFF" };element.s.alignment.horizontal = "center";}// 设置C列表格的样式// if (key.indexOf("C") > -1) {//     element.s.alignment.horizontal = "right";// }}}}// 表头的样式设置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); //设置得到的列宽} else {return parseFloat(5 + value.toString().length * 1.5)}});// console.log("所有表头的宽度：", headsWidth);// 2.所有表体值的宽度const rowsWidth = objTableData.map((item) => {// 每行数据中值的宽度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("每行数据中值的宽度：", valueWidth);// 对比出表头和表体值的最大数return Math.max(valueWidth, headsWidth[index]);});// console.log("本行值中最大宽度：", maxValue);return maxValue;});// console.log("每行数据对比出的最大宽度：", rowsWidth);// 3.对比每列最大值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: 结束位置,   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插件怎么设置导出的excel列宽自适应？

文章链接：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()">导出</button><script>function toExcel() {const excel = XLSX.utils.book_new();const demo = [{"IMEI(设备编号)": "86482351421321111",设备名称: "饭少吃",设备类型: "空开",设备型号: "ML-200",NB卡号: "32113213",批次号: "11113333111",出厂编号: "4213231231215431",出厂日期是发哈是开放的粉红色: "2020-01-22 12:44:10",产品标识: "7665323144642124",设备密钥: "cc76w454321a2674j3g65",},{"IMEI(设备编号)": "86482351422131231321111",设备名称: "上点饭",设备类型: "电能表",设备型号: "ML-2100",NB卡号: "323213",批次号: "111133763433444441153531",出厂编号: "215431",出厂日期是发哈是开放的粉红色: "2020-01-22 12:44:10",产品标识: "7665323144642124",设备密钥: "cc76w45432142312312312312312312a2674j3g65",},{"IMEI(设备编号)": "1231321111",设备名称: "粉丝地方撒",设备类型: "空开",设备型号: "ML-200",NB卡号: "3213213213",批次号: "1111333344444111",出厂编号: "21543881",出厂日期是发哈是开放的粉红色: "2020-01-22 12:44:10",产品标识: "766534642124",设备密钥: "cc76w45432142312312312a2674j3g65",},];let data = XLSX.utils.json_to_sheet(demo, {// origin: "A2", // 设置插入位置});// 表头的样式data["A1"].s = {font: {bold: true,},alignment: {horizontal: "center",vertical: "center",},};// 合并单元格     s: 起始位置,   e: 结束位置,   r: 行,   c: 列// data["!merges"] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 10 } }];//  设置列宽// 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) => {// 每行数据中值的宽度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("每行数据中值的宽度：", valueWidth);// 对比出表头和表体值的最大数return Math.max(valueWidth, headsWidth[index]);});// console.log("本行值中最大宽度：", maxValue);return maxValue;})// console.log("每行数据对比出的最大宽度：", rowsWidth);// 3.对比每列最大值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设置自适应宽度data["!cols"] = aotuWidth;XLSX.utils.book_append_sheet(excel, data);XLSX.writeFile(excel, "空开填写模板.xlsx");}</script></body>
</html>

三、xlsx插件，导入导出Excel （vue中导入导出表格）

文章链接：https://www.cnblogs.com/webSnow/p/15765468.html

3-1、效果：

3-2、实现：

import * as XLSX from "xlsx";//npm i xlsx
import dealExcel from "@/utils/dealExcel";//导出方法
handleExport(filename) {let sheet1data = []this.tableData.forEach(item => {sheet1data.push({ '车道编号': item.laneNumber, '车道名称': item.laneName, '所属区域': 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名称')// 创建一个新的空的workbookvar 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的配置项var wopts = {// 要生成的文件类型bookType: 'xlsx',// // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性bookSST: false,type: 'binary'}var wbout = XLSX.write(workbook, wopts)// 将字符串转ArrayBufferfunction 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) & 0xffreturn buf}let buf = s2ab(wbout)var blob = new Blob([buf], {type: 'application/octet-stream'})return blob
}// 将blob对象 创建bloburl,然后用a标签实现弹出下载框
function openDownloadDialog(blob, fileName) {if (typeof blob === 'object' && blob instanceof Blob) {blob = URL.createObjectURL(blob) // 创建blob地址}var aLink = document.createElement('a')aLink.href = blob// HTML5新增的属性，指定保存文件名，可以不要后缀，注意，有时候 file:///模式下不会生效aLink.download = fileName || ''var eventif (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)
}export default {  workbook2blob, openDownloadDialog }

四、vue+xlsx实现表格的导入导出

文章链接：http://t.csdn.cn/u3ndd

4-1、效果：