前言

大家好呀，我是雪荷。在上篇文章（https://blog.hejiajun.icu/2024/07/26/EasyExcel-%E5%88%9D%E4%BD%BF%E7%94%A8%E2%80%94%E2%80%94-Java-%E5%AE%9E%E7%8E%B0%E5%A4%9A%E7%A7%8D%E5%86%99%E5%85%A5-Excel-%E5%8A%9F%E8%83%BD-CSDN%E5%8D%9A%E5%AE%A2/）中给大家介绍了 Java 是如何写入 Excel 的，那么这篇算是对上篇文章的拓展，主要介绍前端和后端分别是如何导出数据至 Excel 的。

前端导出 Excel

我就用之前比赛的项目给大家演示吧，其组件库为 Ant Design Vue，框架为 Vue3，使用的第三方库为 XLSX。整体的实现并不困难只需写两个函数即可，话不多说直接上代码。

安装命令

1
npm install XLSX

引入 XLSX

1
import * as XLSX from 'xlsx';

vue 的 template 部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<template>
  <a-table
      id="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 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// 将 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);
  // 创建 workbook
  const wb = XLSX.utils.book_new();
  // 将 工作表 添加到 workbook
  XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
  // 将 workbook 写入文件
  XLSX.writeFile(wb, '销售数据.xlsx');
};

ts 请求后端接口代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 层：

1
2
3
4
5
6
7
8
9
@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：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@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 中了。

image-20240729195452336

d0b3d3225e8f16c4acde85a471d64f6

优点：简单，便捷

缺点：

  1. 不灵活，不适合非固定表头和复杂的表头
  2. 前端导出不适合数据量大的场景，因为页面会卡死

后端导出 Excel

据我了解的后端实现导出 Excel 的功能主要有两种。一种是将 Excel 写入流中，前端拿到文件流再转为 blob 后进行下载，另一种是将 Excel 文件转为 base64 编码，前端将 base64 编码转为 blob 再下载。由此可见最终都是要转为 blob 的，拿到 blob 就好搞了。

后端将 Excel 写入流中

后端代码

Controller 层：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@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 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
const downloadExcel = () => {
  const response = myAxios.post('/sale/data/download', null, {
    responseType: 'blob',
  }).then((res) => { // 处理返回的文件流
    const content = res
    const blob = new Blob([content])
    console.log(content)
    const fileName = '销售数据.xlsx';
    if ('download' in document.createElement('a')) { // 非IE下载
      const elink = document.createElement('a')
      elink.download = fileName
      elink.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 下载”按钮导出数据。

219d096f29327fc8e8e9f4c17dabf0d

image-20240729200907785

优点：

  1. 会有跨域问题

缺点：

  1. 传输效率高，网络负载小
  2. 内存占用，适合传输大文件

后端返回 base 64 编码，前端再转为 blob

后端代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@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 代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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 = fileName
      elink.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 进行下载。

点击“导出数据”按钮导出数据。

image-20240729201558898

e3d3341edc8985fd103c87c9dd764cf

优点：

  1. 传输数据是 base64 字符串便于调试
  2. 跨域问题少

缺点：

  1. 内存占用大，适合传输小文件
  2. 传输效率低

全部代码

前端

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
<!-- eslint-disable vue/multi-word-component-names -->
<template>
  <a-table
      id="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);
  // 创建 workbook
  const wb = XLSX.utils.book_new();
  // 将 工作表 添加到 workbook
  XLSX.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 = res
    const blob = new Blob([content])
    console.log(content)
    const fileName = '销售数据.xlsx';
    if ('download' in document.createElement('a')) { // 非IE下载
      const elink = document.createElement('a')
      elink.download = fileName
      elink.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 = fileName
      elink.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 层：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
@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：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@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，可以根据具体的开发需求选择不同的方案，如果有更好的方案欢迎探讨哈。