好久没写文章了，去年写的一套xlsx 前端导出文档忘记写出来了，因为公司业务原因，需要自己写一套方法，所以打算发出来，如果有什么不好的地方也希望大家能多多指点
推荐使用 npm库 xlsx-js-style
npm i -s xlsx-js-style
使用方法
import { utils as xlsxUtils, writeFile, CellObject, WorkSheet, CellStyle } from 'xlsx-js-style';
// 导出多页面excel configData配置的数据--此方法是多页面导出
export const exportMultipleData = (filename: string, configData: Record<string, any>) => {
// 非对象不操作
if (!configData || !(configData instanceof Object)) {
return false;
}
const workbook = xlsxUtils.book_new();
Object.keys(configData).forEach(key => {
// key为页面名称
let options = configData[key];
let { worksheet } = getWorksheetInfo(options.headers, options.originData, options.extraHandler);
xlsxUtils.book_append_sheet(workbook, worksheet, key);
});
writeFile(workbook, filename + '.xlsx');
};
// 基本版本-导出单个页面
export const exportMultipleHeadersData = (
filename: string, //文件名
headers: Cell[], //导出配置
originData: Record<string, any>[], //data数据
extraHandler?: (worksheet: WorkSheet) => void
) => {
const workbook = xlsxUtils.book_new();
let { worksheet } = getWorksheetInfo(headers, originData, extraHandler);
xlsxUtils.book_append_sheet(workbook, worksheet);
writeFile(workbook, filename + '.xlsx');
};
主要方法 getWorksheetInfo
interface Cell {
key: string; //唯一标识
title: string; //标题文字
children?: Cell[]; // 合并单元格
isLeaf?: boolean;
maxRowObj?: { value: number };
row?: number; // row index
col_s?: number; // column start index
col_e?: number; //column end index
header_s?: CellStyle; // header style
s?: CellStyle; // cell style
t?: CellObject['t']; // cell data type
l?: CellObject['l']; //cel link
headFontStyle?: CellStyle['font'];
headFontAlign?: CellStyle['alignment'];
headBg?: string;
fill?: CellStyle['fill'];
colFontAlign?: CellStyle['alignment'];
colBg?: string;
}
// 获取excel信息对象 抽离公共逻辑
const getWorksheetInfo = (
headersList: Cell[],
originData: Record<string, any>[],
extraHandler?: (worksheet: WorkSheet) => void
) => {
let row = 0;
let maxRowObj = { value: 0 };
let maxCol = 0;
let headers = headersList
console.log(headers);
const keys: string[] = [];
const keyCellMap = new Map<string, Cell>();
/** dfs to handle header row and col index */
const dfs = (arr: Cell[]) => {
arr.forEach(cell => {
cell.row = row;
cell.maxRowObj = maxRowObj;
if (!cell.children) {
cell.isLeaf = true;
cell.col_s = maxCol;
maxCol++;
cell.col_e = maxCol;
keys.push(cell.key);
keyCellMap.set(cell.key, cell);
} else {
cell.col_s = maxCol;
row++;
cell.col_e = dfs(cell.children);
}
});
maxRowObj.value = Math.max(maxRowObj.value, row);
row--;
return maxCol;
};
dfs(headers);
const headerData: CellObject[][] = [];
const sequence = [headers];
const merges: typeof worksheet['!merges'] = [];
/** bfs to handle header cell merge */
const bfs = () => {
while (sequence.length > 0) {
let temp = sequence.shift();
if (temp) {
temp.forEach(cell => {
merges.push({
s: {
r: cell.row!,
c: cell.col_s!,
},
e: {
r: cell.children ? cell.row! : cell.maxRowObj!.value,
c: cell.col_e! - 1,
},
});
if (!headerData[cell.row!]) {
headerData[cell.row!] = [];
headerData[cell.row!].length = maxCol;
}
headerData[cell.row!][cell.col_s!] = { v: cell.title, t: 's', s: cell.header_s };
if (cell.children) {
sequence.push(cell.children);
}
});
}
}
};
bfs();
/** handle data */
const data: CellObject[][] = originData.map(row => {
return keys.map(key => {
const cell = keyCellMap.get(key)!;
const cellObj: CellObject = { v: row[key], t: 's' };
if (cell.s) {
cellObj.s = cell.s;
}
if (cell.t) {
cellObj.t = cell.t;
}
return cellObj;
});
});
const excelData = headerData.concat(data);
const worksheet = xlsxUtils.aoa_to_sheet(excelData);
console.log(excelData);
worksheet['!merges'] = merges;
/*设置worksheet每列的最大宽度*/
const colWidth = headers.map(row =>{
if(row.wch){
return {wch:row.wch}
}
let val = row.title;
/*先判断是否为null/undefined*/
if (val == null) {
return { wch: 10 };
} else if (val.toString().charCodeAt(0) > 255) {
/*再判断是否为中文*/
return { wch: val.toString().length * 2 + 5 };
} else {
return { wch: val.toString().length + 5 };
}
}
);
console.log(colWidth);
worksheet['!cols'] = colWidth;
extraHandler && extraHandler(worksheet);
return {
worksheet,
};
};
header配置参考
{
headers: [
{
key: 'scheme_name',
title: '名称',
headFontStyle: {
name: '楷体',
sz:10,
bold: true,
color: {rgb: "ffffff"}
},
headFontAlign: { horizontal: 'left', vertical: 'center' }, //设置标题水平竖直方向居中，并自动换行展示
headBg: '2C497D', //设置标题单元格的背景颜色
wch:24
},
]
