자바 jxl 이용한 엑셀 파일 디비 인서트 프로그램 / JAVA EXCEL DBConnect

jxl.jar

엑셀 파일을 인서트 하기위한 jxl.jar파일

먼저 서버 연결을 위한 프로그램을 작성합니다

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



public class DBConnect {
	
	public static Connection getConnection(){
		Connection con=null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String dbUrl = "jdbc:mysql://IP:PORT/DB";
			String user="USER";
			String password="PASSWORD";
			con=DriverManager.getConnection(dbUrl,user,password);
			
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	
	public static void close(Connection con, PreparedStatement pstmt){
		if(con !=null){
			try{
				con.close();
				pstmt.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	
	public static void close(ResultSet rs, PreparedStatement pstmt){
		try {
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
	}
	
public static void close(PreparedStatement pstmt){
		
		try {
			
			pstmt.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}

	}
	public static void rollback(Connection conn){
		try{
			conn.rollback();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}

이제 엑셀파일을 데이터 베이스에 넣기위한 클래스를 작성합니다

public class SkillInsert {

//엑셀파일명

public static String fileName="경로";

public static String tableName="테이블 이름";

public static String lastColumnName="마지막 컬럼명";

public static int statCell=시작 셀;

public static int endCell=마지막 셀;

public static void main(String args[]){

Workbook workbook=null;

       PreparedStatement pstmt=null;

Connection conn=DBConnect.getConnection();

try{

conn.setAutoCommit(false);

workbook=Workbook.getWorkbook(new File(fileName));

Sheet sheet=workbook.getSheet(2);

if(workbook !=null){

sheet=workbook.getSheet(2);

if(sheet !=null){

String value = null;

for(int x=statCell; x<endCell; x++){

String sqlQuery="INSERT INTO "+tableName+"(CATEGORY,SKILL_ID,DESCRIPTION,SKILLTYPE,LS,POINT,NAME)";

String sqlValue="VALUES('L',BINARY('";

for(int y=0;y<sheet.getColumns(); y++){

if(y<6){

if(sheet.getCell(y, 0).getContents().equals("SKILL_ID")){

sqlValue+=sheet.getCell(y, x).getContents().concat("'),'");

}

value=sheet.getCell(y, x).getContents();

if(sheet.getCell(y, x).getContents().contains("%")){

value=sheet.getCell(y, x).getContents().replace("%", "");

}

if(sheet.getCell(y, 0).getContents().equals("DESC")){

           

            if(sheet.getCell(y,x).getContents().equals("")){

           

            int z=1;

            while(sheet.getCell(y, x).getContents().equals("") ){

           

           

            value=sheet.getCell(y, x-z).getContents();

            z++;

            if(!value.equals("")){

            break;

            }

            }

           

            }

            }

if(y==5){

sqlValue+=value+"')";

}else if(!sheet.getCell(y, ).getContents().equals("SKILL_ID"))                                                                                      {

sqlValue+=value+"','";

}

}

}

System.out.println(sqlQuery+sqlValue);

pstmt=conn.prepareStatement(sqlQuery+sqlValue);

pstmt.execute(sqlQuery+sqlValue);

}

conn.commit();

}else

           {

               System.out.println( "Sheet is null!!" );

           }

}

}catch(Exception e){

e.printStackTrace();

}finally{

 workbook.close();

 DBConnect.close(conn,pstmt);

 System.out.println("프로그램 종료");

}

}

}