EXCEL_TO_DB

前言：
将EXCEL表的数据导入到数据库中，问题并不复杂，但是是个细活，麻烦！仔细整理整理自己花费时间的地方

---

1.导入组织关系

思路：

函数声明

/**
* @param filename
* @param table
* @param from
* @param name
* 算法的思路：
* 读一行 插一行
* 对于要插的表 要知道它的列号 对于被插的表 要知道它的列名
* 还要知道二者的对应关系 对应关系体现在str中的值
* 对于每一行都必须差别对待因为 数据的类型不一样
*/

public void InsetFromExcel_department(String filename, String table, int from[], String name[])

调用：

 int []col_department={0,1,5,2};
 String []NAME_department={"ID", "NAME", "DEPT_TYPE", "PARENT_ID"};
 op.InsetFromExcel_department("Department_cx_v2.xls","SYS_P_DEPARTMENT",col_department,NAME_department);

需要注意的地方

2.生成一个sql语句

  String sqlinsert = "INSERT INTO " + table + "(";
            for (int item = 0; item < name.length; item++) {
                if (item != name.length - 1) {
                    sqlinsert += name[item] + ",";
                } else {
                    sqlinsert += name[item];
                }
            }
            sqlinsert += ") values(?,?,?,?) ";

3.读一行 插一行

  int rowth = 0;  //rowth 为0的时候 表示会读列名

            //读一行 插一行
            //记得在Excelread这个函数中加入if (row == null) { return null;}
            while (er.readoneline(rowth, from) != null) {
                str = er.readoneline(rowth, from);//读取指定列 依次放在str中

                pstm = connection.prepareStatement(sqlinsert);// 准备插入

                //由于数据类型不一样所以每一个都必须单独对待
                pstm.setInt(1, Integer.parseInt(str[0]));//ID
                pstm.setString(2, str[1]);//NAME
                pstm.setInt(3, Integer.parseInt(str[2]));//DEPT_TYPE
                pstm.setString(4, str[3]);//PARENT
                pstm.executeUpdate();


                //重启一下，这是为了解决 游标的数量不够的问题
                if (rowth % 100 == 0) {
                    connection.close();
                    connection = getConnection();
                }

                rowth++;

            }

4.由于数据类型不一样所以每一个都必须单独对待（这就是特别麻烦的地方）

时间数据 ：怕遇到空值，怕遇到格式不对的值
整数数据 ：怕遇到空值
字符串数据 ：这个最简单

5.重启一下，这是为了解决 游标的数量不够的问题

这个问题是由于sql的连接是有限的，所以要记得关闭一些链接 ，这是一种不彻底的解决办法

 //相当于重启一下，这是为了解决 游标的数量不够的问题
                if (rowth % 100 == 0) {
                    connection.close();
                    connection = getConnection();
                }

6.另外就是换行的时候

思路是 如果读取的行数不为空就干嘛干嘛
while (er.readoneline(rowth, from) != null) {。。。}

所以读取的函数里面，需要加入这样的一行 return null;

  stream = new FileInputStream(this.filename);
            fs = new POIFSFileSystem(stream);
            wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = sheet.getRow(rowNum);

           //如果row为空就返回null
            if (row == null) {
                return null;
            }

2.导入人员表

和上面的基本上差不多，额外需要注意的是上面提到的第四点

4.由于数据类型不一样所以每一个都必须单独对待（这就是特别麻烦的地方）

时间数据 ：怕遇到空值，怕遇到格式不对的值
整数数据 ：怕遇到空值
字符串数据 ：这个最简单

               //出生日期 有的时候为空值
                if (str[3] == null) {
                   // str[3]="2000-00-00 00:00:00.0"; //注意了月和日都是从1开始的
                    str[3] = "2018-09-16 00:00:00.0";
                }

                //时间格式的转换
                Timestamp buydate = Timestamp.valueOf(str[3]);


                //如果性别为空值就默认为1吧 
                if (str[2] == null) {
                    str[2] = "1";
                }

不同类型字符的插入（时间 整数 字符串）

 //由于数据类型不一样所以必须这样做
                pstm.setString(1, str[0]);//工号
                pstm.setString(2, str[1]);//姓名
                pstm.setInt(3, Integer.parseInt(str[2]));//性别
                pstm.setTimestamp(4, buydate);//出生日期  时间类型
                pstm.setString(5, str[4]);//政治状态
                pstm.setString(6, str[5]);//名族
                pstm.setString(7, str[6]);//邮箱
                pstm.setString(8, str[7]);//证件类型
                pstm.setString(9, str[8]);//身份证号
                pstm.setString(10, str[9]);//所属部门编码
                pstm.executeUpdate();
                rowth++;

代码

1 读函数

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelRead {
    InputStream stream;
    POIFSFileSystem fs;
    HSSFWorkbook wb;
    String filename = "UserInfo_2.xls";

    public ExcelRead() {
    }

    public void ExcelRead(String filename) {
        this.filename = filename;
    }

    public String[] readoneline(int rowNum, int[] col) {
        int colnum = 10;
        String[] str = new String[colnum];

        try {
            this.stream = new FileInputStream(this.filename);
            this.fs = new POIFSFileSystem(this.stream);
            this.wb = new HSSFWorkbook(this.fs);
            HSSFSheet sheet = this.wb.getSheetAt(0);
            HSSFRow row = sheet.getRow(rowNum);
            if (row == null) {
                return null;
            }

            int i = false;
            int j = false;

            for(int i = 0; i < col.length; ++i) {
                HSSFCell cell = row.getCell(col[i]);
                str[i] = readCellSecondMethod(cell);
            }

            this.stream.close();
        } catch (Exception var10) {
            var10.printStackTrace();
        }

        return str;
    }

    public static String readCellSecondMethod(HSSFCell cell) {
        if (cell == null) {
            return null;
        } else {
            switch(cell.getCellType()) {
            case 0:
                String strVal = String.valueOf(cell.getNumericCellValue());
                strVal = strVal.contains(".") ? strVal.substring(0, strVal.indexOf(".")) : strVal;
                return strVal;
            case 1:
                return cell.getStringCellValue();
            case 2:
                return cell.getCellFormula();
            case 3:
                return null;
            case 4:
                return cell.getBooleanCellValue() + "";
            case 5:
                return cell.getErrorCellValue() + "";
            default:
                return "";
            }
        }
    }
}

2插入函数

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Formatter;

public class Operator {
    public static String DRVIER = "oracle.jdbc.driver.OracleDriver";
    private Formatter f;
    private static String USERNAMR = "ugonghui";
    private static String PASSWORD = "ugonghui";
    private static String URL = "jdbc:oracle:thin:@//10.113.1.50:1521/orcl";
    Connection connection;
    PreparedStatement pstm;
    PreparedStatement pstm2;
    PreparedStatement pstm3;
    Statement stmt;
    ResultSet rsfrom;
    ResultSet rsto;

    public Operator() {
        this.f = new Formatter(System.out);
        this.connection = null;
        this.pstm = null;
        this.pstm2 = null;
        this.pstm3 = null;
        this.stmt = null;
        this.rsfrom = null;
        this.rsto = null;
    }





    public void InsetFromExcel_department(String filename, String table, int[] from, String[] name) {
        ExcelRead er = new ExcelRead();

        try {
            er.filename = filename;
            this.connection = this.getConnection();
            String sqlinsert = "INSERT INTO " + table + "(";

            int rowth;
            for(rowth = 0; rowth < name.length; ++rowth) {
                if (rowth != name.length - 1) {
                    sqlinsert = sqlinsert + name[rowth] + ",";
                } else {
                    sqlinsert = sqlinsert + name[rowth];
                }
            }

            sqlinsert = sqlinsert + ") values(?,?,?,?) ";

            for(rowth = 0; er.readoneline(rowth, from) != null; ++rowth) {
                String[] str = er.readoneline(rowth, from);
                this.pstm = this.connection.prepareStatement(sqlinsert);
                this.pstm.setInt(1, Integer.parseInt(str[0]));
                this.pstm.setString(2, str[1]);
                this.pstm.setInt(3, Integer.parseInt(str[2]));
                this.pstm.setString(4, str[3]);
                this.pstm.executeUpdate();
                if (rowth % 100 == 0) {
                    this.connection.close();
                    this.connection = this.getConnection();
                }
            }

            this.connection.close();
        } catch (Exception var9) {
            var9.printStackTrace();
        }

    }

    public void InsetFromExcel_people(String filename, String table, int[] from, String[] name) {
        ExcelRead er = new ExcelRead();

        try {
            er.filename = filename;
            this.connection = this.getConnection();
            String sqlinsert = "INSERT INTO " + table + "(";

            int rowth;
            for(rowth = 0; rowth < name.length; ++rowth) {
                if (rowth != name.length - 1) {
                    sqlinsert = sqlinsert + name[rowth] + ",";
                } else {
                    sqlinsert = sqlinsert + name[rowth];
                }
            }

            sqlinsert = sqlinsert + ") values(?,?,?,?,?,?,?,?,?,?) ";
            rowth = 1;

            while(er.readoneline(rowth, from) != null && rowth < 10) {
                String[] str = er.readoneline(rowth, from);
                System.out.println(str[3] + "--" + rowth);
                if (str[3] == null) {
                    str[3] = "2018-09-16 00:00:00.0";
                }

                Timestamp buydate = Timestamp.valueOf(str[3]);
                if (str[2] == null) {
                    str[2] = "1";
                }

                this.pstm = this.connection.prepareStatement(sqlinsert);
                this.pstm.setString(1, str[0]);
                this.pstm.setString(2, str[1]);
                this.pstm.setInt(3, Integer.parseInt(str[2]));
                this.pstm.setTimestamp(4, buydate);
                this.pstm.setString(5, str[4]);
                this.pstm.setString(6, str[5]);
                this.pstm.setString(7, str[6]);
                this.pstm.setString(8, str[7]);
                this.pstm.setString(9, str[8]);
                this.pstm.setString(10, str[9]);
                this.pstm.executeUpdate();
                ++rowth;
                if (rowth % 100 == 0) {
                    this.connection.close();
                    this.connection = this.getConnection();
                }
            }
        } catch (Exception var10) {
            var10.printStackTrace();
        }

    }


    public Connection getConnection() {
        try {
            Class.forName(DRVIER);
            this.connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
        } catch (ClassNotFoundException var2) {
            throw new RuntimeException("class not find !", var2);
        } catch (SQLException var3) {
            throw new RuntimeException("get connection error!", var3);
        }

        return this.connection;
    }

    public void ReleaseResource() {
        if (this.rsfrom != null) {
            try {
                this.rsfrom.close();
            } catch (SQLException var4) {
                var4.printStackTrace();
            }
        }

        if (this.pstm != null) {
            try {
                this.pstm.close();
            } catch (SQLException var3) {
                var3.printStackTrace();
            }
        }

        if (this.connection != null) {
            try {
                this.connection.close();
            } catch (SQLException var2) {
                var2.printStackTrace();
            }
        }

    }
}