基于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 = fileName
    a.target = '_blank'
    a.style.display = 'none'
    document.body.appendChild(a)
    a.click()
    a.remove()
}

---

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

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