Vue表格导出Excel数据，自定义表头，使用xlsx-style修饰
vue
vuejs/vue: 是一个用于构建用户界面的 JavaScript 框架，具有简洁的语法和丰富的组件库，可以用于开发单页面应用程序和多页面应用程序。
项目地址：https://gitcode.com/gh_mirrors/vu/vue
免费下载资源
·
继续上篇文章封装导出方法:Vue表格导出Excel数据（支持多级表头）
效果图：
1、安装xlsx-style依赖：
yarn add xlsx-style2、安装node-polyfill-webpack-plugin依赖：
yarn add node-polyfill-webpack-plugin -D解决报错：jszip is not a constructor
3、配置vue.config.js： ！！！配置完重新运行程序
解决报错：Module not found: Error: Can't resolve 'fs' in \***\xlsx-style
和 Can‘t resolve ‘./cptable‘ in ‘xxx\node_modules_xlsx
const NodePolyfillPlugin = require("node-polyfill-webpack-plugin")configureWebpack: {
plugins: [
new NodePolyfillPlugin()
],
resolve: {
fallback: {
fs: false
}
},
externals: {
'./cptable': 'var cptable',
},
},4、在根目录src文件夹下新建excel文件导入Blob.js文件：
/*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */
(function(view){view.URL=view.URL||view.webkitURL;if(view.Blob&&view.URL){try{new Blob;return}catch(e){}}var BlobBuilder=view.BlobBuilder||view.WebKitBlobBuilder||view.MozBlobBuilder||(function(view){var get_class=function(object){return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1]},FakeBlobBuilder=function BlobBuilder(){this.data=[]},FakeBlob=function Blob(data,type,encoding){this.data=data;this.size=data.length;this.type=type;this.encoding=encoding},FBB_proto=FakeBlobBuilder.prototype,FB_proto=FakeBlob.prototype,FileReaderSync=view.FileReaderSync,FileException=function(type){this.code=this[this.name=type]},file_ex_codes=("NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "+"NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR").split(" "),file_ex_code=file_ex_codes.length,real_URL=view.URL||view.webkitURL||view,real_create_object_URL=real_URL.createObjectURL,real_revoke_object_URL=real_URL.revokeObjectURL,URL=real_URL,btoa=view.btoa,atob=view.atob,ArrayBuffer=view.ArrayBuffer,Uint8Array=view.Uint8Array;FakeBlob.fake=FB_proto.fake=true;while(file_ex_code--){FileException.prototype[file_ex_codes[file_ex_code]]=file_ex_code+1}if(!real_URL.createObjectURL){URL=view.URL={}}URL.createObjectURL=function(blob){var type=blob.type,data_URI_header;if(type===null){type="application/octet-stream"}if(blob instanceof FakeBlob){data_URI_header="data:"+type;if(blob.encoding==="base64"){return data_URI_header+";base64,"+blob.data}else{if(blob.encoding==="URI"){return data_URI_header+","+decodeURIComponent(blob.data)}}if(btoa){return data_URI_header+";base64,"+btoa(blob.data)}else{return data_URI_header+","+encodeURIComponent(blob.data)}}else{if(real_create_object_URL){return real_create_object_URL.call(real_URL,blob)}}};URL.revokeObjectURL=function(object_URL){if(object_URL.substring(0,5)!=="data:"&&real_revoke_object_URL){real_revoke_object_URL.call(real_URL,object_URL)}};FBB_proto.append=function(data){var bb=this.data;if(Uint8Array&&(data instanceof ArrayBuffer||data instanceof Uint8Array)){var str="",buf=new Uint8Array(data),i=0,buf_len=buf.length;for(;i<buf_len;i++){str+=String.fromCharCode(buf[i])}bb.push(str)}else{if(get_class(data)==="Blob"||get_class(data)==="File"){if(FileReaderSync){var fr=new FileReaderSync;bb.push(fr.readAsBinaryString(data))}else{throw new FileException("NOT_READABLE_ERR")}}else{if(data instanceof FakeBlob){if(data.encoding==="base64"&&atob){bb.push(atob(data.data))}else{if(data.encoding==="URI"){bb.push(decodeURIComponent(data.data))}else{if(data.encoding==="raw"){bb.push(data.data)}}}}else{if(typeof data!=="string"){data+=""}bb.push(unescape(encodeURIComponent(data)))}}}};FBB_proto.getBlob=function(type){if(!arguments.length){type=null}return new FakeBlob(this.data.join(""),type,"raw")};FBB_proto.toString=function(){return"[object BlobBuilder]"};FB_proto.slice=function(start,end,type){var args=arguments.length;if(args<3){type=null}return new FakeBlob(this.data.slice(start,args>1?end:this.data.length),type,this.encoding)};FB_proto.toString=function(){return"[object Blob]"};FB_proto.close=function(){this.size=this.data.length=0};return FakeBlobBuilder}(view));view.Blob=function Blob(blobParts,options){var type=options?(options.type||""):"";var builder=new BlobBuilder();if(blobParts){for(var i=0,len=blobParts.length;i<len;i++){builder.append(blobParts[i])}}return builder.getBlob(type)}}(typeof self!=="undefined"&&self||typeof window!=="undefined"&&window||this.content||this));
5、在文件夹/utils/TableToExcel.js引入（全部代码，复制使用即可）：
import * as XLSX from "xlsx";
import XLSXS from "xlsx-style";
import FileSaver from "file-saver";
import '@/excel/Blob.js';
function datenum(v, date1904) {
if (date1904) v += 1462;
let epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data) {
let ws = {};
let range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (let R = 0; R !== data.length; ++R) {
for (let 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;
let cell = {
v: data[R][C]
};
if (cell.v == null) continue;
let 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) {
let buf = new ArrayBuffer(s.length);
let view = new Uint8Array(buf);
for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
function autoWidth(data) {
/*设置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 + 4
};
} else {
return {
'wch': val.toString().length + 4
};
}
}))
/*以第一行为初始值*/
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'];
}
}
}
return result;
}
const uppercaseAlphabet = [];
for (let i = 65; i <= 90; i++) {
const letter = String.fromCharCode(i);
uppercaseAlphabet.push(letter);
}
function isKey(key) {//判断是不是表头
let Y = false
for (let i = 65; i <= 90; i++) {
const letter = String.fromCharCode(i);
if (key === `${letter}1`) {
Y = true
}
}
return Y
}
function export_json_to_excel(tHeader, jsonData, defaultTitle) {
/* original data */
let data = jsonData;
data.unshift(tHeader);
let ws_name = "SheetJS";
let wb = new Workbook();
let ws = sheet_from_array_of_arrays(data);
ws['!cols'] = autoWidth(data); //设置单元格宽度自适应
Object.keys(ws).forEach((key) => { //这里遍历单元格给单元格对象设置属性,s为控制样式的属性
if (key.indexOf('!') < 0) {
// console.log(key);
if (isKey(key)) {
// 设置表头文字加粗字号突出
ws[key].s = {
alignment: { //对齐方式
horizontal: 'center', //水平居中
vertical: 'center', //竖直居中
},
font: {
sz: 13, // 字号
name: '楷体', // 字体
bold: true, //加粗
},
fill: {
fgColor: { //单元格背景颜色
rgb: "FFFFAA00"
},
}
}
} else {
//为所有的单元格设置样式
ws[key].s = {
alignment: { //对齐方式
horizontal: 'center', //水平居中
vertical: 'center', //竖直居中
wrapText: true, //自动换行
},
font: {
sz: 12, // 字号
name: '楷体' // 字体
},
}
}
}
})
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
let wbout = XLSXS.write(wb, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
let title = defaultTitle || '表单';
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), title + ".xlsx")
}
export default {
// 导出Excel表格
// name表示生成excel的文件名 IDName表示表格的id
exportExcel(name, IDName) {
var fix_l = document.querySelector('.el-table__fixed'); //fixed 或者 fixed='left'
var fix_r = document.querySelector('.el-table__fixed-right'); //fixed='right'
var wb;
// 以下代码解决table中使用fixed属性导致导出的数据重复问题
if (fix_l) { //判断是否有浮动或者是左浮动
wb = XLSX.utils.table_to_book(document.querySelector(IDName).removeChild(fix_l));
document.querySelector(IDName).appendChild(fix_l);
} else if (fix_r) { //判断是否有右浮动
wb = XLSX.utils.table_to_book(document.querySelector(IDName).removeChild(fix_r));
document.querySelector(IDName).appendChild(fix_r);
} else if (fix_l && fix_r) { //左右浮动都存在
wb = XLSX.utils.table_to_book(document.querySelector(IDName).removeChild(fix_l).removeChild(fix_r));
document.querySelector(IDName).appendChild(fix_l).appendChild(fix_r);
} else { //没有浮动
wb = XLSX.utils.table_to_book(document.querySelector(IDName))
}
// var wb = XLSX.utils.table_to_book(document.querySelector(IDName))
var wbout = XLSX.write(wb, {
bookType: 'xlsx',
bookSST: true,
type: 'array'
})
try {
FileSaver.saveAs(new Blob([wbout], {
type: 'application/octet-stream'
}), `${name}.xlsx`);
} catch (e) {
if (typeof console !== 'undefined') console.log(e, wbout)
}
return wbout;
},
// 导出Excel表格自定义标头和内容
// excelData表示el-table数据 tableName表示生成excel的文件名
export2Excel(excelData, excelObj, tableName) {
require.ensure([], () => {
// 导出的excel的表头字段可自定义
let tHeader = []
// 对象属性，对应于tHeader，即prop的值，可自定义导出字段
let filterVal = []
for (let i in excelObj) {
tHeader.push(i)
filterVal.push(excelObj[i])
}
// 格式转换数据
const data = excelData.map(v => filterVal.map(j => v[j]))
// console.log(data);
export_json_to_excel(tHeader, data, tableName) // 导出的表格名称，可自定义
})
},
}6、全局引入注册main.js：
import TableToExcel from '@/utils/TableToExcel';
Vue.prototype.$TableToExcels = TableToExcel7、页面使用：
exportBtn() {
//导出
// this.$TableToExcels.exportExcel('导出的Excel名', '#table的id名')
let excelData = this.tableData//导出的所有数据集合
let excelObj = { //自定义表头 自定义对应的字段值 即prop的值
'所属疗区':'OrgName',
'坐落名称':'StandName',
'土地总面积(m²)':'StandArea',
'坐标所在地':'StandEncode'
}
this.$TableToExcels.export2Excel(excelData,excelObj,'导出的Excel名')
},另外使用xlsx-style还可以设置单元格的其他的格式，可以参考这篇文章，很全。
JavaScript导出excel文件，并修改文件样式
GitHub 加速计划 / vu / vue
207.55 K
33.66 K
下载
vuejs/vue: 是一个用于构建用户界面的 JavaScript 框架，具有简洁的语法和丰富的组件库，可以用于开发单页面应用程序和多页面应用程序。
最近提交(Master分支：3 个月前 )
73486cb5
* chore: fix link broken
Signed-off-by: snoppy <michaleli@foxmail.com>
* Update packages/template-compiler/README.md [skip ci]
---------
Signed-off-by: snoppy <michaleli@foxmail.com>
Co-authored-by: Eduardo San Martin Morote <posva@users.noreply.github.com> 5 个月前
e428d891
Updated Browser Compatibility reference. The previous currently returns HTTP 404. 5 个月前
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
2
0- 0
已为社区贡献4条内容
所有评论(0)