java 导入excel 到Oracle数据库

package com.hcycom.datashare;


import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @program: ctg_datashare
 * @description:
 * @author: dhq
 * @create: 2020-03-17 16:51
 **/
public class A {
    //获取数据库连接
    public Connection conn(){
        try {
            //第一步：加载JDBC驱动
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //第二步：创建数据库连接
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@//xx:1521/xx", "xx", "xxxx");
            return con;
        }catch(ClassNotFoundException cnf){
            System.out.println("driver not find:"+cnf);
            return null;
        }catch(SQLException sqle){
            System.out.println("can't connection db:"+sqle);
            return null;
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return null;
        }
    }


    //读取excel
    public void getExcel() throws Exception {
        InputStream is = new FileInputStream(new File("D:/home/7条数据.xlsx"));
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        // 获取每一个工作薄（sheet）
        XSSFSheet sheet=xssfWorkbook.getSheetAt(0);
        for (Row row : sheet) {
            int index = 0;
            for (org.apache.poi.ss.usermodel.Cell cell : row) {
                //读取数据前设置单元格类型
                cell.setCellType(CellType.STRING);
                index++;
            }
        }
        int rsRows=sheet.getLastRowNum()+1;//获取总行数
        String simNumber="";//每个单元格中的数据
        String str="";//拼接要插入的列
        XSSFRow row=sheet.getRow(0);//获取第一行
        int rsColumns=row.getPhysicalNumberOfCells();//列数
        for(short i=0;i<rsColumns;i++){
            XSSFCell cell=row.getCell(i);
            simNumber = cell.getStringCellValue();
            if(i==rsColumns-1){
                str+= simNumber;
            }else {
                str+= simNumber+",";
            }
        }
        System.out.println(str);
//        System.out.println(str.s);
        for(short j=1;j<rsRows;j++){
            XSSFRow row1=sheet.getRow(j);//获取行
            //拼接sql
            String sql="insert into inn_bsscrm_prod_ieplBck("+str+") values(";
            for(short k=0;k<rsColumns;k++){
                XSSFCell cell=row1.getCell(k);
                if(cell==null){
                    simNumber = "";
                }else {
                    simNumber = cell.getStringCellValue();
                }

                if(k==rsColumns-1){
                    sql+="'"+simNumber+"'";
                }else {
                    sql+="'"+simNumber+"',";
                }
            }
            sql+=")";
            System.out.println("第"+j+"行："+sql);
            this.insert(sql);
            System.out.println("完成"+j+"行："+sql);
        }
//        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
//            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
//            if (xssfSheet == null) {
//                continue;
//            }
//            // 获取当前工作薄的每一行
//            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
//                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
//                if (xssfRow != null) {
//                    //读取第一列数据
//                    String a = getValue(xssfRow.getCell(0));
//                    Integer one = Integer.parseInt(a.substring(0,a.indexOf(".")));
//                    //读取第二列数据
//                    String two = getValue(xssfRow.getCell(1));
//                    //读取第三列数据
//                    String three = getValue(xssfRow.getCell(2));
//
//                    String insert="insert into a values("+one+",'"+two+"','"+three+"')";
//                    System.out.println("SQL:"+insert);
//
//                    this.insert(insert);
//                }
//            }
//        }
    }
    //转换数据格式
    private String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }



    //添加数据
    public int insert(String insert) throws SQLException{
        Connection conn = this.conn();
        int re = 0;
        try{
            conn.setAutoCommit(false);//事务开始
            Statement sm = conn.createStatement();
            re = sm.executeUpdate(insert);
            if(re < 0){               //插入失败
                System.out.println("回滚sql："+insert);
                conn.rollback();      //回滚
                sm.close();
                conn.close();
                return re;
            }
            conn.commit();            //插入正常
            System.out.println("正常完成sql："+insert);
            sm.close();
            conn.close();
            return re;
        }
        catch(Exception e){
            System.out.println("報錯sql："+insert);
            e.printStackTrace();
        }
        conn.close();
        return 0;
    }




    //测试
    public static void main(String[] args) throws Exception {
        A e=new A();
        e.getExcel();
        System.out.println("导入完成！");
    }
}