读取Excel中数据存到Mysql用

利用jxl类包

一个Excel文件中有多个Sheet表

//一个读取Excel中在值，然后将Excel的值插入到MySql库中。
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ReadData {

    @SuppressWarnings("unchecked")
    public void createData(String path,String sheetname,String sqlStr){
        try {
            String sourcefile = path;
            InputStream is = new FileInputStream(sourcefile);
            Workbook rwb = Workbook.getWorkbook(is);
            Sheet sheet = rwb.getSheet(sheetname);
//            System.out.println(sheet.getName());
            /**行数*/
            int cr =sheet.getRows();
            
            String header = "";
            String preheader = "";
            List<String> fieldsList = new ArrayList<String>();
            List<String> dataList = new ArrayList<String>();
            //取得每行的单元数
            Cell[] firstLine = sheet.getRow(3);//从第三行开始
            int cellSize = firstLine.length;
            //去掉头部，取数据
            for(int i = 4;i<cr;i++){
                Cell[] testcell  = sheet.getRow(i);          
                //get cells of row
                for (int j = 0; j < cellSize; j++) {
                    String str1 = testcell[j].getContents();                
                    if(str1 == null && "".equals(str1)){                       
                        str1="";
                    }
                    dataList.add(str1);
                }
                inserData(header,fieldsList,dataList,sqlStr);
                System.out.println("成功插入");
                dataList.clear();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
    }

    
    
    public int inserData(String header,List<String> fieldsList,List<String> dataList,String sqlstr){
    	 
        StringBuffer sql = new StringBuffer(sqlstr);
       
        
        sql.append(" values (");
        
        for(int i = 0; i<dataList.size();i++){
            sql.append("'"+dataList.get(i)+"',");
        }
        
        sql.delete(sql.length()-1, sql.length());
        sql.append(")");
        System.out.println("SQL>>>>>>>>>:"+sql.toString());
        excute(sql.toString());
        
        return 0;
    }
    
    
    private void excute(String sql){
    	 String DatabaseDriver = "com.mysql.jdbc.Driver";
    	 String DbIp="localhost";
    	 String DbName="db_rlativequery";
    	//&zeroDateTimeBehavior=convertToNull设置这个属性的原因是 当数据库中日期为‘0000-00-00’时不抛出异常
    	 String DatabaseConnAdd = "jdbc:mysql://"+DbIp+":3306/"+DbName+"?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
    	 String DatabaseConnUser = "root";
    	 String DatabaseConnPwd = "";
        try {
            //Class.forName("oracle.jdbc.OracleDriver").newInstance();
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException ex) {
            ex.printStackTrace();
            System.out.println("载入MySQL数据库驱动时出错");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            System.out.println("载入MySQL数据库驱动时出错");
        } catch (IllegalAccessException ex) {
            ex.printStackTrace();
            System.out.println("载入MySQL数据库驱动时出错");
        }

        java.sql.Connection conn = null;
        try{
          //连接Mysql库
              conn = java.sql.DriverManager.getConnection(
            		  DatabaseConnAdd, DatabaseConnUser, DatabaseConnPwd);
        } catch (Exception ex){
            ex.printStackTrace();
            System.out.println("连接到MySQL数据库时出错！");
            System.exit(0);
        }
        
        
        //得到MySQL操作流
       
        try {
            System.out.println("-----------------  "+sql);
            java.sql.PreparedStatement stat = conn.prepareStatement(sql);
            boolean rs = stat.execute();
            
        } catch(Exception ex) {
            ex.printStackTrace();
            System.exit(0);
        }
        
        //关半程序所占用的资源
        try{
            conn.close();
        }catch(Exception ex){
            ex.printStackTrace();
            System.out.println("关闭程序所占用的资源时出错");
            System.exit(0);
        }
    }


    public static void main(String[] args) {
    	String sqlStr1="insert into tb_criminals(C_id,C_Name,C_Heavycrime,C_Outofdate,C_Charges,C_Prisonarea) ";
    	String sqlStr2="insert into tb_meetingrecords(MRE_Cid,MRE_Cname,MRE_Mdate) ";
    	String sqlStr3="insert into tb_Awardpunish(AP_Cid,AP_Date,AP_Form,AP_Reason) ";
    	String sqlStr4="insert into tb_Commutation(CT_Cid,CT_Preterm,CT_Dealdate,CT_Changedate,CT_Presentterm) ";
        ReadData readData = new ReadData();
                
        //造数据----------------------------------------------------------
        String path = "E:/监狱管理表.xls";

        readData.createData(path, "基本信息",sqlStr1);
        readData.createData(path, "会见记录信息",sqlStr2);
        readData.createData(path, "奖惩记录",sqlStr3);
        readData.createData(path, "加减刑记录",sqlStr4);

    }
}

件中有多个Sheet