Poi读取excel 并写入mysql

解决技术难题：

1、EXCEL空值问题

2、把excel日期写成mysql日期类型

3、数字强制转换为int型

 

private void handleExcelAction(ActionEvent event) {
        String filePath = "C:\\Users\\Administrator\\Desktop\\we1.xls";
        try {
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
            // 在Excel文档中，第一张工作表的缺省索引是0
            // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
            //HSSFSheet sheet = wookbook.getSheet("Sheet1");
            HSSFSheet sheet = wookbook.getSheetAt(0);
 
            //获取最长的列
            //获取到Excel文件中的所有行数
            int rows = sheet.getPhysicalNumberOfRows();
            int max_cells = 0;
            //获取最长的列
            for (int i = 0; i < rows; i++) {
                HSSFRow row = sheet.getRow(i);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    if (max_cells < cells) {
                        max_cells = cells;
                    }
 
                }
            }
 
            //遍历行
            for (int i = 0; i < rows; i++) {
                // 读取左上端单元格
                HSSFRow row = sheet.getRow(i);
                // 行不为空
                if (row != null) {
 
                    String value = "";
                    //遍历列
                    for (int j = 0; j < max_cells; j++) {
                        //获取到列的值
                        HSSFCell cell = row.getCell(j);
 
                        if (cell == null) {
                            value += "NULL,";
                        } else {
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    break;
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    //value += "'"+cell.getNumericCellValue() + "',";
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                         SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
                                         Date date2 = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                                         String date1 = dff.format(date2);
                                        value += "'" +date1+ "'";
 
                                    } else {
                                        value += "'" + (int)cell.getNumericCellValue()+ "',";
                                    }
                                    break;
                                case HSSFCell.CELL_TYPE_STRING:
                                    value += "'" + cell.getStringCellValue() + "',";
                                    break;
                                default:
                                    value += "NULL";
                                    break;
                            }
                        }
 
                    }
                    // 将数据插入到mysql数据库中
 
                    System.out.print(value + "\n");
 
                    String driver = "com.mysql.jdbc.Driver";
 
                    // URL指向要访问的数据库名scutcs
                    String url = "jdbc:mysql://127.0.0.1:3306/scutcs";
 
                    // MySQL配置时的用户名
                    String user = "root";
 
                    // MySQL配置时的密码
                    String password = "root";
                    try {
                        // 加载驱动程序
                        Class.forName(driver);
 
                        // 连续数据库
                        Connection conn = DriverManager.getConnection(url, user, password);
 
                        if (!conn.isClosed()) {
                            System.out.println("Succeeded connecting to the Database!");
                        }
 
                        // statement用来执行SQL语句
                        Statement statement = conn.createStatement();
 
                        // 要执行的SQL语句
                        String sql = "INSERT INTO student(sno,sname,sex,bdate) VALUES (" + value + ")";
 
                        // 结果集
                        statement.execute(sql);
 
                        conn.close();
                    } catch (ClassNotFoundException e) {
 
                        System.out.println("Sorry,can`t find the Driver!");
                        e.printStackTrace();
 
                    } catch (SQLException e) {
 
                        e.printStackTrace();
 
                    } catch (Exception e) {
 
                        e.printStackTrace();
 
                    }
 
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
 
    }