poi 读取xls实现批量导入

建表数据:

mysql> show create table t_user \G;
*************************** 1. row ***************************
       Table: t_user
Create Table: CREATE TABLE `t_user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `phone` varchar(20) default NULL,
  `email` varchar(20) default NULL,
  `qq` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

User实体类:

package com.poi.tom;

public class User {
	private int id;
	private String nameString;
	private String phoneString;
	private String email;
	private String qq;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getNameString() {
		return nameString;
	}
	public void setNameString(String nameString) {
		this.nameString = nameString;
	}
	public String getPhoneString() {
		return phoneString;
	}
	public void setPhoneString(String phoneString) {
		this.phoneString = phoneString;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getQq() {
		return qq;
	}
	public void setQq(String qq) {
		this.qq = qq;
	}
	
}

poi.xls:

编号	姓名	联系电话	Email	QQ
1	张三	123	1234567@qq2233.com	11
7	张三1	1234	1234567@qq.com	22
9	张三2	12345	1234567@qq.com	33
12	张三3	123456	1234567@qq.com	44
13	张三4	1234567	1234567@qq.com	55
14	张三5	12345678	1234567@qq.com	66
15	张三6	123456789	1234567@qq.com	77
16	张三7	1	111321@121331.com	88
19	张三8	2	21@q33.com	99
20	张三9	3	231@qq2.com	0
21	张三10	4	12345672233@qq.com	1

poi.xls在F盘

用Spring batch operation

package com.poi.tom;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class TestInsertUsersIntoDBUseTemplate {
	private static Logger LOGGER = LoggerFactory.getLogger(TestInsertUsersIntoDBUseTemplate.class);
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			LOGGER.error("driver is not found!");
		}
	}
	static DriverManagerDataSource dataSource = new DriverManagerDataSource("jdbc:mysql://localhost:3306/poi", "root", "root");
	static JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
	public static void main(String[] args) throws Exception{
		
		InputStream is = new FileInputStream(new File("F:/poi.xls"));
		Workbook wb = new HSSFWorkbook(is);
		Sheet sheet = wb.getSheetAt(0);
		LOGGER.info("total row num is "+sheet.getLastRowNum());
		List<User> userList = new ArrayList<User>();
		for(int i=1;i<=sheet.getLastRowNum();i++){
			if(sheet.getRow(i)==null){
				continue;
			}else {
					User user = new User();
					user.setNameString(getCellValue(sheet.getRow(i).getCell(1)));
					user.setPhoneString(getCellValue(sheet.getRow(i).getCell(2)));
					user.setEmail(getCellValue(sheet.getRow(i).getCell(3)));
					user.setQq(getCellValue(sheet.getRow(i).getCell(4)));
					userList.add(user);
			}
		}
		LOGGER.info(userList.size()+"");
		batchUpdate(userList);
		
	}
	public static String getCellValue(Cell cell){
		if (cell==null) {
			return " ";
		}else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
			double d = cell.getNumericCellValue();
			return String.valueOf(Double.valueOf(d).intValue());
		}else {
			return cell.getStringCellValue();
		}
	}
	public static int[] batchUpdate(final List<User> userList) {
		int[] updateCounts = jdbcTemplate.batchUpdate(
		"insert into t_user(name,phone,email,qq)values(?,?,?,?)",
			new BatchPreparedStatementSetter() {
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					ps.setString(1, ((User)userList.get(i)).getNameString());
					ps.setString(2, ((User)userList.get(i)).getPhoneString());
					ps.setString(3, ((User)userList.get(i)).getEmail());
					ps.setString(4, ((User)userList.get(i)).getQq());
				}
				public int getBatchSize() {
					return userList.size();
				}
			} 
		);
		return updateCounts;
	}
}

查询:

mysql> select * from t_user;
+-----+--------+-----------+--------------------+------+
| id  | name   | phone     | email              | qq   |
+-----+--------+-----------+--------------------+------+
| 105 | 张三   | 123       | 1234567@qq2233.com | 11   |
| 106 | 张三1  | 1234      | 1234567@qq.com     | 22   |
| 107 | 张三2  | 12345     | 1234567@qq.com     | 33   |
| 108 | 张三3  | 123456    | 1234567@qq.com     | 44   |
| 109 | 张三4  | 1234567   | 1234567@qq.com     | 55   |
| 110 | 张三5  | 12345678  | 1234567@qq.com     | 66   |
| 111 | 张三6  | 123456789 | 1234567@qq.com     | 77   |
| 112 | 张三7  | 1         | 111321@121331.com  | 88   |
| 113 | 张三8  | 2         | 21@q33.com         | 99   |
| 114 | 张三9  | 3         | 231@qq2.com        | 0    |
| 115 | 张三10 | 4         | 12345672233@qq.com | 1    |
+-----+--------+-----------+--------------------+------+
11 rows in set (0.00 sec)

复杂点的可以将数据放在任意位置，去导入，可以划一个range，有待研究，另外hardcode比较拿不出去.