前言
在 PC
端将数据导入文件并下载场景还是较常见的,相对而言也比较简单, xlsx 库即可满足。但如果我们需要定制表格格式时,我们可能需要去了解它的扩展库 xlsx-style ,它不仅具有 xlsx
库的所有功能,而且可以按照特定格式和布局生成精美的 Excel
文件。这里不会讲述过多 读取/写入 文件的基本使用,本文主要讲述如何实现定制表格对部分内容,如:表头内容、单元格样式、单元格自适应宽度,以及获取表格内容最大行和列。
说明: xlsx-style 已经不再维护,目前最新版本也存在不少问题并且库是不支持 ts
的。不过这里我们可以使用 xlsx-style-ts 这个库,它很好的解决了这些问题。
XLSX
xlsx 提供了一个中间层用于操作数据,他将不同类型的文件抽象成 js
对象,从而规避了操作不同种类数据数据之间的复杂性。并且围绕着这个对象提供了一系列的抽象功能。其浏览器端和 Node
端的区别仅仅在于怎样导入、导出文件上,对于数据的操作是一致的。
依赖安装
npm i xlsx
写入文件
这里主要以 数据对象 (json_data
) 与 表格数据(sheet) 之间的转换为例,更多数据转换可以查看文档
// 工作簿
const workBook = XLSX.utils.book_new()
// 工作表
const workSheet = XLSX.utils.json_to_sheet(jsonData) as TWorkSheet
// 将工作表添加到工作簿并写入文件
XLSX.utils.book_append_sheet(workBook, workSheet, 'Sheet1')
// 文件下载【实现见下方内容】
downloadExcel(workBook, 'data.xlsx')
文件下载
- 第三方库 file-saver
- 依据
a
标签
方式一
安装 file-saver
依赖
npm i file-saver
示例:
import FileSaver from 'file-saver'
/**
* 下载 excel 文件
* @param workBook 工作簿
* @param fileName 文件名
*/
export const downloadExcel = (workBook: XLSX.WorkBook, fileName: string) => {
// 写出 arraybuffer 数据
const file = XLSX.write(workBook, { bookType: 'xlsx', type: 'array' })
// 构建 Blob 对象
const _blob = new Blob([file], { type: 'application/octet-stream' })
// 下载 Excel 文件
FileSaver(_blob, fileName)
}
方式二
使用 a
标签形式
/**
* 下载 excel 文件
* @param workBook 工作簿
* @param fileName 文件名
*/
export const downloadExcel = (workBook: XLSX.WorkBook, fileName: string) => {
// 将 workbook 转换成二进制
const file = XLSX_STYLE.write(workBook, { type: 'binary', bookType: 'xlsx' })
// 创建 ArrayBuffer 对象来存储二进制数据
const fileBuffer = new ArrayBuffer(file.length)
// 创建视图来存储并操作二进制数据
const fileView = new Uint8Array(fileBuffer)
// 将字符串转换为二进制
for (let i = 0; i < file.length; i++) {
fileView[i] = file.charCodeAt(i) & 0xff
}
// 创建 Blob 对象
const blob = new Blob([fileBuffer], {
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
})
// 创建 a 标签
const link = document.createElement('a')
// 创建下载的链接
link.href = URL.createObjectURL(blob)
// 下载后文件名
link.download = fileName
// 点击下载
link.click()
// 释放掉 blob 对象
URL.revokeObjectURL(link.href)
}
定制内容
样式自定义
xlsx-style 已经不再维护,目前最新版本存在问题并且库不支持 ts
。这里也使用 xlsx-style-ts 这个库,它基于 xlsx-style
库的 ts
版本库,并且解决 xlsx-style
存在的问题。
安装依赖
npm i xlsx-style-ts
代码实现
import XLSX_STYLE from 'xlsx-style-ts'
/**
* 自定义单元格样式
* @param workSheet 工作表
* @param data 数据源
* @param headerFields 头部字段相关信息
* @returns 新的工作表
*/
export const handleCellStyle = (workSheet: TWorkSheet) => {
const cloneSheet = cloneDeep(workSheet)
for (const key in workSheet) {
const cellStyle = {
font: { color: { rgb: 'ff0000' }, // 定义红色字体
...
}
cloneSheet[key] = {
...cloneSheet[key],
s: cellStyle
}
}
return cloneSheet
}
// 写入数据的时使用 XLSX_STYLE 即可
const file = XLSX_STYLE.write(workBook, { type: 'binary', bookType: 'xlsx' })
头部自定义
excel
中的单元格坐标(A1)与 XLSX
中的数值相互转化
说明: XLSX
数值从 0
开始,转化过成中总是在单元格基础上减 1
,更多可以查看文档
- 单元格转
XLSX
数值:A1 => 00
// 列【第一列】
XLSX.utils.decode_col('A') // 'A' => 0
// 行【第一行】
XLSX.utils.decode_row('1') // '1' => 0
// 单元格【第二列第五行(B5)】
XLSX.utils.decode_cell('B5') // 'B5' => {c: 1, r: 4}
// 表格范围【第一列第一行到第三列第四行】
XLSX.utils.decode_range('A1:C4') // 'A1:B2' => {e: {c: 0, r: 0},s: {c: 2, r: 3}}
XLSX
数值转单元格:00 => A1
// 列【第二列】
XLSX.utils.encode_col(1) // 1 => 'B'
// 行【第二行】
XLSX.utils.encode_row(1) // 1 => '2'
// 单元格【第一列第一行】
XLSX.utils.encode_cell({c:0,r:0}) // {c:0,r:0} => A1
// 表格范围【第一列第一行到第二列第二行】
XLSX.utils.encode_range({s: {c:0,r:0},e: {c:1,r: 1}}) // A1:B2 => {s: {c:0,r:0},e: {c:1,r: 1}}
实现思路
- 依据参数数据源、表头字段及表头字段映射关系,获取表头字段的表格坐标
- 通过
XLSX.utils.decode_range
方法与sheet['!ref']
表格值获取表格数值范围range
- 依据表头字段的表格坐标重设单元格范围,剔除不需要的(数据中的 extra)列
- 循环
range
范围,依据表头字段的表格坐标重设表头单元格的值 - 返回新的工作表
代码实现
type THeaderField = {
header: string[] // 表头字段
headerNameMap: { // 表头字段映射
[propsName: string]: string
}
}
/**
* 处理表头名称
* @param sheet 工作表
* @param data 数据源
* @param headerFields 头部字段相关信息
* @returns 新的工作表
*/
export const handleHeaderNames = (sheet: TWorkSheet, data: any[], headerFields?: THeaderField) => {
const cloneSheet = cloneDeep(sheet)
const headerField = headerFields?.header || []
const headerNameMap = headerFields?.headerNameMap || {}
const colLength = headerField?.length > 0 ? headerField?.length : Object.keys(data?.[0])?.length
const lastCol = XLSX.utils.encode_col(colLength - 1)
const lastRow = XLSX.utils.encode_row(data?.length)
// 找出这个表格的单元格范围
const range = XLSX.utils.decode_range(cloneSheet['!ref'])
// 重新设置单元格范围,剔除不需要的(数据中的 extra)列
cloneSheet['!ref'] = `A1:${lastCol}${lastRow}`
// 找到 worksheet 中 A1,B1,C1... 等设置表头的字段,替换为上方 headerNameMap 对应的数据
for (let c = range.s.c; c <= range.e.c; c++) {
const header = XLSX.utils.encode_col(c) + '1'
const val = cloneSheet[header].v
cloneSheet[header].v = headerNameMap[val] || val
}
return cloneSheet
}
获取表格行、列
实现思路
- 通过
XLSX.utils.decode_range
方法与sheet['!ref']
表格值获取表格数值范围range
- 返回行、列如需包含空内容【有值但为空】单元格,直接返回
range
数值e
值的c
、r
值加1
- 如果想要获取有值单元格行、列,则遍历
range
范围内的单元格,通过判断单元格的值是否为空,来赋值比较max
值 - 循环比较赋值后,则返回行、列
max
数值加1
即是我们需要的行、列值
代码实现
/**
* 获取读取 excel 文件内容行、列
* @param sheet 工作表
* @param isContainEmpty 计算时是否包含空内容
* @returns 工作表的行、列数组
*/
const getSheetRowNum = (sheet: TWorkSheet, isContainEmpty = true) => {
const range = XLSX.utils.decode_range(sheet['!ref']!)
if (isContainEmpty) {
return [range.e.r + 1, range.e.c + 1]
}
let maxRow = 0,
maxCol = 0
// 通过 sheet 数据获取表格有值的行数
for (let col = 0; col <= range.e.c; col++) {
for (let row = range.e.r; row >= range.s.r; row--) {
const cellKey = XLSX.utils.encode_cell({ r: row, c: col })
const cell = sheet[cellKey]
if (cell && cell.v !== '') {
maxCol = Math.max(maxCol, col)
maxRow = Math.max(maxRow, row)
}
}
}
return [maxRow + 1, maxCol + 1]
}
自适应宽度
以下是不同字体和大小时 Excel
文件中各字符占的宽度:
- 字体:
Calibri
,字号:11
- 数字和标点符号:
2.46
个点 - 小写字母:
5.55
个点 - 大写字母:
6.37
个点
- 数字和标点符号:
- 字体:
Calibri
,字号:12
- 数字和标点符号:
2.78
个点 - 小写字母:
6.28
个点 - 大写字母:
7.22
个点
- 数字和标点符号:
- 字体:
Calibri
,字号:14
- 数字和标点符号:
3.22
个点 - 小写字母:
7.29
个点 - 大写字母:
8.37
个点
- 数字和标点符号:
实现思路
- 获取表格最大列
sheet['!ref'].split(':')[1].replace(/\d/gi, '')
- 获取表格最大行
sheet['!ref'].split(':')[1].replace(/\D/gi, '')
- 通过
letterToNum
方法将字母转换为数字,循环遍历表格最大行和列数值,然后获取单元格值对象sheet[String.fromCharCode(65 + i) + (j + 1)]
- 匹配单元格
cell.v
值中各字符的长度,结合对应字符宽度计算比较,获取当前列最大列宽maxWidth
- 将
maxWidth
赋值给sheet
的!cols
属性数组即可
代码实现
/**
* 将字母转换为数字
* @param str 字母字符串
* @returns 转化后的数字
*/
export const letterToNum = (str: string) => {
let num = 0
for (let i = 0; i < str.length; i++) {
num = num * 26 + str.charCodeAt(i) - 64
}
return num
}
/**
* 获取单元格宽度
* @param sheet 工作表
* @param isAuto 是否自动
* @returns 新的工作表面
*/
export const getCellWidth = (sheet: TWorkSheet, isAuto: boolean) => {
const cloneWorkSheet = cloneDeep(sheet)
const cols = []
for (let i = 0; i < letterToNum(cloneWorkSheet['!ref'].split(':')[1].replace(/\d/gi, '')); i++) {
// 计算最大宽度
let maxWidth = 0
for (let j = 0; j < Number(cloneWorkSheet['!ref'].split(':')[1].replace(/\D/g, '')); j++) {
const cell = cloneWorkSheet[String.fromCharCode(65 + i) + (j + 1)]
const cellValue = String(cell?.v)
let cellWidth = cellValue.length * 7
if (isAuto) {
const chineseNum = cellValue.match(/[\u4e00-\u9fa5]/g)?.length || 0
const lowerCaseNum = cellValue.match(/[a-z]/g)?.length || 0
const upperCaseNum = cellValue.match(/[A-Z]/g)?.length || 0
const numberNum = cellValue.match(/[0-9]/g)?.length || 0
const otherNum = cellValue.match(/[^a-zA-Z0-9\u4e00-\u9fa5]/g)?.length || 0
cellWidth =
chineseNum * 15 + lowerCaseNum * 7 + upperCaseNum * 10 + numberNum * 3 + otherNum * 12
}
maxWidth = Math.max(maxWidth, cellWidth)
}
cols.push({ wpx: maxWidth })
}
// 设置列宽到 sheet 对象
cloneWorkSheet['!cols'] = cols
return cloneWorkSheet
}
回顾总结
- 讲述了
XLSX
写入Excel
文件的基本流程及下载文件的方法 - 结合
xlsx-style-ts
库实现对Excel
文件单元格的样式设置 - 通过
XLSX
的utils
工具类,实现了表头名称的替换、获取工作表的行、列等功能 - 依据
Excel
文件中不同字体和大小时各字符占的宽度,实现了自适应宽度的功能