vue使用 xlsx及xlsx-style导出表格(自定义表格样
2023-05-25 本文已影响0人
CoderZb
涉及到合并单元+去网格线+总列数不固定的情况
使用的是xlsx文档，使用方法等同于SheetJS中文文档 。
-
最终效果
image.png
步骤1：安装xlsx及xlsx
cnpm install xlsx --save
cnpm install xlsx-style --save
image.png
步骤2：修改xlsx-style插件包的代码
- 修改1：找到 node_modules-----> xlsx-style---->dist---->cpexcel.js
807行将
var cpt = require('./cpt' + 'able');改为var cpt = cptable;
image.png
- 修改2：找到 node_modules-----> xlsx-style---->ods.js
10行将
return require('../' + 'xlsx').utils;改为return require('./' + 'xlsx').utils;
image.png
- 修改3：如果你的excel想要设置行高功能的话，xlsx-style是无法设置的，但是该功能xlsx是有的，因此将相关功能代码复制粘贴到xlsx-style中就可以让使其具有设置行高的功能。
找到xlsx下的xlsx.js,复制如下两个截图代码到xlsx-style下的xlsx.js中
var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function write_ws_xml_data(ws, opts, idx, wb) {
var o = [], r = [], range = safe_decode_range(ws['!ref']), cell="", ref, rr = "", cols = [], R=0, C=0, rows = ws['!rows'];
var dense = Array.isArray(ws);
var params = ({r:rr}), row, height = -1;
for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
for(R = range.s.r; R <= range.e.r; ++R) {
r = [];
rr = encode_row(R);
for(C = range.s.c; C <= range.e.c; ++C) {
ref = cols[C] + rr;
var _cell = dense ? (ws[R]||[])[C]: ws[ref];
if(_cell === undefined) continue;
if((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
}
if(r.length > 0 || (rows && rows[R])) {
params = ({r:rr});
if(rows && rows[R]) {
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if(row.hpx) height = px2pt(row.hpx);
else if(row.hpt) height = row.hpt;
if(height > -1) { params.ht = height; params.customHeight = 1; }
if(row.level) { params.outlineLevel = row.level; }
}
o[o.length] = (writextag('row', r.join(""), params));
}
}
if(rows) for(; R < rows.length; ++R) {
if(rows && rows[R]) {
params = ({r:R+1});
row = rows[R];
if(row.hidden) params.hidden = 1;
height = -1;
if (row.hpx) height = px2pt(row.hpx);
else if (row.hpt) height = row.hpt;
if (height > -1) { params.ht = height; params.customHeight = 1; }
if (row.level) { params.outlineLevel = row.level; }
o[o.length] = (writextag('row', "", params));
}
}
return o.join("");
}
image.png
image.png
image.png
步骤3：使用
<script>
import * as XLSX from 'xlsx'
import xlsxStyleObj from 'xlsx-style'
export default {
data() {
return {
maxNum:0,
globalRowArray: [],
// 网络数据，其中couponDetailList和otherCouponDetailList包含的数据的个数，最终决定有多少列数据
allOrderListAll: [
{ "originalPayment": 251, "totalDiscountMoney": 22, "payment": 229, "couponName": "游泳消费券3", "discountMoney": 50, "enterpriseCouponName": "测试券1", "enterpriseCouponDiscountMoney": 22, "originalPrice": 360, "couponDetailList": [], "couponsDiscountMoney": 0, "otherCouponDetailList": [], "otherCouponsDiscountMoney": 50, "allInPayOrderNo": null, "price": 251, "title": null, "num": null, "accountNo": null, "benefitCategoryCode": null, "benefitCategoryName": null, "storeName": null, "couponList": null, "orderInfo": null, },
{ "originalPayment": 251, "totalDiscountMoney": 110, "payment": 141, "couponName": "体育消费券1", "discountMoney": 10, "enterpriseCouponName": null, "enterpriseCouponDiscountMoney": null, "originalPrice": 360, "couponDetailList": [{ "couponNo": "23J4J5HHLU ", "isOnline": null, "couponName": "体育消费券66", "discountMoney": 50, "couponId": null, "userId": null, "managerId": null }], "couponsDiscountMoney": 0, "otherCouponDetailList": [{ "couponNo": "1NAUJ885A3", "isOnline": null, "couponName": "体育消费券77", "discountMoney": 60, "couponId": null, "userId": null, "managerId": null }], "otherCouponsDiscountMoney": 50, "allInPayOrderNo": null, "price": 251, "title": null, "num": null, "accountNo": null, "benefitCategoryCode": null, "benefitCategoryName": null, "storeName": null, "couponList": null, "orderInfo": null, },
{ "originalPayment": 251, "totalDiscountMoney": 73, "payment": 178, "couponName": "体育消费券6", "discountMoney": 50, "enterpriseCouponName": "测试券34", "enterpriseCouponDiscountMoney": 43, "originalPrice": 360, "couponDetailList": [{ "couponNo": "4R1O833EUI", "isOnline": null, "couponName": "体育消费券5", "discountMoney": 30, "couponId": null, "userId": null, "managerId": null }], "couponsDiscountMoney": 0, "otherCouponDetailList": [], "otherCouponsDiscountMoney": 50, "allInPayOrderNo": null, "price": 251, "title": null, "num": null, "accountNo": null, "benefitCategoryCode": null, "benefitCategoryName": null, "storeName": null, "couponList": null, "orderInfo": null, }
]
};
},
methods: {
// 按钮点击
exportBtn() {
// 1、数据源设置
for (var i = 0; i < this.allOrderListAll.length; i++) {// 动态向数据源中添加带有索引的couponName,discountMoney属性 并赋值，为的是在excel列中取出该属性的属性值并展示
var list1 = [...this.allOrderListAll[i].couponDetailList];
var list2 = [...this.allOrderListAll[i].otherCouponDetailList];
if (list1.length || list2.length) {
var list3 = [];
list1.forEach(item => {
list3.push(item);
});
list2.forEach(item => {
list3.push(item);
});
for (var j = 0; j < list3.length; j++) {
// 必须修改数据源，这样导出的时候，自动匹配数据源中的key
this.allOrderListAll[i]['couponName' + (j + 1)] = list3[j] ? list3[j].couponName : '';
this.allOrderListAll[i]['discountMoney' + (j + 1)] = list3[j].discountMoney ? list3[j].discountMoney : '';
}
}
}
var couponCountList = [];
for (var i = 0; i < this.allOrderListAll.length; i++) {// 统计数据中，最多使用了几张券
couponCountList.push(this.allOrderListAll[i].couponDetailList.length + this.allOrderListAll[i].otherCouponDetailList.length);
}
console.log('couponCountList---',couponCountList);
this.maxNum = Math.max(...couponCountList);// 所有数据中，找到每条数据中最多使用的券的张数. 用于计算excel中一共多少列数据
// 2、表头设置
let rowArray = [ // 3行
["核销结算表"],
["年 月 日 至", '', ''],
["商品原价(元)", "总优惠(元)","实付金额(元)"],
];
for (var i = 0; i < this.maxNum; i++) {
rowArray[1].push('');
rowArray[1].push('');
rowArray[2].push('惠民券名称' + (i + 1) + '');
rowArray[2].push('惠民券金额' + (i + 1) + '(元)');
}
rowArray[1].push('');
rowArray[1].push(' 年 月 日');
rowArray[2].push('企业券名称');
rowArray[2].push('企业券金额');
// 3、表头对应字段设置
var keyList = this.allOrderListAll.map((x, index) => {
var tempList = [x.originalPayment, x.totalDiscountMoney, x.payment];
for (var i = 0; i < this.maxNum; i++) {
tempList.push(x["couponName" + (i + 1)]);
tempList.push(x["discountMoney" + (i + 1)]);
}
tempList.push(x["enterpriseCouponName"]);
tempList.push(x["enterpriseCouponDiscountMoney"]);
return tempList;
})
var finalList = [];
for (var i = 0; i < keyList.length; i++) {
var dealList = [];
for (var j = 0; j < keyList[0].length; j++) {
dealList.push(keyList[i][j] == null ? '' : keyList[i][j]);
}
finalList.push(dealList);
}
const body = finalList;
body.unshift(...rowArray);
this.globalRowArray = rowArray;
var monenyList = [];
monenyList.push('汇总金额合计：');
monenyList.push('');
monenyList.push('');
for (var i = 0; i < this.maxNum; i++) {
monenyList.push('');
monenyList.push('');
}
monenyList.push('');
monenyList.push('');
body.push(monenyList)
var footerList = [];
footerList.push('填表人：');
footerList.push('');
footerList.push('时间：');
footerList.push('');
footerList.push('单位：（盖章）');
footerList.push('');
body.push(footerList)
const sheet = this.addStyleFunc(body);
this.downloadFunc(this.sheetToBlobFunc(sheet), rowArray[0] + '.xlsx');
},
// 将Blob下载到本地
downloadFunc(data, filename) {
if (window.navigator && window.navigator.msSaveOrOpenBlob) {// IE浏览器使用 msSaveOrOpenBlob()方法下载
window.navigator.msSaveOrOpenBlob(data, filename)// 兼容IE
} else {// 其他浏览器
const url = URL.createObjectURL(data);// 创建URL对象
const link = document.createElement('a');// 创建a标签
link.href = url;// 给a标签添加超链接
link.download = filename;
document.body.appendChild(link);// 模拟点击链接实现下载操作
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(url); // 释放URL对象
}
},
// 将sheet转成Blob
sheetToBlobFunc(sheet) {
var workbookJson = {
SheetNames: ["zb_sheet1"],
Sheets: {},
};
workbookJson.Sheets["zb_sheet1"] = sheet;
// 配置excel的相关属性
var workbookOptions = {
bookType: "xlsx", // 要生成的文件类型
bookSST: false,
showGridLines: false,// 是否显示网格线，默认为true
type: "binary",
};
var generateFile = xlsxStyleObj.write(workbookJson, workbookOptions);
var blobObj = new Blob([this.strToArrayBufferFunc(generateFile)], { type: "application/octet-stream" });
return blobObj;
},
// 字符串转成ArrayBuffer
strToArrayBufferFunc(str) {
var buffer = new ArrayBuffer(str.length);
var view = new Uint8Array(buffer);
for (var i = 0; i !== str.length; ++i) view[i] = str.charCodeAt(i) & 0xff;
return buffer;
},
// 为excel表格增加样式
addStyleFunc(xlsx) {
const sheet = XLSX.utils.aoa_to_sheet(xlsx);// 将JS数据的数组转换为工作表。
console.log('----sheet---', sheet);// 包含着每行每列的相关信息(v: 单元格的值;t: 单元格的类型，b布尔值、n数字、e错误、s字符串、d日期;s: 单元格的样式)。空行空列不
var mergeArr = []; // 要合并的单元格数组
// const rowList = []; // 表格每列高度
// 单元格边框-自定义边框样式
const borderCustomer = {
top: { style: "medium", color:{rgb: "000000"} },
bottom: { style: "medium", color:{rgb: "000000"} },
left: { style: "medium", color:{rgb: "000000"} },
right: { style: "medium", color:{rgb: "000000"} },
};
// 单元格边框--去掉边框
const borderCut = {
top: { style: "" },
bottom: { style: "" },
left: { style: "" },
right: { style: "" },
};
for (const key in sheet) {
if (Object.hasOwnProperty.call(sheet, key)) {
const element = sheet[key];
console.log('sheet--', sheet, '---key---', key, '----element--', element);
if (typeof element === "object") {// v: 单元格的值;t: 单元格的类型，b布尔值、n数字、e错误、s字符串、d日期;s: 单元格的样式
// slice() 方法可从已有的数组中返回选定的元素。
// key如果为A234,那么key.slice(2)，就从第2个索引开始，截到末尾，也就是34 (已验证)
const index = Number(key.slice(1)) - 1;// 所有同行的，index一样。如A2和B2，得到的index都为1
console.log('index---', index);
element.s = {
alignment: {
horizontal: "center", // 所有单元格剧中对其
vertical: "center", // 所有单元格垂直居中
},
font: {
name: "黑体",
sz: 10,
italic: false,
underline: false,
},
border: borderCustomer,
fill: {
fgColor: { rgb: "FFFFFFFF" },
},
};
// if (key.indexOf("H") != -1) {// key中有H，则右对齐。 -1表示没找到H。
// element.s.alignment.horizontal = "right";
// }
if (index === 0) {
element.s.font.bold = true;
element.s.font.sz = 24;
element.s.border = borderCut;
element.s.fill.fgColor = { rgb: "ffffff" };
} else if (index === 1) {
element.t = 's';
element.s.alignment.horizontal = 'right';
element.s.font.sz = 14;
element.s.font.bold = true;
element.s.border = borderCut;
} else if (index === 2) {
element.s.font.bold = true;
element.s.fill.fgColor = { rgb: "FFD39B" };
element.t = 's';
element.s.font.sz = 13;
element.s.font.color = { rgb: "1C1C1C" }; /// FF00FF b8ddb0
} else if (index === this.allOrderListAll.length + 3) {
element.s.font.bold = true;
element.s.font.sz = 20;
element.s.font.name = 'Courier';
} else if (index === this.allOrderListAll.length + 4) {
element.s.font.bold = true;
element.s.font.sz = 15;
element.s.font.name = 'Courier';
element.s.border = borderCut;
}
}
}
}
let maxColumnNumber = 1; // 默认最大列数为1
this.globalRowArray.map(item => item.length > maxColumnNumber ? maxColumnNumber = item.length : '');// 遍历每一行，找到最大列数
console.log('--globalRowArray--', this.globalRowArray.length, '---maxColumnNumber--', maxColumnNumber);
mergeArr = [
{ s: { c: 0, r: 0 }, e: { c: maxColumnNumber - 1, r: 0 } },
{ s: { c: 0, r: 1 }, e: { c: maxColumnNumber - 2, r: 1 } },
{ s: { c: 0, r: 3 + this.allOrderListAll.length }, e: { c: 1, r: 3 + this.allOrderListAll.length } },
{ s: { c: 0, r: 4 + this.allOrderListAll.length }, e: { c: 1, r: 4 + this.allOrderListAll.length } },
{ s: { c: 2, r: 4 + this.allOrderListAll.length }, e: { c: 3, r: 4 + this.allOrderListAll.length } },
{ s: { c: 4, r: 4 + this.allOrderListAll.length }, e: { c: 5, r: 4 + this.allOrderListAll.length } }
];
sheet["!merges"] = mergeArr;
// 单元格的列宽
sheet['!cols'] = [];
for(var i = 0;i < 5+this.maxNum*2;i++){
var json = {wpx:200};
sheet['!cols'].push(json);
}
// 单元格的行宽
sheet['!rows'] = [];
for(var i = 0;i < 5+this.allOrderListAll.length;i++){
if(i == 0){
var json = {hpx:58};
sheet['!rows'].push(json);
}else if(i == 1){
var json = {hpx:38};
sheet['!rows'].push(json);
}else if(i == 2){
var json = {hpx:32};
sheet['!rows'].push(json);
}else if(i == this.allOrderListAll.length + 3){
var json = {hpx:58};
sheet['!rows'].push(json);
}else if(i == this.allOrderListAll.length + 4){
var json = {hpx:58};
sheet['!rows'].push(json);
}else{
var json = {hpx:26};
sheet['!rows'].push(json);
}
}
return sheet;
},
},
mounted() {
},
};
</script>
<style scoped>
</style>
