图例
1.先下载相关的依赖
npm i -S xlsx
npm i -S file-saver
npm i -S xlsx-style
如果安装完成,启动报错Can‘t resolve ‘./cptable‘ in ‘xxx\node_modules_xlsx,有两个方案解决
(1)找到node_modules\xlsx-style\dist\cpexcel.js中将
var cpt = require(‘./cpt’ + ‘able’) 修改为var cpt = cptable
(2)vue.config.js中修改配置
module.exports = {
configureWebpack: {
externals: {
‘./cptable’: ‘var cptable’
}
}
}
2.vue文件
<template>
<div class="main-container">
<el-button type="primary" icon="el-icon-document-delete" @click="exportExcel()">导出excel</el-button>
<el-table :data="tableData" style="width:100%" class="accept-excel-table" border stripe highlight-current-row>
<el-table-column label="登记情况">
<el-table-column label="统计表">
<el-table-column label="单位:件">
<el-table-column label="作品类别" width="110">
<el-table-column prop="belongRegionName" label="登记点" width="110"></el-table-column>
</el-table-column>
<el-table-column label="总计(件)" resizable show-overflow-tooltip min-width="60"
prop="sum"></el-table-column>
<el-table-column label="文字" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.a.value }}</template>
</el-table-column>
<el-table-column label="口述" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.m.value }}</template>
</el-table-column>
<el-table-column label="音乐" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.b.value }}</template>
</el-table-column>
<el-table-column label="戏剧" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.c.value }}</template>
</el-table-column>
<el-table-column label="曲艺" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.d.value }}</template>
</el-table-column>
<el-table-column label="舞蹈" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.e.value }}</template>
</el-table-column>
<el-table-column label="杂技艺术" show-overflow-tooltip min-width="70">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.n.value }}</template>
</el-table-column>
<el-table-column label="美术" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.f.value }}</template>
</el-table-column>
<el-table-column label="工程设计图、产品设计图" show-overflow-tooltip min-width="140">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.j.value }}</template>
</el-table-column>
<el-table-column label="建筑" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.o.value }}</template>
</el-table-column>
<el-table-column label="摄影" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.g.value }}</template>
</el-table-column>
<el-table-column label="电影" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.h.value }}</template>
</el-table-column>
<el-table-column label="类电影" show-overflow-tooltip min-width="80">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.i.value }}</template>
</el-table-column>
<el-table-column label="地图、示意图" show-overflow-tooltip min-width="90">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.k.value }}</template>
</el-table-column>
<el-table-column label="模型" show-overflow-tooltip min-width="50">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.p.value }}</template>
</el-table-column>
<el-table-column label="录音制品" show-overflow-tooltip min-width="70">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.s.value }}</template>
</el-table-column>
<el-table-column label="录像制品" show-overflow-tooltip min-width="70">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.v.value }}</template>
</el-table-column>
<el-table-column label="视听" show-overflow-tooltip min-width="70">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.t.value }}</template>
</el-table-column>
<el-table-column label="其他作品" show-overflow-tooltip min-width="70">
<template slot-scope="scope">{{ scope.row.anlaysisWorkTypeDTO.l.value }}</template>
</el-table-column>
</el-table-column>
</el-table-column>
</el-table-column>
</el-table>
</div>
</template>
<script>
import {
chars,
border,
styleBold1,
styleBold2,
styleBold3,
styleText,
styleHead,
styleHeadDiagonal,
saveAs,
getCharCol,
s2ab,
} from "@/assets/js/statisticsReport.js";
import {
getExcelVaules,
transTable,
} from "@/assets/js/acceptTotalExcel.js";
import FileSaver from "file-saver";
import XLSX2 from "xlsx";
import XLSX from "xlsx-style";
export default {
name: "",
components: {},
data() {
return {
tableData: [],
invoce_data: { project_name: "统计表" },
wopts: {
bookType: "xlsx",
bookSST: true,
type: "binary",
cellStyles: true,
},
jsono: [
{
登记情况: "统计表",
列头2: "",
列头3: "",
列头4: "",
列头5: "",
列头6: "",
列头7: "",
列头8: "",
列头9: "",
列头10: "",
列头11: "",
列头12: "",
列头13: "",
列头14: "",
列头15: "",
列头16: "",
列头17: "",
列头18: "",
列头19: "",
列头20: "",
列头21: "",
},
{
登记情况: "",
列头2: "",
列头3: "",
列头4: "",
列头5: "",
列头6: "",
列头7: "",
列头8: "",
列头9: "",
列头10: "",
列头11: "",
列头12: "",
列头13: "",
列头14: "",
列头15: "",
列头16: "",
列头17: "",
列头18: "",
列头19: "",
列头20: "",
列头21: "单元:件",
},
{
登记情况: "作品类别\n登记点",
列头2: "总计(件)",
列头3: "文字",
列头4: "口述",
列头5: "音乐",
列头6: "戏剧",
列头7: "曲艺",
列头8: "舞蹈",
列头9: "杂技艺术",
列头10: "美术",
列头11: "工程设计图、产品设计图",
列头12: "建筑",
列头13: "摄影",
列头14: "电影",
列头15: "类电影",
列头16: "地图、示意图",
列头17: "模型",
列头18: "录音制品",
列头19: "录像制品",
列头20: "视听",
列头21: "其他作品",
},
],
};
},
created() {
getExcelVaules(this);
},
mounted() { },
methods: {
exportExcel(type) {
transTable(this);
let json = this.jsono;
var tmpdata = json[0];
json.unshift({});
var keyMap = []; //获取keys
for (var k in tmpdata) {
keyMap.push(k);
json[0][k] = k;
}
var tmpdata = []; //用来保存转换好的json
json
.map((v, i) =>
keyMap.map((k, j) =>
Object.assign(
{},
{
v: v[k],
position:
(j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) +
(i + 1),
}
)
)
)
.reduce((prev, next) => prev.concat(next))
.forEach(
(v, i) =>
(tmpdata[v.position] = {
v: v.v,
})
);
var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
tmpdata["!merges"] = [
{
s: { c: 0, r: 0 }, //开始 A1
e: { c: 20, r: 0 }, //结束S1
},
{
s: { c: 0, r: 1 }, //开始 A2
e: { c: 20, r: 1 }, //结束S2
},
]; //合并单元格
//第一行样式
tmpdata["A1"].s = styleBold1;
//第二行样式
for (let index = 0; index < 21; index++) {
tmpdata[chars[index] + "2"].s = styleBold2;
}
//第三行样式
tmpdata["S3"].s = styleBold3;
//第四行样式
tmpdata["A4"].s = styleHeadDiagonal;
for (let index = 1; index < 21; index++) {
tmpdata[chars[index] + 4].s = styleHead;
}
//剩余行样式
if (this.jsono.length > 4) {
for (let i = 5; i <= this.jsono.length; i++) {
for (let index = 0; index < 21; index++) {
tmpdata[chars[index] + i].s = styleText;
}
}
}
tmpdata["!cols"] = [
//设置列宽度
{ wpx: 110 }, //作品类别
{ wpx: 60 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 70 }, //
{ wpx: 50 }, //
{ wpx: 180 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 50 }, //
{ wpx: 80 }, //
{ wpx: 100 }, //
{ wpx: 50 }, //
{ wpx: 70 }, //
{ wpx: 70 }, //
{ wpx: 70 }, //
{ wpx: 70 }, //
];
tmpdata["!rows"] = [
//设置行高度
{ hpt: 60 }, //作品类别
];
var tmpWB = {
SheetNames: ["统计表"], //保存的表标题
Sheets: {
统计表: Object.assign(
{},
tmpdata, //内容
{
"!ref": outputPos[0] + ":" + outputPos[outputPos.length - 1], //设置填充区域
}
),
},
};
var tmpDown = new Blob(
[
s2ab(
XLSX.write(
tmpWB,
{
bookType: type == undefined ? "xlsx" : type,
bookSST: false,
type: "binary",
} //这里的数据是用来定义导出的格式类型
)
),
],
{
type: "",
}
);
saveAs(
tmpDown,
this.invoce_data.project_name +
"." +
(this.wopts.bookType == "biff2" ? "xls" : this.wopts.bookType)
);
},
search() {
getExcelVaules(this);
},
clear() {
this.form = {
startDate: "",
endDate: "",
};
},
},
};
</script>
<style lang='scss'>
.accept-excel-table {
&.el-table thead.is-group th {
// background: none;
background: #e9ecef;
}
&.el-table--border th {
border-bottom: 1px solid #d7d7d7;
}
&.el-table--border thead.is-group tr:first-of-type th {
border-bottom: 0;
}
&.el-table thead.is-group tr:first-of-type,
&.el-table thead.is-group tr:nth-child(2) {
th {
.cell {
text-align: center;
font-size: 17px;
color: #333;
}
}
}
&.el-table thead.is-group tr:first-of-type {
th .cell {
padding-top: 12px;
height: 40px;
line-height: 40px !important;
}
}
&.el-table thead.is-group tr:nth-child(3) {
th {
.cell {
text-align: right;
color: #333;
}
}
}
&.el-table thead.is-group tr:nth-child(2) {
th .cell {
padding-bottom: 44px;
}
}
&.el-table thead.is-group tr:nth-child(4) th:first-of-type {
border-bottom: none;
background: #e9ecef;
.cell {
text-align: right;
padding-right: 2px;
}
}
&.el-table thead.is-group tr:last-of-type th:first-of-type {
.cell {
padding-left: 2px;
}
}
&.el-table thead.is-group tr:nth-child(4) th:first-of-type:before {
content: "";
position: absolute;
width: 1px;
height: 69px;
/*这里需要自己调整,根据td的宽度和高度*/
top: 0;
left: 0;
background-color: grey;
opacity: 0.3;
display: block;
transform: rotate(-60deg);
/*这里需要自己调整,根据线的位置*/
transform-origin: top;
-ms-transform: rotate(-60deg);
/*这里需要自己调整,根据线的位置*/
-ms-transform-origin: top;
}
&.el-table thead.is-group tr:last-of-type th:first-of-type:before {
content: "";
position: absolute;
width: 1px;
height: 163px;
/*这里需要自己调整,根据td的宽度和高度*/
bottom: 0;
right: 0;
background-color: grey;
opacity: 0.3;
display: block;
transform: rotate(-60deg);
/*这里需要自己调整,根据线的位置*/
transform-origin: bottom;
-ms-transform: rotate(-60deg);
/*这里需要自己调整,根据线的位置*/
-ms-transform-origin: bottom;
}
}
</style>
3.acceptTotalExcel.js
//获取受理量统计
export const getExcelVaules = (_this) => {
let res = {
"data": {
"code": 200,
"msg": "成功!",
"data": [
{
"belongRegionName": "甘肃省中心",
"anlaysisWorkTypeDTO": {
"j": {
"key": null,
"code": 13,
"value": 0
},
"b": {
"key": null,
"code": 3,
"value": 4
},
"i": {
"key": null,
"code": 12,
"value": 0
},
"k": {
"key": null,
"code": 14,
"value": 0
},
"o": {
"key": null,
"code": 9,
"value": 0
},
"s": {
"key": null,
"code": 16,
"value": 10
},
"g": {
"key": null,
"code": 10,
"value": 0
},
"l": {
"key": null,
"code": 18,
"value": 0
},
"t": {
"key": null,
"code": 19,
"value": 0
},
"m": {
"key": null,
"code": 2,
"value": 1
},
"n": {
"key": null,
"code": 7,
"value": 0
},
"d": {
"key": null,
"code": 5,
"value": 4
},
"h": {
"key": null,
"code": 11,
"value": 0
},
"p": {
"key": null,
"code": 15,
"value": 0
},
"e": {
"key": null,
"code": 6,
"value": 0
},
"c": {
"key": null,
"code": 4,
"value": 0
},
"v": {
"key": null,
"code": 17,
"value": 3
},
"a": {
"key": null,
"code": 1,
"value": 85
},
"f": {
"key": null,
"code": 8,
"value": 3
}
}
},
{
"belongRegionName": "国信版权登记",
"anlaysisWorkTypeDTO": {
"j": {
"key": null,
"code": 13,
"value": 0
},
"b": {
"key": null,
"code": 3,
"value": 0
},
"i": {
"key": null,
"code": 12,
"value": 0
},
"k": {
"key": null,
"code": 14,
"value": 0
},
"o": {
"key": null,
"code": 9,
"value": 0
},
"s": {
"key": null,
"code": 16,
"value": 0
},
"g": {
"key": null,
"code": 10,
"value": 0
},
"l": {
"key": null,
"code": 18,
"value": 0
},
"t": {
"key": null,
"code": 19,
"value": 0
},
"m": {
"key": null,
"code": 2,
"value": 0
},
"n": {
"key": null,
"code": 7,
"value": 0
},
"d": {
"key": null,
"code": 5,
"value": 0
},
"h": {
"key": null,
"code": 11,
"value": 0
},
"p": {
"key": null,
"code": 15,
"value": 0
},
"e": {
"key": null,
"code": 6,
"value": 0
},
"c": {
"key": null,
"code": 4,
"value": 0
},
"v": {
"key": null,
"code": 17,
"value": 0
},
"a": {
"key": null,
"code": 1,
"value": 0
},
"f": {
"key": null,
"code": 8,
"value": 1
}
}
}
]
},
}
if (res.data.code == 200 && res.data.data.length > 0) {
_this.tableData = res.data.data;
let arrData = res.data.data;
for (let i = 0; i < res.data.data.length; i++) {
let obj = arrData[i].anlaysisWorkTypeDTO;
let sum = obj.a.value + obj.m.value + obj.b.value + obj.c.value + obj.d.value + obj.e.value + obj.n.value + obj.f.value + obj.j.value + obj.o.value + obj.g.value + obj.h.value + obj.i.value + obj.k.value + obj.p.value + obj.s.value + obj.v.value + obj.t.value + obj.l.value;
_this.tableData[i].sum = sum;
}
} else {
_this.tableData = [];
}
}
export const transTable = (_this) => {
_this.jsono = [
{
登记情况: "统计表",
列头2: "",
列头3: "",
列头4: "",
列头5: "",
列头6: "",
列头7: "",
列头8: "",
列头9: "",
列头10: "",
列头11: "",
列头12: "",
列头13: "",
列头14: "",
列头15: "",
列头16: "",
列头17: "",
列头18: "",
列头19: "",
列头20: "",
列头21: ""
},
{
登记情况: "",
列头2: "",
列头3: "",
列头4: "",
列头5: "",
列头6: "",
列头7: "",
列头8: "",
列头9: "",
列头10: "",
列头11: "",
列头12: "",
列头13: "",
列头14: "",
列头15: "",
列头16: "",
列头17: "",
列头18: "",
列头19: "",
列头20: "",
列头21: "单元:件"
},
{
登记情况: "作品类别\n登记点",
列头2: "总计(件)",
列头3: "文字",
列头4: "口述",
列头5: "音乐",
列头6: "戏剧",
列头7: "曲艺",
列头8: "舞蹈",
列头9: "杂技艺术",
列头10: "美术",
列头11: "工程设计图、产品设计图",
列头12: "建筑",
列头13: "摄影",
列头14: "电影",
列头15: "类电影",
列头16: "地图、示意图",
列头17: "模型",
列头18: "录音制品",
列头19: "录像制品",
列头20: "视听",
列头21: "其他作品",
}
]
let arrData = _this.tableData;
for (let i = 0; i < _this.tableData.length; i++) {
let obj = arrData[i].anlaysisWorkTypeDTO;
_this.jsono.push({
"登记情况": arrData[i].belongRegionName,
列头2: arrData[i].sum,
列头3: obj.a.value,
列头4: obj.m.value,
列头5: obj.b.value,
列头6: obj.c.value,
列头7: obj.d.value,
列头8: obj.e.value,
列头9: obj.n.value,
列头10: obj.f.value,
列头11: obj.j.value,
列头12: obj.o.value,
列头13: obj.g.value,
列头14: obj.h.value,
列头15: obj.i.value,
列头16: obj.k.value,
列头17: obj.p.value,
列头18: obj.s.value,
列头19: obj.v.value,
列头20: obj.t.value,
列头21: obj.l.value,
});
}
//添加合计行
}
4.statisticsReport.js
//边框样式
export const border = {
bottom: { style: "thin", color: { rgb: "000000" } },
top: { style: "thin", color: { rgb: "000000" } },
left: { style: "thin", color: { rgb: "000000" } },
right: { style: "thin", color: { rgb: "000000" } }
};
export const borderTitle1 = {
bottom: { style: "thin", color: { rgb: "ffffff" } },
top: { style: "thin", color: { rgb: "000000" } },
left: { style: "thin", color: { rgb: "000000" } },
right: { style: "thin", color: { rgb: "000000" } }
};
export const borderTitle2 = {
bottom: { style: "thin", color: { rgb: "000000" } },
top: { style: "thin", color: { rgb: "ffffff" } },
left: { style: "thin", color: { rgb: "000000" } }
};
export const borderUnit = {
bottom: { style: "thin", color: { rgb: "d7d7d7" } },
top: { style: "thin", color: { rgb: "d7d7d7" } },
left: { style: "thin", color: { rgb: "d7d7d7" } },
right: { style: "thin", color: { rgb: "d7d7d7" } }
};
export const borderDiagonal = {
bottom: { style: "thin", color: { rgb: "000000" } },
top: { style: "thin", color: { rgb: "000000" } },
left: { style: "thin", color: { rgb: "000000" } },
right: { style: "thin", color: { rgb: "000000" } },
diagonal: { style: "thin", color: { rgb: "000000" } },
diagonalDown: true
};
//加粗
export const styleBold1 = {
border: borderTitle1,
alignment: { horizontal: "center", wrapText: true, vertical: "center" },
font: {
sz: 18,
bold: true,
color: { rgb: "000000" },
outline: true
}
};
export const styleBold2 = {
border: borderTitle2,
alignment: { horizontal: "center", wrapText: true, vertical: "center" },
font: {
sz: 18,
bold: true,
color: { rgb: "000000" },
outline: true
}
};
export const styleBold3 = {
border: borderUnit,
alignment: { horizontal: "center", wrapText: true, vertical: "center" },
font: { sz: 12, bold: true, color: { rgb: "000000" }, outline: true }
};
//不加粗
export const styleText = {
border: border,
alignment: { horizontal: "center", wrapText: true, vertical: "center" },
font: { sz: 12, color: { rgb: "000000" }, outline: true }
};
//表头样式
export const styleHeadDiagonal = {
border: borderDiagonal,
alignment: {
wrapText: true,
readingOrder: 1, horizontal: "center", vertical: "center"
},
font: { sz: 12, bold: true, color: { rgb: "000000" }, outline: true },
fill: { fgColor: { rgb: "d7d7d7" } }
};
export const styleHead = {
border: border,
alignment: { horizontal: "center", wrapText: true, vertical: "center" },
font: { sz: 12, bold: true, color: { rgb: "000000" }, outline: true },
fill: { fgColor: { rgb: "d7d7d7" } }
};
export const chars = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T','U','V','W','X'];
export const saveAs = (obj, fileName) => {
var tmpa = document.createElement("a");
tmpa.download = fileName || "下载";
tmpa.href = URL.createObjectURL(obj);
tmpa.click();
setTimeout(function () {
URL.revokeObjectURL(obj);
}, 100);
}
export const getCharCol = (n) => {
(s = ""), (m = 0);
while (n > 0) {
m = (n % 26) + 1;
s = String.fromCharCode(m + 64) + s;
n = (n - m) / 26;
}
return s;
}
export const s2ab = (s) => {
if (typeof ArrayBuffer !== "undefined") {
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;
} else {
var buf = new Array(s.length);
for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff;
return buf;
}
}