java导入导出

文章目录

方式一：采用easyexcel导入方式二：采用POI导入

方式一：采用easyexcel导入

1.需要导入的依赖

     
                com.alibaba
                easyexcel
                2.2.8

实体类代码：

@Data
@Entity  // 该注解声明一个实体类，与数据库中的表对应
@Table(name = "tb_user")  //表示当映射多个表时，指定表的表中的字段。默认值为主表的表名。
@ApiModel(description = "用户表")
public class User{
    //主键标识：表明该声明的属性字段和数据库的主键字段一一对应
    @Id
    //主键生成策略，不写意味着没有主键生成策略，也意味着是手动主键
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @ApiModelProperty(hidden = true)   //swagger注解
    private Integer id;
    @ApiModelProperty(value = "用户名")
    @ExcelProperty(index = 1)
    //unique
    //表示该字段是否为唯一标识，默认为false。如果表中有一个字段需要唯一标识，则既可以使用该标记，也可以使用@Table标记中的@UniqueConstraint。
    //nullable 表示该字段是否可以为null值，默认为true。
    // length  表示字段的长度，当字段的类型为varchar时，该属性才有效，默认为255个字符。
    @Column(name = "user_name", unique = true, nullable = false, length = 50)
    private String userName;
    @ApiModelProperty(value = "用户工号")
    @ExcelProperty(index = 2)
    @Column(name = "login_name", length = 20)
    private String loginName;

    @ApiModelProperty(value = "密码")
    @Column(name = "password", length = 50)
    private String password;

    @ApiModelProperty(value = "头像")
    @Column(name = "avatar", length = 100)
    private String avatar;

    @ApiModelProperty(value = "性别")
    @ExcelProperty(index = 3)
    @Column(name = "gender", nullable = false, length = 1)
    private String gender;

    @ApiModelProperty(value = "邮箱")
    @ExcelProperty(index = 4)
    @Column(name = "email", length = 50)
    private String email;

    @ApiModelProperty(value = "描述")
    @ExcelProperty(index = 7,value = "描述")
    @Column(name = "description")
    private String description;

    @ApiModelProperty(value = "所属公司")
    @ExcelProperty(index = 5,value = "所属公司")
    @Column(name = "company", length = 50)
    private String company;

    @ApiModelProperty(value = "职位")
    @ExcelProperty(index = 6,value ="职位")
    @Column(name = "position", length = 20)
    private String position;

    @ApiModelProperty(hidden = true)
    private Date createTime;

    @ApiModelProperty(hidden = true)
    private Date updateTime;
}

实现监听回调

public class PrtcpntInfoListener extends AnalysisEventListener {
    private static final Logger log = LoggerFactory.getLogger(PrtcpntInfoListener.class);
    private static int TOTAL_COUNT;

    
    private static final int BATCH_COUNT = 110000;
    List list = new ArrayList<>();
    private BatchLead batchLead;

    public PrtcpntInfoListener(BatchLead batchLead) {
        this.batchLead = batchLead;
    }

    
    @Override
    public void invoke(User data, AnalysisContext context) {
       log.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 每达到阈值，就去做批量入库，防止内存溢出
        if (list.size() >= BATCH_COUNT) {
            ThreadOperation.readExcel(list);
            TOTAL_COUNT += list.size();
            list.clear();
        }
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //执行完让线程直接进入等待
         batchLead.save(list);
        TOTAL_COUNT += list.size();
        log.info("所有数据已入库完成，总计：{}条", TOTAL_COUNT);
    }


    public static void main(String[] args) {
        Long startTime = System.currentTimeMillis();
        ExcelReader excelReader = null;
        try {
            String filePath = "C:/Users/86135/Desktop/用户信息.xlsx";
            excelReader = EasyExcel.read(filePath, User.class, new PrtcpntInfoListener(new BatchLead())).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(2).build();
            excelReader.read(readSheet);
        } finally {
            // 关闭资源
            if (excelReader != null) {
                excelReader.finish();
            }
        }
        log.info("总耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "(s)");
    }
}

保存数据 JDBC直连方式

public class BatchLead {
    private static final Logger log = LoggerFactory.getLogger(BatchLead.class);
    int i = 0;

    public void save(List list) {
        System.out.println("保存="+list.size());
        Long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "insert into tb_user(user_name,login_name,password,avatar,gender,email,description,company,position)values(?,?,?,?,?,?,?,?,?)";
        conn = getConnection();
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        for (User dataInfo : list) {
            try {
                pstmt.setString(1, dataInfo.getUserName());
                pstmt.setString(2, dataInfo.getLoginName());
                pstmt.setString(3, dataInfo.getPassword());
                pstmt.setString(4, dataInfo.getAvatar());
                pstmt.setString(5, dataInfo.getGender());
                pstmt.setString(6, dataInfo.getEmail());
                pstmt.setString(7, dataInfo.getDescription());
                pstmt.setString(8, dataInfo.getCompany());
                pstmt.setString(9, dataInfo.getPosition());
                pstmt.addBatch();
                i++;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        try {
            pstmt.executeBatch();
            conn.commit();
            conn.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        log.info("批量入库，本次入库数据：{}条，耗时：{}(s)", list.size(), (System.currentTimeMillis() - startTime) / 1000);
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/localtest?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC",
                    "root", "root");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

或者使用mybatis，在覆盖doAfterAllAnalysed方法中调用

   @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        UserMapper userMapper = ApplicationContextProvider.getBean(UserMapper.class);
        userMapper.batchAddUser(list);
        TOTAL_COUNT += list.size();
        log.info("所有数据已入库完成，总计：{}条", TOTAL_COUNT);
    }

controller层调用：

  
    @PostMapping("/users/add")
    @ApiOperation(value = "批量导入")
    public ResultDTO saveBatchUser(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        if (!StrUtil.endWithAny(fileName, EXCEL_XLS, EXCEL_XLSX)) {
            return ResultDTO.error("只支持.xlsx与.xls格式的文件导入");
        }
        return  myUserService.saveBatchUser(file,fileName);
    }

service层代码：

  public ResultDTO saveBatchUser(MultipartFile multipartFile, String fileName) {
        Long startTime = System.currentTimeMillis();
        ExcelReader excelReader = null;
        File file = null;
        try {
            String[] filename = fileName.split("\.");
            file=File.createTempFile(filename[0], ".xlsx");
            multipartFile.transferTo(file);
            excelReader = EasyExcel.read(file, User.class, new PrtcpntInfoListener(new BatchLead())).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(1).build();
            excelReader.read(readSheet);
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (excelReader != null) {
                excelReader.finish();
            }
           // file.deleteonExit();
        }
        log.info("总耗时：" + (System.currentTimeMillis() - startTime) / 1000 + "(s)");

        return ResultDTO.isSuccess();
    }

上面是单线程版
多线程保存数据库方式
先定义线程资源类

public class importThread implements Runnable {
    BatchLead batchLead;
    private List list;
    private CountDownLatch end;
    private MyUserMapper myUserMapper;

    public importThread(List list, CountDownLatch begin, CountDownLatch end, BatchLead batchLead) {
        this.list = list;
        this.end = end;
        this.batchLead = batchLead;
    }

  @Override
    public void run() {
        //这里三种方式选取一种
        //1.mybatis方式
        UserMapper userMapper = ApplicationContextProvider.getBean(UserMapper.class);
        userMapper.batchAddUser(list);
        //2.jpa方式
      //  MyUserMapper myUserMapper = ApplicationContextProvider.getBean(MyUserMapper.class);
      //  myUserMapper.saveAll(list);
        //3.JDBC直连
      //  batchLead.save(list);
      //  end.countDown();
    }
}

上面连接数据库如果使用mybatis方式，sql语句如下

     
        insert into tb_user(user_name,login_name,password,avatar,gender,email,description,company,position) values
        
            (#{item.userName},#{item.loginName},#{item.password},#{item.avatar},#{item.gender},#{item.email},#{item.description},#{item.company},#{item.position})

使用线程池

public class ThreadOperation {
    public static void readExcel(List list) {
        //一个线程处理数据数
        int count = 11000;
        // 开启的线程数
        int runSize = 10;
        // 存放每个线程的执行数据
        List newlist = null;
//        int corePoolSize,核心线程数
//        int maximumPoolSize,最大线程数
//        long keepAliveTime,线程空闲时最大存活时间
//        TimeUnit unit,最大存活时间单位
//        BlockingQueue workQueue,任务队列
//        ThreadFactory threadFactory,线程工厂用默认的即可
//        RejectedExecutionHandler handler)线程池和队列都满时的拒绝策略

        //创建一个线程池,数量和开启线程的数据量一样
        ThreadPoolExecutor executor = new ThreadPoolExecutor(
                runSize, runSize,  1, TimeUnit.SECONDS, new ArrayBlockingQueue<>(5),
                new ThreadPoolExecutor.DiscardOldestPolicy());
                //并发容器，计数器，等待协同
        CountDownLatch end = new CountDownLatch(runSize);
        //循环创建线程
        for (int i = 0; i < runSize; i++) {
            //计算每个线程执行的数据
            if ((i+1)==runSize){
                newlist=list.subList((i*count),list.size());
            }else {
                newlist=list.subList((i*count),(i+1)*count);
            }
            //线程类 操作资源
            importThread mythead = new importThread(newlist, end,new BatchLead());
            //执行线程
            executor.execute(mythead);
        }
        try {
            end.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        //执行完关闭线程池
        executor.shutdown();
    }
}

方式二：采用POI导入

依赖准备

             
                org.apache.poi
                poi
                3.9
            

            
                org.apache.poi
                poi-ooxml
                3.9
            

            
                org.apache.commons
                commons-lang3
                3.4

自定义注解

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExport {
     String fileName();
}


@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface EnableExportField {
     int colWidth() default  100;
     String colName();
}


@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface importIndex {
     int index() ;
}

//pojo
@EnableExport(fileName = "XXX功能")
public class DataCheckExport {
    @EnableExportField(colName = "编码",colWidth = 90)
    @importIndex(index = 0)
    private String code;
    ....
    }

导入、导出、自定义表格颜色工具类

public class ExcelUtils {
    
    public static final  Map> ALL_SELECT_LIST_MAP = new HashMap> ();
        
        public static List parseExcelToList(File excel,Class clazz){
            List

java导入导出

Java相关栏目本月热门文章