POI를 이용하여 엑셀의 파일 타입을 변환해주는 프로그램[?] <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% request.setCharacterEncoding("UTF-8"); %> 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 공유하기 게시글 관리 구독하기Storm Of Two Yellow Beans저작자표시 비영리 동일조건 'JSP > Example' 카테고리의 다른 글[JSP] JSP 페이지를 엑셀로 저장. (0)2011.11.03[JSP & javascript] 프린트 출력시 참고사항. (0)2011.11.03[JSP & javascript] JSP 페이지 프린트 하기. (1)2011.11.02[JSP & javascript] 각 달에 따른 날짜 자동맞춤. (0)2011.10.07 |