SPIRNG EXCEL UPLOAD 하기

안녕하세요

EXCEL UPLOAD입니다.

 

pom.xml

<!-- excel 처리 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>

 

home.jsp

$("#excelUpForm").change(function(){
                var form = $("#excelUpForm")[0];
 
                var data = new FormData(form);
                $.ajax({
                   enctype:"multipart/form-data",
                   method:"POST",
                   url: './excelUp.do',
                   processData: false,   
                   contentType: false,
                   cache: false,
                   data: data,
                   success: function(result){  
                       alert("업로드 성공!!");
                   }
                });
            });
 
 
 
엑셀업로드 : <br/>
<form name="excelUpForm" id="excelUpForm" enctype="multipart/form-data" method="POST" action="./excelDown.do">
    <input type="file" id="excelFile" name="excleFile" value="엑셀 업로드" />
</form>

 

ExcelController.java

@RequestMapping(value = "/excelUp.do", method = RequestMethod.POST)
    public void ExcelUp(HttpServletRequest req, HttpServletResponse rep){
        logger.info("@@@@@@@@@@@@@@@ExcelUp START@@@@@@@@@@@@@@@");
 
        Map returnObject = new HashMap(); 
        
        try { // MultipartHttpServletRequest 생성 
            MultipartHttpServletRequest mhsr = (MultipartHttpServletRequest) req; 
            Iterator iter = mhsr.getFileNames(); 
            MultipartFile mfile = null; 
            String fieldName = ""; 
            
            // 값이 나올때까지
            while (iter.hasNext()) { 
                fieldName = iter.next().toString(); // 내용을 가져와서 
                mfile = mhsr.getFile(fieldName); 
                String origName; 
                origName = new String(mfile.getOriginalFilename().getBytes("8859_1"), "UTF-8"); //한글꺠짐 방지 // 파일명이 없다면 
                
                returnObject.put("params", mhsr.getParameterMap()); 
                
                
                //위치 및 파일
                homeService.getExcelUpload("D:\\"+origName);
            }
            
            } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block 
                e.printStackTrace(); 
            }catch (IllegalStateException e) { // TODO Auto-generated catch block 
                e.printStackTrace(); 
            } catch (IOException e) { // TODO Auto-generated catch block 
                e.printStackTrace(); 
            }
 
        
        logger.info("@@@@@@@@@@@@@@@ExcelUp END@@@@@@@@@@@@@@@");
        
    }

 

ExcelUtil.java

public static Workbook getWorkbook(String filePath) {
 
        /*
         * FileInputStream은 파일의 경로에 있는 파일을 읽어서 Byte로 가져온다.
         * 
         * 파일이 존재하지 않는다면은 RuntimeException이 발생된다.
         */
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
 
        Workbook wb = null;
 
        /*
         * 파일의 확장자를 체크해서 .XLS 라면 HSSFWorkbook에 .XLSX라면 XSSFWorkbook에 각각 초기화 한다.
         */
        if (filePath.toUpperCase().endsWith(".XLS")) {
            try {
                wb = new HSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        } else if (filePath.toUpperCase().endsWith(".XLSX")) {
            try {
                wb = new XSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
 
        return wb;
 
    }
 
    //Excel Upload시에 데이터를 얻어옵니다.
    @SuppressWarnings("deprecation")
    public static String cellValue(Cell cell) {
 
        String value = null;
        if (cell == null)
            value = "";
        else {
            switch (cell.getCellType()) { // cell 타입에 따른 데이타 저장
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // you should change this to your application date format
                    SimpleDateFormat objSimpleDateFormat = new SimpleDateFormat(
                            "yyyy-MM-dd");
                    value = ""
                            + objSimpleDateFormat.format(cell
                                    .getDateCellValue());
                } else {
                    value = ""
                            + String.format("%.0f",
                                    new Double(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_STRING:
                value = "" + cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                // value=""+cell.getBooleanCellValue();
                value = "";
                break;
            case Cell.CELL_TYPE_ERROR:
                value = "" + cell.getErrorCellValue();
                break;
            default:
            }
        }
 
        return value.trim();
    }

 

HomeService.java

public List<?> getExcelUpload(String excelFile);

 

 

HomeServiceImpl.java

public List<?> getExcelUpload(String excelFile){
        
        logger.info("@@@@@@@@@@@@@@@getExcelUpload START@@@@@@@@@@@@@@@ "+excelFile);
        
        Map<String, Object> map = new HashMap<String, Object>();
        List<?> list = null;
        
        try {
//            Workbook wbs = WorkbookFactory.create(new FileInputStream(excelFile));
            Workbook wbs = ExcelUtil.getWorkbook(excelFile);
            
            Sheet sheet = (Sheet) wbs.getSheetAt(0);
 
            //excel file 두번쨰줄부터 시작
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
                
                logger.info("@@@@@@@@map @@@@@@@@@@@@@@@@ i : "+i);
                
                Row row = sheet.getRow(i);
                
                //map.put("IDCOL", ""+ExcelUtil.cellValue(row.getCell(0)));
                map.put("NAMECOL", ""+ExcelUtil.cellValue(row.getCell(1)));
                map.put("VALUECOL", ""+ExcelUtil.cellValue(row.getCell(2)));
                
                //신규삽입
                homeMapper.insertDB(map);
            }
 
            logger.info("@@@@@@@@map @@@@@@@@@@@@@@@@"+map.toString());
            //데이터가져옵니다.
            list = homeMapper.testDbList(map);
            
        }catch(Exception e){
            logger.error("error : "+e.getMessage());
            logger.error("error : "+e);
        }
        
        logger.info("@@@@@@@@@@@@@@@getExcelUpload END@@@@@@@@@@@@@@@");
        return list;
        
    }

HomeMapper.java

public int insertDB(Map<String, Object> map);

 

testMapper.xml

<!-- 데이터 삽입 -->
    <insert id="insertDB" parameterType="java.util.Map">
        INSERT INTO testTable(IDCOL, NAMECOL, VALUECOL)
        VALUES(seq_id.nextval, #{NAMECOL}, #{VALUECOL})
    </insert>

 

 

감사합니다.