前端实现excel导出表格(纯前端实现)

梁子猫 2024-08-12 13:03:01 阅读 100

1.先安装相应的插件

<code>npm install xlsx@0.16.8

npm install file-saver

npm install xlsx-style-medalsoft

2.封装好公共导出功能。我这里在utils文件夹下的tools.js

import FileSaver from "file-saver";

import XLSX from "xlsx";

import XLSXStyle from "xlsx-style-medalsoft";

const OMS = {};

// 导出Excel-单表格Excel 带样式

OMS.downLoadXlsx = ({

dom = "el-table",

name = "文件",

ColumnWdth = [],

rowName = null,

}) => {

const table = document.getElementById(dom);

// 这里是如果前面设置了导出合并行列用display的话，这里要删除掉，不然会导出多空格

const hiddenCells = table.querySelectorAll('td[style*="display: none"]');

hiddenCells.forEach((cell) => cell.parentNode.removeChild(cell));

// 因为element-ui的表格的fixed属性导致多出一个table，会下载重复内容，这里删除掉

if (table.querySelector(".el-table__fixed")) {

table.removeChild(table.querySelector(".el-table__fixed"));

}

if (table.querySelector(".el-table__fixed-right")) {

table.removeChild(table.querySelector(".el-table__fixed-right"));

}

const et = XLSX.utils.table_to_book(table, { raw: true }); // 此处传入table的DOM节点,raw为true表示单元格为文本格式(未加工)

const wbs = et.Sheets.Sheet1;

// 删掉末尾空行

Object.keys(wbs).forEach((item, index) => {

if (!item.startsWith("!") && wbs[item].v === "") {

delete wbs[item];

}

if (rowName!='') {

if (item.includes(rowName)) {

delete wbs[item];

}

});

console.log(wbs, "wbs");

// debugger;

// 设置表格列行高

// 设置表格列宽度

if (ColumnWdth.length === 0) {

for (let i = 0; i < 30; i++) {

wbs["!cols"][i] = { wch: 12.5 };

// wbs["!rows"][i] = { hpt: 60 };

}

} else {

ColumnWdth.forEach((item, i) => {

// 这里设置全部行高

wbs["!cols"][i] = { wch: item };

wbs["!rows"][i] = { hpt: item };

});

}

// 循环遍历每一个表格，设置样式

for (const key in wbs) {

if (!key.startsWith("!")) {

wbs[key].s = {

font: {

sz: 11, // 字体大小

bold: false, // 加粗

name: "宋体", // 字体

color: {

rgb: "000000", // 十六进制，不带#

},

alignment: {

// 文字居中

horizontal: "center",

vertical: "center",

wrapText: false, // 文本自动换行

textIndent: 1, // 设置文本溢出时的自动缩进，单位为字符数

},

border: {

// 设置边框

// top: { style: "thin" },

// bottom: { style: "thin" },

// left: { style: "thin" },

// right: { style: "thin" },

},

};

}

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",

];

// 行列合并

const range = wbs["!merges"];

if (range) {

range.forEach((item) => {

const startColNumber = Number(item.s.r);

const endColNumber = Number(item.e.r);

const startRowNumber = Number(item.s.c);

const endRowNumber = Number(item.e.c);

const test = wbs[arr[startRowNumber] + (startColNumber + 1)];

for (let col = startColNumber; col <= endColNumber; col++) {

for (let row = startRowNumber; row <= endRowNumber; row++) {

wbs[arr[row] + (col + 1)] = test;

}

});

}

const etout = XLSXStyle.write(et, {

bookType: "xlsx",

type: "buffer",

});

// eslint-disable-next-line no-useless-catch

try {

FileSaver.saveAs(

new Blob([etout], { type: "application/octet-stream" }),

`${name}.xlsx`

); // 导出的文件名

} catch (e) {

throw e;

}

};

export default OMS;

3.页面中使用

给表格绑定一个id，通过获取dom来导出

import OMS from "@/utils/tools";

<el-table

v-if="showTable"code>

id="kuTable"code>

max-height="525"code>

v-loading="loading"code>

:data="wmsStockList"code>

@selection-change="handleSelectionChange"code>

>

</el-table>

watch: {

tableData: {

immediate: true,

async handler () {

console.log("tableData", this.tableData);

await this.$nextTick(); //根据实际选择延迟调用

// const tds = document.querySelectorAll('#table .el-table__fixed-footer-wrapper tr>td');//表格有固定列时写法

const tds = document.querySelectorAll(

"#kuTable .el-table__footer-wrapper tr>td"

)

tds[0].colSpan = 3;

tds[1].colSpan = 0;

tds[2].colSpan = 0;

tds[1].rowSpan = 0;

tds[2].rowSpan = 0;

tds[0].style.textAlign = "center";

tds[1].style.display = "none";

tds[2].style.display = "none";

},

// 导出

handleExport() {

// const that = this

this.$confirm("是否确认导出入库列表?", "警告", {

confirmButtonText: "确定",

cancelButtonText: "取消",

type: "warning",

})

.then(() => {

// console.log(this.pmsSpuInfoList, "this.pmsSpuInfoList");

this.exportLoading = true;

OMS.downLoadXlsx({

dom: "kuTable",

name: "入库列表",

ColumnWdth: [

12, 12, 20, 30, 20, 20, 30, 20, 40, 12, 20, 12, 14, 15, 15,

], // 每一列的宽度，需要直接指定，接受数字

rowName: "P", //可以设置哪行不要的

});

this.exportLoading = false;

this.showTable = false;

this.$nextTick(() => {

// this.getList();

this.showTable = true;

});

})

.catch(function () {});

},

<template>

<BasicLayout>

<template #wrapper>

<el-card class="box-card">code>

<el-form ref="queryForm" :model="queryParams" label-width="68px">code>

<el-row :gutter="20">code>

<el-col :span="4">code>

<el-form-item label="供应商ID" prop="supplierId">code>

<el-input clearable v-model="queryParams.supplierId" placeholder="请填入供应商ID" />code>

</el-form-item>

</el-col>

<el-col :span="4">code>

<el-form-item label="支付:" label-width="50px">code>

<selector clearable :options="payOptions" :select-value.sync="queryParams.paymentMethod"code>

:placeholders="''" :widths="widths"></selector>code>

</el-form-item>

</el-col>

<el-col :span="4">code>

<el-form-item label="统计时间">code>

<el-date-picker style="width: 100%; padding-top: 2px" v-model="queryParams.at" type="month"code>

placeholder="选择月" :picker-options="pickerOptions" format="yyyy-MM">code>

</el-date-picker>

</el-form-item>

</el-col>

<el-col :span="3">code>

<el-form-item class="comClass" style="margin-left: 12px; margin-bottom: 0px">code>

<el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery"code>

v-permisaction="['srm-supplier:report']">查询</el-button>code>

<el-button style="margin-left: 12px" type="warning" icon="el-icon-download" size="mini"code>

@click="handleExport">导出</el-button>code>

</el-form-item>

</el-col>

</el-row>

<el-row> </el-row>

</el-form>

<el-table id="kuTable" :data="tableData" border style="width: 100%" v-loading="loading" :max-height="500"code>

show-summary :summary-method="getSummaries" ref="table">code>

code>

<el-table-column header-align="center" :label="headerItem">code>

<el-table-column label="供应商ID" align="center" width="120">code>