前端 vue/react 或者 js 导入/导出 xlsx/xls (带样式)表格的功能

• Z时代
• 2024-01-10
• 分类：综合

第一种
导出表格的功能:

yarn add xlsx script-loader file-saver xlsx-style

效果展示

xlsx-style的bug修复:node_module/xlsx-style/dist/cpexcel.js的807行的var cpt = require(\'./cpt\' + \'able\')改为var cpt = cptable;

上封装的代码:

require(\'script-loader!file-saver\');
import XLSX from \'xlsx-style\';
import XLSX2 from \'xlsx\';
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;
}
// 通过table标签渲染导出表格
export function export_table_to_excel({
id,
filename,
bookType = \'xlsx\',
styleFun
} = {}) {
var table = document.querySelector(id);
var ws = XLSX2.utils.table_to_sheet(table);
styleFun(ws);
var wb = XLSX2.utils.book_new();
XLSX2.utils.book_append_sheet(wb, ws, "SheetJS");
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: \'binary\'
});
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;
}
saveAs(new Blob([s2ab(wbout)], { type: "" }), filename + "." + bookType)
}
//通过json渲染导出表格
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}`);
}

提供了2种调用方式:

1.直接获取table标签获取

exportTable() {
//通过table标签渲染导出表格
import("@/vendor/Export2Excel").then(excel => {
excel.export_table_to_excel({
id: this.id,
filename: this.filename,
bookType: this.bookType,
styleFun: function(ws) { // 自定义样式
for (let item in ws) {
switch (item) {
case "!merges":
break;
case "!ref":
break;
case "A1":
ws[\'A1\'].s = {
font: {
sz: 13,
bold: true,
color: {
rgb: "FFFFAA00"
}
},
alignment: {
horizontal: "center",
vertical: "center"
}
};
break;
default:
ws[item].s = {
font: {
sz: 13,
bold: true,
},
alignment: {
horizontal: "center",
vertical: "center"
}
};
}
}
}
});
});
},

2.通过后台数据data数组

exportTable() {
import("@/vendor/Export2Excel").then(excel => {
const multiHeader = [
[
"工作情况一览表",
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
[
"截止日期：2019年09月11日",
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
]
];
const tHeader = [
"序号",
"分类",
"报建项目",
"有效期",
"计划开始时间",
"计划完成时间",
"受理",
"所需资料",
"办理周期",
"责任人",
"工作进展"
];
const filterVal = [
"code",
"name",
"orderBy",
"pageviews",
"display_time"
];
const list = this.tableData;
const data = this.formatJson(filterVal, list);
const merges = [\'A1:K1\',\'A2:K2\']; // 合并单元格
excel.export_json_to_excel({
multiHeader,
header: tHeader,
merges,
data,
filename: this.filename,
autoWidth: this.autoWidth,
bookType: this.bookType
});
});
},
// 辅助导出表格的函数
formatJson(filterVal, jsonData) {
return jsonData.map(v =>
filterVal.map(j => {
if (j === "timestamp") {
return parseTime(v[j]);
} else {
return v[j];
}
})
);
},

对应的html就是element的table

<div id="protable">
<el-table :data="tableData" :span-method="objectSpanMethod" border style="width: 100%; margin-top: 20px">
<el-table-column label="工作情况一览表" align="center">
<el-table-column label="截止日期：2019年09月11日" align="right">
<el-table-column prop="id" label="序号" align="center">
</el-table-column>
<el-table-column prop="code" label="分类" align="center">
</el-table-column>
<el-table-column prop="name" label="报建项目" align="center">
</el-table-column>
<el-table-column prop="amount2" label="有效期" align="center">
</el-table-column>
<el-table-column prop="amount3" label="计划开始时间" align="center">
</el-table-column>
<el-table-column prop="amount3" label="计划完成时间" align="center">
</el-table-column>
<el-table-column prop="amount3" label="受理" align="center">
</el-table-column>
<el-table-column prop="amount3" label="所需资料" align="center">
</el-table-column>
<el-table-column prop="amount3" label="办理周期" align="center">
</el-table-column>
<el-table-column prop="amount3" label="责任人" align="center">
</el-table-column>
<el-table-column prop="amount3" label="工作进展概述" align="center">
</el-table-column>
</el-table-column>
</el-table-column>
</el-table>
</div>

第二种

1.新建exportExcel.js文件并填入以下代码

// 导出Excel方法（表格id，不加扩展名的文件名，sheet名）
export function exportExcelMethod(tableId, fileName, sheetName) {
tableToExcel(tableId, fileName, sheetName)
}
const tableToExcel = (function() {
const uri = \'data:application/vnd.ms-excel;base64,\'
// 设置导出表格的单元格默认高度/宽度/边框样式/字体颜色/背景颜色/居中，网页显示表格宽度建议1240，tr/td视情况而定
const template = `<html xmlns:x="urn:schemas-microsoft-com:office:excel"><head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><meta charset="UTF-8"><style type="text/css">table td {border: 1px solid #000000;width:100px;text-align: center;color: #000000;} th {border: 1px solid #000000;width:100px;text-align: center;color: #000000;}</style></head><body><table>{table}</table></body></html>`
const base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }
const format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p] }) }
return function(table, filename, sheetname) {
if (!table.nodeType) table = document.getElementById(table)
const ctx = { worksheet: sheetname || \'Worksheet\', table: table.innerHTML }
const aTag = document.createElement(\'a\')
aTag.href = uri + base64(format(template, ctx))
aTag.download = filename
aTag.click()
}
})()

2.在需要导出EXCEL的页面上引入方法

import { exportExcelMethod } from \'./exportExcel\'

3.将需要导出的表格元素定义一个id,我的table的id=“table”

<table id="table" class="tg" style="table-layout: fixed; width: 1228px;margin: auto;left: 50%;right: 50%">

<tr>
    <th style="background-color: red; height: 60px;" colspan="6">Header 1</th>
</tr>
<tr>
    <th style="background-color: red">Header 1</th>
    <th style="background-color: red">Header 1</th>
    <th style="background-color: red">Header 1</th>
    <th style="background-color: red">Header 1</th>
    <th style="background-color: red">Header 1</th>
    <th style="background-color: red">Header 1</th>
</tr>
<tr>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
</tr>
<tr>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
</tr>
<tr>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
</tr>
<tr>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
    <td>row 1, cell 1</td>
    <td>row 1, cell 2</td>
</tr>

</table>

4.使用导出函数，填入三个参数，第一个为表格元素的id，第二个为导出的excel文件名，第三个为sheet表名

exportExcelMethod(\'table\', \'发货单\', \'sheet1\')

以上是 前端 vue/react 或者 js 导入/导出 xlsx/xls (带样式)表格的功能 的全部内容， 来源链接： utcz.com/z/383353.html