ExcelJS导出excel表格,合并单元格,自定义样式

说明

vue2.6.14
需要用到exceljs这个库

npm install exceljs

实现操作

导出xlsx表格
设置行高和列宽
合并单元格
自定义表格样式
其他的一些用法

代码部分

下面用到的

//导入ExcelJS
import ExcelJS from "exceljs";

//下载文件
download_file(buffer, fileName) {
	console.log("导出");
	let fileURL = window.URL.createObjectURL(new Blob([buffer]));
	let fileLink = document.createElement("a");
	fileLink.href = fileURL;
	fileLink.setAttribute("download", fileName);
	document.body.appendChild(fileLink);
	fileLink.click();
}

导出xlsx表格的代码

//下面是导出的函数
async export() {
	const workbook = new ExcelJS.Workbook();
	const worksheet = workbook.addWorksheet("Sheet1");
	//这里是数据列表
	const data = [
		{ id: 1, name: "艾伦", age: 20, sex: "男", achievement: 90 },
		{ id: 2, name: "柏然", age: 25, sex: "男", achievement: 86 },
		{ id: 3, name: "程丽", age: 30, sex: "女", achievement: 90 }
	];
	// 设置列,这里的width就是列宽
	worksheet.columns = [
		{ header: "序号", key: "id", width: 10},
        { header: "姓名", key: "name", width: 10 },
        { header: "年龄", key: "age", width: 10 },
	];
	
	// 批量插入数据
	data.forEach(item => worksheet.addRow(item));
	
	// 写入文件
	const buffer = await workbook.xlsx.writeBuffer();
	//下载文件
	this.download_file(buffer, "填报汇总.xlsx");
}

设置行高和列宽

列宽上面已经有了,这里说明一下行高怎么设置
worksheet.getRow(2).height = 30;

//下面是导出的函数
async export() {
	const workbook = new ExcelJS.Workbook();
	const worksheet = workbook.addWorksheet("Sheet1");
	//这里是数据列表
	const data = [
		{ id: 1, name: "艾伦", age: 20, sex: "男", achievement: 90 },
		{ id: 2, name: "柏然", age: 25, sex: "男", achievement: 86 },
		{ id: 3, name: "程丽", age: 30, sex: "女", achievement: 90 }
	];
	// 设置列,这里的width就是列宽
	worksheet.columns = [
		{ header: "序号", key: "id", width: 10},
        { header: "姓名", key: "name", width: 10 },
        { header: "年龄", key: "age", width: 10 },
	];
	
	// 批量插入数据
	data.forEach(item => worksheet.addRow(item));
	
	//设置行高,因为第一行被表头占据了,所以从2开始,也就是数据的第一行
	//想设置其他行设置参数就好了
	worksheet.getRow(2).height = 30;
	
	// 写入文件
	const buffer = await workbook.xlsx.writeBuffer();
	//下载文件
	this.download_file(buffer, "填报汇总.xlsx");
}

合并单元格

worksheet.mergeCells("B1:C1");

//下面是导出的函数
async export() {
	const workbook = new ExcelJS.Workbook();
	const worksheet = workbook.addWorksheet("Sheet1");
	//这里是数据列表
	const data = [
		{ id: 1, name: "艾伦", age: 20, sex: "男", achievement: 90 },
		{ id: 2, name: "柏然", age: 25, sex: "男", achievement: 86 },
		{ id: 3, name: "程丽", age: 30, sex: "女", achievement: 90 }
	];
	// 设置列,这里的width就是列宽
	worksheet.columns = [
		{ header: "序号", key: "id", width: 10},
        { header: "姓名", key: "name", width: 10 },
        { header: "年龄", key: "age", width: 10 },
	];
	
	// 批量插入数据
	data.forEach(item => worksheet.addRow(item));
	
	// 合并单元格,就是把B1开始到D1的单元格合并
	worksheet.mergeCells("B1:D1");
	
	// 写入文件
	const buffer = await workbook.xlsx.writeBuffer();
	//下载文件
	this.download_file(buffer, "填报汇总.xlsx");
}

自定义表格样式

//下面是导出的函数
async export() {
	const workbook = new ExcelJS.Workbook();
	const worksheet = workbook.addWorksheet("Sheet1");
	//这里是数据列表
	const data = [
		{ id: 1, name: "艾伦", age: 20, sex: "男", achievement: 90 },
		{ id: 2, name: "柏然", age: 25, sex: "男", achievement: 86 },
		{ id: 3, name: "程丽", age: 30, sex: "女", achievement: 90 }
	];
	// 设置列,这里的width就是列宽
	worksheet.columns = [
		{ header: "序号", key: "id", width: 10},
        { header: "姓名", key: "name", width: 10 },
        { header: "年龄", key: "age", width: 10 },
	];
	
	// 批量插入数据
	data.forEach(item => worksheet.addRow(item));
	
	//设置样式表格样式,font里面设置字体大小,颜色(这里是argb要注意),加粗
	//alignment 设置单元格的水平和垂直居中
	const B1 = worksheet.getCell('B1')
	B1.font = { size: 20, color:{ argb: 'FF8B008B' }, bold: true }
	B1.alignment = { horizontal: 'center', vertical: 'middle' }
	
	// 写入文件
	const buffer = await workbook.xlsx.writeBuffer();
	//下载文件
	this.download_file(buffer, "填报汇总.xlsx");
}

其他用法

// 批量设置所有表格数据的样式
worksheet.eachRow((row,rowNumber) => {
	//设置表头样式
	if (rowNumber <= 2) {
		row.eachCell(cell =>{
			cell.font = { size: 16, color:{ argb: 'FF8B008B' }, bold: true }
			cell.alignment = { horizontal: 'center', vertical: 'middle' }
		})
	}
	//设置数据列表样式
	if (rowNumber > 2) {
		row.eachCell(cell => {
			cell.font = { size: 12, bold: true };
			cell.alignment = { horizontal: 'center', vertical: 'middle' }
		});
	}
	//设置所有行高
	row.height = 30;
});

//获取行数和列数
const rowCount = worksheet.rowCount;
const columnCount = worksheet.columnCount;
console.log(`行数：${rowCount}，列数：${columnCount}`);