利用EasyExcel导出数据库数据
一、引入EasyExcel的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.1</version>
</dependency>
二、在需要在excel中呈现的字段添加注解
public class Gps{
@ExcelProperty("id")
private Integer id;
@Schema(description = "设备名称")
@ExcelProperty("设备名称")
private String device;
@Schema(description = "上传时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
@ExcelProperty("上传时间")
private Date clientTime;
// ......字段
}
三、核心代码
3.1.这边做的是按照起止时间和设备名做的附带筛选功能的导出
@Override
public void getGpsExcel(GpsQueryReq req, HttpServletResponse response) {
try{
// HttpServletResponse 必须要设置ContentType(相应内容)为Microsoft Excel文件。
response.setContentType("application/vnd.ms-excel");
// HttpServletResponse 设置响应的字符编码为UTF-8
response.setCharacterEncoding("utf-8");
String fileName = "";
// 用UTF-8编码对文件名"GPS-export"进行URL编码。这是为了确保文件名中不包含任何特殊字符或空格,以便在HTTP头中正确传输。
fileName = URLEncoder.encode("GPS-export", StandardCharsets.UTF_8) ;
// 这行代码在响应头中添加一个"Content-disposition"头,指定将该文件作为附件下载,并设置文件名为编码后的"GPS-export.xlsx"
response.addHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 这是设置条件查询,筛选结果放入Excel
LambdaQueryWrapper<Gps> wrapper = new LambdaQueryWrapper<Gps>()
.ge(req.getStartDate() != null, Gps::getClientTime, req.getStartDate())
.le(req.getEndDate() != null, Gps::getClientTime, req.getEndDate())
.eq(StringUtils.isNotEmpty(req.getDevice()), Gps::getDevice, req.getDevice());
long total = count(wrapper);
long currentPage = 1L;
long currentSize = 1000L;
// 将数据写入Excel,设置内容格式为Gps.class
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Gps.class).build();
// sheet名为message
WriteSheet sheet = EasyExcel.writerSheet("message").build();
while((currentPage - 1) * currentSize < total){
List<Gps> list = list(wrapper.last("limit " + (currentPage - 1) * currentSize + "," + 1000));
excelWriter.write(list, sheet);
currentPage ++;
}
// 必须要finish!
excelWriter.finish();
} catch (Exception e){
throw new ServiceException(new ErrorCode(201, "文件名错误"));
}
}
3.2.尝试将其作为工具,置于工具包中
/**
*
* @param response http响应头
* @param filename 文件名
* @param sheetName sheet名
* @param clazz 写入Excel的具体类
* @param wrapper 筛选条件
* @param service Service层,用于数据筛选
* @param size 一次批量导入数据数
* @param <T> 具体类
* @param <S> 具体Service
*/
public static <T, S extends IService<T>> void download(HttpServletResponse response, String filename, String sheetName,
Class<T> clazz, LambdaQueryWrapper<T> wrapper, S service , Long size) {
try{
// 设置响应头信息和URL编码的filename
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
filename = URLEncoder.encode(filename, "UTF-8");
response.addHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
// 计算筛选后的数据量
Long total = service.count(wrapper);
Long currentPage = 1L;
// 写入Excel
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).build();
WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
while((currentPage - 1) * size < total){
List<T> list = service.list(wrapper.last("limit " + (currentPage - 1) * size + "," + size));
excelWriter.write(list, sheet);
currentPage ++;
}
excelWriter.finish();
} catch (Exception e){
throw new ServiceException(new ErrorCode(201, "文件错误"));
}
}
四、巨大问题!
请使用JDK8运行,否则导出的Excel会有问题。
如果运行环境不是JDK8,在启动java -jar的时候添加如下代码
java --add-opens java.base/java.lang=ALL-UNNAMED -jar your-application.jar