废话少说, 直接上代码jym
必不可少的当然是先下载依赖咯, 我们用到的是file-saver和exceljs两个库, 用来表格样式调整和文件导出
本人小菜鸡一枚,只是为了记录一下自己遇到的需求, 大佬手下留情
**还是先看看效果吧(狗头保命)
1.下载file-saver和exceljs插件
npm install file-saver --save
npm install exceljs
2.创建公共js文件, 这边封装了jym直接用, 别跟我客气
import { saveAs } from 'file-saver';
const ExcelJS = require('exceljs');
export const createWsSheetWatermark = (
filename,
arr,
) => {
// 创建工作簿
const workbook = new ExcelJS.Workbook();
workbook.created = new Date()
workbook.modified = new Date()
const EXCEL_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
arr.forEach(item => {
let { header, keyArr, datas, staff, generalHeading, width, sheet, contentType } = item
const dataList = datas.map((item) => {
let res = {}
for (let i = 0; i < header.length; i++) {
res[header[i]] = item[keyArr[i]]
}
return res
})
if (!((header && dataList) && (header.length && dataList.length))) {
// Message.success('导出失败');
return;
}
// 获取水印
const base64 = setWatermark(staff);
const imageId1 = workbook.addImage({ base64, extension: 'png' });
// 创建带有红色标签颜色的工作表
let worksheet = workbook.addWorksheet(sheet, { //新建工作表
// views: [{ state: 'frozen', xSplit: 0, ySplit: 2 }], // 冻结视图：xSplit：冻结多少列
});
worksheet.properties.defaultColWidth = 14
const columns = header.map((item) => {
return {
header: item,
key: item,
}
})
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 = [`${generalHeading}(${staff})`];
worksheet.getRow(1).values = [`${generalHeading}`];
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' },
}
})
// 添加数据
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) => {
if (contentType[colNumber - 1] == 1 && rowNumber > 2) {
// 设置数据类型为数值 第一行和第二行
// worksheet.getCell(cell._address).type = ExcelJS.ValueType.Number;
worksheet.getCell(cell._address).value = cell._value.model.value ? Number(cell._value.model.value) : ''
} else {
// 文字居中
worksheet.getRow(rowNumber).getCell(colNumber).alignment = {
vertical: 'middle',
horizontal: 'center',
wrapText: true // 设置自动换行
}
}
//边框样式
worksheet.getRow(rowNumber).getCell(colNumber).border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
}
})
})
worksheet.columns.forEach((column) => {
column.width = width ? width : 20;
});
})
workbook.xlsx.writeBuffer().then((buffer) => {
const blob = new Blob([buffer], { type: EXCEL_TYPE })
saveAs(blob, `${filename}.xlsx`)
})
// Message.success('导出成功');
};
const isNull = (data) => {
return !!data;
}
const setWatermark = (str) => {
let id = '1.23452384164.123412416';
if (document.getElementById(id) !== null) {
document.body.removeChild(document.getElementById(id));
}
// 创建一个画布
let can = document.createElement('canvas');
// 设置画布的长宽
// can.width = 500;
// can.height = 220;
// 创建一个临时canvas，用于测量文本的宽度和高度
let tempCanvas = document.createElement('canvas');
let tempCtx = tempCanvas.getContext('2d');
tempCtx.font = "300 30px Microsoft JhengHei";
let textMetrics = tempCtx.measureText(str);
let textWidth = textMetrics.width;
let textHeight = parseInt(tempCtx.font); // 假设字体大小为文本高度
// 根据文本的宽度和高度设置原始canvas的宽度和高度
can.width = textWidth + 300; // 增加一定的边距
can.height = textHeight + 300; // 增加一定的边距
let cans = can.getContext('2d');
// 旋转角度
cans.rotate(-25 * Math.PI / 180);
// 设置字体大小
cans.font = "300 30px Microsoft JhengHei";
// 设置填充绘画的颜色、渐变或者模式
// cans.fillStyle = "rgba(130, 142, 162, 0.5)";
cans.fillStyle = "#C5CBCF";
// 设置文本内容的当前对齐方式
cans.textAlign = 'center';
// 设置在绘制文本时使用的当前文本基线
cans.textBaseline = 'Middle';
// cans.fillText(str, 80, 180);
cans.fillText(str, can.width / 2, can.height / 2);
const dataURL = can.toDataURL('image/png');
return dataURL;
}
3.页面调用(里面备注写的很清楚啦, 大家按需调整即可)
import { createWsSheetWatermark } from "@/utils/createWsSheetWatermark"
multipleExportWatermark() {
let tableTitle = [
{
label: '名称',
labelKey: 'name',
},
{
label: '年龄',
labelKey: 'age',
},
{
label: '身高',
labelKey: 'height',
},
{
label: '体重',
labelKey: 'weight',
},
]
let header = []
let keyArr = []
let datas = [
{
name: '小芳',
age: 18,
height: '168cm',
weight: '50kg',
},
{
name: '小晓',
age: 20,
height: '173cm',
weight: '53kg',
}
]
tableTitle.forEach(item => {
header.push(item.label)
keyArr.push(item.labelKey)
})
let contentTypePersonage = [
'0', '1', '0', '0'
]
createWsSheetWatermark(
'我是文件名', //文件名
[
{
header, // 二级标题
keyArr, //对应表格data key值
datas, //表格data
staff: '代号241是帅哥', //水印名称
generalHeading: '一级标题', //一级标题
width: 20, //列宽
sheet: '第一页', //sheet页名称
contentType: contentTypePersonage, //数据类型 0文本, 1数值
},
{
header,
keyArr,
datas,
staff: '185帅哥',
generalHeading: '一级标题',
width: 20,
sheet: '第二页',
contentType: contentTypePersonage,
},
]
);
},
