jxl.jar 를 이용한 엑셀파일 읽고 DB 저장

jxl.jar 를 이용한 엑셀파일 읽고 DB 저장

 

<%@ page import="org.apache.commons.fileupload.*" %>
<%@ page import="jxl.*" %>
<%
    DBHandler dbhandle = new DBHandler();
    Util util = new Util();
    UtilParam param = new UtilParam(request);

    ArrayList> aryDB = new ArrayList>();
    Hashtable hsROW = new Hashtable();

    String sSql = "";
//////////////////////////////////////////////////////////////////////////////
    String sSavePath = sROOTPATH + "ctrlFolder/corporation/dwnExcel/";
    String sFilename = "";

    // MultiPart
    try{
        DiskFileUpload fu = new DiskFileUpload();
        fu.setSizeMax(20*1024*1024);            //  최대 20MB
        fu.setSizeThreshold(4096);
        List items = null;
        try{
            fu.setHeaderEncoding("utf-8");
            items = fu.parseRequest(request);
            Iterator iter = items.iterator();
            String name = "";
            String value = "";

            while(iter.hasNext()){
                FileItem item = (FileItem)iter.next();
                if(!item.isFormField()){
                    name = item.getFieldName();
                    value = item.getName();

                    if(!value.equals("")){
                        if(value.length() > 1){
                            sFilename  = util.getFileName(value).trim();
                            String fileExt = sFilename.substring(sFilename.lastIndexOf(".")).replace(".","");

                            if(!"xls".contains(fileExt.toLowerCase())) {
                                out.println("");
                                dbhandle.close(dbhandle.con);
                                return;
                            }else{
                                File upFile = util.getFile(sSavePath,sFilename);
                                item.write(upFile);
                                sFilename = upFile.getName();
                                break;
                            }
                        }else{
                            out.println("");
                            dbhandle.close(dbhandle.con);
                            return;
                        }
                    }
                }
            }
        }catch(FileUploadBase.SizeLimitExceededException e){
            // System.out.println(e.toString());
            out.println("");
            dbhandle.close(dbhandle.con);
            return;
        }catch(Exception ee){
            // System.out.println("용량이외의 오류:::" + ee.toString());
            out.println("");
            dbhandle.close(dbhandle.con);
            return;
        }

        // No MultiPart
    }catch(Exception ee){
        // System.out.println("87Line:: "+ee.toString());
        out.println("");
        dbhandle.close(dbhandle.con);
        return;
    }

    if(!sFilename.equals("")){
        // 업로드된 파일 불러오기
        String sUP_FileNum = "F"+util.getCurrentDate("yyyyMMddHHmmss");
        Workbook workbook = Workbook.getWorkbook(new File(sSavePath + sFilename));
        Sheet sheet = workbook.getSheet(0);

        int col = sheet.getColumns();  // 시트의 컬럼의 수를 반환한다.
        int row = sheet.getRows();   // 시트의 열의 수를 반환한다.

        String sNum = "";
        String sCOL0 = "";
        String sCOL1 = "";
        String sCOL2 = "";
        String sCOL3 = "";
        String sCOL4 = "";
        String sCOL5 = "";
        String sCOL6 = "";
        String sCOL7 = "";
        String sCOL8 = "";
        String sCOL9 = "";
        String sCOL10 = "";

        int iTotalCnt = 0;
        int iOKcnt = 0;
        int iERRcnt = 0;

        StringBuilder sb = new StringBuilder();

        // DB 입력.
        for(int i = 1; i < row; i++) {               // 열의 시작 정의 : (0 부터 시작함 => 2번째 줄)
            try{
                sNum = i + "";
                sCOL0 = sheet.getCell(0, i).getContents();  if (sCOL0 == null) sCOL0 = "";  sCOL0 = sCOL0.trim();
                sCOL0 = sCOL0.replaceAll("'", "");  sCOL0 = sCOL0.replaceAll("-", "");  sCOL0 = sCOL0.replaceAll(" ", "");      // 사업자번호
                sCOL1 = sheet.getCell(1, i).getContents();  if (sCOL1 == null) sCOL1 = "";  sCOL1 = sCOL1.trim();
                sCOL1 = sCOL1.replaceAll(" ", "");   // 업체명
                sCOL2 = sheet.getCell(2, i).getContents();  if (sCOL2 == null) sCOL2 = "";  sCOL2 = sCOL2.trim();
                sCOL3 = sheet.getCell(3, i).getContents();  if (sCOL3 == null) sCOL3 = "";  sCOL3 = sCOL3.trim();
                sCOL4 = sheet.getCell(4, i).getContents();  if (sCOL4 == null) sCOL4 = "";  sCOL4 = sCOL4.trim();
                sCOL5 = sheet.getCell(5, i).getContents();  if (sCOL5 == null) sCOL5 = "";  sCOL5 = sCOL5.trim();
                sCOL5 = sCOL5.replaceAll(" ", "");   // 대표자
                sCOL6 = sheet.getCell(6, i).getContents();  if (sCOL6 == null) sCOL6 = "";  sCOL6 = sCOL6.trim();
                sCOL7 = sheet.getCell(7, i).getContents();  if (sCOL7 == null) sCOL7 = "";  sCOL7 = sCOL7.trim();
                sCOL8 = sheet.getCell(8, i).getContents();  if (sCOL8 == null) sCOL8 = "";  sCOL8 = sCOL8.trim();
                sCOL8 = sCOL8.replaceAll(" ", "");   // 담당자
                sCOL9 = sheet.getCell(9, i).getContents();    if (sCOL9 == null) sCOL9 = "";    sCOL9 = sCOL9.trim();
                sCOL10 = sheet.getCell(10, i).getContents();    if (sCOL10 == null) sCOL10 = "";    sCOL10 = sCOL10.trim();
            }catch (Exception ee){
                out.println("");
                dbhandle.close(dbhandle.con);
                return;
            }

            if (!sCOL1.equals("")) {
                sSql = "INSERT INTO TB_BIZ ( " +
                        "BIZ_NUM,  " +
                        "BIZ_NAME,  " +
                        "BIZ_GUBUN,  " +
                        "BIZ_TYPE,  " +
                        "BIZ_GOODS,  " +
                        "BIZ_OWNER,  " +
                        "BIZ_TEL,  " +
                        "BIZ_ADDR,  " +
                        "BIZ_CHARGE,  " +
                        "BIZ_CRG_TEL,  " +
                        "BIZ_CRG_EMAIL,  " +
                        "UP_FILENUM,  " +
                        // "ETC1,  " +
                        // "ETC2,  " +
                        // "ETC3,  " +
                        // "ETC4,  " +
                        // "ETC5,  " +
                        "REGDATE,  " +
                        "REG_ID,  " +
                        "REG_IP  " +
                        // "EDITDATE,  " +
                        // "EDIT_ID,  " +
                        // "EDIT_IP " +
                        ") VALUES ( " +
                        "'" + sCOL0 + "',  " +
                        "'" + sCOL1 + "',  " +
                        "'" + sCOL2 + "',  " +
                        "'" + sCOL3 + "',  " +
                        "'" + sCOL4 + "',  " +
                        "'" + sCOL5 + "',  " +
                        "'" + sCOL6 + "',  " +
                        "'" + sCOL7 + "',  " +
                        "'" + sCOL8 + "',  " +
                        "'" + sCOL9 + "',  " +
                        "'" + sCOL10 + "',  " +
                        "'" + sUP_FileNum + "',  " +
                        // "etc1,  " +
                        // "etc2,  " +
                        // "etc3,  " +
                        // "etc4,  " +
                        // "etc5,  " +
                        "now(),  " +
                        "'" + sUSER_ID + "',  " +
                        "'" + sREMOTE_IP + "'  " +
                        // "editdate,  " +
                        // "edit_id,  " +
                        // "edit_ip " +
                        ") ";

                iTotalCnt++;
                try {
                    int iResult = dbhandle.dbUpdate(sSql);
                    if (iResult <= 0) {
                        sb.append("
" + sCOL1 + "(" + sCOL0 + ") : [오류발생] 
");
                        iERRcnt++;
                    } else {
                        iOKcnt++;
                    }
                } catch (Exception ee) {
                    sb.append("
" + sCOL1 + "(" + sCOL0 + ") : [오류발생] 
");
                    iERRcnt++;
                }

            } else {
                break;          //  사업자명 없으면 무조건 Break...
            }

        }

        out.println("");
        if(sb != null) out.println(sb.toString());

    }else {     // 파일명이 없으면 Back!!
        out.println("");
        dbhandle.close(dbhandle.con);
        return;
    }
    dbhandle.close(dbhandle.con);    
%>