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

第一种
导出表格的功能:

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')