java把Excel文件数据导入数据库详解编程语言

import java.io.*; 
import java.sql.*; 
import org.apache.poi.hssf.usermodel.HSSFCell; 
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 ReadxlXLSToDB { 
    // 定义总列数 
    private int columnNum; 
    public int getColumnNum() { 
        return columnNum; 
    } 
    public void setColumnNum(int columnNum) { 
        this.columnNum = columnNum; 
    } 
    private static Connection conn = null; 
    private static Statement stmt = null; 
    static String dbUrl = "jdbc:mysql://localhost:3306/test?user=root&password=blue&useUnicode=true&characterEncoding=utf8"; 
    private final static String driver = "com.mysql.jdbc.Driver"; 
    private static boolean connectionDB() { 
        try { 
            Class.forName(driver); 
            conn = DriverManager.getConnection(dbUrl); 
            stmt = conn.createStatement(); 
        } catch (ClassNotFoundException cnfex) { 
            System.err.println("加载数据库驱动失败！"); 
            cnfex.printStackTrace(); 
            return false; 
        } catch (SQLException sqle) { 
            System.err.println("无法连接数据库！"); 
            sqle.printStackTrace(); 
            return false; 
        } catch (Exception e) { 
            System.err.println("错误"); 
            return false; 
        } 
        return true; 
    } 
    public void readSheet() { 
        POIFSFileSystem fs = null; 
        HSSFWorkbook wb = null; 
        String sql = "", sql1 = "", sql2 = ""; 
        try { 
            fs = new POIFSFileSystem(new FileInputStream("d://1.xls")); 
            wb = new HSSFWorkbook(fs); 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } 
        HSSFSheet sheet = wb.getSheetAt(0); 
        HSSFRow row = null; 
        HSSFCell cell = null; 
        String name = ""; 
        int rowNum, cellNum; 
        int i, j; 
        // 获取总行数 
        rowNum = sheet.getLastRowNum(); 
        for (i = 0; i <= rowNum; i++) { 
            row = sheet.getRow(i); 
            cellNum = row.getLastCellNum(); 
            for (j = 0; j < cellNum; j++) { 
                cell = row.getCell((short) j); 
                name = cell.getStringCellValue(); 
                sql1 = sql1 + "num" + (j + 1) + ","; 
                sql2 = sql2 + "'" + name + "',"; 
            } 
            sql = "insert into xls (" 
                    + sql1.subSequence(0, sql1.lastIndexOf(",")) + ") values (" 
                    + sql2.substring(0, sql2.lastIndexOf(",")) + ")"; 
            System.out.println(sql); 
            try { 
                stmt.executeUpdate(sql); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
                System.err.println("在插入数据时第" + (i + 1) + "失败！"); 
            } 
            sql1 = ""; 
            sql2 = ""; 
        } 
    } 
    public void readOut() { 
        connectionDB(); 
        String sql = "select * from xls"; 
        try { 
            ResultSet rs = stmt.executeQuery(sql); 
            while (rs.next()) { 
                for (int i = 1; i <= columnNum; i++) 
                    System.out.print(rs.getString(i) + "/t"); 
                System.out.println(); 
            } 
        } catch (SQLException e) { 
            System.err.println("无法查询！"); 
            e.printStackTrace(); 
        } 
    } 
    public void deleteDB() { 
        connectionDB(); 
        String sql = "drop table xls"; 
        try { 
            stmt.executeUpdate(sql); 
        } catch (SQLException e) { 
            System.err.println("无法删除数据表！"); 
            e.printStackTrace(); 
        } 
    } 
    public void creatTable(int columnNum) { 
        int i; 
        String sql = "", sql1 = ""; 
        for (i = 1; i <= columnNum; i++) 
            sql1 = sql1 + "`" + "num" + i + "` varchar(50),"; 
        sql = "create table xls(`id` int(11) NOT NULL auto_increment," + sql1 
                + " PRIMARY KEY (`id`))ENGINE=MyISAM DEFAULT CHARSET=utf8"; 
        try { 
            stmt.executeUpdate(sql); 
            System.out.println(sql); 
        } catch (SQLException e) { 
            System.err.println("无法创建数据表！"); 
            e.printStackTrace(); 
        } 
    } 
    public static void main(String args[]) { 
        ReadxlXLSToDB db = new ReadxlXLSToDB(); 
        db.setColumnNum(5); 
        if (ReadxlXLSToDB.connectionDB()) { 
            db.creatTable(db.getColumnNum()); 
            db.readSheet(); 
        } else { 
            System.out.println("不好意思，连接不成功！你失败了！！！"); 
        } 
        db.readOut(); 
    } 
}