读取excel数据插入表

来源：互联网发布：暗网8级网络编辑：程序博客网时间：2024/06/08 12:33

1）准备工作-jar包

a、mysql-connector-java-5.1.20-bin.jar

b、ojdbc14.jar

c、poi-3.10.1-20140818.jar

d、poi-excelant-3.10.1-20140818.jar

e、poi-ooxml-3.10.1-20140818.jar

f、poi-ooxml-schemas-3.10.1-20140818.jar

g、poi-scratchpad-3.10.1-20140818.jar

2）实施-创建普通Java工程，编写代码

// 输入参数判断// 1）hostFileUrl设备文件路径(从设备管理列表导出的excel文件)// 2）DB_URL// 3）Username// 4）Passwordif (args == null || args.length < 4) {System.out.println("传递的参数不足4个【hostFilePath,Db_url,Username,Password】,(含空格用双引号引起)参数之间用空格分开，路径不能含中文。");return;}// 获取JDBC连接Connection connection = getConnection(args);Statement statement = null;// 解析数据HSSFWorkbook hwb = null;try {File file = new File(args[0]);hwb = new HSSFWorkbook(new FileInputStream(file));HSSFSheet e = hwb.getSheet("sheet名");statement = connection.createStatement();for (int i = 1; i <= e.getLastRowNum(); ++i) {HSSFRow row = e.getRow(i);Map<Integer, String> map = new HashMap<Integer, String>();for (int j = 0; j < row.getLastCellNum(); ++j) {HSSFCell cell = row.getCell(j);cell.setCellType(1);map.put(Integer.valueOf(j), cell.getStringCellValue());}// 查询主机是否存在if(StringUtils.isNullOrEmpty(map.get(0))){continue;}ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");if(!resultSet.next()){continue;}// 插入数据库连接StringBuffer sqlStr = new StringBuffer();strBuffer.append("INSERT INTO table values(...)");statement.addBatch(strBuffer.toString());}e = hwb.getSheet("sheet名");for (int i = 1; i <= e.getLastRowNum(); ++i) {HSSFRow row = e.getRow(i);Map<Integer, String> map = new HashMap<Integer, String>();for (int j = 0; j < row.getLastCellNum(); ++j) {HSSFCell cell = row.getCell(j);String value = "";if(cell != null){cell.setCellType(1);value = cell.getStringCellValue();}map.put(Integer.valueOf(j), value);}// 查询主机是否存在if(StringUtils.isNullOrEmpty(map.get(0))){continue;}ResultSet resultSet = statement.executeQuery("select * from `hosts` where `name`='" + map.get(1) + "'");if(!resultSet.next()){continue;}// 插入数据库连接StringBuffer strBuffer = new StringBuffer();strBuffer.append("INSERT INTO table values(...)");statement.addBatch(strBuffer.toString());}statement.executeBatch();System.out.println("数据插入成功.");} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}finally{try {System.out.println("关闭连接.");statement.close();connection.close();} catch (SQLException e) {e.printStackTrace();}}}private static Connection getConnection(String[] args) {// 获取参数String url = args[1];String user = args[2];String password = args[3];// 设置驱动名称String driverName = "oracle.jdbc.driver.OracleDriver";if (url.contains("jdbc:mysql")) {driverName = "com.mysql.jdbc.Driver";}// 加载驱动,建立连接Connection con = null;try {Class.forName(driverName);con = DriverManager.getConnection(url, user, password);System.out.println("db连接成功.");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} return con;}

3）打成jar包，在命令行运行

0 0