推荐一篇排版舒服的公众号
EasyExcel，让 excel 导入导出更加简单

项目中会遇到导入Excel的需求，使用POI的话可能会比较麻烦，最近使用阿里的easyExcel过程记录下。下例为本地环境测试：
一、新建Springboot项目并添加依赖
easyExcel依赖：
       <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>2.2.6</version>
      </dependency>

lombok依赖（不是必需）：
      <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
          <optional>true</optional>
      </dependency>

二、新建Model
 @Data
public class Demo extends BaseRowModel {

  @ExcelProperty(index = 0, value = "ID")
  private String id;

  @ExcelProperty(index = 1,value = "姓名")
  private String name;

  @ExcelProperty(index = 2,value = "地址")
  private String address;

}

说明：easyExcel是根据index读取Excel中的每列数据，value可以不用写，但是为了清晰建议写上；如果给前端返回时也用此model，会返回cellStyleMap什么的，可以用@jsonIgnore注解忽略掉。

本地Excel截图：


三、编写相关utils
ExcelUtil
public class ExcelUtil<T> {

    /***
     * 读取Excel方法
     * 要求类必须继承BaseRowModel
     */
    public static <T extends BaseRowModel> ArrayList<T> readExcel(MultipartFile excel, Class<T> clazz) {
        try {
            InputStream in = new BufferedInputStream(excel.getInputStream());
            GeneralExcelListener<T> listener = new GeneralExcelListener<>();
            ExcelReader excelReader = new ExcelReader(in, ExcelTypeEnum.XLS, listener);
            excelReader.read(new Sheet(1, 1, clazz));
            return listener.getSuccessDatas();

        } catch (Exception e) {

            return new ArrayList<T>();
        }

    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response,
                                  List<? extends BaseRowModel> list,
                                  String fileName, String sheetName,
                                  BaseRowModel object) {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();

    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            return response.getOutputStream();
        } catch (IOException e) {
            throw new RuntimeException("导出文件时为Writer生成OutputStream失败！");
        }
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     */
    private static ExcelReader getReader(MultipartFile excel,
                                         ExcelListener excelListener) {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RuntimeException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

ExcelListener
public class ExcelListener extends AnalysisEventListener {

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

GeneralExcelListener
public class GeneralExcelListener<T> extends AnalysisEventListener<T> {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());


    //自定义用于暂时存储data。
    //可以通过实例获取该值
    private ArrayList<T> successDatas = new ArrayList<>();

    public GeneralExcelListener() {

    }

    @Override
    public void invoke(T o, AnalysisContext analysisContext) {
    //此处是读取Excel每行数据，可通过analysisContext进行分析
        successDatas.add(o);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
    public ArrayList<T> getSuccessDatas() {
        return successDatas;
    }
}

四、使用示例
导入示例
@PostMapping(value = "/import")
public ModelAndView import(@RequestParam(value = "file", required = true) MultipartFile file) {
 
    ModelAndView mv = new ModelAndView();
    //一行代码即可获取解析得到的Excel中的数据
    List<Test> datas = ExcelUtil.readExcel(file, Test.class);
    //然后对获取的数据，在service层进行业务处理
    ResultMsg result = provShareConfigService.insertList(datas);
    return mv;
}

导出示例
@PostMapping(value = "/export")
public void export(HttpServletRequest request, HttpServletResponse response, @RequestBody JSONObject ob) {
    //前端传来的参数
    String date = ob.getString("date");
    String fileName = "退赔导出";
    String sheetName = "sheet1";
    //从数据库得到的list集合
    List<Test> list = testService.selectAll(date);
    //一行代码即可导出
    ExcelUtil.writeExcel(response, list, fileName, sheetName, new Test());
}

五、编写测试用例(导入)
由于前端提供的一般都是MultipartFile，本地测试的话进行了转换。
    @Test
    public void test2() throws IOException {
        File file = new File("C:\\Users\\Light\\Desktop\\demo.xlsx");
        FileInputStream inputStream = new FileInputStream(file);
        MultipartFile multipartFile = new MockMultipartFile(file.getName(),file.getName(),
                ContentType.APPLICATION_OCTET_STREAM.toString(),inputStream);
        //使用ExcelUtil读取
        List<Demo> datas = ExcelUtil.readExcel(multipartFile, Demo.class);
        System.out.println(datas);
    }

执行后可以看到控制台打印信息：
[Demo(id=1, name=张三, address=北京), Demo(id=2, name=李四, address=上海), Demo(id=3, name=王五, address=广州), Demo(id=4, name=田六, address=深圳)]

拿到转换后的集合，可以对数据进行处理，然后进行持久化操作。导出自己可以做完持久化后进行尝试。
参考网上教程： 原文地址

 1. <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>1.1.2-beta5</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.ow2.asm</groupId>
                        <artifactId>asm</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>



 2. @PostMapping(value="/Import")
        public Response importDispatch(@RequestParam("file") MultipartFile file) {
            long start = System.currentTimeMillis();
            Map<String,Object> result = excelUtil.readExcel(file, new DispatchExcelImport(),1);
            List<DispatchExcelImport> dispatchExcelImports = new ArrayList<>();
            Boolean flag = (Boolean) result.get("flag");
            Response response;
            List<Object> list = new ArrayList<>();
            if(flag){
                list = (List<Object>) result.get("datas");
                if(!CollectionUtils.isEmpty(list)){
                    dispatchExcelImports = (List)list;
                }
            }else{
                return BaseUtil.createResponse(ReturnCode.ERROR, "", Arrays.asList(ReturnMessage.EXCEL_HEAD_ERROR));
            }
           //do something
           
            long end = System.currentTimeMillis();
            System.out.println("=======================导入耗时:" + (end - start));
            return response;
        }
    
   
   
    	
 3. @GetMapping(value = "/Export")
            public void export(HttpServletResponse response, DispatchDTO dispatchDTO) {
                Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
                List<DispatchExcelExport> models = new ArrayList<>();
                List<DispatchImport> exportDatas = dispatchImportDao.selectDataForExport(dispatchDTO);
                if (CollectionUtils.isEmpty(exportDatas)) {
                    models.add(new DispatchExcelExport());
                } else {
                    exportDatas.forEach(dispatchData -> {
                        DispatchExcelExport excelExport = new DispatchExcelExport();
                        BaseUtil.copy(dispatchData, excelExport);
                        models.add(excelExport);
                    });
                }
                map.put("wenjian", models);
                excelUtil.createExcel(response, map, ExcelTypeEnum.XLSX, "wenjian");
            }





 4. @Data 
  public class DispatchExcelImport extends BaseRowModel {
        @ExcelProperty(value = "员编", index = 0)
        private String employeeId;
        @ExcelProperty(value = "姓名", index = 1)
        private String employeeName;
        @ExcelProperty(value = "身份证", index = 2)
        private String identityId;
        @ExcelProperty(value = "城市", index = 3)
        private String cityName; }






package com.cmbchina.ccd.oa.socialsecurity.util;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.cmbchina.ccd.oa.socialsecurity.model.bo.ExcelListener;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Component
public class ExcelUtil {
    private static Sheet initSheet;

    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        //设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    /**
     * 读取少于1000行数据
     * 数据量少时，同步读取
     *
     * @param file 读取的文件
     * @return
     */
    public List<Object> readLessThan1000Row(MultipartFile file) throws IOException {
        return readLessThan1000RowBySheet(file, null);
    }

    /**
     * 读小于1000行数据
     * filePath 文件绝对路径
     * initSheet ：
     * sheetNo: sheet页码，默认为1
     * headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取
     * clazz: 返回数据List<Object> 中Object的类名
     */
    public List<Object> readLessThan1000RowBySheet(MultipartFile file, Sheet sheet) throws IOException {
        if (file == null) {
            //log.info("导入文件为空", file);
            return null;
        }
        sheet = sheet != null ? sheet : initSheet;
        InputStream fileStream = null;
        try {
            fileStream = file.getInputStream();
            return EasyExcelFactory.read(fileStream, sheet);
        } catch (FileNotFoundException e) {
            //log.info("文件有误, 文件：{}", file);
        } finally {
            try {
                if (fileStream != null) {
                    fileStream.close();
                }
            } catch (IOException e) {
                //log.info("excel文件读取失败, 失败原因：{}", e);
            }
        }
        return null;
    }

    /**
     * 生成excle
     *
     * @param filePath 绝对路径, 如：/home/Downloads/aaa.xlsx
     * @param data     数据源
     * @param head     表头
     */
    public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {
        writeSimpleBySheet(filePath, data, head, null);
    }

    /**
     * 生成excle
     *
     * @param filePath 绝对路径
     * @param data     数据源
     * @param sheet    excle页面样式
     * @param head     表头
     */
    public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) {
        sheet = (sheet != null) ? sheet : initSheet;

        if (head != null) {
            List<List<String>> list = new ArrayList<>();
            head.forEach(h -> list.add(Collections.singletonList(h)));
            sheet.setHead(list);
        }

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write1(data, sheet);
        } catch (FileNotFoundException e) {
            //log.error("找不到文件或文件路径错误, 文件：{}", filePath);
        } finally {
            try {
                if (writer != null) {
                    writer.finish();
                }
                if (outputStream != null) {
                    outputStream.close();
                }

            } catch (IOException e) {
                //log.error("excel文件导出失败");
            }
        }

    }

    /**
     * @Description 导出excel 支持一张表导出多个sheet
     * @Param OutputStream 输出流
     * Map<String, List>  sheetName和每个sheet的数据
     * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
     */
    public void createExcel(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> sheetNameAndDateList, ExcelTypeEnum type, String fileName) {
        // if (checkParam(SheetNameAndDateList, type)) return;
        try {
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            //解决导出文件名中文乱码
            fileName = new String(fileName.getBytes(), "iso8859-1") + DateUtil.todayStr();
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + type.getValue());
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, type, true);
            setSheet(sheetNameAndDateList, writer);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    /**
     * @Description //setSheet数据
     */
    private void setSheet(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelWriter writer) {
        int sheetNum = 1;
        for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : SheetNameAndDateList.entrySet()) {
            Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
            sheet.setSheetName(stringListEntry.getKey());
            writer.write(stringListEntry.getValue(), sheet);
            sheetNum++;
        }
    }

    /**
     * @Description 校验参数
     */
    private static boolean checkParam(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelTypeEnum type) {
        if (CollectionUtils.isEmpty(SheetNameAndDateList)) {
            //log.error("SheetNameAndDateList不能为空");
            return true;
        } else if (type == null) {
            //log.error("导出的excel类型不能为空");
            return true;
        }
        return false;
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
        return readExcel(excel, rowModel, 1, 1);
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @param sheetNo  sheet 的序号 从1开始
     * @return Excel 数据 list
     */
    public Map<String, Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
        Map<String, Object> result = new HashMap<>();
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
        //校验表头
        Boolean flag = false;
        //维护实体类中 没有@ExcelProperty 放置在最后，会被映射出null表头
        String head = excelListener.getImportHeads().replace("null,", "");
        if (head.equals(excelListener.getModelHeads())) {
            flag = true;
        }
        result.put("flag", flag);
        result.put("datas", excelListener.getDatas());
        return result;
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param rowModel    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
        return excelListener.getDatas();
    }

    /**
     * 读取指定sheetName的Excel(多个 sheet)
     *
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     * @throws IOException
     */
    public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            //读取指定名称的sheet
            if (sheet.getSheetName().contains(sheetName)) {
                reader.read(sheet);
                break;
            }
        }
        return excelListener.getDatas();
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null, excelListener, false);
        } else {
            return null;
        }
    }
}