ExcelJS

讀取，操作并寫入電子表格數據和樣式到 XLSX 和 JSON 文件。
一個 Excel 電子表格文件逆向工程項目。
exceljs 中文API 傳送門

生成文檔并導出

import * as ExcelJS from "exceljs";// 創建文檔對象
const workbook = new ExcelJS.Workbook();
// 創建工作本
const worksheet = workbook.addWorksheet('Sheet 1');
// 凍結表頭
worksheet.views = [{state: 'frozen', // 可以設置為 'frozen' 來固定首行或首列，模擬滾動條效果xSplit: 0, // 從第二列開始凍結（模擬滾動條效果）ySplit: 9, // 從第二行開始凍結（模擬滾動條效果）topLeftCell: 'A1' // 設置起始位置為 B2，模擬滾動效果從第二行第二列開始
}];
// 1. 構建表頭結構
const headerDepth = buildHeaders(filterColumns,7);
// 遞歸生成表頭行
generateHeaderRows(worksheet,headerDepth,filterColumns,7);
// 2. 填充數據
// salesTable.value.tableData.forEach(item => {
//     worksheet.addRows(item);
// })
// 觸發下載
const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const link = document.createElement('a');
link.href = URL.createObjectURL(blob);
link.download = name+'.xlsx';
link.click();

導出表頭其他函數

// 遞歸生成表頭行
export const generateHeaderRows = (worksheet:Worksheet,headerDepth:number,cols:tableColumn[], currentRow = 0, startCol = 0) => {cols.forEach(col => {const cell = {header: col.label,key: col.dataIndex,width: col.minWidth?parseInt(col.minWidth)/8 : 20,colSpan: col.childrens ? col.childrens.length : 1,rowSpan: col.childrens ? 1 : headerDepth - currentRow + 1};const tempCell = worksheet.getCell(currentRow + 1, startCol + 1);// 設置單元格文案tempCell.value = col.label;// 設置單元格字體與大小tempCell.font = { name: 'Arial', size: 10 }// 設置列寬度worksheet.getColumn(startCol+1).width = cell.width;// 設置行高worksheet.getRow(currentRow+1).height = 20;// 合并單元格worksheet.mergeCells(currentRow + 1,startCol + 1,currentRow + (cell.rowSpan || currentRow+1), startCol + (cell.colSpan || 1));// 有子級遞歸if (col.childrens) {generateHeaderRows(worksheet,headerDepth,col.childrens, currentRow + 1, startCol);}startCol += cell.colSpan;});
}// 遞歸處理表頭層級
export const buildHeaders = (columns:tableColumn[], depth = 0, parentSpan = 0) => {let maxDepth = depth;columns.forEach((col:tableColumn) => {if (col.childrens) {const childDepth = buildHeaders(col.childrens, depth + 1, parentSpan + (col.colSpan|| 0));maxDepth = Math.max(maxDepth, childDepth);}});return maxDepth;
};

生成水印

export const createWatermark = () => {// 創建 Canvas 元素并繪制水印const canvas = document.createElement('canvas');const context:CanvasRenderingContext2D | null  = canvas.getContext('2d');canvas.width = 500;canvas.height = 200;context?context.font = '14px Arial':'';// 旋轉角度context?context.rotate(-25 * Math.PI / 180):'';// 設置文本內容的當前對齊方式context?context.textAlign = 'center':'';context?context.fillStyle = 'rgba(0, 0, 0, 0.1)':''; // 半透明水印context?context.fillText('midea', canvas.width / 2 - 100, canvas.height / 2):'';// 將 Canvas 轉換為圖片數據 URLreturn canvas.toDataURL('image/png');
}

設置文檔的背景水印

// 將 Canvas 水印 轉換為圖片數據 URL
const imageDataUrl = createWatermark();
// 添加圖片到工作簿中
const imageId = workbook.addImage({base64: imageDataUrl.replace(/^data:image\/[a-z]+;base64,/, ''),extension: 'png',
});
// 設置文檔水印
worksheet.addBackgroundImage(imageId)

dom 轉圖片

import domToImage from 'dom-to-image';// 把查詢條件轉為圖片
domToImage.toPng(captureRef.value).then(async (dataUrl) => {const image = new Image();image.src = dataUrl;})
.catch(function (error) {console.error('Failed to generate image:', error);
});

插入圖片

const imageId2 = workbook.addImage({ base64: dataUrl, extension: 'png' });
// 工作本插入查詢條件圖片
worksheet.addImage(imageId2, {tl: { col: 0, row: 0 },ext: { width: 1900 / 1.3, height: 800 / 6 }
});

全部代碼

// 遞歸生成表頭行
export const generateHeaderRows = (worksheet:Worksheet,headerDepth:number,cols:tableColumn[], currentRow = 0, startCol = 0) => {cols.forEach(col => {const cell = {header: col.label,key: col.dataIndex,width: col.minWidth?parseInt(col.minWidth)/8 : 20,colSpan: col.childrens ? col.childrens.length : 1,rowSpan: col.childrens ? 1 : headerDepth - currentRow + 1};const tempCell = worksheet.getCell(currentRow + 1, startCol + 1);// 設置單元格文案tempCell.value = col.label;// 設置單元格字體與大小tempCell.font = { name: 'Arial', size: 10 }// 設置列寬度worksheet.getColumn(startCol+1).width = cell.width;// 設置行高worksheet.getRow(currentRow+1).height = 20;// 合并單元格worksheet.mergeCells(currentRow + 1,startCol + 1,currentRow + (cell.rowSpan || currentRow+1), startCol + (cell.colSpan || 1));// 有子級遞歸if (col.childrens) {generateHeaderRows(worksheet,headerDepth,col.childrens, currentRow + 1, startCol);}startCol += cell.colSpan;});
}// 遞歸處理表頭層級
export const buildHeaders = (columns:tableColumn[], depth = 0, parentSpan = 0) => {let maxDepth = depth;columns.forEach((col:tableColumn) => {if (col.childrens) {const childDepth = buildHeaders(col.childrens, depth + 1, parentSpan + (col.colSpan|| 0));maxDepth = Math.max(maxDepth, childDepth);}});return maxDepth;
};
export const createWatermark = () => {// 創建 Canvas 元素并繪制水印const canvas = document.createElement('canvas');const context:CanvasRenderingContext2D | null  = canvas.getContext('2d');canvas.width = 500;canvas.height = 200;context?context.font = '14px Arial':'';// 旋轉角度context?context.rotate(-25 * Math.PI / 180):'';// 設置文本內容的當前對齊方式context?context.textAlign = 'center':'';context?context.fillStyle = 'rgba(0, 0, 0, 0.1)':''; // 半透明水印context?context.fillText('midea', canvas.width / 2 - 100, canvas.height / 2):'';// 將 Canvas 轉換為圖片數據 URLreturn canvas.toDataURL('image/png');
}// 點擊下載const downLoadList = () => {// 創建文檔對象const workbook = new ExcelJS.Workbook();// 創建工作本const worksheet = workbook.addWorksheet('Sheet 1');// 凍結表頭worksheet.views = [{state: 'frozen', // 可以設置為 'frozen' 來固定首行或首列，模擬滾動條效果xSplit: 0, // 從第二列開始凍結（模擬滾動條效果）ySplit: 9, // 從第二行開始凍結（模擬滾動條效果）topLeftCell: 'A1' // 設置起始位置為 B2，模擬滾動效果從第二行第二列開始}];// 1. 構建表頭結構const headerDepth = buildHeaders(filterColumns,7);// 遞歸生成表頭行generateHeaderRows(worksheet,headerDepth,filterColumns,7);emit('downLoadList',workbook,worksheet);}const downLoadListFn = async (workbook:Workbook,worksheet:Worksheet,name:string) => {// 把查詢條件轉為圖片domToImage.toPng(captureRef.value).then(async (dataUrl) => {const image = new Image();image.src = dataUrl;const imageId2 = workbook.addImage({ base64: dataUrl, extension: 'png' });// 工作本插入查詢條件圖片worksheet.addImage(imageId2, {tl: { col: 0, row: 0 },ext: { width: 1900 / 1.3, height: 800 / 6 }});// 2. 填充數據// salesTable.value.tableData.forEach(item => {//     worksheet.addRows(item);// })// 將 Canvas 水印 轉換為圖片數據 URLconst imageDataUrl = createWatermark();// 添加圖片到工作簿中const imageId = workbook.addImage({base64: imageDataUrl.replace(/^data:image\/[a-z]+;base64,/, ''),extension: 'png',});// 設置文檔水印worksheet.addBackgroundImage(imageId)// 觸發下載const buffer = await workbook.xlsx.writeBuffer();const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });const link = document.createElement('a');link.href = URL.createObjectURL(blob);link.download = name+'.xlsx';link.click();}).catch(function (error) {console.error('Failed to generate image:', error);});}