|
import jxl.*;
import java.io.*;
import java.util.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class ReadXLS
{
String filename = null;
//ReadXLS 생성자
public ReadXLS(String filename)
{
this.filename = filename.trim();
}
//String 반환하는 메소드
public boolean getData(int row) throws Exception
{
String preSql = null;
String strURL = "jdbc:oracle:thin:@address:포드:계정정보"; //운영
String strOracleDriver = "oracle.jdbc.OracleDriver";
//파일 생성및 데이터 선언
StringBuffer cellData = new StringBuffer();
File file = new File(filename);
String impType = null;
//존재여부 판단
if(file.exists())
{
//파일여부 판단
if(file.isFile())
{
StringTokenizer st = new StringTokenizer(filename, ".");
String xls = null;
//파일 확장자 구하기
//System.out.println("st.countTokens : " + st.countTokens());
//System.out.println("st.nextToken() : " + st.nextToken());
while(st.hasMoreTokens())
{
xls = st.nextToken();
}
//확장자 엑셀파일 여부판단
if(xls.equals("xls")){
Class.forName(strOracleDriver);
Connection conn = DriverManager.getConnection(strURL,"kaida","kaida123");
Statement st1 = null;
CallableStatement cs = null;
preSql = "insert into 테이블명 (impfile_seq, xlsname,imptype ";
//엑셀정보 에서 쉬트정보 가져오기
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
//데이터 생성및 구분자 삽입 col=>[xxx] / row=>\n
System.out.println("sheet row : " + sheet.getRows());
System.out.println("sheet cloumns : " + sheet.getColumns());
int q;
q=1;
for (int j=1; j<sheet.getColumns(); j++){
preSql += " ,TEMP"+q;
q++;
}
preSql += ") values ( ?,? ";
for (int j=0; j<sheet.getColumns(); j++){
preSql += ",? ";
}
preSql += " )";
System.out.println("preSql : " + preSql);
PreparedStatement stat = conn.prepareStatement(preSql);
int count;
for (int i=row+1; i<sheet.getRows(); i++ ) {
stat.setInt(1, i);
stat.setString(2, filename);
count = 3;
for (int j=0; j<sheet.getColumns(); j++ )
{
Cell cell = sheet.getCell(j,i);
cellData.append("["+cell.getContents().trim()+"]");
stat.setString(count, cell.getContents().trim());
if(count==3) impType = cell.getContents().trim();
count++;
}
cellData.append("\n");
stat.executeUpdate();
}
workbook.close();
try {
st1 = conn.createStatement();
cs = conn.prepareCall("{call cf_imp_excel_Batch(?,?)}");
cs.setString(1, filename);
cs.setString(2, impType);
cs.executeUpdate();
cs.close();
}catch(SQLException se){
System.out.println(se.getMessage());
}
conn.close();
}
else
{
throw new Exception("엑셀파일이 아닙니다.");
}
}
else
{
throw new Exception("파일이 아닙니다.");
}
}
else
{
throw new Exception("존재하지 않는 이름 입니다.");
}
return true;
}//end getData()
public static void main(String [] args)
{
boolean chk = false;
try
{
// 파일명을 받아서 해당 디렉토리에 서 찾아서 올리도록 한다.
ReadXLS readxls = new ReadXLS("test.xls");
chk = readxls.getData(0);
}//End Of try
catch (Exception e)
{
e.printStackTrace();
}//End Of catch
}//End Of Main
}
| 