Insurance 项目——excel导入导出数据库

这是一个java初学者在独立开发一个项目时做的笔记，很多内容仅仅是为了解决当前需求，并未很深入的研究。

项目中需要excel与数据库之间的导入和导出功能，还需要对列名进行一些匹配。

jar包

pom.xml

1  <!-- exceljar包 -->
2     <dependency>
3         <groupId>net.sourceforge.jexcelapi</groupId>
4         <artifactId>jxl</artifactId>
5         <version>2.6</version>
6     </dependency>

pom.xml

 

Excel表导入数据库：

实体类：

  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 }

PatientProfile

 

 

service：

 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             } 

importExcel

这里主要是对上传的excel表的列名做了一个近义匹配。

最开始使用if，后该用switch效率可能更高。

 

数据库导出数据:

 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     }

exportExcel

 

fileName = 导入excel文件的路径/导出excel文件路径

 

转载于:https://www.cnblogs.com/jc79/p/6959448.html