JDOM, Apache commons fileupload, JExcelApi를 사용하여 파일업로드 및 변환

UPLOAD 패키지는 apache commons-fileupload-1.2.jar 를 이용하였다..
org.apache.commons.fileupload를 사용시에는 org.apache.commons.io 패키지가 필요하다.
commons-io-1.3.2.jar를 사용
http://www.apache.org

엑셀 업로드후 데이터를 추출하기위해 JExcelApi 2.6 (jdom.jar)을 이용하였다.
http://jexcelapi.sourceforge.net/
http://www.andykhan.com/jexcelapi/

업로드한 엑셀파일을 XML로 변환시켜주기위해 JDOM 1.1 (jxl.jar)을 이용하였다.
http://www.jdom.org/

엑셀 데이터는 8개의 컬럼
첫 시트를 기준으로 하였다.
엑셀 데이터의 첫 열은 제목표시줄

1, 2, 3 번 컬럼은 DB-String형
4, 5, 6번 컬럼은 DB-int형

허접하지만 나름 열심히 짜보았다.. ㅡ,.ㅡ;;


FileUp.java

package jhtest;

import java.io.PrintWriter;
import java.io.IOException;
import java.io.File;

import java.util.List;
import java.util.Iterator;
import java.util.Calendar;

import java.lang.Exception;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.fileupload.FileItem;

import jhtest.ExcelConvert;

public class FileUp extends HttpServlet
{
   public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException
  {
      try
      {
          response.setContentType("text/html;charset=euc-kr");
          PrintWriter out = response.getWriter();
   
          long yourMaxRequestSize = 5 * 1024 * 1024; // 최대 저장할수 있는 파일 사이즈5MBytes
          int  yourMaxMemorySize  = 1024* 50;           // memory size setting
        
          DiskFileItemFactory factory = new DiskFileItemFactory();
          factory.setSizeThreshold(yourMaxMemorySize);
 
          ServletFileUpload upload = new ServletFileUpload(factory);
          upload.setSizeMax(yourMaxRequestSize);
        
          List list = upload.parseRequest(request);
          out.print("파일 갯수 : " + list.size() + "<br>");

          Iterator iter = list.iterator();

          while(iter.hasNext())
          {
             FileItem item = (FileItem)iter.next();

             if(item.isFormField())            //폼체크
             {
                String name = item.getFieldName();
                String value = item.getString();

                out.print( "name = " + name + ", value = " + value + "<br>");
             }
             else
             {
                //상대경로 설정시(참고)
                //String contextPath = application.getRealPath("/");
                //File uploadedFile = new File(contextPath + "\\upload\\" + fileName );
                String uploadPath = "C:\\worktest\\test_servlet\\web\\test\\";

                //String fullFileName = item.getName();         //업로드파일
                //int pos = fullFileName.lastIndexOf("\\");
                //String fileName = fullFileName.substring(pos + 1);     //파일명
           
                Calendar currentSec = Calendar.getInstance();
                String fileName = Long.toString(currentSec.getTimeInMillis()) + ".xls";
                //파일명의 중복을 막기위해  milli Second로 파일명
           
                File uploadedFile = new File(uploadPath + fileName);  
                item.write(uploadedFile);          //파일업로드

                out.print( "파일명 : " + fileName + "<br>저장완료!!<br>");
        
                String tab = ExcelConvert.ExcelToWeb(uploadPath + fileName); //엑셀을 XML로 변환
                out.print(tab);          
             }
          }
       }
       catch(FileUploadException e)
       {
          e.toString();
       }
       catch(IOException e)
       {
           e.toString();
       }
       catch(Exception e)
       {
          e.toString();
       }
   }
}

ExcelConvert.java

package jhtest;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.IOException;

import java.util.List;

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

import org.jdom.Document;
import org.jdom.Element;

import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import jhtest.*;

public class ExcelConvert
{
   public static String ExcelToWeb(String str)
   {
      String ec="";
      try
      {
         Workbook workbook = Workbook.getWorkbook(new File(str));
   
         //for(int s=0;getSheet!~!~ ) //시트의 길이 검색시 참고
        Sheet sheet = workbook.getSheet(0); //엑셀파일의 시트

        Document doc = new Document();
   
        String[] e_name = {"position", "regno", "name", "w_level", "w_year", "year_h", "w_add", "chargeoff" };
   
        Element root = new Element("root");
   
        String[][] queryCell = new String[sheet.getRows()][sheet.getColumns()];
   
        for(int i=1; i < sheet.getRows() ; i++ )
        {
           Element work = new Element("work");
   
           for(int j=0; j < sheet.getColumns(); j++)
           {     
              Cell c = sheet.getCell(j, i);
              String col = c.getContents();
     
              queryCell[i][j] = col;    

              Element position = new Element(e_name[j]);
              position.setText(col);
              work.addContent(position);
           }
           root.addContent(work);
        }
   
        ec += ConvSqlQuery.ExcelInsertQuery(queryCell);  //DB insert
   
        doc.setRootElement(root);
        workbook.close();
   
        String xmlFullFileName = str.substring(str.lastIndexOf("\\")+1);      
        //업로드파일에서 파일명만 추출
        String xmlFileName = xmlFullFileName.substring(0, xmlFullFileName.lastIndexOf("."));
        //파일명에서 확장자 제외
   
        String savePath2 = "C:/worktest/test_servlet/web/test/" + xmlFileName + ".xml";
        ExcelConvert handler = new ExcelConvert();
        handler.xmlFile(doc, savePath2);
        
        ec += "XML파일명 : " + xmlFileName + ".xml<br>XML 변환 저장 완료!!";
     }
     catch(Exception e)
     {
        e.printStackTrace();
     }
     return ec;
  }
 
  private void xmlFile(Document doc, String file) throws IOException
  {
     XMLOutputter outx = new XMLOutputter();
     Format gF = outx.getFormat();
       
     gF.setEncoding("UTF-8");  //XML 생성 UTF-8 encoding..
     gF.setIndent("    ");
     outx.setFormat(gF);
       
     OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");      //File UTF-8 인코딩
     if (doc != null)
     {
        outx.output(doc, fw);
     }
  }
}

ConvSqlQuery.java

package jhtest;

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

import jhtest.DBConn;

public class ConvSqlQuery
{
   public static String ExcelInsertQuery(String[][] col)
   {
      Connection conn = null;
      PreparedStatement pstmt = null;
      String rtnStr = null;
 
      try
      {
         for(int i=1; i<col.length; i++)
         {
            String sql = null;
            conn = null;
            pstmt = null;
   
            sql = "INSERT INTO PISDBA.PA_SAL_WELFT1 VALUES(?, ?, ?, ?, ?, ?)";
            conn = DBConn.getConn();
            pstmt = conn.prepareStatement(sql);
   
            pstmt.setString(1, JHUtil.StringNullCheck(col[i][1]));
            pstmt.setString(2, JHUtil.StringNullCheck(col[i][0]));
            pstmt.setString(3, JHUtil.StringNullCheck(col[i][2]));
            pstmt.setInt(4, JHUtil.StrToIntNullCheck(col[i][5]));
            pstmt.setInt(5, JHUtil.StrToIntNullCheck(col[i][6]));
            pstmt.setInt(6, JHUtil.StrToIntNullCheck(col[i][7]));
   
            pstmt.executeUpdate();
   
            rtnStr += "<br>DB[" + i + "] 저장완료";
         }  
      }
      catch(SQLException e)
      {
         e.toString();
         rtnStr = "<b>DB Error!!! ::: </b>" + e.toString() + "<br>";
      }
      finally
      {
         DBConn.closeDBObject(pstmt);
         DBConn.closeDBObject(conn);
   
         return rtnStr;
      } 
   }
}

DBConn.java

package jhtest;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConn
{
   public static Connection getConn()
   {
      Connection conn = null;
      try
      {
         Class.forName("oracle.jdbc.driver.OracleDriver");
         conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:XE", "유저명", "패스워드");
      }
      catch(SQLException e)
      {
         System.out.println("SQLException::" + e.toString());
         conn = null;
      }
      catch(Exception e)
      {
         System.out.println("Exception::" + e.toString());
         conn = null;
      }
      return conn;
   }
 
 
   public static void closeDBObject(Connection obj)
   {
      try
      {
         if(obj != null) obj.close();
      }
      catch(Exception ex1)
      {  
      }
   }
 
   public static void closeDBObject(Statement obj)
   {
      try
      {
         if(obj != null) obj.close();
      }
      catch(Exception ex1)
      {  
      }
   }
 
   public static void closeDBObject(PreparedStatement obj)
   {
      try
      {
         if(obj != null) obj.close();
      }
      catch(Exception ex1)
      {   
      }
   }
 
   public static void closeDBObject(ResultSet obj)
   {
      try
      {
         if(obj != null) obj.close();
      }
      catch(Exception ex1)
      {   
      }
   }
}

JHUtil.java

package jhtest;

public class JHUtil
{
   public static String StringNullCheck(String str)
   {
      if(str==null) str="";
      else str.trim();
 
      return str;
   }
 
   public static Integer StrToIntNullCheck(String num)
   {
      int inum;
 
      if(num==null) inum=0;
      else inum = Integer.parseInt(num);
 
      return inum;
   }
}