Spring

xlsx, xls upload

Controller.java

package ~~~;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.POST;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import java.util.logging.Logger;
import ~.~.ExcelServiceImpl;

//@Controller 
public class ExcelController {
//		@POST
//		@RequestMapping(value = "uploadExcelFile.do", method = RequestMethod.POST)
	    public void uploadExcelFile(MultipartHttpServletRequest request, Model model, HttpServletResponse response) {
			
			System.out.println("############## /uploadExcelFile.do Start!!! ##############");
			ExcelServiceImpl excelServiceImpl = new ExcelServiceImpl();
			List<Map> list = null;
			System.out.println("############## response : " + response);
			response.setCharacterEncoding("UTF-8");
			try {
				PrintWriter printWriter = response.getWriter();
				JSONObject jsonObject = new JSONObject();
				
				MultipartFile file = null;
		      	Iterator<String> iterator = request.getFileNames();
		        if(iterator.hasNext()) {
		            file = request.getFile(iterator.next());
		        }
		        list = excelServiceImpl.uploadExcelFile(file);
		        
		        if(list !=null) {
					jsonObject.put("rs", "0000");
		        }else {
		        	jsonObject.put("rs", "9999");
		        }		    
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			System.out.println("############## /uploadExcelFile.do End!!! ##############");
	    }
}

ExcelService.java

package ~~~;

import java.util.List;
import java.util.Map;

import org.springframework.web.multipart.MultipartFile;

public interface ExcelService {
	public List<Map> uploadExcelFile(MultipartFile excelFile);
}

ExcelServiceImpl.java

package ~~~;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

@Service("ExcelService")
public class ExcelServiceImpl implements ExcelService{
	public List<Map> uploadExcelFile(MultipartFile excelFile){
        List<Map> list = new ArrayList<Map>();
        List list_name = new ArrayList();
        List<Map> value_list = new ArrayList();
        try {
            OPCPackage opcPackage = OPCPackage.open(excelFile.getInputStream());
            XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);
            Map<String, String> map1 = new HashMap<>();
            // 첫번째 시트 불러오기
            XSSFSheet sheet = workbook.getSheetAt(0);
            // 컬럼 이름 만들기
            XSSFRow column_list = sheet.getRow(0);
            for(int a =0; a < column_list.getLastCellNum() ; a++){
        		list_name.add(column_list.getCell(a).getStringCellValue()+"");
        	}
            int rowindex = 0;
            int columnindex = 0;
            // 행 관련 
            int rows = sheet.getPhysicalNumberOfRows();
            for(rowindex=1;rowindex<rows;rowindex++){
            	XSSFRow row = sheet.getRow(rowindex);
            	if(row != null){
            		int cells = row.getPhysicalNumberOfCells();
            		for(columnindex=0;columnindex<=cells;columnindex++){
            			XSSFCell cell = sheet.getRow(rowindex).getCell((short)columnindex);
            			String value="";
            			
            			if(cell==null){
            				continue;
            			}else{
            				//타입별로 내용 읽기
            				switch (cell.getCellType()){
            					case XSSFCell.CELL_TYPE_FORMULA:
            						value = cell.getCellFormula();
            						break;
            					case XSSFCell.CELL_TYPE_NUMERIC:
            						value = cell.getNumericCellValue()+"";
            						break;
            					case XSSFCell.CELL_TYPE_STRING:
            						value = cell.getStringCellValue()+"";
            						break;
            					case XSSFCell.CELL_TYPE_BLANK:
            						value = cell.getBooleanCellValue()+"";
            						break;
            					case XSSFCell.CELL_TYPE_ERROR:
            						value = cell.getErrorCellValue()+"";
            						break;
            				}
            			}
            			map1.put((String) list_name.get(columnindex), value);
            		}
            		value_list.add(map1);
            		map1 = new HashMap<>();
            	}
            }
           System.out.println("value_list : "  + value_list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value_list;
    }
}

popup_excel.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title>test</title>
	<script src="https://code.jquery.com/jquery-1.10.2.js"
	type="text/javascript"></script>
	<script src="js/app-ajax.js" type="text/javascript"></script>
	<script type="text/javascript"><%@include file="./index2.js" %></script>
	<script type="text/javascript">
	function doExcelUploadProcess(){
        var form = new FormData(document.getElementById('form1'));
        console.log("지금 doExcelUploadProcess는 동작하는중")
        $.ajax({
            url: "/uploadExcelFile.do",
            data: form,
            processData: false,
            contentType: false,
            type: "POST",
            success: function(data){
            	console.log("success!!!")
                var htmlValue;
            	console.log(data)
            	document.getElementById('result').innerHTML = JSON.stringify(data);            	
            },
            error: function(xhr, status, error){
            	console.log("error!!!")
                console.log("xhr:"+xhr+", status:"+ status + ", error:"+error);
            }

        })
    }
	</script>
</head>
<body>
    <form id="form1" name="form1" method="post" enctype="multipart/form-data">
		<input type="file" id="fileInput" name="fileInput">
		<button type="button" onclick="doExcelUploadProcess()">엑셀업로드 작업</button>
	</form>
</body>