POI和easyExcel

1.poi导入依赖

   <!--07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!--03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <!--单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

2. poi写入操作(03写入快HSSF,但是最大只能写65536行 07可以写入大量数据,但是写入慢 XSFF 07升级版写入快并且可以写入大量SXSFF)

    @Test
    public void test1(){
        String PATH="D:\\excellDownLoad\\";

        // 创建一个工作蒲
         Workbook workbook = new HSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("03统计表");
        // 创建行
        Row row = sheet.createRow(0);
        // 创建第一列单元格（1,1）
        Cell cell = row.createCell(0);
        cell.setCellValue("员工姓名");
        // 创建第二列单元格（1,2）
        Cell cell2 = row.createCell(1);
        cell2.setCellValue("时间日期");


        // (2,1)
        Row row2 = sheet.createRow(1);
        // 创建第二列
        Cell cell3 = row2.createCell(0);
        cell3.setCellValue("小王");
        // 创建第二列单元格（1,2）
        Cell cell4 = row2.createCell(1);
        cell4.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
        OutputStream os=null;
        try {
             os= new FileOutputStream(PATH+"03测试版本.xls");
             workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            try {
                os.close();
                System.out.println("03生成成功");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

3.读取操作

    @Test
    public void test3() throws Exception{

        String PATH="D:\\excellDownLoad\\";
            //获取文件流
            FileInputStream fis = new FileInputStream(PATH +"07测试版本.xls");

            //创建一个工作簿。使用 excel能操作的这边他都可以操作
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);

            //获取标题内容
            Row rowTitle = sheet.getRow(0);
            if (rowTitle != null) {
                //得到一行有多少列有数据
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowTitle.getCell(cellNum);
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = cell.getStringCellValue();
                        System.out.print(cellValue + "|");
                    }
                }
                System.out.println();
            }

            //获取表中的内容
            //获取表中有多少行有数据
            int rowCount = sheet.getPhysicalNumberOfRows();
            for (int rowNum = 1; rowNum < rowCount; rowNum++) {
                Row rowData = sheet.getRow(rowNum);
                if (rowData != null) {
                    //读取列
                    int cellCount = rowTitle.getPhysicalNumberOfCells();
                    for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                        System.out.println("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");

                        Cell cell = rowData.getCell(cellNum);
                        //匹配列的数据类型
                        if (cell != null) {
                            int cellType = cell.getCellType();
                            String cellValue = "";

                            switch (cellType) {
                                case HSSFCell.CELL_TYPE_STRING://字符
                                    System.out.print("【 String】");
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                    System.out.print("【 BOOLEAN】");
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case HSSFCell.CELL_TYPE_BLANK://空
                                    System.out.print("【 BLANK】");
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
                                    System.out.print("【 NUMERIC】");
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期
                                        System.out.print("--【日期】");
                                        Date date = cell.getDateCellValue();
                                        cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                    } else {
                                        //不是日期格式，防止数字过长！
                                        System.out.print("--【转换为字符串输出】");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cellValue = cell.toString();
                                    }
                                    break;
                                case HSSFCell.CELL_TYPE_ERROR://错误
                                    System.out.print("【 数据类型错误】");
                                    break;
                            }
                            System.out.println(cellValue);
                        }
                    }
                }
            }
            //关闭流
            fis.close();
        }

EasyExcel

1.导入依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.2.0-beta2</version>
</dependency>

2.controller层

package com.wl.excell.controller;

import com.wl.excell.easy.EasyExcel2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;

/**
 * @Autor: wl
 * @Date: 2021-10-12     11:51
 * @Version 1.0
 * @Describe
 */
@Controller
public class UserController {
    @Autowired
    private EasyExcel2 easyExcel;

    //导出操作
    @PostMapping("/export")
    @ResponseBody
    public String  exports(){
        easyExcel.simpleWrite();
        return "a";
    }
    // 导入操作
    @PostMapping("/import")
    @ResponseBody
    public String imports(){
        easyExcel.simpleRead();
        return "a";
    }

}

3.easy读写操作

package com.wl.excell.easy;

import com.alibaba.excel.EasyExcel;
import com.wl.excell.dao.UserDao;
import com.wl.excell.dto.UserDTO;
import com.wl.excell.listener.Listener;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @Autor: wl
 * @Date: 2021-10-12     11:20
 * @Version 1.0
 * @Describe
 */
@Service
public class EasyExcel2 {
    @Autowired
    private UserDao userDao;

    private List<UserDTO> data() {
        List<UserDTO> list = userDao.selectAll();
        return list;
    }
    /**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link }
     * <p>
     * 2. 直接写即可
     */
    public void simpleWrite() {
        String Path = "D:\\excelDownLoad\\";
        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String fileName = Path + "积分信息管理" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, UserDTO.class)
                .sheet("模板")
                .doWrite(data());

    }
    /**
     * 最简单的读
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link }
     * <p>
     * 2. 直接读
     */
    public void simpleRead() {
        // 写法1：JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        String Path = "D:\\excelDownLoad\\";
        String fileName =Path+ "simpleWrite1634016318240.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
        EasyExcel.read(fileName, UserDTO.class,new Listener(userDao)).sheet().doRead();
    }

}

4.dao层

package com.wl.excell.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wl.excell.dto.UserDTO;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Autor: wl
 * @Date: 2021-10-12     11:25
 * @Version 1.0
 * @Describe
 */

@Repository
public interface UserDao extends BaseMapper<UserDTO> {
    // 查询所有数据
    List<UserDTO> selectAll();
    // 批量插入
    int insertBatch(List<UserDTO> list);
}

5.DTO层

package com.wl.excell.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.util.Date;


/**
 * @Autor: wl
 * @Date: 2021-10-12     11:13
 * @Version 1.0
 * @Describe
 */
@Data
public class UserDTO {
    @ExcelProperty("员工编号")
    private String staffNO;
    @ExcelProperty("员工姓名")
    private String staffNAME;
    @ExcelProperty("职位")
    private String POSITION;
    @ExcelProperty("标题")
    private String TITLE;
    @ExcelProperty("编码id")
    private String OPENID;
    @ExcelProperty("员工邮箱")
    private String EMAIL;
    @ExcelProperty("所属部门")
    private String DEPARTMENT;
    @ExcelProperty("员工状态")
    private String ORGANIZATION;
    @ExcelProperty("当前总积分")
    private Integer currentTotalPOINTS;
    @ExcelProperty("总积分")
    private Integer totalPOINTS;
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("上次更新时间")
    private Date lastUPDATETIME;
    @ExcelProperty("开始时间")
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    private Date beginTIME;
}

6.监听器

package com.wl.excell.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.wl.excell.dao.UserDao;
import com.wl.excell.dto.UserDTO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @Autor: wl
 * @Date: 2021-10-12     13:30
 * @Version 1.0
 * @Describe
 */

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class Listener extends AnalysisEventListener<UserDTO> {
    private static final Logger LOGGER = LoggerFactory.getLogger(Listener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    /**
     * 缓存的数据
     */
    private List<UserDTO> list = new ArrayList<>(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private UserDao userDao;

    public Listener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//        demoDAO = new DemoDao();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public Listener(UserDao demoDAO) {
        this.userDao = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(UserDTO data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list = new ArrayList<>(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        userDao.insertBatch(list);
        LOGGER.info("存储数据库成功！");
    }
}

7.mapper层

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wl.excell.dao.UserDao">
    <select id="selectAll" resultMap="userCommon">
       select * from t_staff_inf
    </select>

    <insert id="insertBatch" parameterType="java.util.List">
        insert into t_staff_inf2 values
        <foreach collection="list" separator="," index="index" item="item">
         (#{item.staffNO},#{item.staffNAME},#{item.POSITION},#{item.TITLE},#{item.OPENID},#{item.EMAIL},#{item.DEPARTMENT},#{item.ORGANIZATION},#{item.currentTotalPOINTS},#{item.totalPOINTS},#{item.lastUPDATETIME},#{item.beginTIME})
        </foreach>
    </insert>

    <resultMap id="userCommon" type="com.wl.excell.dto.UserDTO">
        <id property="staffNO" column="STAFF_NO"/>
        <result property="staffNAME" column="STAFF_NAME"/>
        <result property="POSITION" column="POSITION"/>
        <result property="TITLE" column="TITLE"/>
        <result property="OPENID" column="OPENID"/>
        <result property="EMAIL" column="EMAIL"/>
        <result property="DEPARTMENT" column="DEPARTMENT"/>
        <result property="ORGANIZATION" column="ORGANIZATION"/>
        <result property="currentTotalPOINTS" column="CURRENT_TOTAL_POINTS"/>
        <result property="totalPOINTS" column="TOTAL_POINTS"/>
        <result property="lastUPDATETIME" column="LAST_UPDATE_TIME"/>
        <result property="beginTIME" column="BINDING_TIME"/>
    </resultMap>
</mapper>```