Insurance 项目——excel导入导出数据库
这是一个java初学者在独立开发一个项目时做的笔记,很多内容仅仅是为了解决当前需求,并未很深入的研究。
项目中需要excel与数据库之间的导入和导出功能,还需要对列名进行一些匹配。
jar包
pom.xml
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 <!-- exceljar包 --> 2 <dependency> 3 <groupId>net.sourceforge.jexcelapi</groupId> 4 <artifactId>jxl</artifactId> 5 <version>2.6</version> 6 </dependency>
Excel表导入数据库:
实体类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 package com.ic.pojo; 2 3 import com.google.gson.annotations.Expose; 4 import com.google.gson.annotations.SerializedName; 5 6 public class PatientProfile extends PatientProfileKey { 7 @Expose 8 private String name; 9 @Expose 10 private String email; 11 @Expose 12 @SerializedName("phone") 13 private String phoneNumber; 14 @Expose 15 private String homeAddress; 16 @Expose 17 @SerializedName("patient_id") 18 private String userId; 19 @Expose 20 @SerializedName("usr_id") 21 private String specialistUid; 22 @Expose 23 private Long createTime; 24 @Expose 25 private Long modifyTime; 26 27 private Integer state; 28 29 private String otherInfo1; 30 31 private String otherInfo2; 32 33 public String getName() { 34 return name; 35 } 36 37 public void setName(String name) { 38 this.name = name == null ? null : name.trim(); 39 } 40 41 public String getEmail() { 42 return email; 43 } 44 45 public void setEmail(String email) { 46 this.email = email == null ? null : email.trim(); 47 } 48 49 public String getPhoneNumber() { 50 return phoneNumber; 51 } 52 53 public void setPhoneNumber(String phoneNumber) { 54 this.phoneNumber = phoneNumber == null ? null : phoneNumber.trim(); 55 } 56 57 public String getHomeAddress() { 58 return homeAddress; 59 } 60 61 public void setHomeAddress(String homeAddress) { 62 this.homeAddress = homeAddress == null ? null : homeAddress.trim(); 63 } 64 65 public String getUserId() { 66 return userId; 67 } 68 69 public void setUserId(String userId) { 70 this.userId = userId == null ? null : userId.trim(); 71 } 72 73 public String getSpecialistUid() { 74 return specialistUid; 75 } 76 77 public void setSpecialistUid(String specialistUid) { 78 this.specialistUid = specialistUid == null ? null : specialistUid.trim(); 79 } 80 81 public Long getCreateTime() { 82 return createTime; 83 } 84 85 public void setCreateTime(Long createTime) { 86 this.createTime = createTime; 87 } 88 89 public Long getModifyTime() { 90 return modifyTime; 91 } 92 93 public void setModifyTime(Long modifyTime) { 94 this.modifyTime = modifyTime; 95 } 96 97 public Integer getState() { 98 return state; 99 } 100 101 public void setState(Integer state) { 102 this.state = state; 103 } 104 105 public String getOtherInfo1() { 106 return otherInfo1; 107 } 108 109 public void setOtherInfo1(String otherInfo1) { 110 this.otherInfo1 = otherInfo1 == null ? null : otherInfo1.trim(); 111 } 112 113 public String getOtherInfo2() { 114 return otherInfo2; 115 } 116 117 public void setOtherInfo2(String otherInfo2) { 118 this.otherInfo2 = otherInfo2 == null ? null : otherInfo2.trim(); 119 } 120 }
service:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public int importExcel(String filename ,PatientProfile ptp) { 2 try { 3 Workbook rwb=Workbook.getWorkbook(new File(filename)); 4 Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0) 5 int clos=rs.getColumns();//得到所有的列 6 int rows=rs.getRows();//得到所有的行 7 System.out.println("clos:"+clos+",rows:"+rows); 8 int id = -1 , name = -1, email = -1 , phone = -1 , ha = -1 , icid = -1; 9 10 for (int i = 0; i < clos; i++) { 11 String closName = rs.getCell(i, 0).getContents(); 12 //System.out.println(closName); 13 /*if(closName.equals("u_id") || closName.equals("id")) { 14 id = 0; 15 }else if (closName.equals("name") || closName.equals("uname")){ 16 name = 1; 17 }else if (closName.equals("email") || closName.equals("Email")){ 18 email = 2; 19 }else if (closName.equals("Phone") || closName.equals("phoneNumber")){ 20 phone = 3; 21 }else if (closName.equals("HomeAddress") || closName.equals("home")){ 22 ha = 4; 23 }else if (closName.equals("icid") || closName.equals("insurranceCompanyProfileNumber")){ 24 icid = 5; 25 }*/ 26 switch (closName) { 27 case "u_id": 28 id = 0; 29 break; 30 case "id": 31 id = 0; 32 break; 33 case "name": 34 name = 1; 35 break; 36 case "uname": 37 name = 1; 38 break; 39 case "email": 40 email = 2; 41 break; 42 case "Email": 43 email = 2; 44 break; 45 case "Phone": 46 phone = 3; 47 break; 48 case "phoneNumber": 49 phone = 3; 50 break; 51 case "HomeAddress": 52 ha = 4; 53 break; 54 case "home": 55 ha = 4; 56 break; 57 case "icid": 58 icid = 5; 59 break; 60 case "insurranceCompanyProfileNumber": 61 icid = 5; 62 break; 63 64 65 } 66 } 67 68 List<PatientProfile> plist = new ArrayList<>(); 69 PatientProfile pt = new PatientProfile(); 70 for (int i = 1; i < rows; i++) { 71 //第一个是列数,第二个是行数 72 String uid=rs.getCell(id, i).getContents();//默认最左边编号也算一列 所以这里得j++ 73 String uname=rs.getCell(name, i).getContents(); 74 String uemail=rs.getCell(email, i).getContents(); 75 String phonenum=rs.getCell(phone, i).getContents(); 76 String homeAddress=rs.getCell(ha, i).getContents(); 77 String uicid = rs.getCell(icid,i).getContents(); 78 pt.setEmail(uemail); 79 pt.setName(uname); 80 pt.setPhoneNumber(phonenum); 81 pt.setHomeAddress(homeAddress); 82 pt.setInsurranceCompanyProfileNumber(uicid); 83 pt.setSpecialistProfileSpecialistNumber(ptp.getSpecialistProfileSpecialistNumber()); 84 pt.setSpecialistUid(ptp.getSpecialistUid()); 85 //调用插入数据库方法逐条插入 86 patientService.insert(pt); 87 88 } 89 return 1; 90 } catch (Exception e) { 91 // TODO Auto-generated catch block 92 e.printStackTrace(); 93 return 0; 94 }
这里主要是对上传的excel表的列名做了一个近义匹配。
最开始使用if,后该用switch效率可能更高。
数据库导出数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public int exportExcel(PatientProfile ptp ,String path ,String fileName) { 2 try { 3 WritableWorkbook wwb = null; 4 //String fileName = "MyExcel"; 5 6 // 创建可写入的Excel工作簿 7 File file=new File(path); 8 if (!file.exists()) { 9 file.mkdirs(); 10 } 11 File filename = new File(path,fileName); 12 if(!filename.exists()) { 13 filename.createNewFile(); 14 } 15 //以fileName为文件名来创建一个Workbook 16 wwb = Workbook.createWorkbook(filename); 17 18 // 创建工作表 19 WritableSheet ws = wwb.createSheet("Test Shee 1", 0); 20 21 //查询数据库中所有的数据 22 List<PatientProfile> plist= patientService.selectP(ptp.getSpecialistProfileSpecialistNumber()); 23 //log.info("plist.size:"+plist.size()); 24 //要插入到的Excel表格的行号,默认从0开始 25 Label luid= new Label(0, 0, "u_id");//表示第 26 Label lname= new Label(1, 0, "name"); 27 Label lemail= new Label(2, 0, "Email"); 28 Label lphone= new Label(3, 0, "Phone"); 29 Label lhomeaddress = new Label(4,0,"HomeAddress"); 30 Label lIcid = new Label(5, 0, "icid"); 31 ws.addCell(luid); 32 ws.addCell(lname); 33 ws.addCell(lemail); 34 ws.addCell(lphone); 35 ws.addCell(lhomeaddress); 36 ws.addCell(lIcid); 37 38 for (int i = 0; i < plist.size(); i++) { 39 Label luid_i= new Label(0, i+1, plist.get(i).getUserId()+""); 40 Label lname_i= new Label(1, i+1, plist.get(i).getName()+""); 41 Label lemail_i= new Label(2, i+1, plist.get(i).getEmail()+""); 42 Label lphone_i= new Label(3, i+1, plist.get(i).getPhoneNumber()+""); 43 Label lha_i = new Label(4,i+1,plist.get(i).getHomeAddress()+""); 44 Label lIcid_i = new Label(5,i+1,plist.get(i).getInsurranceCompanyProfileNumber()); 45 ws.addCell(luid_i); 46 ws.addCell(lname_i); 47 ws.addCell(lemail_i); 48 ws.addCell(lphone_i); 49 ws.addCell(lha_i); 50 ws.addCell(lIcid_i); 51 } 52 log.info("write start"); 53 log.debug("write start"); 54 //写进文档 55 wwb.write(); 56 // 关闭Excel工作簿对象 57 wwb.close(); 58 59 return 1; 60 } catch (Exception e) { 61 e.printStackTrace(); 62 log.error(e); 63 return 0; 64 } 65 66 }
fileName = 导入excel文件的路径/导出excel文件路径