|
// 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();
}
}
} |