读取excel数据插入表

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）Password
		if (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包，在命令行运行