exportExcel.ts
import * as XLSX from 'xlsx';
import XLSXStyle from 'xlsx-style-vite';
/**
*
* @param list 表格数据[{ name: '凡凡',inNum: 10}]
* @param headList 表头 [{name: '姓名',prop: 'name'},{name: '收入',children: [{name: '数量',prop: 'inNum'}]}],
* @param sheetName 表名
* @param hasHead 是否需要导出表头
* 调用exportData(list, headList,sheetName);
*/
interface itemT {
name: string;
prop?: string;
children?: { name: string; prop?: string }[];
}
export default function exportData(
list: { [porp: string]: any }[],
headList: itemT[],
sheetName: string,
hasHead: boolean
) {
// if (!Array.isArray(list) || list.length < 1) {
// console.log('导出数据为空');
// return;
// }
//删除Children为空,不然数据对不上
headList = deleteChildren(headList);
// excel表头
const excelHeader = hasHead ? buildHeader(headList) : [];
// 头部行数,用来固定表头
const headerRows = excelHeader.length;
// 提取数据,当数据为空时只导出表头
const dataList = !Array.isArray(list) || list.length < 1 ? [] : extractData(list, headList);
excelHeader.push(...dataList, []);
// 计算合并
const merges = doMerges(excelHeader);
// 生成sheet
const ws = aoa_to_sheet(excelHeader, headerRows, hasHead) as any;
// 单元格合并
ws['!merges'] = merges;
// 头部冻结
ws['!freeze'] = {
xSplit: '1',
ySplit: '' + headerRows,
topLeftCell: 'B' + (headerRows + 1),
activePane: 'bottomRight',
state: 'frozen',
};
// 设置行宽
const arr = [];
const num = excelHeader[0].length;
// ws['!rows'] = [{
// hpx: 100
// }]
for (let i = 0; i < num; i++) {
arr.push({
wpx: 120,
});
}
ws['!cols'] = arr;
const workbook: { SheetNames: any; Sheets: any } = {
SheetNames: [sheetName],
Sheets: {},
};
workbook.Sheets[sheetName] = ws;
// excel样式
const wbout = XLSXStyle.write(workbook, { bookType: 'xlsx', bookSST: false, type: 'binary', cellStyles: true });
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' });
openDownloadXLSXDialog(blob, sheetName + '.xlsx');
}
function buildHeader(revealList: any) {
const excelHeader: any[] = [];
// 构建生成excel表头需要的数据结构
getHeader(revealList, excelHeader, 0, 0);
// 多行表头长短不一,短的向长的看齐,不够的补上行合并占位符
const max = Math.max(...excelHeader.map((a) => a.length));
excelHeader.filter((e) => e.length < max).forEach((e) => pushRowSpanPlaceHolder(e, max - e.length));
return excelHeader;
}
function getHeader(headers: string | any[], excelHeader: any[], deep: number, perOffset: number) {
let offset = 0;
let cur = excelHeader[deep];
if (!cur) {
cur = excelHeader[deep] = [];
}
// 填充行合并占位符
pushRowSpanPlaceHolder(cur, perOffset - cur.length);
for (let i = 0; i < headers.length; i++) {
const head = headers[i];
cur.push(head.name);
// eslint-disable-next-line no-prototype-builtins
if (head.hasOwnProperty('children') && Array.isArray(head.children) && head.children.length > 0) {
const childOffset = getHeader(head.children, excelHeader, deep + 1, cur.length - 1);
// 填充列合并占位符
pushColSpanPlaceHolder(cur, childOffset - 1);
offset += childOffset;
} else {
offset++;
}
}
return offset;
}
/**
* 根据选中的数据和展示的列,生成结果
* @param list
* @param revealList
*/
function extractData(list: any[], revealList: any) {
// 列
const headerList = flat(revealList);
// 导出的结果集
const excelRows: any[][] = [];
// 如果有children集合的话会用到
const dataKeys = new Set(Object.keys(list[0]));
list.some((e: { children: string | any[] }) => {
if (e.children && e.children.length > 0) {
const childKeys = Object.keys(e.children[0]);
for (let i = 0; i < childKeys.length; i++) {
dataKeys.delete(childKeys[i]);
}
return true;
}
});
flatData(list, (list: any) => {
excelRows.push(...buildExcelRow(dataKeys, headerList, list));
});
return excelRows;
}
function buildExcelRow(mainKeys: Set<string>, headers: string | any[], rawDataList: string | any[]) {
// 合计行
const sumCols: string | any[] = [];
// 数据行
const rows = [];
for (let i = 0; i < rawDataList.length; i++) {
const cols = [];
const rawData = rawDataList[i];
// 提取数据
for (let j = 0; j < headers.length; j++) {
const header = headers[j];
// 父元素键需要行合并
if (rawData['rowSpan'] === 0 && mainKeys.has(header.prop)) {
cols.push('!$ROW_SPAN_PLACEHOLDER');
} else {
let value;
if (typeof header.exeFun === 'function') {
value = header.exeFun(rawData);
} else {
value = rawData[header.prop];
}
cols.push(value);
// 如果该列需要合计,并且是数字类型
if (header['summable'] && typeof value === 'number') {
sumCols[j] = (sumCols[j] ? sumCols[j] : 0) + value;
}
}
}
rows.push(cols);
}
return rows;
}
/**
* 合并头部单元格
**/
function doMerges(arr: string | any[]) {
// 要么横向合并 要么纵向合并
const deep = arr.length;
const merges = [];
for (let y = 0; y < deep; y++) {
// 先处理横向合并
const row = arr[y];
let colSpan = 0;
for (let x = 0; x < row.length; x++) {
if (row[x] === '!$COL_SPAN_PLACEHOLDER') {
row[x] = undefined;
if (x + 1 === row.length) {
merges.push({ s: { r: y, c: x - colSpan - 1 }, e: { r: y, c: x } });
}
colSpan++;
} else if (colSpan > 0 && x > colSpan) {
merges.push({ s: { r: y, c: x - colSpan - 1 }, e: { r: y, c: x - 1 } });
colSpan = 0;
} else {
colSpan = 0;
}
}
}
// 再处理纵向合并
const colLength = arr[0].length;
for (let x = 0; x < colLength; x++) {
let rowSpan = 0;
for (let y = 0; y < deep; y++) {
if (arr[y][x] === '!$ROW_SPAN_PLACEHOLDER') {
arr[y][x] = undefined;
if (y + 1 === deep) {
merges.push({ s: { r: y - rowSpan, c: x }, e: { r: y, c: x } });
}
rowSpan++;
} else if (rowSpan > 0 && y > rowSpan) {
merges.push({ s: { r: y - rowSpan - 1, c: x }, e: { r: y - 1, c: x } });
rowSpan = 0;
} else {
rowSpan = 0;
}
}
}
return merges;
}
/**
* 从github复制过来的
*/
function aoa_to_sheet(data: string | any[], headerRows: number, hasHead: boolean) {
const ws: any = {};
const range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } };
for (let R = 0; R !== data.length; ++R) {
for (let C = 0; C !== data[R].length; ++C) {
if (range.s.r > R) {
range.s.r = R;
}
if (range.s.c > C) {
range.s.c = C;
}
if (range.e.r < R) {
range.e.r = R;
}
if (range.e.c < C) {
range.e.c = C;
}
/// 这里生成cell的时候,使用上面定义的默认样式
const cell: any = {
v: data[R][C] || '',
s: {
font: {
name: '宋体',
sz: 10,
bold: hasHead ? false : C % 2 ? false : true,
color: {
auto: 1,
},
},
// border: {
// top: { style: 'thin', color: { rgb: '000000' } },
// left: { style: 'thin', color: { rgb: '000000' } },
// bottom: { style: 'thin', color: { rgb: '000000' } },
// right: { style: 'thin', color: { rgb: '000000' } },
// },
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: 'center',
vertical: 'center',
indent: 0,
},
},
};
// 头部列表加边框
if (R < headerRows) {
// cell.s.border = {
// top: { style: 'thin', color: { rgb: '000000' } },
// left: { style: 'thin', color: { rgb: '000000' } },
// bottom: { style: 'thin', color: { rgb: '000000' } },
// right: { style: 'thin', color: { rgb: '000000' } },
// };
// cell.s.fill = {
// patternType: 'solid',
// // 背景颜色
// fgColor: { theme: 3, tint: 0.3999755851924192, rgb: 'ffffff' },
// bgColor: { theme: 7, tint: 0.3999755851924192, rgb: 'ffffff' },
// };
cell.s.font = {
name: '宋体',
sz: 10,
bold: true,
color: {
rgb: '000',
},
};
}
const cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
if (typeof cell.v === 'number') {
cell.t = 'n';
} else if (typeof cell.v === 'boolean') {
cell.t = 'b';
} else {
cell.t = 's';
}
ws[cell_ref] = cell;
}
}
if (range.s.c < 10000000) {
ws['!ref'] = XLSX.utils.encode_range(range);
}
return ws;
}
/**
* 填充行合并占位符
* */
function pushRowSpanPlaceHolder(arr: string[], count: number) {
for (let i = 0; i < count; i++) {
arr.push('!$ROW_SPAN_PLACEHOLDER');
}
}
// 填充列合并占位符
function pushColSpanPlaceHolder(arr: string[], count: number) {
for (let i = 0; i < count; i++) {
arr.push('!$COL_SPAN_PLACEHOLDER');
}
}
function flatData(list: string | any[], eachDataCallBack: { (list: any): void; (arg0: any[]): void }) {
const resultList = [];
for (let i = 0; i < list.length; i++) {
const data = list[i];
const rawDataList = [];
// 每个子元素都和父元素合并成一条数据
if (data.children && data.children.length > 0) {
for (let j = 0; j < data.children.length; j++) {
delete data.children[j].bsm;
const copy = Object.assign({}, data, data.children[j]);
rawDataList.push(copy);
copy['rowSpan'] = j > 0 ? 0 : data.children.length;
}
} else {
data['rowSpan'] = 1;
rawDataList.push(data);
}
resultList.push(...rawDataList);
if (typeof eachDataCallBack === 'function') {
eachDataCallBack(rawDataList);
}
}
return resultList;
}
function flat(revealList: any[]) {
const result: any[] = [];
revealList.forEach((e: { [x: string]: any; hasOwnProperty: (arg0: string) => any; children: any }) => {
if (Object.prototype.hasOwnProperty.call(e, 'children')) {
result.push(...flat(e.children));
} else if (Object.prototype.hasOwnProperty.call(e, 'exeFun')) {
result.push(e);
} else if (Object.prototype.hasOwnProperty.call(e, 'prop')) {
result.push(e);
}
});
return result;
}
function s2ab(s: string) {
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;
}
// 下载文件
function openDownloadXLSXDialog(url: string | Blob | MediaSource, saveName: string) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
const aLink = document.createElement('a');
aLink.href = url as string;
aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
let event;
if (window.MouseEvent) {
event = new MouseEvent('click');
} else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
//去掉chidren为空的
function deleteChildren(arr: any) {
const childs = arr;
for (let i = childs.length; i--; i > 0) {
if (childs[i].children) {
if (childs[i].children.length) {
deleteChildren(childs[i].children);
} else {
delete childs[i].children;
}
}
}
return arr;
}