SpringBoot整合EasyPoi 导入导出

引入maven依赖

    <properties>
        <easyui-poi.version>4.0.0</easyui-poi.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>${easyui-poi.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>${easyui-poi.version}</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>${easyui-poi.version}</version>
        </dependency>
    </dependencies>

工具类

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.chitic.module.poi.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Workbook;

public abstract class EasypoiUtil {
    public EasypoiUtil() {
    }

    public static void templateExport(TemplateExportParams templateExcel, Map<String, Object> dataMap, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(templateExcel, dataMap);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        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());
    }

    private static String filename(HttpServletRequest request, String filename) throws Exception {
        String userAgent = request.getHeader("User-Agent");
        if (!userAgent.contains("MSIE") && !userAgent.contains("Trident")) {
            filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
        } else {
            filename = URLEncoder.encode(filename, "UTF-8");
        }

        return filename;
    }
}

导入的实体类

package com.chitic.supplywater.common.api.request.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.chitic.supplywater.common.api.request.worksheet.WorkSheetSourceRequest;
import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.validation.constraints.DecimalMin;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * @Description 工单批量导入参数
 *   IExcelModel, IExcelDataModel 是为了实现返回校验失败的信息
 * @Author GX
 * @Date 2020/05/12 14:51
 * @Version V1.0
 **/
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
public class WorkSheetExcelRequest implements IExcelModel, IExcelDataModel {

    @Excel(name = "第几行")
    private int rowNum;
    @Excel(name = "错误信息")
    private String errorMsg;


    @Override
    public int getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }

    @Excel(name = "工单名称（必填）")
    @NotBlank(message = "工单名称不能为空")
    private String workSheetName;

    /**
     * 工单类型:   对应代码表
     */
    @Excel(name = "工单类型（必填）", type = 10)
    @NotNull(message = "工单类型不能为空")
    private Integer workSheetType;

    /**
     * 泵房id
     */
    private Long houseId;

    @Excel(name = "泵房名称")
    private String houseName;

    /**
     * 单元id
     */
    private Long unitId;

    @Excel(name = "单元编号")
    private String unitSn;

    @Excel(name = "工单内容（说明）")
    private String workSheetExplain;

    @Excel(name = "部位")
    private String position;

    @Excel(name = "计划开始时间（必填）")
    @NotNull(message = "计划开始时间不能为空")
    @Pattern(regexp = "^((?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])-(?:29|30)|(?:0[13578]|1[02])-31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)-02-29)$", message = "开始时间格式不正确")
    private String planStartDateString;
    private Long planStartDate;

    @Excel(name = "计划结束时间（必填）")
    @NotNull(message = "计划结束时间不能为空")
    @Pattern(regexp = "^((?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])-(?:29|30)|(?:0[13578]|1[02])-31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)-02-29)$", message = "结束时间格式不正确")
    private String planEndDateString;
    private Long planEndDate;

    @Excel(name = "计划小时", type = 10)
    @DecimalMin(value = "0", message = "计划小时不能小于0")
    private BigDecimal planHour;

    @Excel(name = "计划人数", type = 10)
    @DecimalMin(value = "1", message = "计划人数不能小于1")
    private Integer planUserNumber;

    @Excel(name = "计划费用: 单位（元）", type = 10)
    @DecimalMin(value = "0", message = "计划费用不能小于0")
    private BigDecimal planOutlay;

    @Excel(name = "备注", width = 15, replace = { "正常_0", "报警_1"})
    private String remarks;

}

导入的controller

@PostMapping(ModuleWaterConstant.MAPPING_PREFIX + "/workSheet/upload")
    public void importExcel(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws Exception {
        String fileName = file.getOriginalFilename();
        boolean fileIsExcel = Objects.requireNonNull(fileName).matches("^.+\\.(?i)(xls)$") || fileName.matches("^.+\\.(?i)(xlsx)$");
        if (!fileIsExcel) {
            throw ChiticSsoException.of(SsoResponseCode.RESOURCE_FILE_READ_TYPE_FAIL);
        }
        InputStream inputStream = file.getInputStream();
        ImportParams importParams = new ImportParams();
        // 需要验证
        importParams.setNeedVerify(true);
        ExcelImportResult<WorkSheetExcelRequest> requestList = ExcelImportUtil.importExcelMore(inputStream, WorkSheetExcelRequest.class, importParams);
        List<WorkSheetExcelRequest> list = requestList.getList();
        list = list.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(WorkSheetExcelRequest :: getWorkSheetName))), ArrayList::new));
        workSheetService.importExcel(list);

        //判断是否有错误
        if(requestList.isVerfiyFail()){
            //getFailList()里面的就是所有校验失败的excel数据
            List<WorkSheetExcelRequest> failList = requestList.getFailList();
            System.out.println(failList);
            ExportParams params = new ExportParams();
            params.setSheetName("工单批量导入错误数据");
            Workbook workbook = ExcelExportUtil.exportExcel(params, WorkSheetExcelRequest.class, failList);
            EasypoiUtil.downLoadExcel("workSheet_error.xls", response, workbook);
        }
    }

导出的controller

    //从项目根路径导出
    @PostMapping(ModuleWaterConstant.MAPPING_PREFIX + "/workSheet/download")
    public void exportExcel(HttpServletResponse response) throws IOException {
        ClassPathResource resource = new ClassPathResource("excel/工单.xlsx");
        Workbook workbook = new XSSFWorkbook(resource.getInputStream());
        try {
            EasypoiUtil.downLoadExcel("WorkOrderTemplate", response, workbook);
        } catch (ChiticException e) {
            throw e;
        } catch (Exception e) {
            throw ChiticException.of(SsoResponseCode.RESOURCE_FILE_EXPORT_FAIL.getCode(), SsoResponseCode.RESOURCE_FILE_EXPORT_FAIL.getMessage());
        }
    }

    //从数据库查询导出
    @PostMapping(ModuleWaterConstant.MAPPING_PREFIX + "/report/water/exportData")
    public void exportWaterData(@RequestBody @Valid HistoryDataExportRequest request, HttpServletResponse response) {
        List<DataWaterHistoryExport> listData = dataWaterHistoryService.exportWaterData(request);
        ExportParams params = new ExportParams();
        params.setSheetName("水质历史数据");
        Workbook workbook = ExcelExportUtil.exportExcel(params, DataWaterHistoryExport.class, listData);
        try {
            EasypoiUtil.downLoadExcel("water.xls", response, workbook);
        } catch (ChiticException e) {
            throw e;
        } catch (Exception e) {
            throw ChiticException.of(SsoResponseCode.RESOURCE_FILE_EXPORT_FAIL.getCode(), SsoResponseCode.RESOURCE_FILE_EXPORT_FAIL.getMessage());
        }
    }

 

 

模板方式导出

//模板路径  resources/model/model-inv.xls
String url = "model/model-inv.xls";
TemplateExportParams exportParams = new TemplateExportParams(url);
exportParams.setHeadingRows(6);
Map<String, Object> dataMap=new HashMap<String, Object>();
dataMap.put("pname",plantinfo.getPlantname());
dataMap.put("punit",request.getPlantunit());
dataMap.put("time", DateStringUtil.Date2String(new Date(),"yyyy-MM-dd HH:mm:ss"));
dataMap.put("list", list);
dataMap.put("sign", "历史");
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, dataMap);
EasypoiUtil.downLoadExcel("DEVICE_HISDATA" + System.currentTimeMillis() + ".xls", response, workbook);

 

 其中  {{$fe:}} 和 t. 默认写法