背景介绍
近期项目有个「导出作业详情」的需求，之前接触大部分导出需求均为后端获取数据生成Excel文件或将Excel文件的下载地址或文件流提供给前端进行下载。在本需求中对下载的文件有如下需求点：
- 命名：用户名作业详单-起止日期
- 格式：xlsx，下载内容CPU与GPU分别显示在两个工作表里
技术调研
在以前的项目中有=接触过前端解析 Excel 导入数据的功能，使用的是 [xlsx](https://www.npmjs.com/package/xlsx)，功能超级强大，便先了解一下这个库是否能满足咱们的需求,经过稍加深入地调研，了解到这个库仅侧重在 Excel 解析，我们的需求是要能根据后端提供的数据动态生成 Excel 文件，可能这个库就不适应了。
经过经一步调研，发现一款采用MIT开源授权， Star 数近 10k 的开源库：exceljs,看描述为：读取，操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。这不就正是我所需要的嘛，接下来的问题就是验证工作了，根据我们的需求，主要需要验证的有如下事项：
1、是否可支持生成多工作表的Excel文件
2、是否支持自定义单元格样式：如背景色、边框、对齐方式、格式化
3、测试Excel文件生成效率
经过对相关API的深入了解及验证，发现完全能满足我们的需求。其中生成效率，一次性生成40000多条数据，不到15秒即可完成Excel生成工作，也能满足我们的实际要求：
技术点梳理
详细见代码及相关注释。仅用于说明相关功能点，非完整代码。
// 引入 exceljs 依赖
import ExcelJS from 'exceljs'
// 创建工作簿
const workbook = new ExcelJS.Workbook()
// 在工作簿中添加两个工作表
const cpuSheet = workbook.addWorksheet('CPU作业详单')
const gpuSheet = workbook.addWorksheet('GPU作业详单')
// 添加表头
const cpuHeaderRow = cpuSheet.addRow(['作业ID', '作业名称', '超算中心', '超算账号', '队列', '运行时长', '核数', '消费核时', '消费金额', '提交时间', '开始时间', '结束时间', '提交账号', '付费账号'])
// 设置表头样式
// 行高
cpuHeaderRow.height = 26.5
// 字体
cpuHeaderRow.font = { bold: true }
// 设置表头单元格样式
cpuHeaderRow.eachCell((cell, rowNumber) => {
// 设置边框
cell.border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
}
// 填充背景
cell.fill = {
type: 'pattern',
pattern: 'darkTrellis',
fgColor: { argb: 'F1F1F1FF' },
bgColor: { argb: 'F1F1F1FF' }
}
// 对齐方式
cell.alignment = { vertical: 'middle', horizontal: cpuLeftAlignCols.includes(rowNumber) ? 'left' : 'center', wrapText: true }
})
// 根据内容适当调整部分列宽度
cpuSheet.getColumn(2).width = 30 // 作业名称
cpuSheet.getColumn(10).width = 20 // 提交时间
cpuSheet.getColumn(11).width = 20 // 开始时间
cpuSheet.getColumn(12).width = 20 // 结束时间
cpuSheet.getColumn(13).width = 15 // 提交账号
cpuSheet.getColumn(14).width = 15 // 付费账号
// 生成相关数据行
for (let i = 0; i < cpuJobList.length; i++) {
const job = cpuJobList[i]
const rowData = [job.jobId, job.jobName, job.cluster, job.user, job.partition, ...]
const dataRow = cpuSheet.addRow(rowData)
// 设置数据行样式
dataRow.height = 26.5
dataRow.eachCell((cell, rowNumber) => {
// 设置单元格边框
cell.border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
}
// 设置对齐方式
cell.alignment = { vertical: 'middle', horizontal: cpuLeftAlignCols.includes(rowNumber) ? 'left' : 'center', wrapText: true }
// 金额格式化
if (rowNumber === 10) {
cell.numFmt = '"¥"#,##.##'
}
})
}
// 保存Excel文件
const buf = await workbook.xlsx.writeBuffer()
const fileName = `${userForFileName}作业详单_${dayjs(startDate).format('YYYYMMDD')}-${dayjs(endDate).format('YYYYMMDD')}.xlsx`
// 基于 file-saver 实现保存文件到本地
saveAs(new Blob([buf]), fileName)最终整合到应用中的效果
扩展
打印相关
// 设置页面方向
workSheet.pageSetup.orientation = 'portrait' // portrait || landscape
// 设置页边距
workSheet.pageSetup.margins = {
left: 0.3,
right: 0.3,
top: 0.2,
bottom: 0.2,
header: 0.2,
footer: 0.2
}
workSheet.pageSetup.horizontalCentered = true
workSheet.pageSetup.verticalCentered = false
// 每页均显示的行
workSheet.pageSetup.printTitlesRow = '1:3'
// 指定打印哪些列
workSheet.pageSetup.printTitlesColumn = 'A:G'解析 Excel
const ExcelJS = require('exceljs')
const excelfile = './test.xlsx'
var workbook = new ExcelJS.Workbook()
workbook.xlsx.readFile(excelfile).then(function() {
// 获取第一个worksheet
var worksheet = workbook.getWorksheet(1)
// 编辑worksheet
worksheet.eachRow(function(row, rowNumber) {
var rowSize = row.cellCount
var numValues = row.actualCellCount
console.log('单元格数量/实际数量:' + rowSize+'/' + numValues)
row.eachCell(function(cell, colNumber) {
// cell.type单元格类型：6-公式 ;2-数值；3-字符串
let cellValue = cell.value
if(cell.type === 6) {
cellValue = cell.result
}
console.log('cell',rowNumber, colNumber, cellValue, cell.numFmt)
})
})
})数值格式化问题
前期为满足跟前端列表展现业务逻辑保持一致，金额及数值格式化均使用 Intl.NumberFormat进行格式化，但是该类格式化后的数据是字符串，不便于导出后对数据进行排序及数值比较等操作。
由于官方文档的单元格格式化说明信息较简单，Excel里对数值格式化的方式较丰富，如下图：
为完整的复原格式化后的内容，可以先在Excel文件中对相关单元格设置好格式后，使用上面的 解析 Excel 章节的代码解析获取单元格的 numFmt数据。下面列举本工程中用到的数值格式化 numFmt 值：
- 数值格式化：
#,##0.00_ - 货币格式化：
"￥"#,##0.00;"￥"-#,##0.00
评论 (0)