대략 소스를 설명하자면 poi라는 라이브러리를 이용하여
excel데이타를 mysql로 집어넣는건데요
blank cell에 대한 처리를 어떻게든 실행이 되게했지만
너무 수동적이라 for문을 돌려서 자동적으로 바꾸려하지만
계속 오류가나네요... 전문가님들 도움을 빌립니다.
public static void main(String[] args) {
// TODO Auto-generated method stub
String url = "jdbc:mysql://localhost/dahan?characterEncoding=utf8";
String user = "root";
String password = "apmsetup";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = (Connection) DriverManager.getConnection(url,user,password);
con.setAutoCommit(false);
PreparedStatement pstm1 = null ;
FileInputStream input = new FileInputStream("C:/Users/hyunwoo/Downloads/Personal Contacts.xls");
POIFSFileSystem fs = new POIFSFileSystem( input );
@SuppressWarnings("resource")
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Row row;
String del = "DROP TABLE IF EXISTS `dahanMail`";
/*String cre = "CREATE TABLE `dahanMail` (`Fullname` varchar(50),`Firstname` varchar(50),`Lastname` varchar(50) ,`Nickname` varchar(50),"
+ "`Company` varchar(500),`Department` varchar(500),`Position` varchar(500),`Mail1` varchar(50),"
+ "`Mail2` varchar(50),`Number1` varchar(50),`Number2` varchar(50),`Number3` varchar(50),"
+ "`PhoneNumber1` varchar(50),`PhoneNumber2` varchar(50),`Fax1` varchar(50),`Fax2` varchar(50),"
+ "`Address` varchar(50),`Website` varchar(50),`id` int NOT NULL ,PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;";*/
pstm1 = (PreparedStatement) con.prepareStatement(del);
pstm1.execute();
del = "CREATE TABLE `dahanMail` (`Fullname` varchar(50) ,`Firstname` varchar(50),`Lastname` varchar(50))";
//+ "`id` int NOT NULL ,PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;";
pstm1 = (PreparedStatement) con.prepareStatement(del);
pstm1.execute();
//String sql = "INSERT INTO dahanmail VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
String sql = "INSERT INTO dahanmail VALUES(?,?,?)";
PreparedStatement pstm = (PreparedStatement) con.prepareStatement(sql);
//cell과 row의 구별
for(int i = sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++){
row = sheet.getRow(i);
if(row.getCell(2)==null)
{
System.out.println("input the null");
pstm.setString(1,row.getCell(0).getStringCellValue());
pstm.setString(2,row.getCell(1).getStringCellValue());
pstm.setString(3,"null");
pstm.execute();
continue;
}
pstm.setString(1,row.getCell(0).getStringCellValue());
pstm.setString(2,row.getCell(1).getStringCellValue());
pstm.setString(3,row.getCell(2).getStringCellValue());
/* pstm.setString(4,row.getCell(3).getStringCellValue());
pstm.setString(5,row.getCell(4).getStringCellValue());
pstm.setString(6,row.getCell(5).getStringCellValue());
pstm.setString(7,row.getCell(6).getStringCellValue());
pstm.setString(8,row.getCell(7).getStringCellValue());
pstm.setString(9,row.getCell(8).getStringCellValue());
pstm.setString(10,row.getCell(9).getStringCellValue());
pstm.setString(11,row.getCell(10).getStringCellValue());
pstm.setString(12,row.getCell(11).getStringCellValue());
pstm.setString(13,row.getCell(12).getStringCellValue());
pstm.setString(14,row.getCell(13).getStringCellValue());
pstm.setString(15,row.getCell(14).getStringCellValue());
pstm.setString(16,row.getCell(15).getStringCellValue());
pstm.setString(17,row.getCell(16).getStringCellValue());
pstm.setString(18,row.getCell(17).getStringCellValue());*/
// pstm.setInt(4, (int) row.getCell(18).getNumericCellValue());
pstm.execute();
pstm.clearParameters();
System.out.println("Import rows "+i);
// Do something useful with the cell's contents
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
}catch(ClassNotFoundException e){
System.out.println(e);
}catch(SQLException ex){
System.out.println(ex);
}catch(IOException ioe){
System.out.println(ioe);
}
}
}
여기서 이부분을 어떻게 자동으로 만들수있을까여?
if(row.getCell(2)==null)
{
System.out.println("input the null");
pstm.setString(1,row.getCell(0).getStringCellValue());
pstm.setString(2,row.getCell(1).getStringCellValue());
pstm.setString(3,"null");
pstm.execute();
continue;
}
프로그램을 시작한지 얼마안되서 for문 돌려서 이쁘게 짜는건 어렵네요ㅠㅠ