[JSP] 엑셀 파일 변환 XLSX -> XLS, XLS -> XLSX (JSP Page Only)

POI를 이용하여 엑셀의 파일 타입을 변환해주는 프로그램[?]

구동 순서
1. 사용자가 파일을 업로드.
2. 해당 파일을 일단 서버에 저장후
3. 그 파일을 가져와서 xlsx면 xls로, xls면 xlsx로 변환해준다.


1. upload 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
%>

xlsx 는 xls로 xls는 xlsx로 변환 해주는 프로그램
<form action="upload_ok.jsp" enctype="multipart/form-data" method="post">      
      엑셀 집어넣기 : <input type="file" name="upfile" size="40"><br>
      <input type="submit" value="변환!">
</form>

2. upload_ok 페이지 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*, java.util.*" %>
<%@ page import="com.oreilly.servlet.MultipartRequest,
                   com.oreilly.servlet.multipart.DefaultFileRenamePolicy,
                   java.util.*" %>
<%@ page import="excel.*" %>                   
<%
request.setCharacterEncoding("UTF-8");
 String savePath="D:/Data/testUploads/"; // 저장할 디렉토리 (절대경로)
 //convert excelconvert = new convert();
 int sizeLimit = 5 * 1024 * 1024 ; // 5메가까지 제한 넘어서면 예외발생
 
 File f = new File(savePath); 
 if(!f.exists())//폴더가 없으면
 {
	 f.mkdir();//폴더를 생성
 } 

 try{

 MultipartRequest multi=new MultipartRequest(request, savePath, sizeLimit,"UTF-8", new DefaultFileRenamePolicy());
 Enumeration<?> fileNames=multi.getFileNames();  // file object의 이름 반환
 String fileName=multi.getFilesystemName(fileNames.nextElement().toString()); // 파일의 이름 얻기

 if(fileName == null) {   // 파일이 업로드 되지 않았을때
  out.print("파일 업로드 되지 않았음");
 } else {  // 파일이 업로드 되었을때
  //fileName=new String(fileName.getBytes("8859_1"),"UTF-8"); // 한글인코딩 - 브라우져에 출력
  
 /* String userName = multi.getParameter("userName");
  out.print("Form Name : " + formName + "<BR>");
  out.print("File Name  : " + fileName);*/

	String type = fileName.substring(fileName.length()-1,fileName.length());
	
  out.clear(); //out--> jsp자체 객체
  out=pageContext.pushBody(); //out--> jsp자체 객체
   //getOutputStream() has already been called for this response
   //오류 해결을 위판 편법
	
	if(type.equals("x"))
	{
		String XLSX = savePath + fileName;
		String XLS = fileName.substring(0,fileName.length()-1);  

		request.setAttribute("XLSX",XLSX);
		request.setAttribute("XLS",XLS);
  
		RequestDispatcher dispatcher = request.getRequestDispatcher("./XLSXtoXLS.jsp");
		dispatcher.forward(request, response);

 	}
 	else if(type.equals("s"))
 	{
 		String XLS = savePath + fileName;
 		String XLSX = fileName + "x";  
 		  
 		request.setAttribute("XLSX",XLSX);
 		request.setAttribute("XLS",XLS);
 		
 		RequestDispatcher dispatcher = request.getRequestDispatcher("./XLStoXLSX.jsp");
 		dispatcher.forward(request, response);
 	}
 }
 
 } catch(Exception e) {
 e.printStackTrace();
 }
%>

3.  XLSXtoXLS 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*, java.util.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.ss.usermodel.*" %>
<%@ page import="org.apache.poi.xssf.usermodel.*" %>
<%@ page import="org.apache.poi.hssf.model.*" %>

<%	
	String XLSX = (String)request.getAttribute("XLSX");
	String XLS = (String)request.getAttribute("XLS");
	
	System.out.println(XLSX + ":" + XLS);	
	response.setHeader("Content-Disposition", "attachment; filename="+ new String(XLS.getBytes("EUC-KR"),"8859_1") );
    InputStream inp; 
     
    try { 
        inp = new FileInputStream(XLSX); 
        Workbook wb = WorkbookFactory.create(inp);        
        HSSFWorkbook newWb = new HSSFWorkbook(); 
        Sheet copia = newWb.createSheet(); 
        Sheet sheet = wb.getSheetAt(0); 
        Iterator<Row> rows = sheet.iterator(); 
        
        while(rows.hasNext()){ 
            Row row = rows.next(); 
            Row newRow = copia.createRow(row.getRowNum()); 
            Iterator<Cell> cells = row.cellIterator(); 
            while( cells.hasNext()){ 
                Cell cell = cells.next(); 
                Cell newCell = 	newRow.createCell(cell.getColumnIndex()); 
                int type = cell.getCellType();
                
                switch(type){
                
                case Cell.CELL_TYPE_BLANK: 
                    break;
                    
                case Cell.CELL_TYPE_NUMERIC:
                	//System.out.print(cell.getNumericCellValue());
                	newCell.setCellValue(cell.getNumericCellValue()); 
                    break;
                    
                case Cell.CELL_TYPE_STRING: 
                    //System.out.print(cell.getStringCellValue() + ""); 
                    newCell.setCellValue(cell.getStringCellValue()); 
                    break;
                    
                case Cell.CELL_TYPE_ERROR:
                	newCell.setCellErrorValue(cell.getErrorCellValue()); 
                    break; 
                    
                case Cell.CELL_TYPE_BOOLEAN:
                	newCell.setCellValue( cell.getBooleanCellValue()); 
                    break; 
                    
                case Cell.CELL_TYPE_FORMULA: 
                	//System.out.print(cell.getCellFormula());
                	newCell.setCellFormula(cell.getCellFormula()); 
                    break; 
                } 
            } 
            
            System.out.println(); 
        }

        
        out.clear(); //out--> jsp자체 객체
        out=pageContext.pushBody(); //out--> jsp자체 객체
         //getOutputStream() has already been called for this response
         //오류 해결을 위판 편법        
        ServletOutputStream sout = response.getOutputStream();               
        newWb.write(sout);
        
    } catch (FileNotFoundException e) { 
        e.printStackTrace(); 
    } catch (IOException e) { 
        e.printStackTrace(); 
    } 
%>

4. XLStoXLS 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.*, java.util.*" %>
<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.ss.usermodel.*" %>
<%@ page import="org.apache.poi.xssf.usermodel.*" %>
<%@ page import="org.apache.poi.hssf.model.*" %>

<%	
	String XLSX = (String)request.getAttribute("XLSX");
	String XLS = (String)request.getAttribute("XLS");
	
	System.out.println(XLSX + ":" + XLS);
	response.setHeader("Content-Disposition", "attachment; filename="+new String(XLSX.getBytes("EUC-KR"),"8859_1"));
    InputStream inp;
    
    try { 
        inp = new FileInputStream(XLS); 
        Workbook wb = WorkbookFactory.create(inp);        
        /*HSSFWorkbook newWb = new HSSFWorkbook(); 
        Sheet copia = newWb.createSheet(); 
        Sheet sheet = wb.getSheetAt(0); 
        Iterator<Row> rows = sheet.iterator();*/
        Workbook newWb = new XSSFWorkbook();
        Sheet copia = newWb.createSheet();
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.iterator();
        
        while(rows.hasNext()){ 
            Row row = rows.next(); 
            Row newRow = copia.createRow(row.getRowNum()); 
            Iterator<Cell> cells = row.cellIterator(); 
            while( cells.hasNext()){ 
                Cell cell = cells.next(); 
                Cell newCell = 	newRow.createCell(cell.getColumnIndex()); 
                int type = cell.getCellType();
                
                switch(type){
                
                case Cell.CELL_TYPE_BLANK: 
                    break;
                    
                case Cell.CELL_TYPE_NUMERIC:
                	//System.out.print(cell.getNumericCellValue());
                	newCell.setCellValue(cell.getNumericCellValue()); 
                    break;
                    
                case Cell.CELL_TYPE_STRING: 
                    //System.out.print(cell.getStringCellValue() + ""); 
                    newCell.setCellValue(cell.getStringCellValue()); 
                    break;
                    
                case Cell.CELL_TYPE_ERROR:
                	newCell.setCellErrorValue(cell.getErrorCellValue()); 
                    break; 
                    
                case Cell.CELL_TYPE_BOOLEAN:
                	newCell.setCellValue( cell.getBooleanCellValue()); 
                    break; 
                    
                case Cell.CELL_TYPE_FORMULA: 
                	//System.out.print(cell.getCellFormula());
                	newCell.setCellFormula(cell.getCellFormula()); 
                    break; 
                } 
            } 
            
            System.out.println(); 
        }                
        
        
        out.clear(); //out--> jsp자체 객체
        out=pageContext.pushBody(); //out--> jsp자체 객체        
        ServletOutputStream xout = response.getOutputStream();
        
        newWb.write(xout);
        
    } catch (FileNotFoundException e) { 
        e.printStackTrace(); 
    } catch (IOException e) { 
        e.printStackTrace(); 
    } 
%>

파일이 업로드 되면 2.upload_ok.jsp에서 해당 엑셀파일의 확장자를 판단하여,
그에 따른 변환 페이지를 불러온다.

변환 페이지에서 좀 주의해야 할 점은. 아직 servlet의 한글처리가 제대로 되지 않는지
전체 페이지를 utf-8로 인코딩 했음에도 outputstream으로 파일 다운로드시 파일의 한글이 깨져서 나온다.

이를 위해

response.setHeader("Content-Disposition", "attachment; filename="+new String(XLSX.getBytes("EUC-KR"),"8859_1"));

를 추가 해주었다.


- 결과

만드는데 참조한 페이지
http://apache-poi.1045710.n5.nabble.com/Reading-as-XLS-writing-as-XLSX-td2312275.html