平日项目里，常常会遇到关于导入导出的功能，同时导入功能一般要制作一个Excel模板，用来提供给用户进行导入。模板里有些字段是只能填入字典值，所以要提供一个字典值的展示来规范用户的填入的信息。本篇内容就是制作一个多sheet页的Excel导入模板(一个用来给导入，一个用来规范填入值)。

图一：第一个sheet页，也就是用来给用户进行导入的表格，其中执行机构名称字段是要与字典值一一对应。

图二：第二个sheet页用来展示字典值

实现技术是esaypoi，代码逻辑很简单，没什么好说的，看一下代码块就明白了，主要用到的是ExcelExportUtil的exportExcel方法。

@Override
    @SneakyThrows
    public void putDownloadTemplate(HttpServletResponse response) {
            List<PutPersonneLDeliveExcelImportVO> list = new ArrayList<>();
            List<SysOrgExcelVo> orgList = new ArrayList<>();
            // 创建参数对象（用来设定excel得sheet得内容等信息）
            ExportParams deptExportParams = new ExportParams();
            // 设置sheet得名称
            deptExportParams.setSheetName("犯罪人员");
            deptExportParams.setStyle(ExcelStyleUtil.class);
            deptExportParams.setTitle("犯罪人员清单");
            deptExportParams.setCreateHeadRows(true);
            // 创建sheet1使用得map
            Map<String, Object> deptExportMap = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            deptExportMap.put("title", deptExportParams);
            // 模版导出对应得实体类型
            deptExportMap.put("entity", PutPersonneLDeliveExcelImportVO.class);
            // sheet中要填充得数据
            deptExportMap.put("data", list);
            ExportParams empExportParams = new ExportParams();
            empExportParams.setSheetName("字段说明");
            empExportParams.setStyle(ExcelStyleUtil.class);
            empExportParams.setTitle("信息填写时，以上字段务必填写规范值，请按照提示规范填写，否则可能会导致导入失败");
            empExportParams.setCreateHeadRows(true);
            // 创建sheet2使用得map
            Map<String, Object> empExportMap = new HashMap<>();
            empExportMap.put("title", empExportParams);
            empExportMap.put("entity", SysOrgExcelVo.class);
            empExportMap.put("data", orgList);
            // 将sheet1、sheet2、sheet3使用得map进行包装
            List<Map<String, Object>> sheetsList = new ArrayList<>();
            sheetsList.add(deptExportMap);
            sheetsList.add(empExportMap);
        ExcelUtils.downloadTemplate(response,sheetsList,"yrjDownloadTemplate", ExcelUtils.ExcelTypeEnum.XLSX);
  
简单说一下ExportParams 指的是导出参数对象，用来给Excel设置sheet名称和样式之类的。
 
  
实体类： 犯罪人员
 
   
@Data
@Accessors(chain = true)
public class PutPersonneLDeliveExcelImportVO extends BaseExcel {
    private static final long serialVersionUID = 1L;
    @Excel(name = "被告人名称", orderNum = "1")
    @NotEmpty(groups = {ValidateGroup.AddGroup.class, ValidateGroup.UpdateGroup.class}, message = "不能为空")
    @ApiModelProperty(value = "被告人名称")
    private String personnelName;
    @Excel(name = "案号", orderNum = "2",width = 30)
    @NotEmpty(groups = {ValidateGroup.AddGroup.class, ValidateGroup.UpdateGroup.class}, message = "不能为空")
    @ApiModelProperty(value = "案号(第三方案号)")
    private String caseNumber;
    @Excel(name = "案由", orderNum = "3",width = 30)
    @NotEmpty(groups = {ValidateGroup.AddGroup.class, ValidateGroup.UpdateGroup.class}, message = "不能为空")
    @ApiModelProperty(value = "案由(第三方案由)")
    private String caseReason;
//    @Excel(name = "(入矫|交付)执行机构", orderNum = "4",width = 30)
//    @NotEmpty(groups = {ValidateGroup.AddGroup.class, ValidateGroup.UpdateGroup.class}, message = "不能为空")
//    @ApiModelProperty(value = "(入矫|交付)执行机构")
//    private String thirdOrgName;
    @Excel(name = "(入矫|交付)执行机构代码", orderNum = "4",width = 30)
    @NotNull(groups = {ValidateGroup.AddGroup.class, ValidateGroup.UpdateGroup.class}, message = "不能为空")
    @ApiModelProperty(value = "执行组织id")
    private Long implementOrgId;
    @Excel(name = "(入矫|收监)时间", orderNum = "5",width = 40,format = "yyyy-MM-dd")
    @NotNull(groups = {ValidateGroup.AddGroup.class, ValidateGroup.UpdateGroup.class}, message = "不能为空")
    @ApiModelProperty(value = "(入矫|收监)时间")
    private Date deliverTime;
   
@Data
public class SysOrgExcelVo {
    private static final long serialVersionUID = 1L;
    @Excel(name = "执行机构代码",orderNum = "1")
    @ApiModelProperty(value = "数据库唯一ID")
    private Long id;
    @Excel(name = "执行机构名称",orderNum = "2")
    @ApiModelProperty(value = "三方组织名称")
    private String thirdOrgName;
    @ApiModelProperty(value = "组织形式：1法院；2检察院；3公安机关；4司法局；5看守所；6医院")
    private String orgForm;
  
两个工具类：Excel样式工具类、Excel工具类
 
   
package com.zyjc.pszg.component.easypoi.util;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
 * @Author: helingyun
 * @Date: 2022/9/01 11:20
 * @Description: excel自定义样式
public class ExcelStyleUtil implements IExcelExportStyler {
    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 9;
    private static final short FONT_SIZE_ELEVEN = 10;
    private static final short FONT_SIZE_TWELVE = 10;
     * 大标题样式
    private CellStyle headerStyle;
     * 每列标题样式
    private CellStyle titleStyle;
     * 数据行样式
    private CellStyle styles;
    public ExcelStyleUtil(Workbook workbook) {
        this.init(workbook);
     * 初始化样式
     * @param workbook
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
        this.styles = initStyles(workbook);
     * 大标题样式
     * @param color
     * @return
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
     * 每列标题样式
     * @param color
     * @return
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
     * 数据行样式
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
     * 获取样式方法
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
     * 模板使用的样式设置
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
     * 初始化--大标题样式
     * @param workbook
     * @return
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
     * 初始化--每列标题样式
     * @param workbook
     * @return
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        //背景色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
     * 初始化--数据行样式
     * @param workbook
     * @return
    private CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
        style.setDataFormat(STRING_FORMAT);
        return style;
     * 基础样式
     * @return
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
     * 字体样式
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
   
package com.zyjc.pszg.component.easypoi.util;
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 cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.zyjc.pszg.component.easypoi.handler.EasyPoiExcelVerifyHandler;
import com.zyjc.pszg.model.vo.BaseExcel;
import com.zyjc.pszg.model.vo.ExcelErrorVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.locks.ReentrantLock;
import java.util.stream.Collectors;
 * Excel 工具类
 * @author zhangshichang
 * @date 19-2-25 下午2:25
@Slf4j
public class ExcelUtils {
     * 创建excel文件模板时加锁
     * 多线程需保证使用同一把锁
    private static final ReentrantLock LOCK_CREATE_EXCEL_TEMPLATE = new ReentrantLock();
     * excel 导出
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response     响应体
     * @param exportParams 导出参数
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, String title, String sheetName,Boolean isCreateHeader,  HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelStyleUtil.class);
        exportParams.setCreateHeadRows(isCreateHeader);
        exportExcel(list, pojoClass, System.currentTimeMillis()+"",exportParams,response);
     * excel 导出
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response     响应体
     * @param exportParams 导出参数
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
     * excel 导出
     * @param list      数据
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response  响应体
     * @param title     表格标题
     * @param sheetName sheet名称
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, String title, String sheetName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
     * 下载excel模板，默认数据为空
     * 操作的sheet默认为第一个
     * @param fileName      文件名，不含后缀。建议英文
     * @param excelTypeEnum 文件类型 xls或xlsx
     * @param exportParams  导出参数
     * @param response      httpResponse
     * @throws IOException IO异常
    public static void downloadTemplate(HttpServletResponse response,List<Map<String, Object>> sheetsList, String fileName, ExcelTypeEnum excelTypeEnum) throws IOException {
        downloadTemplateWithRegionComboBox(response, sheetsList, System.currentTimeMillis()+"",excelTypeEnum,false);
    public static void downloadTemplateWithRegionComboBox(HttpServletResponse response, List<Map<String, Object>> sheetsList, String fileName, ExcelTypeEnum excelTypeEnum,  boolean needRecreate) throws IOException {
                Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
                workbook.write(response.getOutputStream());
                downLoadExcel(fileName, response, workbook);
     * 下载excel模板，默认数据为空
     * 操作的sheet默认为第一个
     * @param pojoClass     模板类
     * @param fileName      文件名，不含后缀。建议英文
     * @param excelTypeEnum 文件类型 xls或xlsx
     * @param exportParams  导出参数
     * @param response      httpResponse
     * @throws IOException IO异常
    public static void downloadTemplate(HttpServletResponse response, Class<?> pojoClass, String fileName, ExcelTypeEnum excelTypeEnum, ExportParams exportParams) throws IOException {
        downloadTemplateWithRegionComboBox(response, pojoClass, fileName, excelTypeEnum, exportParams, null, null);
     * 下载excel模板，默认数据为空
     * 操作的sheet默认为第一个
     * @param response           httpResponse
     * @param pojoClass          模板类
     * @param fileName           文件名，不含后缀。建议英文
     * @param excelTypeEnum      文件类型 xls或xlsx
     * @param exportParams       导出参数
     * @param commonComboBoxList 普通下拉框
     * @throws IOException IO异常
    public static void downloadTemplateWithComboBox(HttpServletResponse response, Class<?> pojoClass, String fileName, ExcelTypeEnum excelTypeEnum,
                                                    ExportParams exportParams, List<CommonComboBox> commonComboBoxList) throws IOException {
        downloadTemplateWithRegionComboBox(response, pojoClass, fileName, excelTypeEnum, exportParams, null, commonComboBoxList);
     * 下载excel模板，默认数据为空
     * 操作的sheet默认为第一个
     * @param response           httpResponse
     * @param pojoClass          模板类
     * @param fileName           文件名，不含后缀。建议英文
     * @param excelTypeEnum      文件类型 xls或xlsx
     * @param exportParams       导出参数
     * @param regionComboBox     行政区域组合下拉框
     * @param commonComboBoxList 普通下拉框
     * @throws IOException IO异常
    public static void downloadTemplateWithRegionComboBox(HttpServletResponse response, Class<?> pojoClass, String fileName, ExcelTypeEnum excelTypeEnum, ExportParams exportParams,
                                                          RegionComboBox regionComboBox, List<CommonComboBox> commonComboBoxList) throws IOException {
        downloadTemplateWithRegionComboBox(response, pojoClass, fileName,
                excelTypeEnum, exportParams, regionComboBox, commonComboBoxList, false);
    public static void downloadTemplateWithRegionComboBox(HttpServletResponse response, Class<?> pojoClass, String fileName, ExcelTypeEnum excelTypeEnum, ExportParams exportParams,
                                                          RegionComboBox regionComboBox, List<CommonComboBox> commonComboBoxList, boolean needRecreate) throws IOException {
        final String dirProperty = System.getProperty("user.dir");
        String filePath = dirProperty + File.separator + "pszg/excel" + File.separator + fileName + "." + excelTypeEnum.getValue();
        // 检索根目录是否存在文件
        File excelFile = new File(filePath);
        if (!needRecreate && excelFile.exists()) {
            log.info("excel模板已存在且无需重生成，直接写出返回。路径：" + filePath);
            writeOutFile(excelFile, fileName, excelTypeEnum, response);
        } else {
            LOCK_CREATE_EXCEL_TEMPLATE.lock();
            try {
                excelFile = new File(filePath);
                // 获取锁，再判断文件是否存在，即是否已有其他线程已生成excel
                if (!needRecreate && excelFile.exists()) {
                    writeOutFile(excelFile, fileName, excelTypeEnum, response);
                    log.info("进入加锁代码块，excel模板已存在且无需重生成，直接写出返回。");
                    return;
                // 创建目录
                excelFile.getParentFile().mkdirs();
                if (ExcelTypeEnum.XLS.equals(excelTypeEnum)) {
                    exportParams.setType(ExcelType.HSSF);
                } else {
                    exportParams.setType(ExcelType.XSSF);
                Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, Collections.emptyList());
                // 是否生成省市区街道村社联动下拉框
                /*if (regionComboBox != null && regionComboBox.rowBeginIndex >= 0 && regionComboBox.rowEndIndex > regionComboBox.rowBeginIndex
                        && ExcelSelectListUtil.checkDropDownCol(regionComboBox.getColNum(), regionComboBox.regionColIndex)) {
                    ExcelSelectListUtil.createRegionComboBox(workbook, 0, regionComboBox.rowBeginIndex, regionComboBox.rowEndIndex, regionComboBox.getColNum(), regionComboBox.regionColIndex);
                // 是否生成普通下拉框
                /*if (CollectionUtils.isNotEmpty(commonComboBoxList)) {
                    for (CommonComboBox commonComboBox : commonComboBoxList) {
                        ExcelSelectListUtil.setComboBoxList(workbook, 0,
                                commonComboBox.getFirstRow(), commonComboBox.getLastRow(), commonComboBox.getFirstCol(), commonComboBox.getLastCol(), commonComboBox.getOptions());
                File file = new File(filePath);
                if (!file.getParentFile().exists()) {
                    file.getParentFile().mkdir();
                if (!file.exists()) {
                    file.createNewFile();
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                workbook.write(fileOutputStream);
                downLoadExcel(fileName, response, workbook);
                log.info("重新生成excel模板成功。路径：" + filePath);
            } finally {
                LOCK_CREATE_EXCEL_TEMPLATE.unlock();
    private static void writeOutFile(File file, String fileName, ExcelTypeEnum excelTypeEnum, HttpServletResponse response) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            String encode = URLEncoder.encode(fileName + "." + excelTypeEnum, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + encode);
            InputStream inputStream = new FileInputStream(file);
            ServletOutputStream outputStream = response.getOutputStream();
            int len;
            byte[] buffer = new byte[1024];
            while ((len = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, len);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
     * 默认的 excel 导出
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response     响应体
     * @param exportParams 导出参数
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        long start = System.currentTimeMillis();
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        log.info("Create workbook take time: " + (System.currentTimeMillis() - start));
        start = System.currentTimeMillis();
        downLoadExcel(fileName, response, workbook);
        log.info("Flush to response take time: " + (System.currentTimeMillis() - start));
     * @param fileName 文件名称
     * @param response 响应体
     * @param workbook excel数据
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            String encode;
            if (workbook instanceof HSSFWorkbook) {
                encode = URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLS.getValue(), "UTF-8");
            } else {
                encode = URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + encode);
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
     * 操作excel导入信息
     * @param successList 成功列表
     * @param errorList   失败列表
     * @param titleRows  忽略的标题行数
    public static Map<String, Object> handleImportReturn(List<? extends BaseExcel> successList, List<? extends BaseExcel> errorList, int titleRows) {
        Map<String, Object> result = new HashMap<>();
        if(successList.size() == 0 && errorList.size() == 0){
            result.put("msg","模板中未找到数据");
            return result;
        result.put("error", errorList.stream()
                .map(fail -> {
                    ExcelErrorVO excelErrorVO = new ExcelErrorVO();
                    excelErrorVO.setErrorMsg("第" + (fail.getRowNum() + titleRows) + "行,"+fail.getErrorMsg()+",添加失败！");
                    return excelErrorVO;
                .collect(Collectors.toList()));
        result.put("success", successList.stream()
                .map(fail -> "第" + (fail.getRowNum() + titleRows) + "行,添加成功!").collect(Collectors.toList()));
        return result;
     * @param file      文件
     * @param t         导入对象
     * @param cla       自定义验证类
     * @param headRows  表头行数 需要设置有几行表头
     * @param titleRows 表格标题行数 序号忽略几行标题
     * @param classes   分组校验类
    public static <T> Map<String, List<T>> importExcel(MultipartFile file, T t, Class<?> cla, int headRows, int titleRows, IExcelDictHandler dictHandler, Class<?>... classes) throws Exception {
        long l = System.currentTimeMillis();
        Map<String, List<T>> resultMap = new LinkedHashMap<>();
        ImportParams importParams = new ImportParams();
        //忽略一行 默认一行
        importParams.setHeadRows(headRows);
        importParams.setVerifyFileSplit(false);
        //标题行数
        importParams.setTitleRows(titleRows);
//        if(dictHandler != null){
//            importParams.setDictHandler(new ExcelDictHandlerImpl());
//        }
        if (cla != null || classes != null) {
            //开启校验
            importParams.setNeedVerify(true);
            //自定义校验
//            if (cla != null) {
//                importParams.setVerifyHandler((IExcelVerifyHandler) SpringContextDataService.getBean(cla));
//            }
            //分组校验
            if (classes != null) {
                importParams.setVerifyGroup(classes);
        //解析excel
        ExcelImportResult<T> excelImportResult = ExcelImportUtil
                .importExcelMore(file.getInputStream(), t.getClass(), importParams);
        //得到成功数据
        resultMap.put("error", excelImportResult.getFailList());
        resultMap.put("success", excelImportResult.getList());
        System.out.println("导入解析时间：" + (System.currentTimeMillis() - l));
        return resultMap;
     * excel 文件形式的方式导入
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @return T
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
     * excel 文件形式的方式导入
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerify 是否检验excel内容
     * @param pojoClass  pojo类型
     * @return List
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
        return importExcel(file.getInputStream(), titleRows, headerRows, needVerify, pojoClass);
     * excel 文件流的方式导入
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerify  是否检验excel内容
     * @param pojoClass   pojo类型
     * @return T
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl(File.separator + "excel" + File.separator);
        params.setNeedSave(false);
        params.setNeedVerify(needVerify);
        params.setVerifyHandler(new EasyPoiExcelVerifyHandler<T>());
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException("excel表单不正确");
     * Excel 类型枚举
    public enum ExcelTypeEnum {
         * Excel 2007版本以前的文件后缀
        XLS("xls"),
         * Excel 2007版本以后的文件后缀
        XLSX("xlsx");
        private String value;
        ExcelTypeEnum(String value) {
            this.value = value;
        public String getValue() {
            return value;
        public void setValue(String value) {
            this.value = value;
     * 深度等级 1省2市3区4乡5村
    public enum DeepLevelEnum {
        PROVINCE(1),
        CITY(2),
        DISTRICT(3),
        TOWN(4),
        VILLAGE(5);
        private int value;
        DeepLevelEnum(int value) {
            this.value = value;
        public int getValue() {
            return value;
        public void setValue(int value) {
            this.value = value;
     * * @param rowBeginIndex 行开始下标，0-based，要确定是否含有表头和表字段行，都具备时建议行开始下标设置为2，即第三行
     * * @param rowEndIndex   行结束下标，0-based，建议设置为大于导入最大限制条数的数
     * * @param regionColInx  下拉框列开始下标，支持多个，0-based，小于0时，则不生成带有省市区街道四级联动的下拉框选项栏
    public static class RegionComboBox {
        private final int rowBeginIndex;
        private final int rowEndIndex;
        private final int[] regionColIndex;
         * 生成列的数量， 默认4： 省、市、区、乡镇街道
        private int colNum = 4;
        public RegionComboBox(int rowBeginIndex, int rowEndIndex, DeepLevelEnum deepLevel, int[] regionColIndex) {
            this.rowBeginIndex = rowBeginIndex;
            this.rowEndIndex = rowEndIndex;
            this.regionColIndex = regionColIndex;
            this.colNum = deepLevel.getValue();
         * 默认省市区街道 4列
        public static RegionComboBox getInstance(int rowBeginIndex, int rowEndIndex, int... regionColIndex) {
            return new RegionComboBox(rowBeginIndex, rowEndIndex, DeepLevelEnum.TOWN, regionColIndex);
         * 指定列数 比如deepLevel为3 只生成省市区的
        public static RegionComboBox getInstance(int rowBeginIndex, int rowEndIndex, DeepLevelEnum deepLevel, int... regionColIndex) {
            return new RegionComboBox(rowBeginIndex, rowEndIndex, deepLevel, regionColIndex);
        public int getRowBeginIndex() {
            return rowBeginIndex;
        public int getRowEndIndex() {
            return rowEndIndex;
        public int[] getRegionColIndex() {
            return regionColIndex;
        public int getColNum() {
            return this.colNum;
    public static class CommonComboBox {
        int firstRow;
        int lastRow;
        int firstCol;
        int lastCol;
        String[] options;
        public CommonComboBox(int firstRow, int lastRow, int colIndex, String[] options) {
            this.firstRow = firstRow;
            this.lastRow = lastRow;
            this.firstCol = colIndex;
            this.lastCol = colIndex;
            this.options = options;
        public static CommonComboBox getInstance(int firstRow, int lastRow, int colIndex, String[] options) {
            return new CommonComboBox(firstRow, lastRow, colIndex, options);
        public int getFirstRow() {
            return firstRow;
        public int getLastRow() {
            return lastRow;
        public int getFirstCol() {
            return firstCol;
        public int getLastCol() {
            return lastCol;
        public String[] getOptions() {
            return options;
  
代码就以上这么多，说得不是很详细，我主要也是记录一下。对于esaypoi不了解的同学可以去官网看看。