Java的poi技术一-------------读取excel到MySql

Java poi 技术可从官网（http://poi.apache.org/）上得知这是java连接Microsoft 文档的API接口

Apache POI是Apache软件基金会提供的100％开源库。大多数中小规模的应用程序开发主要依赖于Apache POI（HSSF+ XSSF）。它支持Excel 库的所有基本功能; 然而，呈现和文本提取是它的主要特点。

poi的jar包地址：链接: https://pan.baidu.com/s/1mieCRMC 密码: vjyh

1.项目目录结构如下：

2.测试数据如下：

3.数据库结构

4.sql文件

/*
Navicat MySQL Data Transfer

Source Server         : mysqlData
Source Server Version : 50717
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50717
File Encoding         : 65001

Date: 2017-05-20 14:21:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for player_info
-- ----------------------------
DROP TABLE IF EXISTS `player_info`;
CREATE TABLE `player_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no` varchar(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` varchar(10) DEFAULT NULL,
  `score` float DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.Client类----用来创建数据库，主函数从xls文件中保存数据到数据库

com.lrq.client

package com.lrq.client;
import java.io.IOException;
import java.sql.SQLException;
import com.lrq.excel.SaveData2DB;
/**
 * @author HaleyLiu
 * @created 2017-5-20
 */
public class Client {
	public static void main(String[] args) throws IOException, SQLException {
		SaveData2DB saveData2DB = new SaveData2DB();
		saveData2DB.save();
		System.out.println("end");
	}
}

6.Common类-----作为数据库属性类

com.lrq.common

/**
 * 
 */
package com.lrq.common;

/**
 * @author HaLeyLiu
 * @created 2017-5-20
 */
public class Common {

	// connect the database
	public static final String DRIVER = "com.mysql.jdbc.Driver";
	public static final String DB_NAME = "test";
	public static final String USERNAME = "root";
	public static final String PASSWORD = "12345";
	public static final String IP = "localhost";
	public static final String PORT = "3306";
	public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME;
	
	// common
	public static final String EXCEL_PATH = "lib/player_info.xls";
	
	// sql
	public static final String INSERT_STUDENT_SQL = "insert into player_info(no, name, age, score) values(?, ?, ?, ?)";
	public static final String UPDATE_STUDENT_SQL = "update player_info set no = ?, name = ?, age= ?, score = ? where id = ? ";
	public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from player_info";
	public static final String SELECT_STUDENT_SQL = "select * from player_info where name like ";
}

7.com.lrq.execel包下的几个类

（1）DBUtil数据库连接工具类

/**
 * 
 */
package com.lrq.excel;
import com.lrq.common.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * @author HaleyLiu
 * @created 2017-5-20
 */
public class DbUtil {

	/**
	 * @param sql
	 */
	public static void insert(String sql,Player player) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName(Common.DRIVER);
			conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);
			ps = conn.prepareStatement(sql);
			ps.setString(1, player.getNo());
			ps.setString(2, player.getName());
			ps.setString(3, player.getAge());
			ps.setString(4, String.valueOf(player.getScore()));
			boolean flag = ps.execute();
			if(!flag){
				System.out.println("Save data : No. = " + player.getNo() + " , Name = " + 
						player.getName() + ", Age = " + player.getAge() + " succeed!");
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static List selectOne(String sql, Player player) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			Class.forName(Common.DRIVER);
			conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				if(rs.getString("no").equals(player.getNo()) || rs.getString("name").equals(player.getName())|| rs.getString("age").equals(player.getAge())){
					list.add(1);
				}else{
					list.add(0);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return list;
	}
	
	
	public static ResultSet selectAll(String sql) throws SQLException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Class.forName(Common.DRIVER);
			conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return rs;
	}

}

（2）Player玩家类

package com.lrq.excel;

public class Player {
	/**
	 * id、
	 */
	private Integer id;
	
	/**
	 * 编号
	 */
	private String no;
	
	/**
	 * 姓名
	 */
	private String name;
	
	/**
	 * 年龄
	 */
	private String age;
	
	/**
	 * 得分
	 */
	private double score;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getNo() {
		return no;
	}

	public void setNo(String no) {
		this.no = no;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAge() {
		return age;
	}

	public void setAge(String age) {
		this.age = age;
	}

	public double getScore() {
		return score;
	}

	public void setScore(double score) {
		this.score = score;
	}

	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((age == null) ? 0 : age.hashCode());
		result = prime * result + ((id == null) ? 0 : id.hashCode());
		result = prime * result + ((name == null) ? 0 : name.hashCode());
		result = prime * result + ((no == null) ? 0 : no.hashCode());
		long temp;
		temp = Double.doubleToLongBits(score);
		result = prime * result + (int) (temp ^ (temp >>> 32));
		return result;
	}

	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		Player other = (Player) obj;
		if (age == null) {
			if (other.age != null)
				return false;
		} else if (!age.equals(other.age))
			return false;
		if (id == null) {
			if (other.id != null)
				return false;
		} else if (!id.equals(other.id))
			return false;
		if (name == null) {
			if (other.name != null)
				return false;
		} else if (!name.equals(other.name))
			return false;
		if (no == null) {
			if (other.no != null)
				return false;
		} else if (!no.equals(other.no))
			return false;
		if (Double.doubleToLongBits(score) != Double.doubleToLongBits(other.score))
			return false;
		return true;
	}

（3）读取Excel类

package com.lrq.excel;

import java.io.FileInputStream;
import com.lrq.common.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
 * @author HaleyLiu
 * @created 2017-5-20
 */
public class ReadExcel {

	public List<Player> readXls() throws IOException {
		InputStream is = new FileInputStream(Common.EXCEL_PATH);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		Player player= null;
		List<Player> list = new ArrayList<Player>();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				continue;
			}
			// 循环行Row
			for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				if (hssfRow != null) {
					player = new Player();
					HSSFCell no = hssfRow.getCell(0);
					HSSFCell name = hssfRow.getCell(1);
					HSSFCell age = hssfRow.getCell(2);
					HSSFCell score = hssfRow.getCell(3);
					player.setNo(getValue(no));
					player.setName(getValue(name));
					player.setAge(getValue(age));
					player.setScore(Double.valueOf(getValue(score)));
					list.add(player);
				}
			}
		}
		return list;
	}
	
	 @SuppressWarnings("static-access")
	private String getValue(HSSFCell hssfCell) {
	        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
	            // 返回布尔类型的值
	            return String.valueOf(hssfCell.getBooleanCellValue());
	        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
	            // 返回数值类型的值
	            return String.valueOf(hssfCell.getNumericCellValue());
	        } else {
	            // 返回字符串类型的值
	            return String.valueOf(hssfCell.getStringCellValue());
	        }
	    }
}

（4）保存数据库

/**
 * 
 */
package com.lrq.excel;
import com.lrq.common.*;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;


/**
 * @author HaleyLiu
 * @created 2017-5-20
 */
public class SaveData2DB {

	@SuppressWarnings({ "rawtypes" })
	public void save() throws IOException, SQLException {
		ReadExcel xlsMain = new ReadExcel();
		Player player = null;
		List<Player> list = xlsMain.readXls();
		for (int i = 0; i < list.size(); i++) {
			player = list.get(i);
			List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + player.getName() + "%'", player);
			if (!l.contains(1)) {
				DbUtil.insert(Common.INSERT_STUDENT_SQL, player);
			} else {
				System.out.println("The Record was Exist : No. = " + player.getNo() + " , Name = " + player.getName() + ", Age = " + player.getAge() + ", and has been throw away!");
			}
		}
	}
}

8.测试结果：

数据库也有结果了：