基于SpringBoot的后端导出Excel文件

后端导出Excel，前端下载。

系列文章指路👉

系列文章-基于SpringBoot3创建项目并配置常用的工具和一些常用的类

后端导出Excel

引入依赖

poi 操作xls，doc…；poi-ooxml操作xlsx，docx…
⚠️使用的版本比较新，可能跟老版本有些写法不兼容

        <!-- poi and poi-ooxml --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency>

写入响应

• 生成Workbook对象这一步应该是个性化的。
• 中文的文件名需要经过编码，不然传到前端会乱码
• 工具类的源码放在文末

    public Object export(String orderNum) {// 1. 生成Excel Workbook对象XSSFWorkbook workbook = initWorkbook(orderNum);HttpServletResponse rep = ServletUtils.getResponse();String errMessage;String fileName = "超市购进单-"+ DateUtil.today() + ".xlsx";if(Objects.isNull(rep)){throw new NullPointerException("HttpServletResponse 为空");}try {// 2. 将HSSFWorkbook文件写入到响应输出流中，供前端下载FileUtils.writeToResponse(workbook, fileName, rep);return null;}catch (IOException ioe){log.error("OrderServiceImpl export --- 导出过程中遇到输入输出异常： {}" ,ioe.toString());errMessage = "导出过程中遇到输入输出异常" + ioe;} catch (Exception e){log.error("OrderServiceImpl export --- 导出过程中遇到其他异常： {}" ,e.toString());errMessage = "导出过程中遇到其他异常：" + e;}return BaseResult.fail(errMessage);}

前端下载

后端导出失败和成功返回的内容类型不同，因此需要分别判断。

• 返回的是json类型的错误信息:
  
• 只有导出成功，才是文件流：

<template><h1>Excel导出测试</h1><p style="margin-top: 40px"><a-space><a-button type="primary" :icon="h(DownloadOutlined)" @click="downloadFile">下载Excel</a-button></a-space></p>
</template>

<script setup>
import {h} from 'vue';
import {DownloadOutlined} from '@ant-design/icons-vue';
import {UploadOutlined} from '@ant-design/icons-vue';
import {message} from "ant-design-vue";
import http from "@/utils/axios/index.js";
import {downloadFile as downer} from "@/utils/file.js";function downloadFile() {http.get('/manage/order/export', {params: {orderNum: '000001'},responseType: 'blob'}).then(resp => {if (resp.data.type === 'application/json') {// 失败了才会返回json类型const reader = new FileReader();reader.readAsText(resp.data, 'utf-8');reader.onload = () => {const result = JSON.parse(reader.result)message.error(`Error: ${result.message}！`);};} else {downer(resp)}}).catch(err => {message.error('导出失败：' + err)console.log(err)})
}
</script>

工具类

ServletUtils.java

package com.ya.boottest.utils.servlet;import com.alibaba.fastjson.JSON;
import com.ya.boottest.utils.result.BaseResult;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.servlet.http.HttpSession;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.MediaType;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;import java.io.IOException;
import java.util.Objects;/*** <p>* Servlet 工具类* </p>** @author Ya Shi* @since 2024/1/4 14:29*/@Slf4j
public class ServletUtils {/*** 获取Attributes** @return ServletRequestAttributes*/public static ServletRequestAttributes getRequestAttributes() {RequestAttributes attributes = RequestContextHolder.getRequestAttributes();if(Objects.isNull(attributes)){log.error("ServletUtils 获取到的RequestAttributes为空");throw new RuntimeException("ServletUtils 获取到的RequestAttributes为空");}return (ServletRequestAttributes) attributes;}/*** 获取request** @return HttpServletRequest*/public static HttpServletRequest getRequest() {return getRequestAttributes().getRequest();}/*** 获取session** @return HttpSession*/public static HttpSession getSession() {return getRequest().getSession();}/*** 获取response** @return HttpServletResponse*/public static HttpServletResponse getResponse() {return getRequestAttributes().getResponse();}   
}

FileUtils.java

package com.ya.boottest.utils.file;import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;/*** <p>*  文件util* </p>** @author Ya Shi* @since 2023/8/11 11:58*/
@Slf4j
public class FileUtils {/*** 将HSSFWorkbook文件写入到响应输出流中，供前端下载* @param workbook 文件对象* @param fileName 文件名* @param response HttpServletResponse响应* @throws IOException IO异常*/public static void writeToResponse(XSSFWorkbook workbook, String fileName, HttpServletResponse response) throws IOException{try {response.setHeader("Content-Disposition", "attachment;filename=" + processFileName(fileName));response.setContentType("application/octet-stream; charset=utf-8");response.setCharacterEncoding("utf-8");ByteArrayOutputStream bos = new ByteArrayOutputStream();workbook.write(bos);byte[] bytes = bos.toByteArray();OutputStream outData = response.getOutputStream();outData.write(bytes);outData.flush();} catch (IOException e) {log.error("FileUtil writeToResponse workbook写入响应失败-----> " + e);throw e;}}/*** 对要下载的文件的名称进行编码，防止中文乱码问题。** @param fileName 文件名* @return String*/public static String processFileName(String fileName) throws IOException {String codedFilename;String prefix = fileName.lastIndexOf(".") != -1 ? fileName.substring(0, fileName.lastIndexOf(".")) : fileName;String extension = fileName.lastIndexOf(".") != -1 ? fileName.substring(fileName.lastIndexOf(".")) : "";String name = java.net.URLEncoder.encode(prefix, StandardCharsets.UTF_8);if (name.lastIndexOf("%0A") != -1) {name = name.substring(0, name.length() - 3);}int limit = 150 - extension.length();if (name.length() > limit) {name = java.net.URLEncoder.encode(prefix.substring(0, Math.min(prefix.length(), limit / 9)), StandardCharsets.UTF_8);if (name.lastIndexOf("%0A") != -1) {name = name.substring(0, name.length() - 3);}}name = name.replaceAll("[+]", "%20");codedFilename = name + extension;log.info("FileUtil processFileName codedFilename-----> " + codedFilename);return codedFilename;}
}

file.js

export function downloadFile(resp) {const tmp = 'filename='const contentDisposition = decodeURIComponent(resp.headers['content-disposition'])const fileName = contentDisposition.substring(contentDisposition.indexOf(tmp) + tmp.length)const contentType = resp.headers['content-type']const blob = new Blob([resp.data], {type: contentType})let a = document.createElement('a')a.href = URL.createObjectURL(blob)a.download = fileNamea.target = '_blank'a.style.display = 'none'document.body.appendChild(a)a.click()a.remove()
}

---

明月别枝惊鹊，清风半夜鸣蝉。

—— 宋朝 · 辛弃疾《 西江月 夜行黄沙道中 》