Java使用EasyExcel导入导出数据

Java使用EasyExcel导入导出数据

---

前言

本次是为了将oracle数据库的数据迁移到MySQL，直接用Navicat复制吧，又太慢了，所以直接将数据导出到Excel，又使用jdbc（感觉速度要快些）将Excel数据导到MySQL数据库，此方法只适合少量表，太多的话太难写了。

---

一.导包

<dependency>
   <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
    <exclusions>
        <exclusion>
            <artifactId>poi-ooxml-schemas</artifactId>
            <groupId>org.apache.poi</groupId>
        </exclusion>
    </exclusions>
</dependency>

---

二、导出数据

NetLogEntity为实体类，查询方式使用的MybatisPlus，getTable()方法为设置表头，可在excelWriter.write(dataList, getSheet(), getTable()) 行取消最后一个参数，

    @PassToken
    @RequestMapping("getExcelInfo")
    public void getExcelInfo(HttpServletResponse response){

        Integer integer = new NetLogEntity().selectCount(new QueryWrapper<NetLogEntity>());

        System.out.println(integer);

        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();

            WriteWorkbook writeWorkbook = new WriteWorkbook();
            writeWorkbook.setOutputStream(outputStream);
            writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
            ExcelWriter excelWriter = new ExcelWriter(writeWorkbook);

            String fileName = new String(("netLog").getBytes(), "UTF-8");

            List<List<String>> dataList = new ArrayList<>();

            List<NetLogEntity> netLogList = new NetLogEntity().selectAll();
            if (netLogList.size() > 0){
                netLogList.forEach(item -> {
                    dataList.add(Arrays.asList(item.getUuid(), item.getPkid(), item.getRealName(), item.getOperation(), item.getOperationTime(), item.getModule(), item.getSignRegionCode(), item.getLogType()));
                });
            }

            excelWriter.write(dataList, getSheet(), getTable());

            // 下载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");

            excelWriter.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static WriteTable getTable(){
        WriteTable writeTable = new WriteTable();
        List<List<String>> titles = new ArrayList<List<String>>();
        titles.add(Arrays.asList("uuid"));
        titles.add(Arrays.asList("pkid"));
        titles.add(Arrays.asList("realName"));
        titles.add(Arrays.asList("operation"));
        titles.add(Arrays.asList("operationTime"));
        titles.add(Arrays.asList("module"));
        titles.add(Arrays.asList("signRegionCode"));
        titles.add(Arrays.asList("logType"));
        writeTable.setHead(titles);

        return writeTable;
    }

    public static WriteSheet getSheet(){
        WriteSheet info = new WriteSheet();
        info.setSheetName("info");

        return info;
    }

三、导入

1.Controller

    @Autowired
    private NetLogService netLogService;

    @PassToken
    @RequestMapping("importExcelInfoToDatabase")
    public void importExcelInfoToDatabase(){

        long startTime = System.currentTimeMillis();
        System.out.println("开始时间:" + startTime + "ms");
        String filePath = "C:\\Users\\Administrator\\Downloads\\netLog.xlsx";

        EasyExcel.read(filePath, new EasyExcelGeneralDataMybatisListener(netLogService)).doReadAll();

        long endTime = System.currentTimeMillis();
        System.out.println("结束时间:" + endTime + "ms");
        System.out.println("用时:" + (endTime - startTime)/1000 + "ms");
    }

2.service

void importData(List<Map<Integer, String>> data);

3.serviceImpl

感觉jdbc速度更快

    @Override
    public void importData(List<Map<Integer, String>> data) {

        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://192.168.1.101:3306/sa?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=convertToNull";

            String userName = "root";
            String passWord = "root";

            connection = DriverManager.getConnection(url, userName, passWord);
            connection.setAutoCommit(false);

            String sql = "INSERT INTO net_log (uuid, pkid, real_name, operation, operation_time, module, sign_region_code, log_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?);";

            ps = connection.prepareStatement(sql);

            for (int i = 0; i < data.size(); i++) {
                Map<Integer, String> integerStringMap = data.get(i);

                for (int j = 0; j < integerStringMap.size(); j++) {

                    ps.setString(j+1, integerStringMap.get(j));
                }

                //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                ps.addBatch();
            }

            ps.executeBatch();
            connection.commit();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

4.listener

每10w条数据写入一次数据库，且每次都要清理list。

public class EasyExcelGeneralDataMybatisListener extends AnalysisEventListener<Map<Integer, String>> {

    private NetLogService netLogService;

    private List<Map<Integer, String>>list = new ArrayList<>();

    public EasyExcelGeneralDataMybatisListener(NetLogService netLogService) {
        this.netLogService = netLogService;
    }

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext analysisContext) {
        list.add(data);

        //判断是否已经读取10W条，达到就插入
        //分批次插入
        if (list.size() > 100000){
            saveData();
            list.clear();
        }
    }

    public void saveData(){
        netLogService.importData(list);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        list.clear();
    }
}

---

总结

EasyExcel版本的不同，excelWriter.write(dataList, getSheet(), getTable())，这个里面传的参数页发生了变化，不过还是需要什么传什么就行，直接new就完事了。