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     }