后端导出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()
}
明月别枝惊鹊，清风半夜鸣蝉。
—— 宋朝 · 辛弃疾《 西江月 夜行黄沙道中 》
