package cn.ccb.odsbsx.common.util;
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();
    }
}