SheetJS -- 前端(浏览器)导出Excel文件
项目中有需求导出业务报表为Excel文件,并且需要有一定的样式和格式要求,后端导出的Excel样式设置比较困难于是只好让前端整了┓( ´∀` )┏
需求调研
前端导出Excel的库基本上大家用的都是SheetJS,我也不找其他了,直接整个SheetJS社区版开干了(SheetJS CE)
SheetJS的功能
SheetJS可以实现数据导入/导出/处理,文档上有一些实际的示例,比如将文档流中的table标签导出为Excel文件, 读取Excel文件生成table标签,将CSV文件转换为HTML表格或者Excel文件. 社区版(CE)的功能相较专业版(Pro)主要是阉割了CSS样式和富文本。
引入/安装方式
可以使用Script标签引入:
<script lang="javascript" src="https://cdn.sheetjs.com/xlsx-0.18.9/package/dist/xlsx.full.min.js"></script>
也可以使用Web Worker,这个没怎么研究,感兴趣可以看下
也可以使用ESM导入方式:
<script type="module"> import { read, writeFileXLSX } from "https://cdn.sheetjs.com/xlsx-0.18.9/package/xlsx.mjs"; </script>
如果需要XLS支持,需要手动导入cpexcel.full.mjs,这部分主要是为了导出旧版的EXCEL格式(.xls,较新的文件格式是.xlsx)
<script type="module"> /* load the codepage support library for extended support with older formats */ import { set_cptable } from "https://cdn.sheetjs.com/xlsx-0.18.9/package/xlsx.mjs"; import * as cptable from 'https://cdn.sheetjs.com/xlsx-0.18.9/package/dist/cpexcel.full.mjs'; set_cptable(cptable); </script>
当然npm库也是有的,因为我们需要额外的样式定制,所以还引入了xlsx-style:
"xlsx": "^0.17.5",
"xlsx-style": "^0.8.13"
使用npm库后,上面兼容XLS部分的标签换成npm库ESM导入的方式如下:
/* load the codepage support library for extended support with older formats */
import { set_cptable } from "xlsx";
import * as cptable from 'xlsx/dist/cpexcel.full.mjs';
set_cptable(cptable);
基础概念
SheetJS工作的数据基础格式是二维数组(Array of Array) 或者对象数组 (Array of Object).
- Array of Array:
[
["Jamie","1990-10-11","Engineer"],
["Tracy","1991-10-11","Designer"],
]
- Array of Object:
[
{ name: "George Washington", birthday: "1732-02-22" },
{ name: "John Adams", birthday: "1735-10-19" },
]
导出示例
JSON/javascript Array -> Excel
首先我们需要创建一个Workbook,也就是Excel中的工作簿,在SheetJS中创建工作簿很简单:
XLSX.utils.book_new,如名字所示,可以创建新的工作薄
const workbook = XLSX.utils.book_new();
XLSX.utils.json_to_sheet,如名字所示,可以将json转换为工作表,我们先新建一个worksheet,也就是Excel中的工作表:
const worksheet = XLSX.utils.json_to_sheet(rows);
再将工作簿放进工作表,并且将工作簿命名为Empoyees:
XLSX.utils.book_append_sheet(workbook, worksheet, "Empoyees");
导出Excel文件,并将导出Excel命名为Empoyees.xlsx:
XLSX.writeFile(workbook, "Empoyees.xlsx");
在浏览器环境下会直接提示用户下载该Excel.
工具函数
SheetJS的工具函数放在导出XLSX.utils下,包括新建工作表,工作表添加工作簿等:
拷贝了一份utils的导出类型声明:
/** General utilities */
export interface XLSX$Utils {
/* --- Import Functions --- */
/** Converts an array of arrays of JS data to a worksheet. */
aoa_to_sheet<T>(data: T[][], opts?: AOA2SheetOpts): WorkSheet;
aoa_to_sheet(data: any[][], opts?: AOA2SheetOpts): WorkSheet;
/** Converts an array of JS objects to a worksheet. */
json_to_sheet<T>(data: T[], opts?: JSON2SheetOpts): WorkSheet;
json_to_sheet(data: any[], opts?: JSON2SheetOpts): WorkSheet;
/** BROWSER ONLY! Converts a TABLE DOM element to a worksheet. */
table_to_sheet(data: any, opts?: Table2SheetOpts): WorkSheet;
table_to_book(data: any, opts?: Table2SheetOpts): WorkBook;
sheet_add_dom(ws: WorkSheet, data: any, opts?: Table2SheetOpts): WorkSheet;
/* --- Export Functions --- */
/** Converts a worksheet object to an array of JSON objects */
sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[];
sheet_to_json(worksheet: WorkSheet, opts?: Sheet2JSONOpts): any[][];
sheet_to_json(worksheet: WorkSheet, opts?: Sheet2JSONOpts): any[];
/** Generates delimiter-separated-values output */
sheet_to_csv(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
/** Generates UTF16 Formatted Text */
sheet_to_txt(worksheet: WorkSheet, options?: Sheet2CSVOpts): string;
/** Generates HTML */
sheet_to_html(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
/** Generates a list of the formulae (with value fallbacks) */
sheet_to_formulae(worksheet: WorkSheet): string[];
/** Generates DIF */
sheet_to_dif(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
/** Generates SYLK (Symbolic Link) */
sheet_to_slk(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
/** Generates ETH */
sheet_to_eth(worksheet: WorkSheet, options?: Sheet2HTMLOpts): string;
/* --- Cell Address Utilities --- */
/** Converts 0-indexed cell address to A1 form */
encode_cell(cell: CellAddress): string;
/** Converts 0-indexed row to A1 form */
encode_row(row: number): string;
/** Converts 0-indexed column to A1 form */
encode_col(col: number): string;
/** Converts 0-indexed range to A1 form */
encode_range(s: CellAddress, e: CellAddress): string;
encode_range(r: Range): string;
/** Converts A1 cell address to 0-indexed form */
decode_cell(address: string): CellAddress;
/** Converts A1 row to 0-indexed form */
decode_row(row: string): number;
/** Converts A1 column to 0-indexed form */
decode_col(col: string): number;
/** Converts A1 range to 0-indexed form */
decode_range(range: string): Range;
/** Format cell */
format_cell(cell: CellObject, v?: any, opts?: any): string;
/* --- General Utilities --- */
/** Creates a new workbook */
book_new(): WorkBook;
/** Append a worksheet to a workbook */
book_append_sheet(workbook: WorkBook, worksheet: WorkSheet, name?: string): void;
/** Set sheet visibility (visible/hidden/very hidden) */
book_set_sheet_visibility(workbook: WorkBook, sheet: number|string, visibility: number): void;
/** Set number format for a cell */
cell_set_number_format(cell: CellObject, fmt: string|number): CellObject;
/** Set hyperlink for a cell */
cell_set_hyperlink(cell: CellObject, target: string, tooltip?: string): CellObject;
/** Set internal link for a cell */
cell_set_internal_link(cell: CellObject, target: string, tooltip?: string): CellObject;
/** Add comment to a cell */
cell_add_comment(cell: CellObject, text: string, author?: string): void;
/** Assign an Array Formula to a range */
sheet_set_array_formula(ws: WorkSheet, range: Range|string, formula: string): WorkSheet;
/** Add an array of arrays of JS data to a worksheet */
sheet_add_aoa<T>(ws: WorkSheet, data: T[][], opts?: SheetAOAOpts): WorkSheet;
sheet_add_aoa(ws: WorkSheet, data: any[][], opts?: SheetAOAOpts): WorkSheet;
/** Add an array of JS objects to a worksheet */
sheet_add_json(ws: WorkSheet, data: any[], opts?: SheetJSONOpts): WorkSheet;
sheet_add_json<T>(ws: WorkSheet, data: T[], opts?: SheetJSONOpts): WorkSheet;
consts: XLSX$Consts;
}
我们应该会用到的几个API:
book_new: 新建工作表,无参数返回WorkBook对象
| 参数名称 | 解释 | 类型 |
|---|---|---|
| 无参数 | ||
| 返回值 | 工作表对象 | WorkBook |
book_append_sheet: 向工作表追加工作簿
| 参数名称 | 解释 | 类型 |
|---|---|---|
| workbook | 工作表 | WorkBook |
| worksheet | 工作簿 | WorkSheet |
| name | 工作簿名称 | String |
| 返回值 | 无 | void |
table_to_sheet: HTML文档流table转换为Sheet
| 参数名称 | 解释 | 类型 |
|---|---|---|
| data | DOM | any |
| opts? | 配置选项 | Table2SheetOpts |
| 返回值 | 工作簿 | WorkSheet |
xlsx-style
xlsx-style其实是从SheetJS项目中fork出来的,能够提供单元格格式的定制功能,基本API和xlsx差不多.
支持的读取格式:
- Excel 2007+ XML Formats (XLSX/XLSM)
- Excel 2007+ Binary Format (XLSB)
- Excel 2003-2004 XML Format (XML "SpreadsheetML")
- Excel 97-2004 (XLS BIFF8)
- Excel 5.0/95 (XLS BIFF5)
- OpenDocument Spreadsheet (ODS)
支持的写入格式
- XLSX
- CSV (and general DSV)
- JSON and JS objects (various styles)
xlsx-style工作的基础就是WorkSheet对象上单元格对应的对象CellObject,可以看到CellObject含有一个s字段,代表单元格的style,但是仅仅支持少量的属性,例如border,font,fgColor等:
/** Worksheet Object */
export interface WorkSheet extends Sheet {
/**
* Indexing with a cell address string maps to a cell object
* Special keys start with '!'
*/
[cell: string]: CellObject | WSKeys | any;
/** Column Info */
'!cols'?: ColInfo[];
/** Row Info */
'!rows'?: RowInfo[];
/** Merge Ranges */
'!merges'?: Range[];
/** Worksheet Protection info */
'!protect'?: ProtectInfo;
/** AutoFilter info */
'!autofilter'?: AutoFilterInfo;
}
/** Worksheet Cell Object */
export interface CellObject {
/** The raw value of the cell. Can be omitted if a formula is specified */
v?: string | number | boolean | Date;
/** Formatted text (if applicable) */
w?: string;
/**
* The Excel Data Type of the cell.
* b Boolean, n Number, e Error, s String, d Date, z Empty
*/
t: ExcelDataType;
/** Cell formula (if applicable) */
f?: string;
/** Range of enclosing array if formula is array formula (if applicable) */
F?: string;
/** Rich text encoding (if applicable) */
r?: any;
/** HTML rendering of the rich text (if applicable) */
h?: string;
/** Comments associated with the cell */
c?: Comments;
/** Number format string associated with the cell (if requested) */
z?: NumberFormat;
/** Cell hyperlink object (.Target holds link, .tooltip is tooltip) */
l?: Hyperlink;
/** The style/theme of the cell (if applicable) */
s?: any;
}
我们的基本思路就是先用xlsx同table表格生成一个WorkBook对象,然后对WorkBook的每个CellObject设置显示样式,再使用xlsx-style的writeAPI将WorkBook对象写入为二进制流(binary),再将该流转换为Blob,通过file-saver导出Excel文件.
//生成工作簿
const workSheet = XLSX.utils.table_to_sheet(dom, { raw: true });
//每个单元格加边框,列序数从0开始,行序数从1开始
for (let columnIndex = 0; columnIndex < getTotalColumns(headers); columnIndex++) {
for (let rowIndex = 1; rowIndex <= items.length + headerRows; rowIndex++) {
const key = `${getColumnKeyFromIndex(columnIndex)}${rowIndex}`;
// console.log(columnIndex, rowIndex, key);
if (!workSheet[key]) {
workSheet[key] = {
t: "z", // b Boolean, n Number, e error, s String, d Date, z Stub
v: "", // cell原始值 string | number | boolean | Date
s: {
//边框
border: {
top: { style: "thin", color: { auto: 1 } },
left: { style: "thin", color: { auto: 1 } },
bottom: { style: "thin", color: { auto: 1 } },
right: { style: "thin", color: { auto: 1 } },
},
},
};
}
}
}
//其他样式,略
//通用样式
Object.keys(workSheet).forEach((key) => {
if (!key.startsWith("!")) {
try {
const [column, row] = getExcelColumnRowFromKey(key);
const rowIndex = row - headerRows - 1;
const columnIndex = column - 1;
const rowItem = rowIndex >= 0 ? props.items[rowIndex] : {};
//excel的列号是从1开始的,需要减一
const columnItem = columnIndex >= 0 ? getTreeLeafs(headers)[columnIndex] : {};
const styleObject = getCellStyle({
row: rowItem,
column: columnItem,
rowIndex: rowIndex,
columnIndex: columnIndex,
});
const bgColorHex = colorToHex(styleObject.backgroundColor).replace("#", "");
const colorHex = "";
workSheet[key].s = {
font: {
sz: 7, //7号字体
bold: row === headerRows, //第三行加粗
color: {
rgb: colorHex ?? "FF333333", //字体颜色
},
},
fill: {
fgColor: {
rgb: bgColorHex,
},
},
alignment: {
horizontal: "center", //水平居中对其
vertical: "center", //垂直居中对其
},
//border 对合并的单元格不起作用
border: {
right: { style: "thin", color: { auto: 1 } },
bottom: { style: "thin", color: { auto: 1 } },
left: { style: "thin", color: { auto: 1 } },
top: { style: "thin", color: { auto: 1 } },
},
};
} catch (error) {
console.log(error);
}
}
});
//设置列宽
workSheet["!cols"] = filteredHeaders
.flatMap((it) => (it.children ? [it, ...it.children] : [it]))
.map((head) => ({
wpx: head.minWidth,
}));
//...
//工作表
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, sheetName);
//生成二进制对象
const WithStyle = XlsxStyle.write(workBook, {
bookType: "xlsx",
bookSST: false,
type: "binary",
});
//导出 file-saver.js 的 saveAs
saveAs(
new Blob([sheetAsArrayBuffer(WithStyle)], {
type: "application/octet-stream",
}),
`${fileName}.xlsx`
);
用到的二进制转ArrayBuffer方法:
function sheetAsArrayBuffer(s) {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i != s.length; ++i) {
view[i] = s.charCodeAt(i) & 0xff;
}
return buf;
}
列序数转Excel列名(A,B, ... AA, AB这种格式):
//根据index获取excel列索引 例如 0->A, 1->B, 26->AA, 27->AB
export function getColumnKeyFromIndex(index) {
//根据index获取excel列索引
let quotient = index + 1;
let reminder = 0;
let column = ""
do {
reminder = quotient % 26;
//console.log(`reminder = ${reminder}`)
quotient = Math.floor(quotient / 26);
//console.log(`quotient = ${quotient}`)
if (reminder === 0) {
reminder = 26
quotient -= 1
}
column = numberToAlphabet(reminder) + column;
} while (quotient !== 0)
return column;
}
从Excel单元格名称获取行列数:
/**
* 从A1格式的字符串获取行列数[1,1](从1开始的)
* @param {String} key
*/
export function getExcelColumnRowFromKey(key) {
//从Excel单元格分成列和行
const column = key.replace(/[0-9]/g, "");
const excelRow = key.replace(/[A-Z]/g, "");
//从Excel列名转换成数字
const excelColumn = column.split("").reverse().reduce((sum, item, index) => {
const value = item.replace(/[A-Z]/g, (match) => {
return match.charCodeAt(0) - 65 + 1;
})
sum += value * Math.pow(26, index);
return sum
}, 0)
return [parseInt(excelColumn), parseInt(excelRow)];
}
代码解释
这部分代码通过列号,行号生成Excel单元格名称(A1,B1这样的),先写入通用的格式
这部分代码对已存在的CellObject写入定制的样式,主要是从业务中获取单元格的样式数据(当前只有背景色),然后设置
TODO
现在的方式要同时维护table的样式和导出的样式,而且需要保持两者一致,还是有一点心智负担的,后面考虑通过table每个cell的computedStyle获取可以映射的样式,设置到导出的对应CellObject.s对象上,这样应该可以做到所见即所得的导出体验..加油吧打工人..
