利用EasyExcel导出数据库数据

最新推荐文章于 2025-03-29 19:30:00 发布

原创最新推荐文章于 2025-03-29 19:30:00 发布 · 362 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#java #excel #mysql

利用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, "文件错误"));
        }
    }