excel文件导入数据库
1、准备jar包：poi
2、前端jsp页面：
<body>
<!--
form表单中必须添加enctype="multipart/form-data"才可以上传文件
servlet中根据file名称来获取文件相对路径
-->
<form action="/phoneQuery" method="post" enctype="multipart/form-data">
<table align="center">
<tr>
<td>
请输入路径：
</td>
<td>
<input type="file" name="file"/>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="提交"/>
</td>
</tr>
</table>
</form>
3、实体类
//注释完成自动建表
1 @TableAnnotaion(tableName="PHONE_LOCATION") 2 public class PhoneInfo { 3 4 @ColumnAnnotation(name="id",length=11,type=ColumnTypeEnum.INT,key=true) 5 private int id; 6 7 @ColumnAnnotation(name="phone_segment",length=11,type=ColumnTypeEnum.INT) 8 private int phone_segment; 9 10 @ColumnAnnotation(name="province",length=128) 11 private String province; 12 13 @ColumnAnnotation(name="city",length=128) 14 private String city; 15 16 @ColumnAnnotation(name="operator",length=128) 17 private String operator; 18 19 @ColumnAnnotation(name="zone_code",length=6,type=ColumnTypeEnum.INT) 20 private int zone_code; 21 22 @ColumnAnnotation(name="zip_code",length=8,type=ColumnTypeEnum.INT) 23 private int zip_code; 24 25 public int getId() { 26 return id; 27 } 28 29 public void setId(int id) { 30 this.id = id; 31 } 32 33 public int getPhone_segment() { 34 return phone_segment; 35 } 36 37 public void setPhone_segment(int phone_segment) { 38 this.phone_segment = phone_segment; 39 } 40 41 public String getProvince() { 42 return province; 43 } 44 45 public void setProvince(String province) { 46 this.province = province; 47 } 48 49 public String getCity() { 50 return city; 51 } 52 53 public void setCity(String city) { 54 this.city = city; 55 } 56 57 public String getOperator() { 58 return operator; 59 } 60 61 public void setOperator(String operator) { 62 this.operator = operator; 63 } 64 65 public int getZone_code() { 66 return zone_code; 67 } 68 69 public void setZone_code(int zone_code) { 70 this.zone_code = zone_code; 71 } 72 73 public int getZip_code() { 74 return zip_code; 75 } 76 77 public void setZip_code(int zip_code) { 78 this.zip_code = zip_code; 79 } 80 81 @Override 82 public String toString() { 83 // TODO Auto-generated method stub 84 return ReflectionToStringBuilder.toString(this, ToStringStyle.MULTI_LINE_STYLE); 85 } 86 87 }
4、servlet文件
1 //注解中加上MultipartConfig才可以处理页面发送过来的file路径 2 3 @MultipartConfig 4 @WebServlet(name="phoneQuery",urlPatterns="/phoneQuery") 5 public class PhoneQueryServlet extends HttpServlet { 6 7 8 9 @Override 10 protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 11 this.dataImport(req, resp); 12 13 } 14 15 /** 16 * Excel文件导入 17 */ 18 public void PhoneInsert(HttpServletRequest req, HttpServletResponse resp){ 19 20 try { 21 Collection<Part> ps = req.getParts();//获取web页面输入框的集合值 22 for(Part p : ps){//分批处理输入框的集合值 23 List<PhoneInfo> phones = new ArrayList<PhoneInfo>(); 24 InputStream in = p.getInputStream();//创建输入流 25 HSSFWorkbook wb = new HSSFWorkbook(in);//创建一个workbook对象(操作/解析excel) 26 int sheetNum = wb.getNumberOfSheets();//获取总的sheet数量 27 // System.out.println("sheetNum = "+sheetNum); 28 for(int i = 0;i<sheetNum;i++){ 29 HSSFSheet sh = wb.getSheetAt(i); 30 int rowNum = sh.getLastRowNum();//获取总的行数 31 // System.out.println("rowNum = "+rowNum); 32 for(int j = 0;j<rowNum;j++){ 33 HSSFRow row = sh.getRow(j); 34 int cellNum = row.getLastCellNum();//获取总的列数 35 // System.out.println("cellNum = "+cellNum); 36 37 PhoneInfo phone = new PhoneInfo(); 38 phone.setId((int)row.getCell(0).getNumericCellValue()); 39 phone.setPhone_segment((int)row.getCell(1).getNumericCellValue()); 40 phone.setProvince(row.getCell(2).getStringCellValue()); 41 phone.setCity(row.getCell(3).getStringCellValue()); 42 phone.setOperator(row.getCell(4).getStringCellValue()); 43 phone.setZone_code((int)row.getCell(5).getNumericCellValue()); 44 phone.setZip_code((int)row.getCell(6).getNumericCellValue()); 45 // System.out.println(phone.toString()); 46 phones.add(phone); 47 // for(int k = 0;k<cellNum;k++){ 48 // HSSFCell cell = row.getCell(k);//取到每一列的单元格 49 // int cellType = cell.getCellType();//取到单元格的类型 50 // System.out.println(cellType); 51 // switch(cellType){//根据单元格类型来选择接受类型 52 // case 0://双精度浮点数 53 // double cellD = cell.getNumericCellValue(); 54 // System.out.println(cellD); 55 // break; 56 // case 1://字符串 57 // String cellS = cell.getStringCellValue(); 58 // System.out.println(cellS); 59 // break; 60 // default: 61 // System.out.println("类型不匹配"); 62 /* 63 * 2、CELL_TYPE_FORMULA 公式型 64 * 3、CELL_TYPE_BLANK 空值 65 * 4、CELL_TYPE_BOOLEAN 布尔型 66 * 5、CELL_TYPE_ERROR 错误 67 */ 68 // } 69 } 70 } 71 } 72 } catch (IOException e) { 73 // TODO Auto-generated catch block 74 e.printStackTrace(); 75 } catch (ServletException e) { 76 // TODO Auto-generated catch block 77 e.printStackTrace(); 78 } 79 80 } 81 82 dao.phoneInsert(phones); 83 84 }
5、dao层
1 /** 2 * 将数据导入数据库 3 */ 4 public void phoneInsert(List<PhoneInfo> phones){ 5 // PhoneInfo phone = new PhoneInfo(); 6 DBManager db = new DBManager(); 7 String sql = "Insert into PHONE_LOCATION values (?,?,?,?,?,?,?)"; 8 PreparedStatement state = db.getPreStatement(sql); 9 int flag = 1; 10 long startTime = System.currentTimeMillis(); 11 for(PhoneInfo phone : phones){ 12 try { 13 // long id = db.getMaxId("PHONE_LOCATION"); 14 // System.out.println((int) id); 15 state.setInt(1,phone.getId()); 16 state.setInt(2, phone.getPhone_segment()); 17 state.setString(3, phone.getProvince()); 18 state.setString(4, phone.getCity()); 19 state.setString(5, phone.getOperator()); 20 state.setInt(6, phone.getZone_code()); 21 state.setInt(7, phone.getZip_code()); 22 state.addBatch(); 23 } catch (NumberFormatException e) { 24 // TODO Auto-generated catch block 25 e.printStackTrace(); 26 } catch (SQLException e) { 27 // TODO Auto-generated catch block 28 e.printStackTrace(); 29 } 30 if(flag%2000==0||flag==phones.size()){ 31 try { 32 state.executeBatch(); 33 db.commit(); 34 System.out.println("已经处理"+flag+"条数据"); 35 } catch (SQLException e) { 36 // TODO Auto-generated catch block 37 e.printStackTrace(); 38 } 39 } 40 flag++; 41 } 42 db.close(); 43 System.out.println(System.currentTimeMillis()-startTime); 44 }
