java通过jdbc操作Excel

ExcelAccess.java


package test;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

 
public class ExcelAccess {
    
    public static void main(String[] args) {
        
        List<DataTableColumns> ls = new ArrayList<DataTableColumns>();
        
        String dateForm ="yyyyMMddHHmmss"; //时间格式。mm指分钟 
        String now = new SimpleDateFormat(dateForm).format(new java.util.Date());
        System.out.println(now);
        String dataTableName = "user_detail2";

        int data_id=1;
        int ret=0;
        //连接oracle
        String url = "jdbc:oracle:" + "thin:@***:1521:edu";
        String user = "***";
        String password = "***";
        DbOracle dbo= new DbOracle();
        dbo.conDbOracle(url,user,password);

        //获取表声明
        dbo.getTableColumns(data_id,ls);
        //建Excel sheet
        String tableName = "test";
        String sql=dbo.getTableInfo(tableName,ls);
        System.out.println(sql); 

        
        //Excel操作
        DbExcel dbExl = new DbExcel();
        if (!dbExl.openConnection()) {
            System.err.println("open connection err.");
            System.exit(1);
        }
        
        //ret = dbExl.createTableInfo(sql,tableName);
        if (ret == 1) {
            System.out.println("建表"+tableName+" success.");
        }
        
        String query = "";
        String colName = "";
        String colNames = "";
        for(int i = 0; i < ls.size(); i++)  
        {  
            colName +=ls.get(i).getValue_name()+",";
        } 
        query = query+colName;
        colNames = query.substring(0,query.length()-1);
        query = "select "+colNames +" from "+dataTableName+" where rownum<60000";
        System.out.println("查询语句"+query+" success.");
        
        //query = "select web_id,省 from user_detail";
        
        String insertSql ="";
        colName="";
        ret = 1;
        try
        {
            dbo.st = dbo.con.createStatement();
            dbo.rs = dbo.st.executeQuery(query.toString());
            
            while (dbo.rs.next() && ret==1) {
                insertSql = "INSERT INTO ["+tableName+"$]("+colNames+") VALUES(";
                
                for(int i = 0; i < ls.size(); i++)  
                {  
                    colName =ls.get(i).getValue_name();
                    insertSql += "‘"+dbo.rs.getString(colName)+"‘,";
                }
                insertSql = insertSql.substring(0,insertSql.length()-1);
                insertSql += " )";
                ret = dbExl.insertExcel(insertSql);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        

        dbExl.closeConnection(); 
        dbo.closeDbOracle();

       
        
        
        

        now = new SimpleDateFormat(dateForm).format(new java.util.Date());
        System.out.println(now);
        // close the connection

    }
}

package test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class DbOracle
{
    public Connection con = null;
    public Statement st = null;
    public ResultSet rs = null;
    

    public void conDbOracle(String url,String user,String password){
        
        
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            con = DriverManager.getConnection(url, user, password);// 获取连接
            System.out.println("连接成功！");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    
    public void getTableColumns(int dataId,List<DataTableColumns> ls){

        StringBuffer sb = new StringBuffer();
        sb.append(" select column_id,column_name,value_name,value_length,value_type,order_no ");
        sb.append(" from data_table_columns_instance ");
        sb.append(" where data_id = "+dataId);
        sb.append(" order by order_no ");
        
        System.out.println(sb);
        
        try
        {
            st = con.createStatement();
            rs = st.executeQuery(sb.toString());
            while (rs.next()) {
                DataTableColumns dataColumns = new DataTableColumns();
                dataColumns.setColumn_id(rs.getInt("column_id"));
                dataColumns.setColumn_name(rs.getString("column_name"));
                dataColumns.setValue_name(rs.getString("value_name"));
                dataColumns.setValue_length(rs.getInt("value_length"));
                dataColumns.setValue_type(rs.getInt("value_type"));
                dataColumns.setOrder_no(rs.getInt("order_no"));
                ls.add(dataColumns);
            }
            
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        
      
    }

    public String getTableInfo(String table_name,List<DataTableColumns> ls){
        String sql = "";
        String valueNames = "";
        
        for(int i = 0; i < ls.size(); i++)  
        {  
            valueNames +=ls.get(i).getValue_name();
            valueNames =valueNames+" TEXT,";
        } 
        sql="create table "+table_name+"("+valueNames.substring(0,valueNames.length()-1)+")";
        return sql;
    }
    
    public void closeDbOracle(){
    
        try
        {
            if (con != null)
                con.close();
            System.out.println("数据库连接已关闭！");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    
}

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DbExcel {
    /** db connection */
    private Connection c;
    /** db statement */
    private Statement stmnt;   
    /** JDBC driver name */
    private String driver ="sun.jdbc.odbc.JdbcOdbcDriver";
    /** url of the db */
    private String url ="jdbc:odbc:DbExcel;ReadOnly=False;";
    /** username to access the db */
    private String username= "";
    /** password for the username */
    private String password= "";

    /**
     * open the connection
     * @return true if opened, false otherwise
     */
    public boolean openConnection() {
        try {
            Class.forName(driver);
            c = DriverManager.getConnection(url, username,
                    password);
            stmnt = c.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
 

    public int createTableInfo(String query,String tableName) {
        int result = -1;
        if (stmnt == null)
            return result; 
        try {
            //建表,这里drop table 竟然是只drop表头
            //result = stmnt.executeUpdate("DROP TABLE "+tableName);
            c.commit();
            result = stmnt.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
 
    public int insertExcel(String query) {

        int result = -1;
        try {
           result = stmnt.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
            closeConnection();
        }
        return result;
      }
    /**
     * close the connection
     */
   public boolean closeConnection() {
        try {
            if (stmnt != null)
                stmnt.close();
            if (c != null)
                c.close();
        } catch (Exception e) {
            System.err.println(e);
            return false;
        }
        return true;
    }
}

package test;

public class DataTableColumns {
    private int data_id = 0;
    private int column_id = 0;
    private String column_name = null;
    private String value_name = null;
    private int value_length = 3;
    private int value_type = 1;
    private int order_no = 0;
    /**
     * @param data_id the data_id to set
     */
    public void setData_id(int data_id) {
        this.data_id = data_id;
    }
    /**
     * @return the data_id
     */
    public int getData_id() {
        return data_id;
    }
    public void setValue_length(int value_length) {
        this.value_length = value_length;
    }
    public int getValue_length() {
        return value_length;
    }
    public void setValue_name(String value_name) {
        this.value_name = value_name;
    }
    public String getValue_name() {
        return value_name;
    }
    public void setColumn_name(String column_name) {
        this.column_name = column_name;
    }
    public String getColumn_name() {
        return column_name;
    }
    public void setValue_type(int value_type) {
        this.value_type = value_type;
    }
    public int getValue_type() {
        return value_type;
    }
    public void setOrder_no(int order_no) {
        this.order_no = order_no;
    }
    public int getOrder_no() {
        return order_no;
    }
    public void setColumn_id(int column_id) {
        this.column_id = column_id;
    }
    public int getColumn_id() {
        return column_id;
    }
    
    
}