前言
大家好呀,我是雪荷。在上篇文章(EasyExcel 初使用—— Java 实现多种写入 Excel 功能-CSDN博客)中给大家介绍了 Java 是如何写入 Excel 的,那么这篇算是对上篇文章的拓展,主要介绍前端和后端分别是如何导出数据至 Excel 的。
前端导出 Excel
我就用之前比赛的项目给大家演示吧,其组件库为 Ant Design Vue,框架为 Vue3,使用的第三方库为 XLSX。整体的实现并不困难只需写两个函数即可,话不多说直接上代码。
安装命令
npm install XLSX
引入 XLSX
import * as XLSX from 'xlsx';
vue 的 template 部分
<template><a-tableid="table-data"style="margin-top: 30px;":columns="columns":dataSource="data"class="antv-table"></a-table><a-button type="primary" @click="exportTableData">导出表格数据</a-button><a-button type="primary" @click="downloadExcel">Excel下载</a-button><a-button type="primary" @click="exportData">导出数据</a-button>
</template>
ts 导出 Excel 代码
// 将 Table 组件的数据转为 Excel 数据
const transData = (columns: any, tableList: any) => {const obj = columns.reduce((acc, cur) => {if (!acc.titles && !acc.keys) {acc.titles = [];acc.keys = [];}acc.titles.push(cur.title);acc.keys.push(cur.dataIndex);return acc;}, {});const tableBody = tableList.map(item => {return obj.keys.map(key => item[key]);});return [obj.titles, ...tableBody];
};
// 将数据写入 Excel 文件
const exportTableData = () => {const tableData = transData(columns,data.value);// 将一组 JS 数据数组转换为工作表const ws = XLSX.utils.aoa_to_sheet(tableData);// 创建 workbookconst wb = XLSX.utils.book_new();// 将 工作表 添加到 workbookXLSX.utils.book_append_sheet(wb, ws, 'Sheet1');// 将 workbook 写入文件XLSX.writeFile(wb, '销售数据.xlsx');
};
ts 请求后端接口代码
watchEffect(async () => {const res: any = await myAxios.post('/sale/data/list',);if (res?.code === 0) {data.value = res.data.map((item: SaleData, index: number) => ({key: index,id: item.id,name: item.name,price: item.price,totalNum: item.totalNum,userId: item.userId,datetime: item.datetime,}));} else {message.error('数据获取失败');}
});
后端代码
Controller 层:
@PostMapping("/data/list")public BaseResponse<List<SaleData>> listSaleData() {List<SaleData> list = new ArrayList<>();list.add(new SaleData(1L, "苹果", new BigDecimal("10.00"), 10, 1L,"2024-01-01 13:00:00"));list.add(new SaleData(2L, "梨子", new BigDecimal("12.00"), 10, 1L,"2025-01-01 13:00:00"));list.add(new SaleData(3L, "西瓜", new BigDecimal("5.00"), 10, 1L,"2024-03-01 13:00:00"));list.add(new SaleData(4L, "香蕉", new BigDecimal("7.00"), 10, 1L,"2024-02-01 13:00:00"));return ResultUtils.success(list);}
SaleData:
@Data
@AllArgsConstructor
public class SaleData implements Serializable {
@ExcelProperty("订单号")private Long id;
@ExcelProperty("品种")private String name;
@ExcelProperty("价格")private BigDecimal price;
@ExcelProperty("数量")private Integer totalNum;
@ExcelProperty("交易对象")private Long userId;
@ExcelProperty("交易时间")private String datetime;
}
前端导出主要是通过 XLSX 这个库实现的,其根据 Ant Design 的 Table 组件的 columns 属性和后端返回的 List 构建成了一个 Excel 文件,最后利用 writeFile 方法导出 Excel 文件。
点击“导出表格数据”按钮,可以看到数据已导出至 Excel 中了。
优点:简单,便捷
缺点:
-
不灵活,不适合非固定表头和复杂的表头
-
前端导出不适合数据量大的场景,因为页面会卡死
后端导出 Excel
据我了解的后端实现导出 Excel 的功能主要有两种。一种是将 Excel 写入流中,前端拿到文件流再转为 blob 后进行下载,另一种是将 Excel 文件转为 base64 编码,前端将 base64 编码转为 blob 再下载。由此可见最终都是要转为 blob 的,拿到 blob 就好搞了。
后端将 Excel 写入流中
后端代码
Controller 层:
@PostMapping("/data/download")public void exportSaleDetails(HttpServletResponse response) throws UnsupportedEncodingException {List<SaleData> list = new ArrayList<>();list.add(new SaleData(1L, "苹果", new BigDecimal("10.00"), 10, 1L, "2024-01-01 13:00:00"));list.add(new SaleData(2L, "梨子", new BigDecimal("12.00"), 10, 1L, "2025-01-01 13:00:00"));list.add(new SaleData(3L, "西瓜", new BigDecimal("5.00"), 10, 1L, "2024-03-01 13:00:00"));list.add(new SaleData(4L, "香蕉", new BigDecimal("7.00"), 10, 1L, "2024-02-01 13:00:00"));
// 设置响应头信息response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");
String fileName = "销售数据1.xlsx";response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
// 写出Excel文件到响应try {EasyExcel.write(response.getOutputStream(), SaleData.class).sheet("合伙人业务订单").doWrite(list);} catch (IOException e) {e.printStackTrace();}}
ts 代码
const downloadExcel = () => {const response = myAxios.post('/sale/data/download', null, {responseType: 'blob',}).then((res) => { // 处理返回的文件流const content = resconst blob = new Blob([content])console.log(content)const fileName = '销售数据.xlsx';if ('download' in document.createElement('a')) { // 非IE下载const elink = document.createElement('a')elink.download = fileNameelink.style.display = 'none'elink.href = URL.createObjectURL(blob)document.body.appendChild(elink)elink.click()URL.revokeObjectURL(elink.href) // 释放URL 对象document.body.removeChild(elink)} else { // IE10+下载navigator.msSaveBlob(blob, fileName)}});
};
点击“Excel 下载”按钮导出数据。
优点:
-
传输效率高,网络负载小
-
内存占用,适合传输大文件
缺点:
-
会有跨域问题
后端返回 base 64 编码,前端再转为 blob
后端代码
@PostMapping("/data/export")public BaseResponse<String> exportSaleData() throws UnsupportedEncodingException {List<SaleData> list = new ArrayList<>();list.add(new SaleData(1L, "苹果", new BigDecimal("10.00"), 10, 1L, "2024-01-01 13:00:00"));list.add(new SaleData(2L, "梨子", new BigDecimal("12.00"), 10, 1L, "2025-01-01 13:00:00"));list.add(new SaleData(3L, "西瓜", new BigDecimal("5.00"), 10, 1L, "2024-03-01 13:00:00"));list.add(new SaleData(4L, "香蕉", new BigDecimal("7.00"), 10, 1L, "2024-02-01 13:00:00"));
// 生成 Excel 文件并转换为 Base64 编码ByteArrayOutputStream outputStream = new ByteArrayOutputStream();EasyExcel.write(outputStream, SaleData.class).sheet("Sheet1").doWrite(list);byte[] bytes = outputStream.toByteArray();String excelBase64 = Base64.getEncoder().encodeToString(bytes);
// 返回 Base64 编码的 Excel 内容给前端return ResultUtils.success(excelBase64);}
ts 代码
const exportData = async () => {const response: any = await myAxios.post('/sale/data/export');if (response?.code === 0) {const excelBase64 = response.data; // 接收后端返回的 Base64 编码字符串console.log(excelBase64)const blob = b64toBlob(excelBase64, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');const fileName = '销售数据.xlsx';if ('download' in document.createElement('a')) { // 非IE下载const elink = document.createElement('a')elink.download = fileNameelink.style.display = 'none'elink.href = URL.createObjectURL(blob)document.body.appendChild(elink)elink.click()URL.revokeObjectURL(elink.href) // 释放URL 对象document.body.removeChild(elink)} else { // IE10+下载navigator.msSaveBlob(blob, fileName)}}
}
// 将 Base64 字符串转换为 Blob 对象
function b64toBlob(b64Data, contentType = '', sliceSize = 512) {const byteCharacters = atob(b64Data);const byteArrays = [];for (let offset = 0; offset < byteCharacters.length; offset += sliceSize) {const slice = byteCharacters.slice(offset, offset + sliceSize);const byteNumbers = new Array(slice.length);for (let i = 0; i < slice.length; i++) {byteNumbers[i] = slice.charCodeAt(i);}const byteArray = new Uint8Array(byteNumbers);byteArrays.push(byteArray);}return new Blob(byteArrays, {type: contentType});
}
主要分为三个阶段,首先后端将文件写入流中再将流转为 base64 编码返回给前端,第二步前端拿到 base64 编码将其转为 blob,最后根据 blob 进行下载。
点击“导出数据”按钮导出数据。
优点:
-
传输数据是 base64 字符串便于调试
-
跨域问题少
缺点:
-
内存占用大,适合传输小文件
-
传输效率低
全部代码
前端
<!-- eslint-disable vue/multi-word-component-names -->
<template><a-tableid="table-data"style="margin-top: 30px;":columns="columns":dataSource="data"class="antv-table"></a-table>
<a-button type="primary" @click="exportTableData">导出表格数据</a-button><a-button type="primary" @click="downloadExcel">Excel下载</a-button><a-button type="primary" @click="exportData">导出数据</a-button>
</template>
<script setup lang="ts">
import {ref, watchEffect} from "vue";
import myAxios from "@/plugins/myAxios";
import {message, TableColumnsType} from "ant-design-vue";
import * as XLSX from 'xlsx';
const columns: TableColumnsType = [{title: '订单号', width: 100, dataIndex: 'id', key: 'id', fixed: 'left'},{title: '品种', dataIndex: 'name', key: 'name', width: 150},{title: '价格', dataIndex: 'price', key: 'price', width: 150},{title: '数量', dataIndex: 'totalNum', key: 'totalNum', width: 150},{title: '交易对象', dataIndex: 'userId', key: 'userId', width: 150},{title: '交易时间', dataIndex: 'datetime', key: 'datetime', width: 150},
];
interface SaleData {id: number;name: string;price: number;totalNum: number;userId: number;datetime: string;
}
interface DataItem {key: number;id: number;name: string;price: number;totalNum: number;userId: number;datetime: string;
}
const data = ref<DataItem[]>([]);
watchEffect(async () => {const res: any = await myAxios.post('/sale/data/list',);if (res?.code === 0) {data.value = res.data.map((item: SaleData, index: number) => ({key: index,id: item.id,name: item.name,price: item.price,totalNum: item.totalNum,userId: item.userId,datetime: item.datetime,}));} else {message.error('数据获取失败');}
});
const transData = (columns: any, tableList: any) => {const obj = columns.reduce((acc, cur) => {if (!acc.titles && !acc.keys) {acc.titles = [];acc.keys = [];}acc.titles.push(cur.title);acc.keys.push(cur.dataIndex);return acc;}, {});const tableBody = tableList.map(item => {return obj.keys.map(key => item[key]);});return [obj.titles, ...tableBody];
};
const exportTableData = () => {const tableData = transData(columns,data.value);// 将一组 JS 数据数组转换为工作表const ws = XLSX.utils.aoa_to_sheet(tableData);// 创建 workbookconst wb = XLSX.utils.book_new();// 将 工作表 添加到 workbookXLSX.utils.book_append_sheet(wb, ws, 'Sheet1');// 将 workbook 写入文件XLSX.writeFile(wb, '销售数据.xlsx');
};
const downloadExcel = () => {const response = myAxios.post('/sale/data/download', null, {responseType: 'blob',}).then((res) => { // 处理返回的文件流const content = resconst blob = new Blob([content])console.log(content)const fileName = '销售数据.xlsx';if ('download' in document.createElement('a')) { // 非IE下载const elink = document.createElement('a')elink.download = fileNameelink.style.display = 'none'elink.href = URL.createObjectURL(blob)document.body.appendChild(elink)elink.click()URL.revokeObjectURL(elink.href) // 释放URL 对象document.body.removeChild(elink)} else { // IE10+下载navigator.msSaveBlob(blob, fileName)}});
};
const exportData = async () => {const response: any = await myAxios.post('/sale/data/export');if (response?.code === 0) {const excelBase64 = response.data; // 接收后端返回的 Base64 编码字符串console.log(excelBase64)const blob = b64toBlob(excelBase64, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');const fileName = '销售数据.xlsx';if ('download' in document.createElement('a')) { // 非IE下载const elink = document.createElement('a')elink.download = fileNameelink.style.display = 'none'elink.href = URL.createObjectURL(blob)document.body.appendChild(elink)elink.click()URL.revokeObjectURL(elink.href) // 释放URL 对象document.body.removeChild(elink)} else { // IE10+下载navigator.msSaveBlob(blob, fileName)}}
}
// 将 Base64 字符串转换为 Blob 对象
function b64toBlob(b64Data, contentType = '', sliceSize = 512) {const byteCharacters = atob(b64Data);const byteArrays = [];for (let offset = 0; offset < byteCharacters.length; offset += sliceSize) {const slice = byteCharacters.slice(offset, offset + sliceSize);const byteNumbers = new Array(slice.length);for (let i = 0; i < slice.length; i++) {byteNumbers[i] = slice.charCodeAt(i);}const byteArray = new Uint8Array(byteNumbers);byteArrays.push(byteArray);}return new Blob(byteArrays, {type: contentType});
}
</script>
<style scoped>
</style>
后端
Controller 层:
@Slf4j
@RestController
@RequestMapping("/sale")
public class SaleController { @PostMapping("/data/download")public void exportSaleDetails(HttpServletResponse response) throws UnsupportedEncodingException {List<SaleData> list = new ArrayList<>();list.add(new SaleData(1L, "苹果", new BigDecimal("10.00"), 10, 1L, "2024-01-01 13:00:00"));list.add(new SaleData(2L, "梨子", new BigDecimal("12.00"), 10, 1L, "2025-01-01 13:00:00"));list.add(new SaleData(3L, "西瓜", new BigDecimal("5.00"), 10, 1L, "2024-03-01 13:00:00"));list.add(new SaleData(4L, "香蕉", new BigDecimal("7.00"), 10, 1L, "2024-02-01 13:00:00"));
// 设置响应头信息response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");
String fileName = "销售数据1.xlsx";response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
// 写出Excel文件到响应try {EasyExcel.write(response.getOutputStream(), SaleData.class).sheet("合伙人业务订单").doWrite(list);} catch (IOException e) {e.printStackTrace();}}
@PostMapping("/data/export")public BaseResponse<String> exportSaleData() throws UnsupportedEncodingException {List<SaleData> list = new ArrayList<>();list.add(new SaleData(1L, "苹果", new BigDecimal("10.00"), 10, 1L, "2024-01-01 13:00:00"));list.add(new SaleData(2L, "梨子", new BigDecimal("12.00"), 10, 1L, "2025-01-01 13:00:00"));list.add(new SaleData(3L, "西瓜", new BigDecimal("5.00"), 10, 1L, "2024-03-01 13:00:00"));list.add(new SaleData(4L, "香蕉", new BigDecimal("7.00"), 10, 1L, "2024-02-01 13:00:00"));
// 生成 Excel 文件并转换为 Base64 编码ByteArrayOutputStream outputStream = new ByteArrayOutputStream();EasyExcel.write(outputStream, SaleData.class).sheet("Sheet1").doWrite(list);byte[] bytes = outputStream.toByteArray();String excelBase64 = Base64.getEncoder().encodeToString(bytes);
// 返回 Base64 编码的 Excel 内容给前端return ResultUtils.success(excelBase64);}@PostMapping("/data/list")public BaseResponse<List<SaleData>> listSaleData() {List<SaleData> list = new ArrayList<>();list.add(new SaleData(1L, "苹果", new BigDecimal("10.00"), 10, 1L,"2024-01-01 13:00:00"));list.add(new SaleData(2L, "梨子", new BigDecimal("12.00"), 10, 1L,"2025-01-01 13:00:00"));list.add(new SaleData(3L, "西瓜", new BigDecimal("5.00"), 10, 1L,"2024-03-01 13:00:00"));list.add(new SaleData(4L, "香蕉", new BigDecimal("7.00"), 10, 1L,"2024-02-01 13:00:00"));return ResultUtils.success(list);}
}
SaleData:
@Data
@AllArgsConstructor
public class SaleData implements Serializable {
@ExcelProperty("订单号")private Long id;
@ExcelProperty("品种")private String name;
@ExcelProperty("价格")private BigDecimal price;
@ExcelProperty("数量")private Integer totalNum;
@ExcelProperty("交易对象")private Long userId;
@ExcelProperty("交易时间")private String datetime;
}
总结
相信大家看完也能学会导出 Excel,可以根据具体的开发需求选择不同的方案,如果有更好的方案欢迎探讨哈。