博客

前端框架：angular

	// 首先，ts需要导入XLSX 和 XLSXS
	// 设置字体加粗，列对齐方式需要用 xlsx-js-style
	import FileSaver from "file-saver";
	import XLSX from "xlsx";
	import XLSXS from "xlsx-js-style";


	constructor(private el: ElementRef) {}

	private exportMatTable() {
		const tableObj = this.el.nativeElement.querySelector("#table");
		const fileName = "table.xlsx";
		// 把matTable转为json后导出
		// 将包裹元素转换成 JSON 格式
		let json = [];
		Array.from(tableObj.querySelectorAll("mat-header-row")).forEach(
			(headerRow: HTMLElement) => {
				const row = {};
				Array.from(
					headerRow.querySelectorAll("mat-header-cell")
				).forEach((cell, index) => {
					row[`column${index}`] = cell.textContent.trim();
				});
				json.push(row);
			}
		);

		Array.from(tableObj.querySelectorAll("mat-row")).forEach(
			(row: HTMLElement) => {
				const data = {};
				Array.from(row.querySelectorAll("mat-cell")).forEach(
					(cell, index) => {
						const cell_ = tableObj.querySelector(
							`mat-header-cell:nth-child(${index + 1})`
						);
						data[`column${index}`] = cell.textContent.trim();
					}
				);
				json.push(data);
			}
		);

		Array.from(tableObj.querySelectorAll("mat-footer-row")).forEach(
			(footerRow: HTMLElement) => {
				const data = {};
				Array.from(
					footerRow.querySelectorAll("mat-footer-cell")
				).forEach((footerCell, index) => {
					const footerCell_ = tableObj.querySelector(
						`mat-header-cell:nth-child(${index + 1})`
					);
					data[`column${index}`] = footerCell.textContent.trim();
				});
				json.push(data);
			}
		);

		// textAlignSetting用来存放需要设置右对齐的列，长这样：[2, 3, 4, 5, 6]
		let textAlignSetting = [];
		for (let i = 0; i < Object.keys(json[0]).length; i++) {
			if (i > 0) textAlignSetting.push(i + 1);
		}
		
		// 调用setFitWidth方法处理数据，设置列宽自适应，字体加粗，列右对齐等
		let worksheet = this.setFitWidth(
			json,
			XLSX.utils.json_to_sheet(json, { skipHeader: true }),
			{
				fontWeight: true, // 是否设置字体加粗
				textAlign: true, // 是否设置列对齐方式
				fontWeightSetting: [
					1,
					json.length - 2,
					json.length - 1,
					json.length,
				], // 字体加粗的行
				textAlignSetting: textAlignSetting, // 需要设置对齐方式的列
			}
		);

		const workbook = XLSX.utils.book_new();
		XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
		const buffer = XLSXS.write(workbook, {
			bookType: "xlsx",
			bookSST: false,
			type: "binary",
		});
		function s2ab(s) {
			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;
		}
		const blob = new Blob([s2ab(buffer)], {
			type: "application/octet-stream",
		});
		FileSaver.saveAs(blob, fileName);
	}



	// json_to_sheet导出excel设置自适应宽度
	public setFitWidth(data, worksheet, style?) {
		let colWidths = [];
		let colNames = Object.keys(data[0]); // 所有列的名称数组
		// 计算每一列的所有单元格宽度
		// 先遍历行
		data.forEach((row) => {
			// 列序号
			let index = 0;
			// 遍历列
			for (const key in row) {
				if (colWidths[index] == null) colWidths[index] = [];

				switch (typeof row[key]) {
					case "string":
					case "number":
					case "boolean":
						colWidths[index].push(this.getCellWidth(row[key]));
						break;
					case "object":
					case "function":
						colWidths[index].push(0);
						break;
				}
				index++;
			}
		});

		// 字体加粗
		if (style && style.fontWeight) {
			for (let i = 0; i < style.fontWeightSetting.length; i++) {
				const element = style.fontWeightSetting[i];
				for (
					let i = 1;
					i <= Object.keys(worksheet).length / data.length;
					i++
				) {
					const cell = String.fromCharCode(64 + i) + element;
					worksheet[cell].s = {
						font: { bold: true },
					};
				}
			}
		}

		// 列右对齐
		if (style && style.textAlign) {
			for (let i = 0; i < style.textAlignSetting.length; i++) {
				const element = style.textAlignSetting[i];
				for (let i = 1; i <= data.length; i++) {
					const cell = this.numberToColumn(element) + i;
					if (worksheet[cell].s)
						worksheet[cell].s["alignment"] = {
							horizontal: "right",
						};
					else
						worksheet[cell].s = {
							alignment: { horizontal: "right" },
						};
				}
			}
		}

		worksheet["!cols"] = [];
		// 每一列取最大值最为列宽
		colWidths.forEach((widths, index) => {
			// 计算列头的宽度
			widths.push(this.getCellWidth(colNames[index]));
			// 设置最大值为列宽
			worksheet["!cols"].push({
				wch: Math.max(...widths),
				textAlign: "right",
			});
		});
		return worksheet;
	}

	private getCellWidth(value) {
		// 判断是否为null或undefined
		if (value == null) {
			return 10;
		} else if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
			// 中文的长度
			const chineseLength = value.match(/[\u4e00-\u9fa5]/g).length;
			// 其他不是中文的长度
			const otherLength = value.length - chineseLength;
			return chineseLength * 2.1 + otherLength * 1.1;
		} else {
			return value.toString().length * 1.2;
		}
	}

	private numberToColumn(num) {
		let result = "";
		while (num > 0) {
			const mod = (num - 1) % 26; // 计算出余数
			result = String.fromCharCode(65 + mod) + result; // 将余数转换为对应的大写字母，并添加到结果字符串的开头
			num = Math.floor((num - mod) / 26); // 更新 num 的值
		}
		return result;
	}