vue导出excel并修改表头样式
vue导出excel并修改样式
本⽂参考（侵删）
1. 安装插件
npm install --save xlsx(修改样式需要下载npm install --save xlsx-style)
npm install -S file-saver
npm install -D script-loader
安装npm install --save xlsx-style会出现不到cpexcel.js的错误
解决⽅案：创建⾃定义node_moduls⽂件夹替换插件内⽂件（）或直接修改源码
\node_modules\xlsx-style\dist\cpexcel.js 807⾏ 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;
2. 下载Blob.js⽂件
3. 新建⼀个Export2Excel.js（摘⾃）
//这是从⽹上的，改了⼀点点 Export2Excel.js(此内容转⾃blog.csdn/qq_38502227/article/details/101702819）
require("script-loader!file-saver");
import XLSX from"xlsx-style";
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;
<R) =R;
<C) =C;
var cell ={
v: data[R][C]
};
if(cell.v ==null)continue;
var cell_ref =de_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"]=de_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;
}
function export_json_to_excel({
title,
multiHeader =[],
header,
data,
filename,
merges =[],
autoWidth =true,
bookType ="xlsx"
}={}){
// const merges = [`A1:${getCharCol(list.length - 2)}1`]; //合并单元格/* original data */
filename = filename ||"excel-list";
data =[...data];
data.unshift(header);
if(title){
data.unshift(title);
}
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:13
};
}else String().charCodeAt(0)>255){
/*再判断是否为中⽂*/
return{
wch: String().length *2
};
}else{
return{
wch: String().length
};
}
})
);
// console.log(colWidth);
/*以第⼀⾏为初始值*/
let result = colWidth[0];
colWidth[0][0]["wch"]=13;
// console.log(colWidth[0][0]["wch"]);
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 dataInfo = wb.Sheets[wb.SheetNames[0]];
// const borderAll = {
//  //单元格外侧框线
//  top: {
//    style: "thin"
//  },
//  bottom: {
/
/    style: "thin"
//  },
//  left: {
//    style: "thin"
//  },
//  right: {
//    style: "thin"
//  }
// };
//给所以单元格加上边框
// for (var i in dataInfo) {
/
/  if (i == '!ref' || i == '!merges' || i == '!cols' || i == 'A1') {
//  } else {
//    dataInfo[i + ''].s = {
//      border: borderAll
//    }
//  }
// }
// 标题⾏
let arr =[
"A1",
"B1",
"C1",
"D1",
"E1",
"F1",
"G1",
"H1",
"J1",
"K1",
"L1",
"M1",
"N1",
"O1",
"P1",
"Q1",
"R1",
"S1",
"T1",
"U1",
"V1",
"W1",
"X1",
"Y1",
"Z1"
];
// arr.some(function(v) {
//  let a = merges[0].split(":");
//  if (v == a[1]) {
//    dataInfo[v].s = {};
//    return true;
//  } else {
//    dataInfo[v].s = {};
//  }
// });
/
/设置主标题样式
let style ={
font:{
// name: "宋体",
// sz: 18,
color:{ rgb:"000000"},
bold:true
// italic: false,
// underline: false
},
alignment:{
horizontal:"center",
vertical:"center"
}
// fill: {
//  fgColor: {rgb: "008000"},
// },
};
// dataInfo["B1"].s = style;
// dataInfo["A1"].s = style;
//excel标题样式
for(var i =0; i < header.length; i++){    dataInfo[arr[i]].s = style;
}
var wbout =XLSX.write(wb,{
bookType: bookType,
bookSST:false,
type:"binary"
});
saveAs(
new Blob([s2ab(wbout)],{
type:"application/octet-stream" }),
`${filename}.${bookType}`
}
function toExportExcel(tHeader, data, filename){
import("@/utils/excel/Export2Excel").then(excel =>{
//表头对应字段
header: tHeader,
data: data,
filename: filename,
autoWidth:true,
bookType:"xlsx"
});
});
}
export{ toExportExcel, export_json_to_excel };
4. 将Blob.js和Export2Excel.js建⽴⼀个excel.js⽂件（摘⾃）(此内容转⾃blog.csdn/qq_38502227/article/details/101702819）
//util.js
import"@/utils/excel/Blob";
export{ json2excel, getCharCol, formatJson };
// function parseTime(time, cFormat) {
//  if (arguments.length === 0) {
//    return null;
//  }
//  const format = cFormat || "{y}-{m}-{d} {h}:{i}:{s}";
//  let date;
//  if (typeof time === "object") {
//    date = time;
//  } else {
//    if (typeof time === "string" && /^[0-9]+$/.test(time)) {
//      time = parseInt(time);
//    }
//    if (typeof time === "number" && String().length === 10) {
//      time = time * 1000;
//    }
//    date = new Date(time);
/
/  }
//  const formatObj = {
//    y: FullYear(),
//    m: Month() + 1,
//    d: Date(),
getsavefilename//    h: Hours(),
//    i: Minutes(),
//    s: Seconds(),
//    a: Day()
//  };
//  // eslint-disable-next-line
/
/  const time_str = place(/{(y|m|d|h|i|s|a)+}/g, (result, key) => {
//    let value = formatObj[key];
//    // Note: getDay() returns 0 on Sunday
//    if (key === "a") {
//      return ["⽇", "⼀", "⼆", "三", "四", "五", "六"][value];
//    }
//    if (result.length > 0 && value < 10) {

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。