관리 메뉴

행이네

Excel Upload 본문

plming/Java - Spring

Excel Upload

행이™ 2010. 12. 20. 18:04
Excel 97~2003(.xls)에 대한 파일을 처리할 때에는
별도의 서드파트의 라이브러리들이 있었지만,

Excel 2007(.xlsx)을 처리하면서는
Apache POI (http://poi.apache.org/) 를 활용하는 방법도 괜찮을 듯 싶다.

이를 위해 poi-로 시작하는 .jar 파일들을 import 하면 처리할 수 있다.

poi-3.7-20101029.jar
poi-examples-3.7-20101029.jar
poi-ooxml-3.7-20101029.jar
poi-ooxml-schemas-3.7-20101029.jar
poi-scratchpad-3.7-20101029.jar


 <!-- excelUpload.jsp -->

 <script type="text/javascript">
 function goUploadExcel(){
     var uploadForm = document.uploadForm;

     if( uploadForm.file1.value == "" ) {
         alert( "파일을 업로드해주세요." );
         return false;
     } else if( !checkFileType(uploadForm.file1.value) ) {
         alert( "엑셀파일만 업로드 해주세요." );
         return false;
     }

     if( confirm("업로드 하시겠습니까?") ) {
         uploadForm.action = "/excelUpload.do";
         uploadForm.submit();
     }
 }

 function checkFileType( filePath ) {
     var fileFormat = filePath.toLowerCase();

     if( fileFormat.indexOf(".xls") > -1 ) return true;
     else return false;
 }
 </script>


 <form name="uploadForm" action="" method="post" onSubmit="return false;" encType="multipart/form-data">
     <input type="file" name="file1" />
     <button type="button" onclick="goUploadExcel();" onkeypress="this.onclick();"></button>
 </form>



 <!-- action.xml -->
 
 <bean id="excelUploadController" parent="defaultController" class="controller.excelUploadController">
     <property name="methodNameResolver">
         <ref local="excelUploadControllerMethodNameResolver" />
     </property>
 </bean>
 <bean id="excelUploadControllerMethodNameResolver" class="org.springframework.web.servlet.mvc.multiaction.PropertiesMethodNameResolver">
     <property name="mappings">
         <props>
             <prop key="/excelUpload.do">excelUpload</prop>
         </props>
     </property>
 </bean>
 
 <bean id="urlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
     <property name="mappings">
         <props>
             <prop key="/excelUpload.do">excelUploadController</prop>
         </props>
     </property>
 </bean>


 // excelUploadController.java

 package controller;


 import java.io.File;
 import java.io.FileInputStream;
 import java.io.IOException;

 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;

 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.DateUtil;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.springframework.web.multipart.MultipartFile;
 import org.springframework.web.multipart.MultipartHttpServletRequest;
 import org.springframework.web.servlet.ModelAndView;
 import org.springframework.web.servlet.mvc.multiaction.MultiActionController;


 public class excelUploadController extends MultiActionController {

     public ModelAndView excelUpload( HttpServletRequest request, HttpServletResponse response ) throws Exception {

         try {
             String rootPath = (request.getSession().getServletContext().getRealPath("/")).replace( "\\", "/" );
             String savePath = rootPath + "uploaddir" + File.separator;

             // 파일이 저장된 실제 경로 + 파일명 찾기

             // Apache POI 사용 시
             // Apache POI (http://poi.apache.org/) - the Java API for Microsoft Documents
             MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest)request;
             MultipartFile mFile = multipartRequest.getFile( "file1" );

             if( multipartFile != null && multipartFile.getSize() > 0 ) {
                 String saveFileName = multipartFile.getOriginalFilename();
                 long fileSize = multipartFile.getSize();

                 if( fileSize > 0 && !saveFileName.equals("") ) {
                     saveFileName = savePath + saveFileName;

                     File tempFile = new File(saveFileName);
                     OutputStream outputStream = new FileOutputStream( tempFile );
                     FileCopyUtils.copy( multipartFile.getInputStream(), outputStream );
                     outputStream.close();


             /******************************************************************************
             // oreilly COS 사용 시
             // oreilly COS (http://www.servlets.com/cos/index.html)
             import com.oreilly.servlet.MultipartRequest
             import com.oreilly.servlet.multipart.DefaultFileRenamePolicy

             int sizeLimit = 30 * 1024 * 1024 ; // 용량제한
             String formName = "";
             String fileName = "";
             Vector vFileName = new Vector();
             Vector vFileSize = new Vector();
             String[] aFileName = null;
             String[] aFileSize = null;
             long fileSize = 0;

             MultipartRequest multi = new MultipartRequest(request, savePath, sizeLimit, "euc-kr", new DefaultFileRenamePolicy());
             Enumeration formNames = multi.getFileNames();
             while( formNames.hasMoreElements() ) {
                 formName = (String)formNames.nextElement();
                 fileName = multi.getFilesystemName(formName);

                 if(fileName != null) {   // 파일이 업로드 되면
                     fileSize = multi.getFile(formName).length();
                     vFileName.addElement(fileName);
                     vFileSize.addElement(String.valueOf(fileSize));
                 }
             }
             ******************************************************************************/


                     // Excel 처리
                     // Excel 97~2003(.xls)과 Excel 2007(.xlsx)은 각각 처리해야 한다.
                     if( saveFileName.indexOf(".xlsx") > -1 ) readExcel2007( saveFileName );
                     else if( saveFileName.indexOf(".xls") > -1 ) readExcel( saveFileName );
                 }
             }
         } catch ( Exception ex ) {
             ex.printStackTrace();
         }

         return new ModelAndView( nextPage );
     }


     // Excel 2007(.xlsx) 이상 파일처리
     public void readExcel2007( String excel ) throws IOException {
         // check file
         File file = new File( excel );
         if( !file.exists() || !file.isFile() || !file.canRead() ) {
             throw new IOException( excel );
         }

         // http://poi.apache.org/apidocs/index.html?overview-summary.html
         // http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html
         XSSFWorkbook wb = new XSSFWorkbook( new FileInputStream(file) );
         try {
             for( int i=0; i<wb.getNumberOfSheets(); i++ ) {
                 for( Row row : wb.getSheetAt(i) ) {
                     for( Cell cell : row ) {
                         switch( cell.getCellType() ) {
                             case XSSFCell.CELL_TYPE_STRING:
                                 cell.getRichStringCellValue().getString();
                                 break;
                             case XSSFCell.CELL_TYPE_NUMERIC:
                                 if( HSSFDateUtil.isCellDateFormatted(cell) ) {
                                     java.util.Date dateValue = cell.getDateCellValue();
                                     Integer year = dateValue.getYear(); // or getYear
                                     if (year != -1) dateFormat.format(dateValue);
                                     else timeFormat.format(dateValue);
                                 }
                                 else {
                                     Double.valueOf( cell.getNumericCellValue() ).intValue();
                                 }
                                 break;
                             case XSSFCell.CELL_TYPE_FORMULA:
                                 cell.getCellFormula();  break;
                             case XSSFCell.CELL_TYPE_BOOLEAN:
                                 cell.getBooleanCellValue();  break;
                             case XSSFCell.CELL_TYPE_ERROR:
                                 cell.getErrorCellString();
                                 cell.getErrorCellValue();  break;
                             case XSSFCell.CELL_TYPE_BLANK: break;
                             default: break;
                         }
                     }
                 }
             }
         } catch( Exception ex ) {
             ex.printStackTrace();
         }
     }


     // Excel 97~2003(.xls) 파일처리
     public void readExcel( String excel ) throws IOException {
         // check file
         File file = new File( excel );
         if( !file.exists() || !file.isFile() || !file.canRead() ) {
             throw new IOException( excel );
         }

         // http://poi.apache.org/apidocs/index.html?overview-summary.html
         // http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html
         HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
         try {
             for( int i=0; i<wb.getNumberOfSheets(); i++ ) {
                 for( Row row : wb.getSheetAt(i) ) {
                     for( Cell cell : row ) {
                         switch( cell.getCellType() ) {
                             case HSSFCell.CELL_TYPE_STRING:
                                 cell.getRichStringCellValue().getString();
                                 break;
                             case HSSFCell.CELL_TYPE_NUMERIC:
                                 if( HSSFDateUtil.isCellDateFormatted(cell) ) {
                                     java.util.Date dateValue = cell.getDateCellValue();
                                     Integer year = dateValue.getYear(); // or getYear
                                     if (year != -1) dateFormat.format(dateValue);
                                     else timeFormat.format(dateValue);
                                 }
                                 else {
                                     Double.valueOf( cell.getNumericCellValue() ).intValue();
                                 }
                                 break;
                             case HSSFCell.CELL_TYPE_FORMULA:
                                 cell.getCellFormula();  break;
                             case HSSFCell.CELL_TYPE_BOOLEAN:
                                 cell.getBooleanCellValue();  break;
                             case HSSFCell.CELL_TYPE_ERROR:
                                 cell.getErrorCellValue();  break;
                             case HSSFCell.CELL_TYPE_BLANK: break;
                             default: break;
                         }
                     }
                 }
             }
         } catch( Exception ex ) {
             ex.printStackTrace();
         }
     }

 }



'plming > Java - Spring' 카테고리의 다른 글

ajax @ResponseBody없이 json 처리  (3) 2016.03.08
전자정부프레임워크(egov) 파일업로드  (1) 2015.10.06
Excel Upload  (1) 2010.12.20
ORM (Object Relation Mapping)  (0) 2010.09.12
Spring MVC - 에러 처리 전략  (0) 2010.09.11
Spring MVC - 다국어 지원  (0) 2010.09.05
1 Comments
댓글쓰기 폼