EasyExcel实现100w数据导入导出

数据导入

druid.properties配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/spring?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
username=root
password=1234
initialSize=10
maxActive=50
maxWait=60000

实体类

/**
 * @Name TestPojo
 * @Author qzz
 * @Date 2022/11/2 8:56
 */
@Data
@TableName("100w_test")
public class TestPojo {

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

    @ExcelProperty(value = "name",index = 1)
    private String name;
    @ExcelProperty(value = "age",index = 2)
    private Integer age;
    @ExcelProperty(value = "bir",index = 3)
    private Integer bir;

}

JDBC工具类

//JDBC工具类
public class JDBCDruidUtils {
    private static DataSource dataSource;

    /*
   创建数据Properties集合对象加载加载配置文件
    */
    static {
        Properties pro = new Properties();
        //加载数据库连接池对象
        try {
            //获取数据库连接池对象
            pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*
    获取连接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    /**
     * 关闭conn,和 statement独对象资源
     *
     * @param connection
     * @param statement
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭 conn , statement 和resultset三个对象资源
     *
     * @param connection
     * @param statement
     * @param resultSet
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(connection, statement);
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
    获取连接池对象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

EasyExcel监听类

// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 处理业务逻辑的Service,也可以是Mapper
     */
    private Service testService;

    /**
     * 用于存储读取的数据
     */
    private final List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();

    public EasyExceGeneralDatalListener() {
    }
    
    public EasyExceGeneralDatalListener(Service testExcelMapper) {
        this.testService = testExcelMapper;
    }
    
    /**
     * easyexcel每读取一行都会调用此方法
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        //数据add进入集合
        dataList.add(data);
        //size是否为500000条:这里其实就是分批.当数据等于10w的时候执行一次插入
        if (dataList.size() >= 500000) {
            //存入数据库:数据小于1w条使用Mybatis的批量插入即可;
            saveData();
            //清理集合便于GC回收
            dataList.clear();
        }
    }

    /**
     * 保存数据到DB
     *
     * @param
     * @MethodName: saveData
     * @return: void
     */
    private void saveData() {
        testService.import2DBFromExcel10w(dataList);
        dataList.clear();
    }
    
    /**
     * Excel中所有数据解析完毕会调用此方法
     *
     * @param: context
     * @MethodName: doAfterAllAnalysed
     * @return: void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}

插入数据库时具体代码

/**
 * @Name Service
 * @Author qzz
 * @Date 2022/11/2 9:18
 */

public class Service {
    public Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {
        HashMap<String, Object> result = new HashMap<>();
        //结果集中数据为0时,结束方法.进行下一次调用
        if (dataList.size() == 0) {
            result.put("empty", "0000");
            return result;
        }
        //JDBC分批插入+事务操作完成对10w数据的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
            conn = JDBCDruidUtils.getConnection();
            //控制事务:默认不提交
            conn.setAutoCommit(false);
            String sql = "insert into 100w_test (id,name,age,bir) values";
            sql += "(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //循环结果集:这里循环不支持"烂布袋"表达式
            for (int i = 0; i < dataList.size(); i++) {
                Map<Integer, String> item = dataList.get(i);
                ps.setString(1, item.get(0));
                ps.setString(2, item.get(1));
                ps.setString(3, item.get(2));
                ps.setString(4, item.get(3));
                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }
            //执行批处理
            ps.executeBatch();
            //手动提交事务
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
            System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
            result.put("success", "1111");
        } catch (Exception e) {
            result.put("exception", "0000");
            e.printStackTrace();
        } finally {
            //关连接
            JDBCDruidUtils.close(conn, ps);
        }
        return result;
    }
}

数据导出具体代码

/**
 * @Name Controller
 * @Author qzz
 * @Date 2022/11/2 12:35
 */
@Controller
public class ControllerTest {
    @Autowired
    private Mybatis mybatis;

    //导出逻辑代码
    @GetMapping("/test")
    public void dataExport300w(HttpServletResponse response) {
        {
            OutputStream outputStream = null;
            try {
                long startTime = System.currentTimeMillis();
                System.out.println("导出开始时间:" + startTime);

                outputStream = response.getOutputStream();

                WriteWorkbook workbook = new WriteWorkbook();
                workbook.setOutputStream(outputStream);
                workbook.setExcelType(ExcelTypeEnum.XLSX);
                ExcelWriter writer = new ExcelWriter(workbook);
                String fileName = new String(("excel100w").getBytes(), StandardCharsets.UTF_8);

                //title
                WriteTable table = new WriteTable();
                List<List<String>> titles = new ArrayList<List<String>>();
                titles.add(Arrays.asList("id"));
                titles.add(Arrays.asList("name"));
                titles.add(Arrays.asList("age"));
                titles.add(Arrays.asList("bir"));
                table.setHead(titles);

                //模拟统计查询的数据数量这里模拟100w
                //int count = 3000001;
                //记录总数:实际中需要根据查询条件进行统计即可
                Integer totalCount = mybatis.selectCount(null);
                //每一个Sheet存放100w条数据
                Integer sheetDataRows = 1000000;
                //每次写入的数据量20w
                Integer writeDataRows = 200000;
                //计算需要的Sheet数量
                Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
                //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
                Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
                //计算最后一个sheet需要写入的次数
                Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

                //开始分批查询分次写入
                //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
                List<List<String>> dataList = new ArrayList<>();
                for (int i = 0; i < sheetNum; i++) {
                    //创建Sheet
                    WriteSheet sheet = new WriteSheet();
                    sheet.setSheetName("测试Sheet1" + i);
                    //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                    for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                        //集合复用,便于GC清理
                        dataList.clear();
                        //分页查询一次20w
                        Page<TestPojo> page = new Page<>(j + 1 + oneSheetWriteCount * i, writeDataRows);
//                        PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);

                        mybatis.selectPage(page, null);
                        List<TestPojo> pageRecords = page.getRecords();
//                        List<ActResultLog> reslultList = actResultLogMapper.findByPage100w();
                        if (!CollectionUtils.isEmpty(pageRecords)) {
                            pageRecords.forEach(item -> {
                                dataList.add(Arrays.asList(item.getId().toString(),item.getName(),item.getAge().toString(),item.getBir().toString()));
                            });
                        }
                        //写数据
                        writer.write(dataList, sheet, table);
                    }
                }

                // 下载EXCEL
                response.setHeader("Content-Disposition", "attachment;filename="
                        + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                writer.finish();
                outputStream.flush();
                //导出时间结束
                long endTime = System.currentTimeMillis();
                System.out.println("导出结束时间:" + endTime + "ms");
                System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (outputStream != null) {
                    try {
                        outputStream.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}

参考https://blog.csdn.net/weixin_44848900/article/details/117701981