用EasyExcel实现表格的导入，导出

EasyExcel

本文使用的是阿里巴巴开源的对Excel操作读写的EasyExcel，它比传统上poi等一些技术省内存，EasyExcel在最大可能性上面节约内存。poi，jxl等技术存在最严重的问题就是耗内存。

一，导入maven依赖

		<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

导出

	//注入server层
 	@Resource
    TableServiceImp tableServiceImp;

    @RequestMapping("exportExcel")
    public void export(HttpServletResponse response) throws IOException {
        System.out.println("导出方法");
        //把数据查询出来
        List<TableEntity> userList = tableServiceImp.find();
        //导出操作
        System.out.println(userList.toString());
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("用户信息表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), TableEntity.class)
                .sheet("用户信息")
                // 设置字段宽度为自动调整，不太精确
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(userList);

    }

实体类

package com.example.demo.entity;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

/**
 * @author 1
 */
@Data//Lombok的注解，会自动帮你get，set
@Entity//表示这是一个实体类
@Table(name = "table_1")//标注它是跟数据库的表对应的
public class TableEntity implements Serializable  {
    @Id
    @ExcelIgnore//该注解是该列不导出到Excel
    private String column_1;
    
	//对应Excel的标题，index表示在哪列
    @ExcelProperty(value = "姓名",index = 0)
    private String column_2;

    @ExcelProperty(value = "姓名全拼",index = 1)
    private String column_3;

    @ExcelProperty(value = "性别",index = 2)
    private String column_4;

    @ExcelProperty(value = "身份证证件类型",index = 3)
    private String column_5;

    @ExcelProperty(value = "身份证证件号码",index = 4)
    private String column_6;

    @ExcelProperty(value = "出生日期",index = 5)
    private String column_7;

    @ExcelProperty(value = "手机号码",index = 6)
    private String column_8;

    @ExcelProperty(value = "电子邮箱",index = 7)
    private String column_9;

    @ExcelIgnore//该注解是该列不导出到Excel
    private String createtime;
    @ExcelIgnore//该注解是该列不导出到Excel
    private String updatetime;
    public TableEntity(){}
    public TableEntity(String column_1,String column_2,String column_3,String column_4,String column_5,String column_6,
                       String column_7,String column_8,String column_9,String createtime,String updatetime){
        setColumn_1(column_1);
        setColumn_2(column_2);
        setColumn_3(column_3);
        setColumn_4(column_4);
        setColumn_5(column_5);
        setColumn_6(column_6);
        setColumn_7(column_7);
        setColumn_8(column_8);
        setColumn_9(column_9);
        setCreatetime(createtime);
        setUpdatetime(updatetime);
    }
}

导入

package com.example.demo.controller;

import com.alibaba.excel.EasyExcel;
import com.example.demo.entity.TableEntity;
import com.example.demo.listener.ImportListener;
import com.example.demo.service.imp.TableServiceImp;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.List;
import java.util.Map;

@Controller
public class ImportExcelController {
    @Resource
    TableServiceImp tableServiceImp;

    @RequestMapping(value="/onlineupload", method = RequestMethod.POST)
    @ResponseBody
    public List<String> onlineupload(@RequestParam("onlinefilename") MultipartFile file) throws IOException {
        //进来这方法先清空list中的错误提示
        ImportListener.getList1().clear();
        EasyExcel.read(file.getInputStream(), TableEntity.class, new ImportListener(tableServiceImp)).sheet().doRead();
        List<String> list = ImportListener.getList1();
        for (String sb:list) {
            System.out.println(sb);
        }
        return list;//返回的是表格中错误的数据
    }

}

导入listener监听器

表格中的每一条数据都会走一遍监听器

package com.example.demo.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.example.demo.entity.TableEntity;
import com.example.demo.service.imp.TableServiceImp;

import java.util.ArrayList;
import java.util.List;

/**
 * @author : 张某
 */
public class ImportListener extends AnalysisEventListener<TableEntity> {
    private List<TableEntity> list = new ArrayList<>();//保存数据
    private static List<String> list1 = new ArrayList<>();//保存错误信息
    private int i = 1;//多少行
    private TableServiceImp tableServiceImp;
    Boolean bool = false;//用于判断有没有错误数据，只要有错误数据就全部不插入
    public ImportListener(){}
    /**
     * 有参构造
     * @param tableServiceImp : 把业务层的类装进来
     */
    public ImportListener(TableServiceImp tableServiceImp){
        this.tableServiceImp = tableServiceImp;
    }
    /**
     * 该方法每一条数据解析都会来调用
     *
     * @param analysisContext
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param tableEntity :
     */
    @Override
    public void invoke(TableEntity tableEntity, AnalysisContext analysisContext) {
        String arg = "^[a-z]+$";
        String email = "\\w+([-+.]\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*";
        String phone = "^[1][3,4,5,7,8,9][0-9]{9}$";
        String date = "^\\d{4}-\\d{1,2}-\\d{1,2}$";
        String date1 = "\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$";
        String name = "^[\\u4e00-\\u9fa5.·\\u36c3\\u4DAE]{2,}$";
        List<TableEntity> list2 = tableServiceImp.find();
        boolean flot = false;
        for(TableEntity tb: list2) {
            if(tableEntity.getColumn_6().equalsIgnoreCase(tb.getColumn_6())){
                flot = true;
            }
        }
        if(flot){
            list1.add("第"+i+"行身份证号码已存在");
            bool = true;
        }
        if(tableEntity.getColumn_2() == null || tableEntity.getColumn_2() == ""){
            System.out.println("第"+i+"行，姓名不能为空");
            list1.add("第"+i+"行姓名不能为空");
            bool = true;
        }
        if(!tableEntity.getColumn_2().matches(name)){
            System.out.println("第"+i+"行，姓名格式不正确");
            list1.add("第"+i+"行姓名格式不正确");
            bool = true;
        }
        if(tableEntity.getColumn_3() == null || tableEntity.getColumn_3() == ""){
            System.out.println("第"+i+"行，姓名全拼不能为空");
            list1.add("第"+i+"行姓名全拼不能为空");
            bool = true;
        }
        if(!tableEntity.getColumn_3().matches(arg)){
            System.out.println("第"+i+"行，姓名全拼只能为小写");
            list1.add("第"+i+"行姓名全拼只能为小写");
            bool = true;
        }
        if(tableEntity.getColumn_4() == null || tableEntity.getColumn_4() == ""){
            System.out.println("第"+i+"行，姓别不能为空");
           list1.add("第"+i+"行姓别不能为空");
            bool = true;
        }
        if(!"男".equals(tableEntity.getColumn_4()) && !"女".equals(tableEntity.getColumn_4())){
            System.out.println("第"+i+"行，姓别只能为'男'或'女'");
            list1.add("第"+i+"行姓别只能为'男'或'女'");
            bool = true;
        }
        if(tableEntity.getColumn_5() == null || tableEntity.getColumn_5() == ""){
            System.out.println("第"+i+"行，身份证类型不能为空");
            list1.add("第"+i+"行身份证类型不能为空");
            bool = true;
        }
        if(!"居民身份证".equals(tableEntity.getColumn_5()) && !"驾驶证".equals(tableEntity.getColumn_5()) && !"士官证".equals(tableEntity.getColumn_5()) && !"护照".equals(tableEntity.getColumn_5()) && !"学生证".equals(tableEntity.getColumn_5()) && !"港澳通行证".equals(tableEntity.getColumn_5())){
            System.out.println("第"+i+"行，身份证类型只能为: 居民身份证,驾驶证,士官证,护照,学生证,港澳通行证");
            list1.add("第"+i+"行身份证类型只能为: 居民身份证,驾驶证,士官证,护照,学生证,港澳通行证");
            bool = true;
        }
        if(tableEntity.getColumn_6() == null || "".equals(tableEntity.getColumn_6())){
            System.out.println("第"+i+"行，身份证号码不能为空");
            list1.add("第"+i+"行身份证号码不能为空");
            bool = true;
        }
        if("居民身份证".equals(tableEntity.getColumn_5()) && !validate(tableEntity.getColumn_6())){
            System.out.println("第"+i+"行，身份证号码不正确");
            list1.add("第"+i+"行身份证号码不正确");
            bool = true;
        }
        if(tableEntity.getColumn_7() == null || tableEntity.getColumn_7() == ""){
            System.out.println("第"+i+"行，出生日期不能为空");
            list1.add("第"+i+"行出生日期不能为空");
            bool = true;
        }
        if(!tableEntity.getColumn_7().matches(date) && !tableEntity.getColumn_7().matches(date1)){
            System.out.println("第"+i+"行，出生日期格式不正确");
            list1.add("第"+i+"行出生日期格式不正确");
            bool = true;
        }
        if(tableEntity.getColumn_8() == null || tableEntity.getColumn_8() == ""){
            System.out.println("第"+i+"行，手机号码不能为空");
            list1.add("第"+i+"行手机号码不能为空");
            bool = true;
        }
        if(tableEntity.getColumn_8() != null && tableEntity.getColumn_8() != "" && !tableEntity.getColumn_8().matches(phone)){
            System.out.println("第"+i+"行，手机号码不正确");
            list1.add("第"+i+"行手机号码不正确");
            bool = true;
        }
        if(tableEntity.getColumn_9() == null || tableEntity.getColumn_9() == ""){
            System.out.println("第"+i+"行，邮箱不能为空");
            list1.add("第"+i+"行邮箱不能为空");
            bool = true;
        }
        if(tableEntity.getColumn_9() != null && tableEntity.getColumn_9() != "" && !tableEntity.getColumn_9().matches(email)){
            System.out.println("第"+i+"行，邮箱格式不正确");
            list1.add("第"+i+"行邮箱格式不正确");
            bool = true;
        }
        list.add(tableEntity);
        i++;
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            //saveData();
            // 存储完成清理 list
            //list.clear();
    }

    /**
     * 身份证校验
     * @param no : 传过来的身份证号码
     * @return : 返回值为true身份证号码就是正确的 为false就是错误的
     */
    public static boolean validate(String no) {
        // 对身份证号进行长度等简单判断
        String identityCard = "^\\d{15}(\\d{2}[A-Za-z0-9])?$";
        boolean matches = no.matches(identityCard);
        if (matches) {
            if (no.length() == 18) {
                try {
                    char[] charArray = no.toCharArray();
                    //前十七位加权因子
                    int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
                    //这是除以11后，可能产生的11位余数对应的验证码
                    String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
                    int sum = 0;
                    for (int i = 0; i < idCardWi.length; i++) {
                        int current = Integer.parseInt(String.valueOf(charArray[i]));
                        int count = current * idCardWi[i];
                        sum += count;
                    }
                    char idCardLast = charArray[17];
                    int idCardMod = sum % 11;
                    if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {
                        return true;
                    } else {
                        System.out.println("身份证最后一位:" + String.valueOf(idCardLast).toUpperCase() +
                                "错误,正确的应该是:" + idCardY[idCardMod].toUpperCase());
                        return false;
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println("异常:" + no);
                    return false;
                }
            }

        }
        return matches;
    }
    public static List<String> getList1(){
        return list1;
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param analysisContext :
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        if(bool == false){
            saveData();
            list.clear();
            System.out.println("所有数据解析完成");
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) {
        if(exception.getMessage() == null){
            i++;
        }
        System.out.println("解析失败，但是继续解析下一行:{}"+exception.getMessage());
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            System.out.println("第" + excelDataConvertException.getRowIndex() + "行，第" + excelDataConvertException.getColumnIndex() + "列解析异常");
        }
    }

    private void saveData() {
        if (list.size() > 0) {
            System.out.println(list.size() + "条数据，开始存储数据库！");
            for (TableEntity tb : list) {
                tableServiceImp.add(tb);
            }
            System.out.println("存储数据库成功！");
        }else{
            System.out.println("没有数据！");
        }
    }
}