Springboot使用EasyPoi进行Excel大数据导出

maven

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.2</version>
        </dependency>

controller

    /**
     * 导出excel
     * @param request
     */
    @GetMapping("/export")
    public void export(HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> requestQueryParams = CommonUtil.requestQueryParams(request);
        areaStayService.export(requestQueryParams,response);
    }

service

@Service
public class ExcelExportService implements IExcelExportServer {
    @Autowired
    private VisitorAreaStayService visitorAreaStayService;

    @Override
    public List<Object> selectListForExcelExport(Object o, int page) {
        return visitorAreaStayService.selectListForExcelExport(JSONObject.parseObject(JSONObject.toJSONString(o)),page);
    }
}

public interface VisitorAreaStayService {

    void export(Map<String, Object> requestQueryParams, HttpServletResponse response);
    
    List<Object> selectListForExcelExport(Map<String, Object> queryParams, int page);
}

@Slf4j
@Service
public class VisitorAreaStayServiceImpl implements VisitorAreaStayService{
    @Autowired
    private VisitorAreaStayMapper mapper;
    @Autowired
    private ExcelExportService excelExportServer;
    
    @Override
    public void export(Map<String, Object> requestQueryParams, HttpServletResponse response) {
        try {
            Date start = new Date();
            ExportParams params = new ExportParams("大数据测试", "测试");
            Workbook workbook = ExcelExportUtil.exportBigExcel(params, VisitorAreaStay.class, excelExportServer, requestQueryParams);
            log.debug("excel导出耗时：{} 秒", new Date().getTime() - start.getTime());
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode("区域停留记录.xls", "UTF-8"));
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
            //下载至指定路径
//        File savefile = new File("D:/excel/");
//        if (!savefile.exists()) {
//            savefile.mkdirs();
//        }
//        FileOutputStream fos = new FileOutputStream("D:/excel/data.xlsx");
//        workbook.write(fos);
//        fos.close();
        } catch (IOException e) {
            e.printStackTrace();
            log.error("excel导出失败：", e);
        }
    }

    @Override
    public List<Object> selectListForExcelExport(Map<String, Object> queryParams, int page) {
        int end = 20;
        queryParams.put("start", (page - 1) * end);
        queryParams.put("end", end);
        return mapper.selectListForExcelExport(queryParams);
    }
}

dao

    List<Object> selectListForExcelExport(Map<String, Object> queryParams);

mapper.xml

    <select id="selectListForExcelExport" parameterType="hashMap" resultMap="EntityResult">
        <include refid="selectEntityVo"></include>
        <where>
            <if test="areaId!=null">AND area_id like concat('%', #{areaId}, '%')</if>
            <if test="areaName!=null and areaName!=''">AND area_name like concat('%', #{areaName}, '%')</if>
            <if test="visitorName!=null and visitorName!=''">AND visitor_name like concat('%', #{visitorName}, '%')</if>
            <if test="visitorId!=null and visitorId!=''">AND visitor_id like concat('%', #{visitorId}, '%')</if>
        </where>
        ORDER BY create_time DESC LIMIT #{start},#{end};
    </select>