react+antd 导出excel文件（简单数据&多级表头）
需求：
在基于react+antd进行开发的页面中，实现导出excel报表的功能
实际场景：
1.简单数据：单层表头+数据
2.复杂数据：多层表头+数据
实现方式：
1.简单数据
简单数据的导出使用了js-export-excel插件，需要自己转换表头数据与表格数据，代码如下：
import { useState } from 'react';
import { message, Button } from 'antd';
import ExportJsonExcel from 'js-export-excel';
import type { RequestData } from '@/utils/request';
import type { ProjectInfoParams } from '@/services/summary';
import type { PaginationProps } from 'antd';
import { downloadChannelSettle } from '@/services/xxx'; // 接口地址
interface DownloadEffortsProps {
columnsData: any[];
totalList?: any[];
tableParams?: ProjectInfoParams & PaginationProps & { order?: string; sort?: string };
}
const DownloadEfforts: React.FC<DownloadEffortsProps> = (props) => {
const { columnsData, tableParams } = props;
// 按钮loading
const [downLoading, setDownLoading] = useState<boolean>(false);
// 下载结算申请报表
const downloadEffectExcel = async () => {
// 请求所有数据
const downloadParams = { ...tableParams, current: 1, pageSize: 0, pageNum: 1 };
// 开启按钮loading
setDownLoading(true);
try {
const res: RequestData = await downloadChannelSettle(downloadParams);
if (res.result) {
const { tableList } = res.result;
// 表头数组去掉操作栏
const newestHeaderList = columnsData.filter((arr: any) => arr.valueType !== 'option');
// 导出报表的表头与数据的处理
const resultData: any[] = [];
if (tableList) {
tableList.forEach((item: any, index: number) => {
const resultItem = {};
newestHeaderList.forEach((el: any, i: number) => {
if (newestHeaderList[i].title) {
resultItem[newestHeaderList[i].title] =
tableList[index][newestHeaderList[i].dataIndex];
}
});
resultData.push(resultItem);
});
}
const newHeader = newestHeaderList.map((item: any) => item.title);
const option = {
fileName: '导出数据', // 文件名字
datas: [
{
sheetData: resultData, // 数据
sheetName: 'sheet',
sheetFilter: newHeader, // 表头
sheetHeader: newHeader, // 表头
},
],
};
const toExcel = new ExportJsonExcel(option);
toExcel.saveExcel();
} else {
message.error('下载失败！');
}
} catch (error) {
//
} finally {
// 关闭按钮loading
setDownLoading(false);
}
};
return (
<Button key="downloadExcel" type="ghost" onClick={downloadEffectExcel} loading={downLoading}>
下载报表
</Button>
);
};
export default DownloadEfforts;
2.复杂数据
复杂数据，我只处理了多层表头的情况，table组件展示如图：
因为js-export-excel插件无法直接导出多层表头的数据（并且产品要求excel文件要与table展示布局相同），因此选用了react-html-table-to-excel插件进行开发，代码如下：
import { useState, useRef, useEffect } from 'react';
import { isEmpty } from 'lodash';
import { message, Table, Button } from 'antd';
import ReactHTMLTableToExcel from 'react-html-table-to-excel';
import type { RequestData } from '@/utils/request';
import type { ParamsType } from '@ant-design/pro-provider';
import type { PaginationProps } from 'antd';
import { downloadChannelGoBackReport } from '@/services/xxx';
import type { SettleApplicationParams } from '@/services/xxx';
interface DownloadEffortsProps {
columnsData: any[];
totalList?: any[];
tableParams?: ParamsType & PaginationProps & { order?: string; sort?: string; month?: string[] };
}
const DownloadEfforts: React.FC<DownloadEffortsProps> = (props) => {
const { columnsData, tableParams } = props;
const buttonRef = useRef<any>(null);
const tableRef = useRef<any>(null);
const [tableData, setTableData] = useState<SettleApplicationParams[]>([]);
// 按钮loading
const [downLoading, setDownLoading] = useState<boolean>(false);
// 下载报表
const downloadEffectExcel = async (e: any) => {
e.stopPropagation();
// 请求所有数据
const downloadParams = { ...tableParams, current: 1, pageSize: 0, pageNum: 1 };
if (downloadParams.month && !isEmpty(downloadParams.month)) {
// 开启按钮loading
setDownLoading(true);
try {
const res: RequestData = await downloadChannelGoBackReport(downloadParams);
if (res.result) {
const { tableList } = res.result;
setTableData(tableList);
} else {
message.error('下载失败！');
setDownLoading(false);
}
} catch (error) {
//
}
} else {
message.error('查询时间不能为空');
}
};
useEffect(() => {
// 拿到本次请求的数据后，再进行下载
if (columnsData.length > 0 && tableData.length > 0) {
const table = tableRef?.current?.querySelector('table');
table.setAttribute('id', 'table-to-xls');
buttonRef.current.handleDownload();
// 关闭按钮loading
setDownLoading(false);
// 下载完后，清除数据
setTimeout(() => {
setTableData([]);
}, 0);
}
// eslint-disable-next-line react-hooks/exhaustive-deps
}, [tableData])
useEffect(() => {
// 禁止组件按钮的默认点击事件(因为ts默认用的是Element，需要声明为HTMLElement)
const button = document.querySelector('#test-table-xls-button') as HTMLElement;
if (button) button.style['pointer-events'] = ('none');
}, []);
return (<>
<Button key="downloadExcel" type="ghost" onClick={(e) => downloadEffectExcel(e)} loading={downLoading}>
下载报表
</Button>
<span style={{ visibility: 'hidden' }}>
<ReactHTMLTableToExcel
ref={buttonRef}
key="downloadExcel"
id="test-table-xls-button"
className="ant-btn"
table="table-to-xls"
filename="报表"
sheet="报表"
buttonText="下载报表"
/>
</span>
<div ref={tableRef} style={{ position: 'absolute', zIndex: -1, top: 0, left: 0, width: 0, height: 0 }}>
<Table
rowKey='id'
columns={columnsData.filter((arr: any) => arr.valueType !== 'option')}
dataSource={tableData}
/>
</div>
</>
);
};
export default DownloadEfforts;
最终导出效果：
【ps】：因为YYYY-MM格式的表头数据会在导出时自动转格式（如上图），因此可以在表头处理时手动转为【YYYY年MM月】的格式避免此类情况。
参考文档：https://blog.csdn.net/weixin_44240581/article/details/115265810
这两个依赖的配置项很接近，肉眼可以明确属性名的含义，不做赘述。
