使用POI操作Excel将数据导入mysql

原本的工程：POI-Excel.zip（下载）【需要自己注册了一个码云帐号才可以下载】

类似的工程：（csdn下载页面）

 利用POI将excel数据导入数据库目前还是比较流行，但是表格某些字段需要进行特殊处理，比如日期，excel的日期在java里面读出来是一个数字（number）并非日期格式的字符串，等等。

1 . 首先看一下本次讲解的工程目录树

 

2 . 看一下需要导入的excel的规律，然后再在数据库建立相对应字段的数据表

2 . 在数据库建立对应的数据表(id为表中的序号)

3 . 导入POI相关jar包以及数据库连接的jar

4 . 建立数据库连接类

db.properties

DBUtil.java

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
	static String driver;
	static String username;
	static String pwd;
	static String url;

	static {

		try {
			ClassLoader classLoader = DBUtil.class.getClassLoader();

			InputStream is = classLoader.getResourceAsStream("config/props/db.properties");

			// System.out.println(is.available());

			Properties props = new Properties();
			props.load(is);

			url = props.getProperty("url");
			username = props.getProperty("user");
			pwd = props.getProperty("password");
			driver = props.getProperty("driver");

			Class.forName(driver);
		} catch (IOException e) {

			e.printStackTrace();
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException {
		Connection conn = (Connection) DriverManager.getConnection(url, username, pwd);
		if (conn == null) {
			System.out.println("Failed to connect database...");
		} else {
			System.out.println("database connected successful...");
		}
		return conn;

	}

	public static void release(ResultSet rs, PreparedStatement sta, Connection conn) {

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (sta != null) {
			try {
				sta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("Resource release successful...");
	}

	public static void release(PreparedStatement sta, Connection conn) {
		if (sta != null) {
			try {
				sta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("Resource release successful...");
	}

}

5 . 建立model存储器（Course.java）

public class Course {

	private Integer id;
	private String dept;
	private String course;
	private double credit;
	private String book_num;
	private String publish_date;
	private String book_name;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getDept() {
		return dept;
	}
	public void setDept(String dept) {
		this.dept = dept;
	}
	public String getCourse() {
		return course;
	}
	public void setCourse(String course) {
		this.course = course;
	}
	public double getCredit() {
		return credit;
	}
	public void setCredit(double credit) {
		this.credit = credit;
	}
	public String getBook_num() {
		return book_num;
	}
	public void setBook_num(String book_num) {
		this.book_num = book_num;
	}
	public String getPublish_date() {
		return publish_date;
	}
	public void setPublish_date(String publish_date) {
		this.publish_date = publish_date;
	}
	public String getBook_name() {
		return book_name;
	}
	public void setBook_name(String book_name) {
		this.book_name = book_name;
	}
}

6 .函数入口，根据入口再看看入口方法里面到底怎么处理（Main.java）

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
public class Main {
	
	public static void main(String[] args) throws FileNotFoundException, IOException {
		String path = "E:/test.xlsx";
		InputStream in = new FileInputStream(new File(path));
		InsertExcel.start(in,path);		
	}	
}

7 . Main执行start方法，然后看下 InsertExcel.java

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.lee.dao.CourseImp;
import org.lee.model.Course;

public class InsertExcel {

	public InsertExcel(){}
	
	public static void start(InputStream in ,String path) throws FileNotFoundException, IOException {
		Workbook book = getWorkBook(in,path);   //1.获取工作簿
		List<Sheet> sheets = getSheets(book);   //2.获取所有工作表
		SheetIterator(sheets);    //3.对所有工作表进行操作	
	}
	
	//1.获取工作簿
	public static Workbook getWorkBook(InputStream in,String path) throws FileNotFoundException, IOException {
		return path.endsWith(".xls") ? (new HSSFWorkbook(in))
				: (path.endsWith(".xlsx") ? (new XSSFWorkbook(in)) : (null));
	}
	//2.获取所有工作表
	private static List<Sheet> getSheets(Workbook book) {
		int numberOfSheets = book.getNumberOfSheets();
		System.out.println("numberOfSheets:" + numberOfSheets);
		List<Sheet> sheets = new ArrayList<Sheet>();
		for (int i = 0; i < numberOfSheets; i++) {
			sheets.add(book.getSheetAt(i));
		}
		return sheets;
	}
	//3.对所有工作表进行操作
	private static void SheetIterator(List<Sheet> sheets) {
		for (int i = 0; i < sheets.size(); i++) {    //循环每一张工作表
			Sheet sheet = sheets.get(i);
			if (sheet.getLastRowNum() > 1) {    //判断是否为空表，获取有数据的最后一行的行数。如果为零则为空表
				System.out.println(sheet.getSheetName() + "=============");     //打印不为空的工作表名字
			}
			Iterator<Row> iterator = sheet.iterator();   //迭代器
			//用两个while循环遍历所有单元格
			while (iterator.hasNext()) {           //遍历每一行
				Row nextRow = iterator.next();
				if (nextRow.getRowNum() < 1) {
					continue;    
					//nextRow.getRowNum()就是获取行数，由表中看出第一行(getRowNum()=0)为表头，直接跳过
				}
				
				//从第二行开始是有用的数据，要保存早数据库，第二行：nextRow.getRowNum()=1
				Iterator<Cell> cellIterator = nextRow.cellIterator();
				Course course = new Course();
				while (cellIterator.hasNext()) {         //遍历每一行的每一列
					Cell cell = cellIterator.next();
					switch(cell.getColumnIndex()){
					case 0:
						//将单元格内容设置为String类型，也可以这样写cell.setCellType(Cell.CELL_TYPE_STRING);
						cell.setCellType(1);  
						course.setId(Integer.parseInt(cell.getStringCellValue()));
						break;
					case 1:   //第二列（系）
						cell.setCellType(1);  
						course.setDept(cell.getStringCellValue());
						break;
					case 2:   //第三列（课程）
						cell.setCellType(1);
						course.setCourse(cell.getStringCellValue());
						break;
					case 3:
						cell.setCellType(1);
						course.setCredit(Double.parseDouble(cell.getStringCellValue()));
						break;
					
                   case 4:
                	   cell.setCellType(1);
                	   course.setBook_num(cell.getStringCellValue());
						break;
                   case 5:   //第六列是日期，需要进行特殊处理
                  	    //将单元格内容设置为number类型，也可以这样写cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                	    cell.setCellType(0);
						SimpleDateFormat ftm = new SimpleDateFormat("yyyy-MM-dd");
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							course.setPublish_date(ftm.format(date));  
							//如果这里打印的话，还是会打印出数字，但是保存到数据库会变成日期格式
						}
						break;
                   case 6:
                	   cell.setCellType(1);
                	   course.setBook_name(cell.getStringCellValue());
						break;
					}	
					System.out.print("   ");
				}
				
				try {
					//到这里已经遍历完一行Execl的所有单元格，并存储到model里面了，现在调用方法保存到数据库
					CourseImp c = new CourseImp();  
					if(c.Exist(course.getId())==true){
						System.out.println("此id对象已存在于数据库,不可重复添加");
						continue;
					}else{
						c.save(course);
					}
					
				} catch (Exception e) {
					e.printStackTrace();
				}
			
				System.out.println("   ");
			}
			System.out.println("   ");
		}
	}
	
}

8 . 保存到数据库的方法（CourseImp.java）

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.lee.model.Course;
import org.lee.util.DBUtil;

public class CourseImp{

	private static final String sql_findById = " SELECT * FROM courses WHERE id = ?";
	private static final String sql_save = " INSERT INTO courses(id,dept,course,credit,book_num,publish_date,book_name) VALUES(?,?,?,?,?,?,?)";
	
	public boolean Exist(Integer id) throws Exception {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		boolean exist = false;
		try {
			conn = DBUtil.getConnection();
			prep = conn.prepareStatement(sql_findById);
			prep.setInt(1, id);
			rs  = prep.executeQuery();

			 if(rs.next() == true){
				
				exist = true;
			 }else{
				 exist = false;
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.release( prep, conn);
		}
		return exist;
	}
	
	public Integer save(Course course) throws Exception {
		Connection conn = null;
		PreparedStatement prep = null;
        int i = 0;
		try {
			conn = DBUtil.getConnection();
			prep = conn.prepareStatement(sql_save);
			prep.setInt(1, course.getId());
			prep.setString(2, course.getDept());
			prep.setString(3, course.getCourse());
			prep.setDouble(4, course.getCredit());
			prep.setString(5, course.getBook_num());
			prep.setString(6, String.valueOf(course.getPublish_date()));
			prep.setString(7, course.getBook_name());
			i  = prep.executeUpdate();

			 if(i>0){
				System.out.println("1 row affected...");
			 }else if(i==0){
				 System.out.println("insert failed...");
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.release( prep, conn);
		}
		return i;
	}

}

9 . 运行main函数，成功导入数据库

10.POI操作Excel完整流程已经全部写出来了，谢谢大家的支持