엑셀 업로드(전자정부프레임워크)

1. context-common.xml (bean 추가)

<!-- MultipartResolver 설정 -->

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

<property name="maxUploadSize" value="100000000" />

<property name="maxInMemorySize" value="100000000" />

</bean>

2. ExcelUtil.java (공통 엑셀 유틸 추가)

package egovframework.cmn.cmn;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.multipart.commons.CommonsMultipartFile;

public class ExcelUtil {

/**

* 엑셀 파일 읽기

* @param file

* @param sheetNum

* @param strartRowNum

* @param startCelNum

* @return List<HashMap<Integer, String>>

* @throws Exception

*/

public List<HashMap<Integer, String>> excelReadSetValue(CommonsMultipartFile file, int sheetNum, int strartRowNum, int startCelNum) throws Exception {

List<HashMap<Integer, String>> resultList = new ArrayList<>();

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

//xls, xlsx 구분

Workbook workbook = null;

if(file.getOriginalFilename().toUpperCase().endsWith("XLSX")) {

workbook = new XSSFWorkbook(file.getInputStream());

}

else {

workbook = new HSSFWorkbook(file.getInputStream());

}

//Sheet 수 확인

int sheetCnt = workbook.getNumberOfSheets();

int listNum = 0;

try {

if (sheetCnt > 0) {

//첫번째 Sheet 선택

Sheet sheet = workbook.getSheetAt(sheetNum);

//Sheet의 Row와 Cell 수 확인

int rows = sheet.getPhysicalNumberOfRows();

int cells = sheet.getRow(0).getPhysicalNumberOfCells();

HashMap<Integer, String> valueMap = null;

//Header Row 빼고 시작(0에서 시작)

for(int r = strartRowNum ; r < rows; r++) {

//String device_id = "";

valueMap = new HashMap<Integer, String>();

//한 줄씩 읽고 데이터 저장

Row row = sheet.getRow(r);

if (row != null) {

//Cell 기본값 빼고 시작(0에서 시작)

for(int c = startCelNum ; c < cells ; c++) {

Cell cell = row.getCell(c);

if (cell != null) {

String value = "";

switch(cell.getCellType()) {

case Cell.CELL_TYPE_BLANK :

value = "";

break;

case Cell.CELL_TYPE_BOOLEAN :

value = "" + cell.getBooleanCellValue();

break;

case Cell.CELL_TYPE_ERROR :

value = "" + cell.getErrorCellValue();

break;

case Cell.CELL_TYPE_FORMULA :

value = cell.getCellFormula();

break;

case Cell.CELL_TYPE_NUMERIC :

if(HSSFDateUtil.isInternalDateFormat(cell.getCellStyle().getDataFormat())) {

value = sdf.format(cell.getDateCellValue());

}

else {

cell.setCellType(Cell.CELL_TYPE_STRING ); 

value = cell.getStringCellValue(); 

}

break;

case Cell.CELL_TYPE_STRING :

value = cell.getStringCellValue();

break;

}

//공백과 트림 제거

value = value.trim().replaceAll(" ", "");

valueMap.put(c, value);

}

}//end col for

resultList.add(listNum++, valueMap);

}//end if

}//end row for

}

} catch(Exception e) {

e.getStackTrace();

}

return resultList;

}

}

3. XxxController.java

/**

* 엑셀 업로드 처리

* @param multiRequest

* @param request

* @return

* @throws Exception

*/

@RequestMapping("cfgUploadAction.do")

public String cfgUploadAction(HttpServletRequest request, RedirectAttributes redirectAttributes) throws Exception {

try {

cfgService.excelUpload(request);

redirectAttributes.addFlashAttribute("Code", 0);

redirectAttributes.addFlashAttribute("Message", egovMessageSource.getMessage("proc.success"));

} catch (Exception ex) {

redirectAttributes.addFlashAttribute("Code", 1);

redirectAttributes.addFlashAttribute("Message", "오류가 발생하였습니다. 엑셀양식을 확인해 주세요.");

}

return "redirect:/mng/fac/cfg/cfgUploadPop.do";

}

4. XxxServiceImpl.java

/**

* 엑셀 업로드 처리

* @param multiRequest

* @return String

* @throws Exception

*/

@SuppressWarnings("unchecked")

public void excelUpload(HttpServletRequest request) throws Exception{

MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest)request;

//파일 정보

CommonsMultipartFile file = (CommonsMultipartFile)multiRequest.getFile("excelFile");

//엑셀정보

ExcelUtil eu = new ExcelUtil();

int sheetNum = 0; //1번째 시트 읽음 

int strartRowNum = 1; //2번째 줄부터 읽음

int startCelNum = 2; //3번째 줄부터 읽음(지역ID)

List<HashMap<Integer, String>> excelList = eu.excelReadSetValue(file, sheetNum, strartRowNum, startCelNum);

//테이블 Key 정보

DeviceBaseVO deviceBaseVO = null;

//엑셀 Row 수 만큼 For문 조회 

for(Object obj : excelList) {

Map<Integer, String> mp = (Map<Integer, String>)obj;

Set<Integer> keySet = mp.keySet();

Iterator<Integer> iterator = keySet.iterator();

deviceBaseVO = new DeviceBaseVO();

while(iterator.hasNext()) {

int key = iterator.next();

String value = StringUtil.nullConvert(mp.get(key));

switch(key) {

case 2 :

deviceBaseVO.setAreaId(value);

break;

case 4 :

deviceBaseVO.setFacilityId(value);

break;

case 5 :

deviceBaseVO.setDeviceNm(value);

break;

case 6 :

deviceBaseVO.setDeviceId(value);

break;

case 7 :

deviceBaseVO.setInstDt(value);

break;

case 8 :

deviceBaseVO.setUseYn(value);

break;

}

}

if(!"".equals(deviceBaseVO.getAreaId()) && deviceBaseVO.getAreaId() != null) {

cfgMapper.updateCfgInfo(deviceBaseVO);

}

}

}

5. Xxx.jsp

<form name="popForm" method="post" action="${actionUrl}" enctype="multipart/form-data">

<table>

<caption>엑셀 업로드</caption>

<colgroup>

<col><col>

</colgroup>

<tbody>

<tr>

<th><label for="code2">파일찾기</label></th>

<td><input name="excelFile" id="excelFile" type="file" size="30"></td>

</tr>

</table>

<!-- 버튼 영역 -->

<div class="btn-area">

<a href="#" onclick="doSubmit();" class="btn btn-yellow btn-ok">업로드</a>

<a href="javascript:self.close();" class="btn btn-yellow btn-cancel">창닫기</a>

</div>

</form>