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:

编号姓名联系电话EmailQQ1张三[e-mail]117张三[e-mail]229张三[e-mail]3312张三[e-mail]4413张三[e-mail]5514张三[e-mail]6615张三[e-mail]7716张三[e-mail]8819张三[e-mail]9920张三[e-mail]021张三[e-mail]在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       | [e-mail] | 11   |
| 106 | 张三1  | 1234      | [e-mail]     | 22   |
| 107 | 张三2  | 12345     | [e-mail]     | 33   |
| 108 | 张三3  | 123456    | [e-mail]     | 44   |
| 109 | 张三4  | 1234567   | [e-mail]     | 55   |
| 110 | 张三5  | 12345678  | [e-mail]     | 66   |
| 111 | 张三6  | 123456789 | [e-mail]     | 77   |
| 112 | 张三7  | 1         | [e-mail]  | 88   |
| 113 | 张三8  | 2         | [e-mail]         | 99   |
| 114 | 张三9  | 3         | [e-mail]        | 0    |
| 115 | 张三10 | 4         | [e-mail] | 1    |
+-----+--------+-----------+--------------------+------+
11 rows in set (0.00 sec)

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