엑셀 파일 업로드 양식 체크 및 버전 체크 샘플 코드

다음과 같은 엑셀 양식이 있다. 11번가 셀러들이 상품 업로드할 때 사용하는 엑셀파일 양식을 일부 참고하였다.

코드는 다음과 같다.

	// 마스터정보 엑셀 파일 업로드 처리
	@RequestMapping(value = {"/excelUpload.do", "/business/excelUpload.do"})
	@ResponseBody
	public ResponseEntity<String> excelFileUpload(@ModelAttribute("uploadForm")FileUploadForm uploadForm, HttpServletResponse res, HttpServletRequest req, Model model)
	{
		Map<String, String> resultMap = new HashMap<>();		
		try
		{
			MultipartFile file = uploadForm.getFiles().get(0);
			String category = req.getParameter("category");
			
			if(file.isEmpty())
			{
				resultMap.put(ComnConst.ARGS_RETURN_CODE, "ERR_02");
				return Utility.getJSONResponse(resultMap);	
			}
			log.info("* dmnCond -->  "+req.getParameter("dmnCond"));			
			
			
			//파일 타입 체크
			boolean isExcel = Utility.isExcelFileExt(file.getOriginalFilename());
			if (!isExcel) {	
				resultMap.put(ComnConst.ARGS_RETURN_CODE, "ERR_01");
				return Utility.getJSONResponse(resultMap);				
			} else {	
				//
				Workbook wokbook = ValidationCheckUtil.getWorkbook(file);
				
				//엑셀 양식 버전 체크
				String verStr = ValidationCheckUtil.getExcelTemplateVersion(wokbook);
				if (!verStr.equals("ver. 1.0")) { //버전 정보 하드코딩 : 관리자페이지에서 버전 양식 관리가 필요한 경우 DB에서 버전 정보 조회 후 체크하세요!
					resultMap.put(ComnConst.ARGS_RETURN_CODE, "ERR_03");
					return Utility.getJSONResponse(resultMap);
				}
				
				//마스터 정보 엑셀 양식 체크
				boolean isCorrect = ValidationCheckUtil.isCorrectExcelTemplate(wokbook, category);
				if (!isCorrect) {
					resultMap.put(ComnConst.ARGS_RETURN_CODE, "ERR_04");
					return Utility.getJSONResponse(resultMap);
				}
			}			

			switch (category) {
			case "EMP" :  // 임직원 정보 업로드
				excelDataUploadProc(uploadForm, res, req, new EmpExcelUploadService());
				break;
			case "DEPT" :  // 부서 정보 업로드
				excelDataUploadProc(uploadForm, res, req, new DeptExcelUploadService());
				break;
			case "PROD" :    // 제품 정보 업로드
				excelDataUploadProc(uploadForm, res, req, new ProdExcelUploadService());
				break;				
			}
			
		}
		catch(Exception ex)
		{
			log.info(ex.toString());
			resultMap.put(ComnConst.ARGS_RETURN_CODE, "ERR_911");
			resultMap.put(ComnConst.ARGS_RETURN_STRING, ex.toString());
			return Utility.getJSONResponse(resultMap);
		}
		
		return new ResponseEntity<>("[[]]", HttpStatus.OK);
	
	}
	
	
	
	
	
	// 엑셀 Workbook return 메소드
	public static Workbook getWorkbook(MultipartFile multipart) {
		Workbook book = null;		
		try 
		{		
			// File convFile = new File( multipart.getOriginalFilename());
			// multipart.transferTo(convFile);
			String fileName = multipart.getOriginalFilename();			 
			FileInputStream fis = new FileInputStream(fileName);					
			
			if(fileName.toLowerCase().endsWith(".xls"))			book = new HSSFWorkbook(fis);
			else if(fileName.toLowerCase().endsWith(".xlsx"))	book = new XSSFWorkbook(fis);
			
		}
		catch(Exception ex)
		{
			return null;
		}
		return book;		
	}
	
	// 엑셀 버전 체크 메소드
	public static String getExcelTemplateVersion(Workbook book) {
		String versionInfo = "";		
		try 
		{			
			if(book.getNumberOfSheets() > 0)
			{
				Sheet versionRowsheet =	book.getSheetAt(0);
//				Row clsRow = versionRowsheet.getRow(0); //헤더 정보
				Row clsRow = versionRowsheet.getRow(1); //2번째 행의 버전 정보 체크
				versionInfo = clsRow.getCell(0).toString().trim(); // 첫번째 컬럼의 버전정보
			}
		}
		catch(Exception ex)
		{
			return versionInfo;
		}
		return versionInfo;		
	}
	
	// 엑셀 양식 체크 메소드
	public static boolean isCorrectExcelTemplate(Workbook book, String category) {
		boolean isCorrect = false;		
		try 
		{			
			if(book.getNumberOfSheets() > 0)
			{
				Sheet versionRowsheet =	book.getSheetAt(0);
				String name = versionRowsheet.getSheetName();
				
				switch (category) {
					case "EMP" :
						if(name.equals("임직원등록")) isCorrect = true;
						break;
					case "DEPT" :
						if(name.equals("부서등록")) isCorrect = true;
						break;
					case "PROD" :
						if(name.equals("제품등록")) isCorrect = true;
						break;				
				}				 
			}
		}
		catch(Exception ex)
		{
			return isCorrect;
		}
		return isCorrect;		
	}	
	
	// 엑셀 Workbook 생성 및 버전 체크 메소드
	public static String checkExcelTemplate(MultipartFile multipart) {
		Workbook book = null;
		String versionInfo = "";		
		try 
		{		
			String fileName = multipart.getOriginalFilename();			 
			FileInputStream fis = new FileInputStream(fileName);					
			
			if(fileName.toLowerCase().endsWith(".xls"))			book = new HSSFWorkbook(fis);
			else if(fileName.toLowerCase().endsWith(".xlsx"))	book = new XSSFWorkbook(fis);
			
			
			if(book.getNumberOfSheets() > 0)
			{
				Sheet versionRowsheet =	book.getSheetAt(0);
//				Row clsRow = versionRowsheet.getRow(0); //헤더 정보
				Row clsRow = versionRowsheet.getRow(1); //2번째 행의 버전 정보 체크
				versionInfo = clsRow.getCell(0).toString().trim(); // 첫번째 컬럼의 버전정보
			}
		}
		catch(Exception ex)
		{
			return versionInfo;
		}
		return versionInfo;		
	}

EmpExcelUploadService.java

import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.ss.usermodel.Sheet;
import org.springframework.web.multipart.MultipartFile;

import com.ibatis.sqlmap.client.SqlMapClient;


public class EmpExcelUploadService extends AbstractXLFileUploadService 
{
	
	//엑셀 한글 칼럼명
	public final String COLUMN_NAME_0		= "버전";
	public final String COLUMN_NAME_1		= "이름";
	public final String COLUMN_NAME_2		= "아이디";
	public final String COLUMN_NAME_3		= "ADMIN사용여부";
	public final String COLUMN_NAME_4		= "MR사용여부";
	public final String COLUMN_NAME_5		= "사번";
	public final String COLUMN_NAME_6		= "이메일";
	public final String COLUMN_NAME_7		= "핸드폰번호";
	public final String COLUMN_NAME_8		= "회사전화번호";
	public final String COLUMN_NAME_9		= "영문이름";
	
	//엑셀 한글 칼럼명
	private String[] excelHeaderK = 
	{
		COLUMN_NAME_0,
		COLUMN_NAME_1,	
		COLUMN_NAME_2,
		COLUMN_NAME_3,
		COLUMN_NAME_4,
		COLUMN_NAME_5,
		COLUMN_NAME_6,
		COLUMN_NAME_7,
		COLUMN_NAME_8,
		COLUMN_NAME_9,
	};	

	@Override
	protected int getNewFileId(MultipartFile file, PathInfo savePath)
	{		 
		int intResult = 0;
		List<Sheet> sheet = getSheetMulti(file, savePath);
		
		if (!sheet.isEmpty()) 
		{
			for (Sheet sheetAt : sheet) 
			{				
				intResult = saveSheetData(sheetAt);
			}
		} 
		else 
		{
			intResult = ComnConst.RETURN_NO_DATA;
		}
		
		return intResult;
	}
	
	@Override
	protected String getSheetXML(Sheet sheet)
	{
		return "";
	}

	@Override
	protected boolean saveData(String strXML)
	{
		return false;
	}	
	
	
	/**
	 * Sheet내에 Data를 저장한다.
	 * @param sheet
	 * @return
	 */
	int saveSheetData(Sheet sheet)
	{
		log.info("**saveEmpSheetData() START");
		// 기본 정보 초기화
		int intReturnCode = ComnConst.RETURN_UNKNOWN;
		int intInsertCount = 0;
		int intExcelRowCount = sheet.getLastRowNum();
		int headIndex = 0;  //헤더 위치
		String strDmnCond = this.request.getParameter("dmnCond");
//		String params = this.request.getParameter("params");
//		String[] arrParam = params.split(",");
//		String proId = arrParam[1];
		
		SqlMapClient smc = commonDAO.getSqlMapClientTemplate("dataSource").getSqlMapClient();
		try
		{
			UserModel userSession = Utility.getPrincipal();
			smc.startTransaction();
			smc.startBatch();
			
			
			//기존 업로드 히스토리 삭제
//			UBSalesParamMap delParam = new UBSalesParamMap();
//			delParam.put("workTp", 		"D");	
//			delParam.put("userId", 	userSession.getUserId());
//			smc.update("contact.saveSalesSourceExcel", delParam);
			
			String inserttime = StringUtil.getNowTime();
			
			if(intExcelRowCount > 3)
			{
				// 헤더 정보 (3번째 데이터) 부터 시작
				String[] arsColumns = getHeaderColumns(sheet, headIndex);
				if (arsColumns.length > 0) 
				{					
					log.info("* EMP DATA INSERT START");
					// 저장될 Row 데이터 셋팅용 hashMap
					Map<String, Object> mpRow = new HashMap<>();
					List<String> userIdList = new ArrayList<>();
					
					for(int intRowNum = 3;intRowNum <= intExcelRowCount ; intRowNum++)
					{
						// 데이터 정제 (sheet column 속성별 데이터 셋팅)
						mpRow = rowToMap(sheet.getRow(intRowNum), arsColumns);
						// 데이터 insert
						if(mpRow!= null && !mpRow.isEmpty())
						{						
							UBSalesParamMap excelRow = new UBSalesParamMap();
							excelRow.put("workType", 		"EXCEL_REG_EMP_TEMP");
							excelRow.put("dmnCond", 	strDmnCond);									
							excelRow.put("empNm", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_1)));
							excelRow.put("empId", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_2)));
							
							excelRow.put("adSiteUseYn", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_3)));
							excelRow.put("mrSiteUseYn", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_4)));
							excelRow.put("empNo", 		StringUtil.replaceString(mpRow.get(COLUMN_NAME_5)));
							excelRow.put("emailAddr", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_6)));
							excelRow.put("mobilePhone",  StringUtil.replaceString(mpRow.get(COLUMN_NAME_7)));
							excelRow.put("officeTel", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_8)));
							
							excelRow.put("empNmEm", 	StringUtil.replaceString(mpRow.get(COLUMN_NAME_9)));
							
							// 벨리데이션 체크 로직 추가
							String rtnStr = ValidationCheckUtil.checkEmpExcelData(mpRow , userIdList
									, COLUMN_NAME_1
									, COLUMN_NAME_2
									, COLUMN_NAME_3
									, COLUMN_NAME_4
									, COLUMN_NAME_5
									, COLUMN_NAME_6
									, COLUMN_NAME_7
									, COLUMN_NAME_8
									, COLUMN_NAME_9
									);

							if(rtnStr.equals("")) {
								excelRow.put("xlsResultYn", 	"Y");
								excelRow.put("xslResultNote", 	"");
							} else {
								excelRow.put("xlsResultYn", 	"N");
								excelRow.put("xslResultNote", 	rtnStr);								
							}
							
							excelRow.put("dmn", 	userSession.getDmn());
							excelRow.put("userId", 	userSession.getUserId());
							
							excelRow.put("crtTime", inserttime);
							
							smc.update("mstEmp.saveExcelUploadMstEmp", excelRow);
							
							intInsertCount++;
						}
					}
				}
			}
			else
			{
				intReturnCode = ComnConst.RETURN_NO_DATA;
			}
			
			intReturnCode = ComnConst.RETURN_CODE_SUCCESS;
			smc.executeBatch();
			log.info("* EMP DATA INSERT END");
			log.info("-------------------- RESULT --------------------");
			log.info("* EMP DATA   Insert Count  =  "+intInsertCount);
			log.info("* Excel Row    Count  =  "+intExcelRowCount);
			log.info("* Return Code = "+intReturnCode);
			log.info("------------------------------------------------");
		}
		catch(Exception ex)
		{
			intReturnCode = ComnConst.RETURN_UNKNOWN;
			ex.printStackTrace();
			log.info(ex.toString());
			log.info(ex.getMessage());
		}
		finally
		{
			try
			{
				smc.endTransaction();
				smc = null;
			}
			catch(SQLException e)
			{
				log.info("ERROR INFO : " + e.getMessage());
			}
		}
		
		
		log.info("**saveSheetData() END");
		return intReturnCode;
	}
	

	
	/**
	 * Get Excel Header Columns
	 * @param sheet
	 * @return arsColumns
	 */
	String[] getHeaderColumns(Sheet sheet, int headerIdx)
	{
		String[] arsColumns = null;
		
		int intColIndex = 0;
		Row clsRow = sheet.getRow(headerIdx);
		while(clsRow.cellIterator().hasNext()
		&& clsRow.getCell(intColIndex) != null
		&& !"".equals(clsRow.getCell(intColIndex).toString().trim()))
		{
			intColIndex++;
		}
		
		arsColumns = new String[intColIndex];
		String headerStr = "";
		for(int intDataIndex = 0; intDataIndex < intColIndex; intDataIndex++) 
		{
			headerStr = clsRow.getCell(intDataIndex).toString().trim();
			
			// 한글 header값 영문 변경
			for(int i=0; i < excelHeaderK.length; i++) 
			{
				if(excelHeaderK[i].equals(headerStr)) 
				{
					headerStr = excelHeaderK[i]; //excelHeaderE[i];
					break;
				}
			}			
			
			arsColumns[intDataIndex] = headerStr;
		}
		return arsColumns;
	}
	
	/**
	 * Get Excel Header Columns
	 * @param row
	 * @param columns
	 * @return Map<String, Object>
	 */
	Map<String, Object> rowToMap(Row row, String[] columns)
	{
		Map<String, Object> resultMap = null;
		Cell cell;
		int cellCnt;
		Object value;
		int blankCnt = 0;
		
		if(row != null)
		{
			resultMap = new HashMap<>();

			cellCnt = columns.length;

			for(int i = 0; i < cellCnt; i++)
			{
				cell = row.getCell(i);
				if(cell != null)
				{
					switch(cell.getCellType())
					{
						case Cell.CELL_TYPE_STRING:
							value = (cell.getStringCellValue().replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")).trim();
							break;
						case Cell.CELL_TYPE_NUMERIC:
							if (DateUtil.isCellDateFormatted(cell)) 
							{
								Date date = cell.getDateCellValue();
								value = new SimpleDateFormat("yyyyMMdd").format(date);
							}
							else
							{
								String fStr = (String.format("%.10f", cell.getNumericCellValue()));
								String tStr = fStr.substring(fStr.indexOf('.') + 1, fStr.length());
								
								if (Long.valueOf(tStr) > 0) {
									value = fStr;
								}
								else {
									value = fStr.substring(0, fStr.indexOf('.'));
								}
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN:
							value = cell.getBooleanCellValue() ? "1" : "0";
							break;
						case Cell.CELL_TYPE_FORMULA:
							// 수식 셀 처리
							switch (cell.getCachedFormulaResultType())
							{
								case Cell.CELL_TYPE_NUMERIC:
									String fofStr = (String.format("%.10f", cell.getNumericCellValue()));
									String fotStr = fofStr.substring(fofStr.indexOf('.') + 1, fofStr.length());
									
									if (Long.valueOf(fotStr) > 0) {
										value = fofStr;
									}
									else {
										value = fofStr.substring(0, fofStr.indexOf('.'));
									}
									break;
								case Cell.CELL_TYPE_STRING:
									value = (cell.getStringCellValue().replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")).trim();
									break;
								default:
									value = cell.getCellFormula();
									break;
							}
							break;
						case Cell.CELL_TYPE_BLANK:
							value = null;
							blankCnt ++;
							break;
						case Cell.CELL_TYPE_ERROR:
							value = (cell.getErrorCellValue() + "").trim();
							break;
						default:
							value = "";
							blankCnt ++;
							break;
					}
					
					resultMap.put(columns[i], value);
				}
				else
				{
					resultMap.put(columns[i], "");
					blankCnt++;
				}
			}

			if(cellCnt == blankCnt)
			{
				resultMap = null;
			}
		}
		return resultMap;
	}
	
	/**
	 * Upload 결과 값 Script 반환
	 */
	@Override
	public String getResultScript()
	{
		StringBuilder buff = new StringBuilder();
		
		buff.append(result.getOriFileNameList().get(0));
		
//		String str = new String(result.getJsonString().getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
//		
//		if(!str.isEmpty())
//		{
//			buff.append("<script>");
//			buff.append(String.format("	var result = %s;", str));
//			buff.append("	parent.closeCurrentWindow(result);");
//			buff.append("</script>");
//		}
//		else
//		{
//			buff.append("<script> var result = {};</script>");
//		}
		return buff.toString();
	}
}

AbstractXLFileUploadService.java

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.dom.DOMDocument;
import org.springframework.web.multipart.MultipartFile;

import ubsales.mdl.file.model.PathInfo;
import ubsales.mdl.file.service.AbstractFileUploadService;

 
public abstract class AbstractXLFileUploadService extends AbstractFileUploadService
{
	private static final String MSG_XL_UPLOAD_FAIL = "Excel upload failed - %s";
	private static final String MSG_FILE_DELETE_FAIL = "File delete failed";
	
	@Override
	protected int getNewFileId(MultipartFile file, PathInfo savePath)
	{
		int intResult = -1;
		Workbook book;
		
		File fileObj = null;
		String strXML;
		
		try
		{
			fileObj = new File(savePath.getSavePath());
		}
		catch(Exception ex)
		{
			log.info(ex.toString());
			log.info(ex.getMessage());
		}
		
		if(fileObj != null && fileObj.exists())
		{
			try(FileInputStream fis = new FileInputStream(fileObj);)
			{	
				log.info("Create book start");
				book = checkExcelExt(fis, savePath.getOriginalName());
				if(savePath.getOriginalName().toLowerCase().endsWith(".xls"))
				{
					book = new HSSFWorkbook(fis);
				}
				else if(savePath.getOriginalName().toLowerCase().endsWith(".xlsx"))
				{
					book = new XSSFWorkbook(fis);
				}
				log.info("Create book end");
				
				if(book != null)
				{
					strXML = getSheetXML(book.getSheetAt(0));
					log.info("get sheet end");			
					saveData(strXML);
					log.info("data save complete");
				}
				intResult = 0;
			}
			catch(Exception ex)
			{
				log.info(String.format(MSG_XL_UPLOAD_FAIL, ex.toString()));
				return intResult;
			}
			finally
			{
				try
				{
					Files.delete(Paths.get(savePath.getSavePath()));
				}
				catch(IOException e)
				{
					log.info(MSG_FILE_DELETE_FAIL);
				} 
			}
		}
		return intResult;
	}
	
	Sheet getSheet(MultipartFile file, PathInfo savePath, int intSheetNum)
	{
		Sheet sheet = null;
		Workbook book = null;		
		File fileObj = new File(savePath.getSavePath());
		
		try(FileInputStream fis = new FileInputStream(fileObj);)
		{
			book = checkExcelExt(fis, savePath.getOriginalName());
			if(book == null)
			{
				return sheet;
			}			
			sheet = book.getSheetAt(intSheetNum);
		}
		catch(Exception ex)
		{
			log.info(String.format(MSG_XL_UPLOAD_FAIL, ex.toString()));
			return sheet;
		}
		finally
		{
			try
			{
				Files.delete(Paths.get(savePath.getSavePath()));
			}
			catch(IOException e)
			{
				log.info("convertFile delete failed");
			}
		}		
		return sheet;
	}
	
	List<Sheet> getSheetMulti(MultipartFile file, PathInfo savePath)
	{
		List<Sheet> sheet = new ArrayList<>();
		Workbook book = null;
		File fileObj = new File(savePath.getSavePath());
		
		try(FileInputStream fis = new FileInputStream(fileObj);)
		{			
			book = checkExcelExt(fis, savePath.getOriginalName());
			if(book == null) return sheet;
			
			int intSheetNum = book.getNumberOfSheets();
			if(intSheetNum > 0)
			{
				for(int i=0; i < intSheetNum; i++)
				{
					sheet.add(book.getSheetAt(i));
				}
			}
		}
		catch(Exception ex)
		{
			log.info(String.format(MSG_XL_UPLOAD_FAIL, ex.toString()));
			return sheet;
		}
		finally
		{
			try
			{
				Files.delete(Paths.get(savePath.getSavePath()));
			}
			catch(IOException e)
			{
				log.info(MSG_FILE_DELETE_FAIL);
			}
		}		
		return sheet;
	}
	
	Workbook checkExcelExt(FileInputStream fis, String fileName)
	{
		Workbook book = null;
		
		try
		{
			if(fileName.toLowerCase().endsWith(".xls"))			book = new HSSFWorkbook(fis);
			else if(fileName.toLowerCase().endsWith(".xlsx"))	book = new XSSFWorkbook(fis);
		}
		catch(Exception ex)
		{
			log.info(String.format(MSG_XL_UPLOAD_FAIL, ex.toString()));
			return book;
		}
		return book;
	}
	
	String convertDOM2XML(DOMDocument doc)
	{
		StringWriter sw = new StringWriter();
		try
		{
			doc.write(sw);
			return sw.getBuffer().toString().replaceAll("n|r", "");
		}
		catch(Exception ex)
		{
			return "";
		}
	}

	protected abstract String getSheetXML(Sheet sheet);
	
	protected abstract boolean saveData(String strXML);	
}