exceljs生成excel表格样式记录

本文首发地址: https://www.guofengs.com
exceljs的功能非常强大，基本能满足我们所有需求。
官方文档地址：https://github.com/exceljs/exceljs 。

虽然官方文档由详尽的说明，但是有时候我们的文档可能需要定制化的，比如下面的文档样式，这个样式官网并没有，但是在问题里面有的，我翻了好久踩翻出来，这里总结记录，送给有需要的人

由于是demo，所以大家不要在意代码格式，虽然很烂，但是求大家轻踩

一 安装依赖

npm install exceljs --save

二 先定义数据

// report.json

{
  "filename": "我的报表",
  "sheetName": "sheet1",
  "sheetDescript": [
    "人口统计",
    "创建人: https://guofengs.com",
    "创建时间: 2019/12/03",
    "统计时间范围: 2019/12/03~2019/12/31"
  ],
  "header": [
    {
      "id": "id"
    },
    {
      "name": "名字"
    },
    {
      "email": "电子邮件"
    },
    {
      "addr": "地址"
    }
  ],
  "contentLists": [
    {
      "id": "1",
      "name": "张三",
      "email": "1@zs.com",
      "addr": "上海市浦东新区"
    },
    {
      "id": "2",
      "name": "李四",
      "email": "1@ls.com",
      "addr": "上海市浦东新区"
    },
    {
      "id": "3",
      "name": "王五",
      "email": "1@ww.com",
      "addr": "上海市浦东新区"
    },

    {
      "id": "3",
      "name": "赵六",
      "email": "1@zl.com",
      "addr": "上海市浦东新区"
    }
  ]

}

三 代码实现

然后是代码： //app.js

/**
 * @Description: 自定义excel表格样式
 * @author https://www.guofengs.com
 * @date 2019-12-93 21:28
 */
const Excel = require("exceljs");
const jsonData = require("./report")

const COLOR = {
	BLUE: "9BC2E6",
	GREEN: "A9D08E",
	REPORT: "9BC2E6"
}


function GetFileStyle(sheet) {
	// 合并单元格
	this.setMerge = function (rows, columns) {
		let char = String.fromCharCode(64 + columns)
		for (let i = 1; i <= rows; i++) {
			sheet.mergeCells(`A${i}:${char}${i}`);
		}
		return this;
	}

	// 设置通用值 type = 1 desc, type = 2 header, 3 type
	this.setGeneralValue = function (type, numberLine, params) {
		switch (type) {
			case 1:
				this.setDesc(numberLine, params);
				break;
			case 2:
				this.setHeader(numberLine, params);
				break;
			case 3:
				this.setContent(numberLine, params)
			default:
				break;
		}
		return this;
	}

	// 垂直插值
	this.setDesc = function (numberLines, value) {
		let item = null;
		for (let i = 0, length = value.length; i < length; i++) {
			item = value[i];
			sheet.getCell(`A${i + 1}`).value = item;
		}
		return this
	}

	// 设置header
	this.setHeader = function (numberLines, value) {
		let keys = []
		for (let i = 0, length = value.length; i < length; i++) {
			let currentLines = numberLines ? numberLines : sheet.rowCount + 1;
			let columnsChar = String.fromCharCode(64 + i + 1);
			let cellNumbers = `${columnsChar}${currentLines}`;
			let item = value[i];
			for (let its in item) {
				keys.push({
					key: its,
					width: 18
				})
				sheet.getCell(cellNumbers).value = item[its];
			}
		}
		sheet.columns = keys
		return this
	}

	// 设置内容
	this.setContent = function (numberLines, value) {
		sheet.addRows(value)
		return this;
	}

	// 设置字体是否居中
	this.setAlignment = function (cell, verticalValue, horizontalValue) {
		sheet.getCell(cell).alignment = {vertical: verticalValue, horizontal: horizontalValue}
		return this;
	}

	// 设置字体大小
	this.setFontSize = function (cell) {
		sheet.getCell(cell).font = {
			color: { argb: "FF0000" },
			family: 2,
			size: 14,
			bold: true
		};
		return this;
	}

	// 设置背景色
	this.setBackgroundColor = function (cell, color) {
		console.log("color", color)
		sheet.getCell(cell).fill = {
			type: 'pattern',
			pattern: 'solid',
			fgColor: {
				argb: `FF${color}`
			}
		};
		return this;
	}

	// 设置表格边框
	this.setBorderStyle = function (top, right, bottom, left, color, columns, rows) {
		for (let i = 1; i <= columns; i++) {

			let char = String.fromCharCode(64 + i)
			console.log(`fdsjklfjdskfjdk${char}${rows}`)
			sheet.getCell(`${char}${rows}`).border = {
				top: {
					style: top,
					color: {
						argb: `FF${color}`
					}
				},
				left: {
					style: top,
					color: {
						argb: `FF${color}`
					}
				},
				bottom: {
					style: top,
					color: {
						argb: `FF${color}`
					}
				},
				right: {
					style: top,
					color: {
						argb: `FF${color}`
					}
				}
			};
		}
		return this;
	}
	this.setBorderRightStyle = function () {
		for (let i = 1; i <= 5; i++) {
			sheet.getCell(`A${i}`).border = {
				right: {
					style: "thin",
					color: {
						argb: `FF000000`
					}
				}
			};
		}
		return this;
	}

	return this
}


function createReportFile() {
	let {
		filename,
		sheetName,
		sheetDescript,
		header,
		contentLists
	} = jsonData;

	let length = header.length || 4
	let workbook = new Excel.Workbook();
	let sheet = workbook.addWorksheet(sheetName);

	let getFileObj = new GetFileStyle(sheet)
	getFileObj
		.setGeneralValue(1, null, sheetDescript)
		.setGeneralValue(2, 6, header)
		.setGeneralValue(3, null, contentLists)
		.setFontSize("A1")
		.setBorderStyle("thin", "thin", "thin", "thin", "000000", length,6)
		.setBorderRightStyle()
		.setMerge(5, length)
		.setBackgroundColor("A1", COLOR.GREEN)
		.setAlignment("A1", "middle", "center");

	workbook.xlsx.writeFile(`${filename}.xlsx`);
	return filename;
}

createReportFile()

以上就是所有内容，送给所有需要的童鞋