java_poi

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        

        <!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>
    </dependencies>

 

 

/**
     * 读取数据插入到oracle
     */
    @Test
    public void m4() {

        // String execelFile = "F:\\work\\尹家乡.xlsx";
        String execelFile = "F:\\work\\Excel\\玉山镇.xlsx";
        Connection conn = null;
        try {
            Class.forName(DRVIER);
            conn = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
            QueryRunner qr = new QueryRunner();
            Workbook book = new XSSFWorkbook(new FileInputStream(execelFile));
            DecimalFormat df = new DecimalFormat("0");
            // 读取表格的第一个sheet页
            Sheet sheet = book.getSheetAt(0);
            // 定义 row、cell
            Row row;
            // 总共有多少行,从0开始
            int totalRows = sheet.getLastRowNum();
            // 循环输出表格中的内容,首先循环取出行,再根据行循环取出列
            String id = "";
            for (int i = 3; i <= totalRows; i++) {
                row = sheet.getRow(i);
                // 处理空行
                if (row == null || row.getCell(0) == null || row.getCell(1) == null) {

                    continue;
                }
                String index = row.getCell(0).toString() == null ? "id" : row.getCell(0).toString();
                String center = row.getCell(1).toString() == null ? "" : row.getCell(1).toString().trim();// 帮扶单位
                String USER_NAME = row.getCell(2).toString() == null ? "" : row.getCell(2).toString().trim();// 姓名
                String POSITION = row.getCell(3).toString() == null ? "" : row.getCell(3).toString().trim();// 职务
                String PHONE = "";
                try {

                    PHONE = df.format(row.getCell(4).getNumericCellValue()) == null ? ""
                            : df.format(row.getCell(4).getNumericCellValue()).trim();// 联系电话
                } catch (IllegalStateException e) {
                    PHONE = row.getCell(4).toString().trim();
                    // TODO: handle exception
                }

                String center_posi = center + "-" + POSITION;
                String ADDRESS = row.getCell(5).toString() == null ? "" : row.getCell(5).toString().trim();// 所在地
                String sflb = "2";

                System.out.print(index + "\t");
                System.out.print(USER_NAME + "\t");
                System.out.print(center_posi + "\t");// 职务
                System.out.print(PHONE + "\t");// 联系电话
                System.out.print(ADDRESS + "\t");
                

                // 查询记录 根据姓名和手机号来查询
                String querySql = "select count(*) from BF_BFRY where user_name = ? and phone = ?";
                Object[] o = { USER_NAME, PHONE };
                BigDecimal count = qr.query(conn, querySql, o, new ScalarHandler<BigDecimal>());

                if (count.intValue() <= 0 && !USER_NAME.equals("")) {
                    System.out.println("没有");
                    String sql = "insert into BF_BFRY (KEYID, USER_NAME, PHONE,ADDRESS, POSITION, SFLB) values(?,?,?,?,?,?)";

                    id = getKeyid();// 帮扶负责人
                    Object[] params = { id, USER_NAME, PHONE, ADDRESS, center_posi, "2" };
                    qr.update(conn, sql, params);

                }
                if (id.equals("")) {
                    id = getKeyid();
                }
                System.out.println(id);
                System.out.print("|");
                ///////////////// 被帮扶人员////////
                String HOUSEHOLD = row.getCell(6).toString() == null ? "" : row.getCell(6).toString().trim();// 户主

                String HOUSEHOLD_SIZE = row.getCell(7).toString();// 家庭人数
                String CONTACT = "";

                try {

                    CONTACT = df.format(row.getCell(8).getNumericCellValue()) == null ? ""
                            : df.format(row.getCell(8).getNumericCellValue()).trim();// 联系电话
                } catch (IllegalStateException e) {
                    CONTACT = row.getCell(8).getStringCellValue().trim();
                }

                String POVERTY_ALLEVIATION_TIME;// 脱贫时间
                try {

                    POVERTY_ALLEVIATION_TIME = df.format(row.getCell(9).getNumericCellValue()) == null ? ""
                            : df.format(row.getCell(9).getNumericCellValue()).trim();
                } catch (IllegalStateException e) {
                    POVERTY_ALLEVIATION_TIME = row.getCell(9).getStringCellValue();
                    if (POVERTY_ALLEVIATION_TIME.contains("年")) {
                        POVERTY_ALLEVIATION_TIME = POVERTY_ALLEVIATION_TIME.replace('年', ' ');
                    }
                    
                }

                String POVERTY_REASON = row.getCell(10).toString() == null ? "" : row.getCell(10).toString().trim();// 贫困原因
                String ISPOVERTY = row.getCell(11).toString() == "是" ? "1" : "0"; // 是否贫困
                System.out.print(ADDRESS + "\t");
                System.out.print(HOUSEHOLD + "\t");
                System.out.print(HOUSEHOLD_SIZE + "\t");
                System.out.print(CONTACT + "\t");
                System.out.print("日期:" + POVERTY_ALLEVIATION_TIME + "\t");
                System.out.print("\t==" + POVERTY_REASON + "\t");
                System.out.print(ISPOVERTY + "\t");
                System.out.println();
                POVERTY_ALLEVIATION_TIME = POVERTY_ALLEVIATION_TIME.equals("0") ? "2020" : POVERTY_ALLEVIATION_TIME;
                
                String insertSql = "insert into BT_FUPIN (KEYID, HOUSEHOLD, HOUSEHOLD_SIZE, POVERTY_REASON, ADDRESS, CONTACT, RESPONSIBLE,ISPOVERTY, POVERTY_ALLEVIATION_TIME ) values(?,?,?,?,?,?,?,?,to_date(?, 'yyyy'))";
                Object[] params = { getKeyid(), HOUSEHOLD, HOUSEHOLD_SIZE, POVERTY_REASON, ADDRESS, CONTACT, id,
                        ISPOVERTY, POVERTY_ALLEVIATION_TIME };

                qr.update(conn, insertSql, params);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }