一个工具类搞定批量插入增加数据到Mysql数据库

package oaec.util; import java.io.FileInputStream; import java.io.InputStream; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletRequest; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import oaec.entity.User; import org.springframework.stereotype.Repository; @Repository public class BulkImportUtil { // 解析EXCEL文件 public List<User> analysisXLS(HttpServletRequest request,String fileName) throws Exception { // String filename=request.getParameter("filenamesys"); System.out.println("解析EXCEL文件========fileName==============" + fileName); InputStream is = new FileInputStream(fileName);// 创建输入 Workbook rwb = Workbook.getWorkbook(is); Sheet rs = rwb.getSheet(0); System.out.println("====sheets名称====" + rs.getName()); if (rwb.getSheets().length == 0) { System.out.println("EXCEL文件为空或格式不对，请确认后重新导入!"); } int colNum = rs.getColumns();// 列数 int rowNum = rs.getRows();// 行数 if (rowNum < 2) { System.out.println("导入的EXCEL中无记录!至少有一行有效记录"); } // rowNum=rowNum-1; System.out.println("colNum rowNum------------------" + rowNum + "行," + colNum + "列"); String strValue1 = ""; String strValue2 = ""; String strValue3 = ""; String strValue4 = ""; String strValue5 = ""; String strValue6 = ""; String strValue7 = ""; String strValue8 = ""; String strValue9 = ""; int j = 0; // 得到导入成功的条数 int y = 0; // 得到导入失败的条数 // List<Wall> list=new ArrayList<Wall>(); // Wall wa=new Wall(); String errorId = ""; String sum = "0";// 记录哪些行是不正确的 List<User> users=new ArrayList<User>(); for (int i = 1; i < rowNum; i++) {// 从第二行开始取数据 Cell c1 = rs.getCell(0, i); strValue1 = c1.getContents() == null ? "" : c1.getContents().trim();// 第1列的是角色id Cell c2 = rs.getCell(1, i); strValue2 = c2.getContents() == null ? "" : c2.getContents().trim();// 第2列的是姓名 Cell c3 = rs.getCell(2, i); strValue3 = c3.getContents() == null ? "" : c3.getContents().trim();// 第3列的是密码 Cell c4 = rs.getCell(3, i); strValue4 = c4.getContents() == null ? "" : c4.getContents().trim();// 第4列的是性别 Cell c5 = rs.getCell(4, i); strValue5 = c5.getContents() == null ? "" : c5.getContents().trim();// 第5列的是地址 Cell c6 = rs.getCell(5, i); strValue6 = c6.getContents() == null ? "" : c6.getContents().trim();// 第6列的是加入时间 Cell c7 = rs.getCell(6, i); strValue7 = c7.getContents() == null ? "" : c7.getContents().trim();// 第7列的是邮箱 Cell c8 = rs.getCell(7, i); strValue8 = c8.getContents() == null ? "" : c8.getContents().trim();// 第8列的是激活状态 Cell c9 = rs.getCell(8, i); strValue9 = c9.getContents() == null ? "" : c9.getContents().trim();// 第8列的是电话号码 System.out.println("数据获取成功。！" + strValue1 + "," + strValue2+ "," + strValue3+ "," + strValue4+ "," + strValue5+ "," + strValue6+ "," + strValue7+ "," + strValue8+ "," + strValue9); if (strValue1.equals("") ||strValue2.equals("") ) { y++; sum = sum + "," + i; continue; } User user = new User(); user.setRoleid(Integer.parseInt(strValue1)); user.setUserName(strValue2); user.setPass(strValue3); user.setSex(strValue4); user.setAddress(strValue5); user.setJoinDate(strToDate(strValue6)); user.setEmail(strValue7); user.setIsEnable(Integer.parseInt(strValue8)); user.setTelPhone(strValue9); j++; users.add(user); // new UserDao().addUser(user);//将解析出来的数据添加到数据库 } return users; } /** * @throws ParseException * @author徐亮 * 将形如yyyy-MM-dd的字符串转化为java.util.Date * 2016-04-15 */ public Date strToDate(String str) throws ParseException{ SimpleDateFormat sdf=new SimpleDateFormat(); sdf.applyPattern("yyyy/MM/dd"); Date date=null; date=sdf.parse(str); return date; } }