npm_libs/excel/src/libs/ExcelWriter.ts

import writeXlsxFile from "write-excel-file";
import { Options } from "./ExcelInterface";

const columnNames = getColumnNames();

function getColumnNames() {
	let wordList = Array.from(new Array(26), (ele, index) => {
		return String.fromCharCode(65 + index);
	});
	let columnNames = [];
	for (let i = 0; i < 26; i++) {
		columnNames.push(wordList[i]);
	}
	for (let i = 0; i < 26; i++) {
		for (let j = 0; j < 26; j++) {
			columnNames.push(wordList[i] + wordList[j]);
		}
	}
	return columnNames;
}
function convertType(value: any, type: any) {
	switch (type) {
		case String:
			return String(value);
		case Number:
			let output = Number(value);
			if (isNaN(output)) {
				output = 0;
			}
			return output;
		case Boolean:
			return Boolean(value);
		case Array:
			return Array.isArray(value) ? value : [value];
		case Object:
			return Object(value);
		case null:
			return null;
		case undefined:
			return undefined;
		default:
			return String(value);
	}
}
const ExcelWriter = {
	async getColumnNames() {
		return columnNames;
	},
	async outputExcel(headerConfig: Array<any>, data: Array<any>, options: Options) {
		let excelData: Array<any> = [];

		data.forEach((row, rowIndex) => {
			let rowObject = [] as any;
			headerConfig.forEach((headerItem, headerIndex) => {
				let column = this._dealCellData(row[headerItem.key], headerItem, options);

				rowObject.push(column);
			});
			excelData.push(rowObject);
		});

		if (!options.hasOwnProperty("headerRow")) {
			// 对象 options 上没有名为 "headerRow" 的属性
			options.headerRow = 1;
		}
		if (options.headerRow > 0) {
			let excelDataHeader = [] as any;
			headerConfig.forEach((headerItem, headerIndex) => {
				let column = this._dealCellData(headerItem.title, headerItem, options);
				column.type = String;
				column.value = headerItem.title;
				excelDataHeader.push(column);
			});
			excelData.splice(options.headerRow - 1, 0, excelDataHeader);
			//excelDataBody.unshift(excelDataHeader);
		}
		//excelData = [excelDataHeader, ...excelDataBody];
		await this._output(excelData, options);
	},
	//==================私有方法============================

	/**
	 * 生成单元格数据
	 * @param {*} columnCellValue
	 * @param {*} header
	 * @param {*} options
	 * @returns
	 */
	_dealCellData(columnCellValue: any, header: any, options: Options) {
		let cell = {
			value: convertType(columnCellValue, header?.type ? header?.type : String) // columnCellValue.toString().trim()
		} as any;
		if (header?.width) {
			cell.width = header.width;
		}
		if (header?.type) {
			cell.type = header.type;
		}
		if (options?.border) {
			//如果有边框
			cell.borderColor = options?.borderColor ?? "#000000";
		}
		return cell;
	},
	/**
	 * 导出excel表格
	 * @param {*} data
	 * @param {*} options
	 */
	async _output(data: Array<any>, options: Options) {
		let excelRows = [];
		data.forEach((row: Array<any>, rowIndex) => {
			row.forEach((cell, cellIndex) => {
				let column = {
					value: cell
				};
				excelRows.push(column);
			});
		});
		let fileName = options?.fileName ? options.fileName : "导出.xlsx";
		let columns = options?.columns ? options.columns : [];
		let optionsOutput = {
			columns, // (optional) column widths, etc.
			fileName
		};
		await writeXlsxFile(data, optionsOutput);
	},

	/**
	 * 导出示例
	 */
	async outputTest() {
		const HEADER_ROW = [
			{
				value: "Name",
				fontWeight: "bold"
			},
			{
				value: "Date of Birth",
				fontWeight: "bold"
			},
			{
				value: "Cost",
				fontWeight: "bold"
			},
			{
				value: "Paid",
				fontWeight: "bold"
			}
		];

		const DATA_ROW_1 = [
			[
				// "Name"
				{
					type: String,
					value: "John Smith",
					borderColor: "#000000"
				},

				// "Date of Birth"
				{
					type: Date,
					value: new Date(),
					format: "mm/dd/yyyy"
				},

				// "Cost"
				{
					type: Number,
					value: 1800
				},

				// "Paid"
				{
					type: Boolean,
					value: true
				}
			],
			[
				// "Name"
				{
					type: String,
					value: "第2行",
					borderColor: "#000000"
				},

				// "Date of Birth"
				{
					type: Date,
					value: new Date(),
					format: "yyyy-mm-dd"
				},

				// "Cost"
				{
					type: Number,
					value: 1800,
					span: 3,
					borderColor: "#ff0000"
				},

				// "Paid"
				null,
				null,
				{
					type: String,
					value: "第2行00",
					borderColor: "#000000",
					fontSize: 18
				}
			]
		];

		const data = [HEADER_ROW, ...DATA_ROW_1] as any;
		let options = {
			// columns, // (optional) column widths, etc.
			fileName: "file.xlsx"
		};
		await writeXlsxFile(data, options);
	}
};

export default ExcelWriter;