java把Excel文件数据导入数据库，javaexcel数据导入,import java.

java把Excel文件数据导入数据库，javaexcel数据导入,import java.

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&amp;password=blue&amp;useUnicode=true&amp;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();    }}