Java uses EasyExcel to import and export data

Java uses EasyExcel to import and export data

Article directory

• Java uses EasyExcel to import and export data
• Preface
• 1. Guide package
• 2. Export data
• 3. Import
• • 1.Controller
  • 2.service
  • 3.serviceImpl
  • 4.listener
• Summarize

Foreword

This time, in order to migrate the data of the Oracle database to MySQL, I copied it directly using Navicat. It was too slow, so I exported the data directly to Excel and used jdbc (It feels faster). Import Excel data to a MySQL database. This method is only suitable for a small number of tables. If there are too many tables, it will be too difficult to write.

1. Guide package

<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>

2. Export data

NetLogEntity is an entity class. The query method uses MybatisPlus. The getTable() method is to set the table header. You can cancel the last parameter in the excelWriter.write(dataList, getSheet(), getTable()) line.

 @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());

            //Download 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;
    }

3. Import

1.Controller

 @Autowired
    private NetLogService netLogService;

    @PassToken
    @RequestMapping("importExcelInfoToDatabase")
    public void importExcelInfoToDatabase(){<!-- -->

        long startTime = System.currentTimeMillis();
        System.out.println("Start time:" + startTime + "ms");
        String filePath = "C:\Users\Administrator\Downloads\\
etLog.xlsx";

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

        long endTime = System.currentTimeMillis();
        System.out.println("End Time:" + endTime + "ms");
        System.out.println("Time taken:" + (endTime - startTime)/1000 + "ms");
    }

2.service

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

3.serviceImpl

It feels like jdbc is faster

 @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 & amp;characterEncoding=UTF-8 & amp;useJDBCCompliantTimezoneShift=true & amp;useLegacyDatetimeCode=false & amp;serverTimezone=Asia/Shanghai & amp;useSSL=false & amp;allowPublicKeyRetrieval=true & amp;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));
                }

                //Add a set of parameters to this PreparedStatement object's batch command.
                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

Every 100,000 pieces of data are written to the database, and the list must be cleaned each time.

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);

        //Determine whether 10W items have been read, and insert when reached
        //Insert in batches
        if (list.size() > 100000){<!-- -->
            saveData();
            list.clear();
        }
    }

    public void saveData(){<!-- -->
        netLogService.importData(list);
    }

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

Summary

Different versions of EasyExcel, excelWriter.write(dataList, getSheet(), getTable()), the parameter page passed in this has changed, but you can still pass whatever you need, just use new and it’s done .