java jxl读取Excel文件数据插入数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package oracleTestProject;

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

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ReadExcel {
	static Connection conn = null;
	
	public static void main(String[] args) throws Exception{
        File file = new File("F:/study/20190713/test.xls");
        dataBaseConnection();
        excel2DataBase(file);
    }
	
	/**
	 * 数据库连接
	 * @throws Exception
	 */
	public static void dataBaseConnection() throws Exception{
		System.out.println("连接数据库开始......");
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","Deng123456");
		System.out.println("连接数据库成功......");
	}
	
	/**
	 * 读取Excel文件数据插入数据库
	 * @param file
	 * @throws Exception
	 */
	public static void excel2DataBase(File file) throws Exception{
		System.out.println("读取Excel文件开始......");
        String sSql = "";//SQL语句
        String sInsertColName = "";
        String sInsertColValue = "";
        Statement st = null;
        try{
        	//插入数据表列名，第一个为空，抵消Excel表格第一列序号，使遍历时下标对应
        	String[] sColName = {"","Stu_No","Stu_Name","Stu_Age","Stu_Sex","Stu_Qq","Stu_Phone","Stu_Addr"};
            FileInputStream fileInputStream = new FileInputStream(file);   
            jxl.Workbook rwb = Workbook.getWorkbook(fileInputStream); 
            Sheet sheet = rwb.getSheet(0);//取第一个 工作表
            for (int i = 1; i < sheet.getRows(); i++) {//第一行为表头，从第二行开始读取
               Cell[] cells = sheet.getRow(i); 
               sInsertColName = "";//每读一行都要置空
               sInsertColValue = "";//每读一行都要置空
               for(int j = 1; j < cells.length; j++){//第一列为序号，从第二列开始遍历
            	   if(cells[j].getContents() != null && !"".equals(cells[j].getContents())){
            			   sInsertColName += sColName[j]+",";
            			   sInsertColValue += "'"+cells[j].getContents().trim()+"',";
            	   }
               }
               if(!"".equals(sInsertColName)){
            	   sInsertColName = sInsertColName.substring(0, sInsertColName.length()-1);
            	   sInsertColValue = sInsertColValue.substring(0, sInsertColValue.length()-1);
            	   sSql = " insert into student_temp ("+sInsertColName+") values("+sInsertColValue+") ";
            	   st = conn.createStatement();
           		   st.executeUpdate(sSql);
               }
            }
            fileInputStream.close();
        }catch(Exception e){
            e.printStackTrace();
        }
        if(st != null){
        	st.close();
        }
        if(conn != null){
        	conn.close();
        }
        System.out.println("读取Excel文件结束......");
    }
}