Excel Reader

jxl.jar 파일이 필요합니다. 

jxl.jar

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;

import java.io.File;
import jxl.Workbook;
import jxl.Sheet;

 

public class Ex {
 
 String url = "jdbc:oracle:thin:@DB IP를 넣을 부분:SID 넣을 부분";
 String user = "유저ID";
 String passwd = "패스워드";
 Connection conn;
  
 public Ex() {
//    System.out.println("Ex 실행");
 }
  
 public boolean dbConn() {
  boolean flag = true;

  try {
          Class.forName("oracle.jdbc.driver.OracleDriver");
   conn = DriverManager.getConnection (url, user, passwd);

  } catch ( Exception e ) {
          flag = false;
          System.out.println(e.getMessage());
  }
     
  return flag;
 }

   /**
    * Excel File Read/DB Insert
    *
    */
   public void readXls(){
    try{
     Workbook workbook = Workbook.getWorkbook(new File("c:/엑셀파일.xls"));
    
     PreparedStatement pstmt = null;
         
     for(int a=0 ; a< workbook.getNumberOfSheets() ; a++){
     
      Sheet sheet = workbook.getSheet(a);

      int colCnt = sheet.getColumns();
      int rowCnt = sheet.getRows();
     
      StringBuffer sb = new StringBuffer();
     
      sb.append(" insert into 테이블명\n ");
      sb.append(" values ( 입력값 갯수만큼 '?' )");
     
      pstmt = conn.prepareStatement(sb.toString());
     
      for(int n=0 ; n<rowCnt ; n++){
      
       int m = 0;
       int idx = 1;
      
       for(int k=0 ; k<colCnt ; k++){
        pstmt.setString(idx++, sheet.getCell(m++,n).getContents());
       }

       pstmt.addBatch();
      }
     
      pstmt.executeBatch();
      System.out.println("Batch_작업 끝");
     }
    
     conn.close();
           pstmt.close();
          
    }catch(Exception e){
     System.out.println("error -- " + e.getMessage());
    }
   }
  

   public static void main(String args[]) {
       
       Ex ex = new Ex();

       if(ex.dbConn()) {
        System.out.println("DB 연결 성공");
       
        /* Excel File Read & DB Insert */
        ex.readXls();
               
       }else{
        System.err.println("DB 연결 실패");
       }
      
   }
}