1 安裝依賴(lài)
npm install file-saver --save
npm install exceljs
2 在plugins文件夾下創(chuàng)建creatWsSheet.js文件
image.png
/**
*
* 導(dǎo)出 excel（帶水颖） 公共方法
* @param header v 為頭无蜂，k 為對(duì)應(yīng) data 數(shù)據(jù)的 name
* 例:
const header = [
{
k: 'region',
v: '所屬區(qū)域',
}
]
* @param data 要導(dǎo)出的數(shù)據(jù)
例:
const data = [
{
region:'廣州'
},
{
region:'深圳'
}
]
* @param filename 導(dǎo)出的 excel 文件名稱(chēng)
* @param sheetName 指定第一個(gè)工作表的名稱(chēng)
* @param staff 水印名稱(chēng)
* @param params 導(dǎo)出多個(gè)工作表
* 格式為:
* [{header:[],data:[],sheetName:''}]
* @returns
*/
import { saveAs } from 'file-saver';
const ExcelJS = require('exceljs');
/* eslint-disable */
export const createWsSheet = (
header,
columns,
dataList,
staff,
filename,
) => {
if (!((header && dataList) && (header.length && dataList.length))) {
// Message.error('導(dǎo)出失敗');
return;
}
const EXCEL_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
// 創(chuàng)建工作簿
const workbook = new ExcelJS.Workbook();
workbook.created = new Date()
workbook.modified = new Date()
// 獲取水印
const base64 = setWatermark(staff);
const imageId1 = workbook.addImage({ base64, extension: 'png' });
// 創(chuàng)建帶有紅色標(biāo)簽顏色的工作表
let worksheet = workbook.addWorksheet('Sheet1', { //新建工作表
views: [{ state: 'frozen', xSplit:0, ySplit: 2}], // 凍結(jié)視圖：xSplit：凍結(jié)多少列
});
worksheet.properties.defaultColWidth = 14
worksheet.columns = columns
worksheet.columns.forEach(function (column) {
var dataMax = 0;
column.eachCell({ includeEmpty: true }, function (cell) {
dataMax = cell.value?cell.value.toString().length:0;
if(dataMax <= (column.header.length+2) ){
if(column.width > dataMax){
//retain its default width
} else {
column.width = column.header.length+3;
}
} else {
column.width = dataMax+3;
column.header.length = dataMax+3;
}
dataMax = 0;
})
});
// 添加背景圖片
worksheet.addBackgroundImage(imageId1);
worksheet.getRow(1).values = [`${filename}(${staff})`];
worksheet.mergeCells(1, 1, 1, columns.length) //第1行 第1列 合并到第1行的第n列
// worksheet.getRow(1).height = 40
worksheet.getRow(1).eachCell({ includeEmpty: true }, (cell, colNumber) => {
worksheet.getRow(1).getCell(colNumber).fill = {
type: 'pattern',
pattern: 'solid',
// fgColor: { argb: 'ffd3d3d3' },
// bgColor: { argb: 'FF0000FF' },
}
worksheet.getRow(1).getCell(colNumber).font = {
bold: true,
}
worksheet.getRow(1).getCell(colNumber).border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
}
})
// 添加數(shù)據(jù)
worksheet.getRow(2).values = []
worksheet.getRow(2).values = header
// 表頭樣式
worksheet.getRow(2).eachCell({ includeEmpty: true }, (cell, colNumber) => {
worksheet.getRow(2).getCell(colNumber).fill = {
type: 'pattern',
pattern: 'solid',
// fgColor: { argb: 'ffd3d3d3' },
// bgColor: { argb: 'FF0000FF' },
}
worksheet.getRow(2).getCell(colNumber).font = {
bold: true,
}
worksheet.getRow(2).getCell(colNumber).border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
}
})
worksheet.addRows(dataList)
// 自定義樣式
worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
// if (rowNumber > 2) {
// worksheet.getRow(rowNumber).height = 28.6
// }
worksheet.getRow(rowNumber).eachCell({ includeEmpty: true }, (cell, colNumber) => {
// 文字居中
worksheet.getRow(rowNumber).getCell(colNumber).alignment = {
vertical: 'middle',
horizontal: 'center',
wrapText: true // 設(shè)置自動(dòng)換行
}
//邊框樣式
worksheet.getRow(rowNumber).getCell(colNumber).border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
}
})
})
worksheet.columns.forEach((column) => {
column.width = 20;
});
workbook.xlsx.writeBuffer().then((buffer) => {
const blob = new Blob([buffer], { type: EXCEL_TYPE })
saveAs(blob, `${filename}.xlsx`)
})
// Message.success('導(dǎo)出成功');
};
/**
* 判斷該數(shù)據(jù)是否為空
* @param data 要判斷的數(shù)據(jù)
* @returns 布爾值:true/false
*/
const isNull = (data) => {
return !!data;
}
/**
* 繪畫(huà)水印
* @param str 要做出水印的文字
* @returns Base64
*/
const setWatermark = (str) => {
let id = '1.23452384164.123412416';
if (document.getElementById(id) !== null) {
document.body.removeChild(document.getElementById(id));
}
// 創(chuàng)建一個(gè)畫(huà)布
let can = document.createElement('canvas');
// 設(shè)置畫(huà)布的長(zhǎng)寬
can.width = 500;
can.height = 220;
let cans = can.getContext('2d');
// 旋轉(zhuǎn)角度
cans.rotate(-25 * Math.PI / 180);
// 設(shè)置字體大小
cans.font = "300 30px Microsoft JhengHei";
// 設(shè)置填充繪畫(huà)的顏色、漸變或者模式
cans.fillStyle = "rgba(130, 142, 162, 0.5)";
// 設(shè)置文本內(nèi)容的當(dāng)前對(duì)齊方式
cans.textAlign = 'center';
// 設(shè)置在繪制文本時(shí)使用的當(dāng)前文本基線(xiàn)
cans.textBaseline = 'Middle';
cans.fillText(str, 80, 180);
const dataURL = can.toDataURL('image/png');
return dataURL;
}
3 引用
<template>
<div>
<div @click="exportExcel" >導(dǎo)出EXCEL</div>
</div>
</template>
<script>
import { createWsSheet } from '../../../plugins/creatWsSheet'
export default {
data() {
return {
itemsExcel: [],
}
},
methods: {
async getDbType() {
this.applyDb = await getDict({ dicttype: "dbType"});
},
//導(dǎo)出報(bào)告
exportExcel(){
const header = ["平臺(tái)系統(tǒng)名稱(chēng)", "是否并網(wǎng)", "主機(jī)關(guān)聯(lián)數(shù)據(jù)", "數(shù)據(jù)庫(kù)關(guān)聯(lián)數(shù)據(jù)"]
const columns = header.map((item) => {
return {
header: item,
key: item,
}
})
let itemsExcel = this.watermark == '' ? this.itemsExcel : this.itemsExcel.slice(0,this.exportNum) //測(cè)試版加水印罗侯，導(dǎo)出前exportNum條數(shù)據(jù)
const datas = itemsExcel.map((item) => {
let res = {}
res.平臺(tái)系統(tǒng)名稱(chēng) = item.name
res.是否并網(wǎng) = item.isonline
res.主機(jī)關(guān)聯(lián)數(shù)據(jù) = item.host
res.數(shù)據(jù)庫(kù)關(guān)聯(lián)數(shù)據(jù) = item.db
return res
})
createWsSheet(header, columns, datas, this.watermark ,'平臺(tái)系統(tǒng)清單');
},
}
</script>
4 結(jié)果
image.png
