easypoi导入工具类

    cn.afterturn
    easypoi-base
    4.0.0

package diit.platform.bussiness.utils;

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.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelimportResult;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

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

//Excel导入导出工具类
public class ExcelUtils {
    public static void exportExcel(List list, String title, String sheetName, Class pojoClass, String fileName,
                                   boolean isCreateHeader, HttpServletResponse response) {
        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) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List list, Class pojoClass, String fileName, HttpServletResponse response,
                                      ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    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 NormalException(e.getMessage());
        }
    }

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

    public static  List importExcel(String filePath, Integer titleRows, Integer headerRows, Class pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        importParams params = new importParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List list = null;
        try {
            list = ExcelimportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
        }
        return list;
    }

    
    public static  List importExcel(MultipartFile file, Integer titleRows, Integer headerRows,Class pojoClass) {
        if (file == null) {
            return null;
        }
        importParams params = new importParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedVerfiy(true);//是否开启校验
        List list = null;
        try {
            list = ExcelimportUtil.importExcel(file.getInputStream(), pojoClass, params);
//            ExcelimportResult objectExcelimportResult = ExcelimportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
//            objectExcelimportResult.getFailList();
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }

    
    public static  ExcelimportResult importExcelAndCheck(MultipartFile file, Integer titleRows, Integer headerRows,Class pojoClass) {
        if (file == null) {
            return null;
        }
        importParams params = new importParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedVerfiy(true);//是否开启校验
        ExcelimportResult result = null;
        try {
            result = ExcelimportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
//             throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return result;
    }

}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MineralBatchExcel implements IExcelModel, IExcelDataModel {
	
	@Excel(name = "分类编号",fixedIndex = 0)
    @NotNull(message = "分类编号不能为空")
    private String flbh;
    
    @Excel(name = "矿产地编号",fixedIndex = 1)
    @Pattern(regexp = "\d{6}",message = "编码是数字6位数")
    private String kcdbh;
    
    //......................
}

public Resut importExcel(MultipartFile file){
 		//titleRows 标题有几行，headerRows 头部有几行
        ExcelimportResult result = ExcelUtils.importExcelAndCheck(file, 0, 5, MineralBatchExcel.class);
        //查看是否有错误行
        if(result.isVerfiyFail()){
            String errorMsg = "";
            //拼接整理错误信息，目前简单处理拼接成一批文字提示，后续可以不操作，直接返回前端做表格展示
            for(MineralBatchExcel excel : result.getFailList()){
                errorMsg += "第"+excel.getRowNum()+"行有错误，" + excel.getErrorMsg() + "；
";
            }
            return Resut(ResponeCode.FAIL,errorMsg);
        }
        //需要保存的数据list
        List saveList = result.getList();
 }