Excel Sheet Configuration XML 아래와 같이 src/main/resources/excel/폴더 아래에 위치한다.

Excel Sheet Configuration XML의 최상위 Tag는 <COLUMNS>이고 하위 Tag로 복수의 <COLUMN>를 갖는다. 다음은 Excel Sheet Configuration XML의 예이다.

<?xml version="1.0" encoding="UTF-8"?>
<COLUMNS>
    <COLUMN>
        <COLUMN_NAME>ID</COLUMN_NAME>
        <FIELD_NAME>movieId</FIELD_NAME>
        <COLUMN_WIDTH>20</COLUMN_WIDTH>
        <REQUIRED>true</REQUIRED>
        </COLUMN>
    <COLUMN>
        <COLUMN_NAME>장르</COLUMN_NAME>
        <FIELD_NAME>genreName</FIELD_NAME>
        <COLUMN_WIDTH>20</COLUMN_WIDTH>
    </COLUMN>
    <COLUMN>
        <COLUMN_NAME>제목</COLUMN_NAME>
        <FIELD_NAME>title</FIELD_NAME>
        <COLUMN_WIDTH>50</COLUMN_WIDTH>
    </COLUMN>
    <COLUMN>
        <COLUMN_NAME>감독</COLUMN_NAME>
        <FIELD_NAME>director</FIELD_NAME>
        <COLUMN_WIDTH>10</COLUMN_WIDTH>
    </COLUMN>
    <COLUMN>
        <COLUMN_NAME>배우</COLUMN_NAME>
        <FIELD_NAME>actors</FIELD_NAME>
        <COLUMN_WIDTH>10</COLUMN_WIDTH>
    </COLUMN>
    <COLUMN>
        <COLUMN_NAME>티켓 가격</COLUMN_NAME>
        <FIELD_NAME>ticketPrice</FIELD_NAME>
        <COLUMN_WIDTH>10</COLUMN_WIDTH>
    </COLUMN>
    <COLUMN>
        <COLUMN_NAME>개봉일자</COLUMN_NAME>
        <FIELD_NAME>releaseDate</FIELD_NAME>
        <COLUMN_WIDTH>10</COLUMN_WIDTH>
        <COLUMN_TYPE>Date</COLUMN_TYPE>
        <MASK>yyyy-MM-dd</MASK>
    </COLUMN>
</COLUMNS>

Excel Sheet Configuration XML의 COLUMN의 속성 값은 다음과 같다.

다음은 Anyframe excel plugin에서 샘플로 제공하고 있는 화면이다.

화면 하단의 Excel Down 버튼을 클릭하면 jsp에서 지정한 query id를 이용해 쿼리가 실행되고 그 결과가 Excel파일로 다운로드 된다. 아래는 Excel Download를 하기 위해 필요한 변수값을 jsp파일에 세팅한 예이다.

<form:form method="post" name="excelDownloadForm">
    <input name="queryId" type="hidden" value="" />
    <input name="title" type="hidden" value="" />
    <input name="nowPlaying" type="hidden" value="Y" />
    <input name="fileName" type="hidden" value="movie" />
    <input name="columnInfoFile" type="hidden" value="movie" />
</form:form>
        

queryId, columnInfoFile, fileName은 Excel Download기능을 위한 필수 변수이다.

title, nowPlaying과 같은 변수는 Query를 실행할 때 필요한 변수들이다(조회조건)

Excel Download 버튼을 클릭했을 때 실행되는 javascript는 다음과 같다.

function fncExcelDownload() {
    document.excelDownloadForm.queryId.value="excel.findMovieList";
    document.excelDownloadForm.action="<c:url value='/excelDownload.do?method=excelDownload'/>";
    document.excelDownloadForm.submit();	
}

Excel Download의 URL은 /excelDownload.do?method=excelDownload이다. Excel Down버튼을 클릭해 Query Id가 excel.findMovieList인 쿼리를 실행하고 결과가 정상적으로 Excel파일로 만들어졌다면 아래와 같은 화면을 볼 수 있다.

Anyframe Excel Plugin은 약 65000 ROW에 대한 Download 테스트가 완료 되었다.

아래는 Download된 Excel 파일의 일부이다.

ExcelController의 excelDownload는 ExcelService의 download메소드를 호출하고 그 결과값을 HSSF를 이용해 Excel파일을 생성한다. 아래는 ExcelController excelDownload 메소드 소스코드의 일부이다.

@RequestMapping(params = "method=excelDownload")
public void excelDownload(HttpServletRequest request, HttpServletResponse response) throws Exception {
//중략
        List<Map> resultList = excelService.download(searchMap);
		
//중략
    	
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(fileName);
    	
        OutputStream fileOut = null;
        try {
            fileOut = response.getOutputStream();
            HSSFRow row;
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		
            HSSFCell cell;
    		
            row = sheet.createRow(0);
    		
            List<ColumnInfo> columnInfoList = getColumnInfo(request, columnInfoFile);
    		
            int columnCount = columnInfoList.size();
            String[] header = new String[columnCount];
            int[] cellwidth = new int[columnCount];
            String[] fieldName = new String[columnCount];
            String[] columnType = new String[columnCount];
            String[] mask = new String[columnCount];
    		
            ColumnInfo columnInfo = null;
            short width = 265;
//중략
    }
}

다음은 Anyframe excel plugin에서 샘플로 제공하고 있는 화면이다.

화면 하단의 파일 찾기 버튼을 누르면 아래와 같이 로컬에서 Excel파일을 선택 할 수 있다.

Excel 파일을 Upload 하기 위해서는 Download와 마찬가지로 jsp에서 서버로 전송해야할 변수 값들이 있다. 아래는 Upload하기 위해 설정된 jsp파일 내의 변수들이다. Upload를 위해 사용되는 form tag의 enctype은 반드시 multipart/form-data이다.

<form:form method="post" name="excelUploadForm" enctype="multipart/form-data">
    <input type="file" name="excelFile" width="0">
    <input name="queryId" type="hidden" value="" />
    <input name="columnInfoFile" type="hidden" value="movie" />
    <input name="resultPage" type="hidden" value="redirect:/excelMovieFinder.do?method=list" />
</form:form>
        

Excel Upload 버튼을 클릭했을 때 실행되는 javascript는 다음과 같다.

function fncExcelUpload() {
    document.excelUploadForm.queryId.value="excel.excelMovieInsert";

    var filePath = document.excelUploadForm.excelFile.value;

    if(filePath.indexOf('.xls') == -1){
        alert("Excel 파일이 아닙니다.");
    return;
    }
    document.excelUploadForm.action="<c:url value='/excelDownload.do?method=excelUpload'/>";
    document.excelUploadForm.submit();	
}

Excel Upload의 URL은 /excelDownload.do?method=excelUpload이다. Excel 파일의 데이터를 DB에 저장하기 위해 실행되는 쿼리의 ID는 excel.excelMovieInsert이다.

ExcelController의excelUpload메소드 전송 된 Excel파일을 분석해 데이터를 HashMap형태로 만든 다음 ExcelService의 upload메소드를 호출해 DB에 데이터를 저장한다. 아래는 ExcelController excelUpload 메소드 소스코드의 일부이다.

@RequestMapping(params = "method=excelUpload")
public String excelUpload(HttpServletRequest request, @RequestParam(value = "excelFile", required = false) MultipartFile excelFile) throws Exception {
		
    Map paramMap = bindRequestToMap(request);
		
    int startRow = 1;
//저장을 시작할 row값, 설정이 없을 경우 1부터 시작, 0은 Heder정보
    if( paramMap.get("startRow") != null ){
    startRow = Integer.parseInt(paramMap.get("startRow").toString());
    }
		
    POIFSFileSystem fs = null;		
    try{
        fs = new POIFSFileSystem(excelFile.getInputStream());
    }catch(Exception e){
        e.printStackTrace();
    }
		
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    int sheetNum = workbook.getNumberOfSheets();
		
//시트는 한개로 제약
    HSSFSheet sheet = workbook.getSheetAt(0);
		
//저장한 데이터의 마직막 줄, 설정이 없을 경우는 시트 전체의 Row
    int endRow = 0;
    if( paramMap.get("endRow") != null ){
        endRow = (Integer)paramMap.get("endRow");
    }else{
        endRow = sheet.getLastRowNum();
    }
    int rowCount = endRow;
		
    String columnInfoFile = "";
		
//ColumnInfo파일에서 ColumnName과 FieldName값을 HashMap으로 추출
    if( paramMap.get("columnInfoFile") == null ){
        throw new Exception("fail to get column info xml file name");
    }else{
        columnInfoFile = paramMap.get("columnInfoFile").toString();
    }
		
    List<ColumnInfo> columnInfoList = getColumnInfo(request, columnInfoFile);
		
    Map<String, String> fieldMap = new HashMap<String, String>();
    Map<String, Boolean> requiredMap = new HashMap<String, Boolean>();
    for ( int i = 0 ; i < columnInfoList.size() ; i ++ ){
        ColumnInfo columnInfo = columnInfoList.get(i);
        fieldMap.put(columnInfo.getColumnName(), columnInfo.getFieldName());
        requiredMap.put(columnInfo.getColumnName(), columnInfo.isRequired());
    }
		
    String[] header = null; 
    String[] filedName = null;  
    Boolean[] requiredField = null;
		
//컬럼 해더 정보 추출
    HSSFRow row = sheet.getRow(0);
    if( row != null ){
        int headerCount = row.getPhysicalNumberOfCells();
        header = new String[headerCount];
        filedName= new String[headerCount];
        requiredField = new Boolean[headerCount];
			
        for( int i = 0 ; i < headerCount ; i ++ ){
            HSSFCell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
            header[i] = getCellValue(cell);
            filedName[i] = fieldMap.get(header[i]);
            requiredField[i] = requiredMap.get(header[i]);
        }
    }
}
		
    List<ListOrderedMap> insertList = new ArrayList();
    ListOrderedMap insertMap = null;
		
//컬럼 값 추출
    for( int i = startRow ; i <= endRow ; i ++ ){
        boolean isInsertRow = true;
        row = sheet.getRow(i);
        if( row != null ){
            insertMap = new ListOrderedMap();
            for( int j = 0 ; j < filedName.length ; j ++ ){
                HSSFCell cell = row.getCell(j);
                if( cell != null ){
                    if( !requiredField[j] ){
                        insertMap.put(filedName[j], getCellValue(cell));
                    }else if( cell.getCellType() != HSSFCell.CELL_TYPE_BLANK ){
                        insertMap.put(filedName[j], getCellValue(cell));
                    }else{
                        isInsertRow = false;
                        break;
                    }
                }
            }
            if(isInsertRow){
            insertList.add(insertMap);
        }
    }
    }
//ExcelService의 upload method호출
    excelService.upload(paramMap, insertList);
    return paramMap.get("resultPage").toString();
}