springboot easypoi excel导出功能

pom文件中引入如下包

  <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>
        </dependencies>

添加以下工具类

package com.tendyron.acs.management.gate.access.excel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;

import com.tendyron.acs.management.gate.access.dao.AccessEntity;
import io.micrometer.core.instrument.util.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author lnxu
 * @date 2021/9/7 17:46
 */
public class ExcelUtil {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws Exception {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }


    public static void exportExcel(List<?> list, ExportParams exportParams, Class<?> pojoClass,String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, fileName, response);
    }

    public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) throws Exception {
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);

        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception{
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new Exception(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }
    }

//    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass)throws Exception {
//        if (StringUtils.isBlank(filePath)) {
//            return null;
//        }
//        ImportParams params = new ImportParams();
//        params.setTitleRows(titleRows);
//        params.setHeadRows(headerRows);
//        List<T> list = null;
//        try {
//            list = ExcelImportUtil.importExcel(new com.sun.java.util.jar.pack.Package.File(filePath), pojoClass, params);
//        } catch (NoSuchElementException e) {
//            throw new Exception("模板不能为空");
//        } catch (Exception e) {
//            e.printStackTrace();
//            throw new Exception(e.getMessage());
//        }
//        return list;
//    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new Exception("excel文件不能为空");
        } catch (Exception e) {
            throw new Exception(e);
        }

        return list;
    }

    public static void handleExcel(HttpServletResponse response, String fileName, TemplateExportParams params, Map<String, Object> map) {
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);


        if (workbook != null) {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                workbook.write(response.getOutputStream());
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
//
//    public static ArrayList<ExcelExportEntity> getExcelColumnList(List<AccessEntity> detailList) {
//        ArrayList<ExcelExportEntity> columnList = new ArrayList<>();
//        if (detailList != null && detailList.size() > 0) {
//            for (AccessEntity firstTempDetail : detailList) {
//                //处理自定义表头
//                ExcelExportEntity excelExportEntity = new ExcelExportEntity(firstTempDetail.getName(), firstTempDetail.getName());
//                List<AccessEntity> items = (List<AccessEntity>) firstTempDetail.getBirthday();
//                if (items != null && items.size() > 0) {
//                    List<ExcelExportEntity> excelExportList = new ArrayList<>();
//                    for (Person item : items) {
//                        excelExportList.add(new ExcelExportEntity(item.getName(), item.getName()));
//                    }
//                    excelExportEntity.setList(excelExportList);
//                }
//                columnList.add(excelExportEntity);
//            }
//        }
//        return columnList;
//    }


}

导出excel实体代码如下，实体中添加了一**解，

@ExcelTarget("accessEntity")

字段上的注解

@Excel(name = "id", width = 20,orderNum = "0") name是表头的名字。

package com.tendyron.acs.management.gate.access.rest.param;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.hibernate.annotations.GenericGenerator;

import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.io.Serializable;
import java.util.Date;

/**
 * @author lnxu
 * @date 2021/9/7 19:13
 */
@Data
@ExcelTarget("access")
public class AccessExcelParam implements Serializable {

//    @Excel(name = "id", width = 20,orderNum = "0")
//    private String id ;

    @Excel(name = "姓名", width = 20,orderNum = "0")
    private String name ;

    @Excel(name = "门名称", width = 20,orderNum = "1")
    private String doorName ;

    @Excel(name = "出入方向", width = 20,orderNum = "2")
    private String accessDirection ;

    @Excel(name = "开门因子", width = 20,orderNum = "3")
    private String accessFactor ;

    @Excel(name = "开门结果", width = 20,orderNum = "4")
    private String accessResult ;

    /**人员出入时间，降序排列**/
    @Excel(name = "出入时间",width = 25,exportFormat = "yyyy-MM-dd HH:mm:ss", importFormat = "yyyy-MM-dd HH:mm:ss",orderNum = "5")
    private Date accessTime ;

    @Column(name = "gm_sign")
    @Excel(name = "国密签名", width = 30,orderNum = "6")
    private String gmSign;
}

导出代码

 @ApiOperation(value = "出入记录导出excel",notes="")
    @PostMapping(value = "/exportExcel",consumes = "application/x-www-form-urlencoded")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response)
    {
//        log.info("分页查询出入记录，分页参数:{}", JSON.toJSONString();
        AccessPageQueryParam accessPageQueryParam = new AccessPageQueryParam();
        accessPageQueryParam.setName(request.getParameter("staffName"));
        accessPageQueryParam.setCredentialNo(request.getParameter("credentialNo"));
        accessPageQueryParam.setDoorName(request.getParameter("doorName"));
        accessPageQueryParam.setStartTime(request.getParameter("ctrlTimeBegin"));
        accessPageQueryParam.setEndTime(request.getParameter("ctrlTimeEnd"));
        accessPageQueryParam.setSize(50000);


        Page<AccessEntity> accessEntityList = accessService.AccessExcelList(accessPageQueryParam) ;


        long accessEntitySize = accessEntityList.getTotalElements();
        if(accessEntitySize>50000){
            log.error("下载数据量为"+accessEntityList.getSize()+"条，内容过大，请输入检索条件，缩小范围。");
        }

//        for (int i = 0;i<accessEntityList.getTotalPages();i++)
//        {
            List<AccessExcelParam> accessExcelParams = new ArrayList<>();
            accessEntityList.getContent().stream().forEach(accessEntity -> {
                AccessExcelParam accessExcelParam = new AccessExcelParam();
                BeanUtils.copyProperties(accessEntity,accessExcelParam);
                accessExcelParams.add(accessExcelParam);

            });

            try {

                //导出操作
                ExcelUtil.exportExcel(accessExcelParams,null,"出入记录",AccessExcelParam.class,"出入记录.xls",response);

                log.info("请求 exportExcel end ......");
            } catch (Exception e) {
                log.info("请求 exportExcel 异常：{}", e.getMessage());
                throw new AcsRuntimeException("下载数据量为"+accessEntityList.getSize()+"条，内容过大，请输入检索条件，缩小范围。");
            }


        log.info("成功下载"+accessEntityList.getTotalElements()+"条数据。");

    }