使用EasyPoi导入数据并返回失败xls

添加依赖

<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-base -->
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
	<version>4.4.0</version>
</dependency>

工具类

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * @author PIGS
 * @version 1.0
 * @date 2020/4/25 14:16
 * @effect :
 * 表格数据工具类
 */
public final class EasyPoiUtils {

    private EasyPoiUtils() {
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        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 RuntimeException(e);
        }
    }

    private static <T> void defaultExport(List<T> dataList, Class<?> clz, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clz, dataList);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }

    public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(dataList, clz, fileName, response, exportParams);
    }

    public static <T> void exportExcel(List<T> dataList, String title, String sheetName, Class<?> clz, String fileName, HttpServletResponse response) {
        defaultExport(dataList, clz, fileName, response, new ExportParams(title, sheetName));
    }

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

    public static void exportExcel(List<Map<String, Object>> dataList, String fileName, HttpServletResponse response) {
        defaultExport(dataList, fileName, response);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clz) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);

        try {
            return ExcelImportUtil.importExcel(new File(filePath), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clz) {
        if (file == null) {
            return null;
        }

        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);

        try {
            return ExcelImportUtil.importExcel(file.getInputStream(), clz, params);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入数据
     * userEnity 你自己新建的实体类 实体类代码在下面
     * @param file
     * @param clz
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> clazz) {
        if (file == null) {
            return null;
        }
        try(InputStream inputStream = file.getInputStream()){
            return ExcelImportUtil.importExcel(inputStream, clazz, new ImportParams());
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
}

代码

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;


/**
 * <p>
 *  前端控制器
 * </p>
 *
 */
@RestController
@RequestMapping("/api/base")
@Slf4j
@Api(tags = "基础接口")
public class BaseController {

    @Resource
    private IProductService service;

    @PostMapping("/importData")
    @ApiOperation("导入数据返回失败xls")
    public void importData(@RequestParam("file") MultipartFile file, HttpServletResponse response) {
        long l = System.currentTimeMillis();
        List<SalesDataEntity> checkingIns = EasyPoiUtils.importExcel(file, SalesDataEntity.class);
        List<SalesDataEntity> failedList = new ArrayList<>();
        for (SalesDataEntity salesDataEntity : checkingIns) {
            if (ObjUtil.isEmpty(salesDataEntity.getProducer())) {
                log.error("信息不可为空");
                failedList.add(salesDataEntity);
                continue;
            }
            try {
                // xxxx  业务逻辑处理
                service.insert(xxx);
            }catch (Exception e){
                e.printStackTrace();
                failedList.add(salesDataEntity);
            }
        }
        log.info(String.valueOf(System.currentTimeMillis() - l));
        EasyPoiUtils.exportExcel(failedList,null,"导入失败数据",SalesDataEntity.class,"失败数据",response);
    }

}

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @author PIGS
 * @version 1.0
 * @date 2020/4/25 14:16
 * @effect :
 * 用户实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SalesDataEntity {

    @Excel(name = "序号", width = 5)
    private String rowNo;

    @Excel(name = "生产厂家", width = 36)
    private String producer;

    @Excel(name = "联系电话", width = 19)
    private String contactTel;

    @Excel(name = "产品型号", width = 38)
    private String model;

    @Excel(name = "流向地区", width = 25)
    private String targetArea;

    @Excel(name = "用户单位", width = 123)
    private String userCompany;

    @Excel(name = "用户单位地址", width = 91)
    private String userCompanyAddr;

    @Excel(name = "销售时间", width = 11)
    private String saleDate;

    @Excel(name = "销售数量", width = 9)
    private String saleCount;

}