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 {
    // 定義總列數(shù)
    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("加載數(shù)據(jù)庫驅(qū)動失��！");
            cnfex.printStackTrace();
            return false;
        } catch (SQLException sqle) {
            System.err.println("無法連接數(shù)據(jù)庫！");
            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;
        // 獲取總行數(shù)
        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("在插入數(shù)據(jù)時第" + (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("無法刪除數(shù)據(jù)表！");
            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("無法創(chuàng)建數(shù)據(jù)表！");
            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();
    }
}

標(biāo)簽： Mysql 數(shù)據(jù)庫

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。