使用VUE+XLSX实现纯前端的导出Excel
此方法仅在@vue/cli3的项目中使用，其他项目暂未测试～
1、安装需要的插件
npm i xlsx -S
npm i xlsx-style -S
npm i file-saver -S
安装完xlsx-style插件并引入后启动项目会报错
./cptable in ./node_modules/xlsx-style/dist.cpexcel.js
出现这个问题时，需要在vue.config.js中的configureWebpack配置项中添加如下配置即可：
externals:{
'./cptable':'var cptable'
}
2、引入并使用
点击查看代码
//引入相关插件
import XLSX from "xlsx";
import XLSXStyle from 'xlsx-style';
import XLSX_SAVE from 'file-saver';
//导出的源数据
let originData = [//每一个Object对应excel一行的内容
{
"表头1":'value1',
"表头2":"value2",
"表头3":"value3"
},
{
"表头1":'value1',
"表头2":"value2",
"表头3":"value3"
}
];
let wb = {
SheetNames:['工作簿名称'],
Sheets:{},
Props:{}
};
let ws = XLSX.utils.json_to_sheet(originData);
//设置表格每列的宽度
ws['!cols'] = [200,200,200];
//设置单元格属性
Object.keys(ws).forEach(key => {
if(key.indexOf('!') < 0) {//判断是否是每个单元格
//获取单元格的序号
let num = key.replace(/[^0-9]/ig,"");
if(num.length === 1 && +num ===1){//判断是否为首行
//设置首行样式
ws[key].s = {
alignment: {
horizontal:'center',//水平居中
vertical:'center',//垂直居中
wrapText:true//是否自动换行
},
border: {//设置边框
top:{style:'thin'},
bottom:{style:'thin'},
right:{style:'thin'}
},
fill:{
fgColor:{rgb:ws[key].v.indexOf('必填') === -1 ? "E1F3D8" : "F56C6C"};//单元格背景色的填充
}
}
} else {//非首行的单元格样式
ws[key].s = {
alignment: {
horizontal:'center',
vertical:'center',
wrapText:true
}
}
}
}
})
wb.Sheets[工作簿名称] = ws;
const wbout = XLSXStyle.write(wb,{
type:"binary",
bookType:"xlsx"
})
XLSX_SAVE.saveAs(new Blob([s2ab(wbout)],{type:'application/octet-stream'}),导出的excel文件名称);
const 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;
}
3、vue项目中的简单封装使用
单工作簿的导出，可直接复制调用
点击查看代码
//导出excel方法
import XLSX from "xlsx";
import XLSXStyle from 'xlsx-style';
import XLSX_SAVE from 'file-saver';
/*
*
* originData => 导出的表格数据，最后传入的格式为： ****必传****
* [
* {
* '表头1':'xxx1',
* '表头2':'yyy1',
* '表头3':'zzz1',
* },
* {
* '表头1':'xxx2',
* '表头2':'yyy2',
* '表头3':'zzz2',
* },
* ]
*
* sheetName => excel表格里的sheet名称 ****必传****
*
* excelName => excel表格的名称 ****必传****
*
* colWidth => 表格列宽 可传可不传
* ==> 不传默认为200
* ==> 可传数字，代表所有列宽均为此宽度
* ==> 可传数组，[200,300,400,500,...]按数组值依次设置列宽
*
*/
export const exportExcel = (originData,sheetName,excelName,colWidth) =>{
let sheet = sheetName ? sheetName : '导出的excel';
let excel = excelName ? excelName : '导出的excel';
let columnWidth = [];
if (colWidth){
if (typeof colWidth === "number"){//只传了列宽的值，表明所有列宽均为此值
if (originData.length) {
Object.keys(originData[0]).forEach(item=>{
columnWidth.push({wpx:colWidth})
})
}
}else if (colWidth instanceof Array) {//传进来的是数组，按顺序依次赋值
colWidth.forEach(item=>{
columnWidth.push({wpx:item});
})
}
}else {
if (originData.length) {
Object.keys(originData[0]).forEach(item=>{
columnWidth.push({wpx:200})
})
}
}
var wb = {
SheetNames: [sheet],
Sheets: {},
Props: {},
}
var ws = XLSX.utils.json_to_sheet(originData);
ws['!cols'] = columnWidth;
Object.keys(ws).forEach((key) => {//设置单元格属性
if (key.indexOf('!') < 0) {
//获取单元格的数字
let num = key.replace(/[^0-9]/ig,"");
if (num.length === 1 && +num === 1){
ws[key].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true,
},
border: { //单元格外侧框线
top: {
style: 'thin'
},
bottom: {
style: 'thin'
},
right: {
style: 'thin'
}
},
fill:{
fgColor: { rgb: ws[key].v.indexOf('必填') === -1 ? "E1F3D8" : 'F56C6C' },
}
}
}else {
ws[key].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true,
},
}
}
}
})
wb.Sheets[sheet] = ws
const wbout = XLSXStyle.write(wb, {
type: 'binary',
bookType: 'xlsx',
})
XLSX_SAVE.saveAs(new Blob([s2ab(wbout)], {type: 'application/octet-stream',}), `${excel}.xlsx`);
}
const 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;
}
多工作簿的导出，可直接复制调用
点击查看代码
//导出excel方法
import XLSX from "xlsx";
import XLSXStyle from 'xlsx-style';
import XLSX_SAVE from 'file-saver';
/*
*
* originArr => 导出的表格数据，最后传入的格式为： ****必传****
* [
* {
* originData:[
* {
* '表头1':'xxx1',
* '表头2':'yyy1',
* '表头3':'zzz1',
* },
* {
* '表头1':'xxx2',
* '表头2':'yyy2',
* '表头3':'zzz2',
* },
* ],
* sheetName:"xxxx",
* colWidth:200
* }
* ]
*
*
* excelName => excel表格的名称 ****必传****
*
*
*/
export const exportMoreExcel = (originArr,excelName) =>{
let sheetName = [];
let excel = excelName ? excelName : '导出的excel';
originArr.forEach(item=>{
sheetName.push(item.sheetName);
})
var wb = {
SheetNames: sheetName,
Sheets: {},
Props: {},
}
originArr.forEach(originItem=>{
let {originData,sheetName,colWidth} = originItem;
let columnWidth = [];
if (colWidth){
if (typeof colWidth === "number"){//只传了列宽的值，表明所有列宽均为此值
if (originData.length) {
Object.keys(originData[0]).forEach(item=>{
columnWidth.push({wpx:colWidth})
})
}
}else if (colWidth instanceof Array) {//传进来的是数组，按顺序依次赋值
colWidth.forEach(item=>{
columnWidth.push({wpx:item});
})
}
}else {
if (originData.length) {
Object.keys(originData[0]).forEach(item=>{
columnWidth.push({wpx:200})
})
}
}
var ws = XLSX.utils.json_to_sheet(originData);
ws['!cols'] = columnWidth;
Object.keys(ws).forEach((key) => {//设置单元格属性
if (key.indexOf('!') < 0) {
//获取单元格的数字
let num = key.replace(/[^0-9]/ig,"");
if (num.length === 1 && +num === 1){
ws[key].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true,
},
border: { //单元格外侧框线
top: {
style: 'thin'
},
bottom: {
style: 'thin'
},
right: {
style: 'thin'
}
},
fill:{
fgColor: { rgb: ws[key].v.indexOf('必填') === -1 ? "E1F3D8" : 'F56C6C' },
}
}
}else {
ws[key].s = {
alignment: {
horizontal: 'center',
vertical: 'center',
wrapText: true,
},
}
}
}
})
wb.Sheets[sheetName] = ws
})
const wbout = XLSXStyle.write(wb, {
type: 'binary',
bookType: 'xlsx',
})
XLSX_SAVE.saveAs(new Blob([s2ab(wbout)], {type: 'application/octet-stream',}), `${excel}.xlsx`);
}
const 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;
}