[JAVA] 엑셀 다운로드

@Override
public void execExcelDownload(HttpServletRequest request, HttpServletResponse response) throws Exception {
	// TODO Auto-generated method stub
	JSONObject param = DevUtil.getJsonFromRequestParameter(request);
	String lang = SessionUtil.getLangcode();
	param.put("LANG", lang);

	String cells[] = {"CUST_PART_NO","PART_NM","PART_NO","ATTR4_NM","ATTR19_NM","MAT_TYPE_NM","ATTR1_NM","KIND_CD","ATTR13","ATTR10","ATTR18","EO_NO"};
		
	String filePath = "xlsx/HJ_BOM_DOWNLOAD.xlsx";
	ClassPathResource resource = new ClassPathResource(filePath);
	File file = resource.getFile();
	FileInputStream fis = new FileInputStream(file);
		
	List<JSONObject> list = mapper.selectPartListToExcel(param);
	@SuppressWarnings("resource")
	Workbook workbook = new XSSFWorkbook(fis);
	XSSFSheet dataSheet = (XSSFSheet) workbook.getSheetAt(0);	// 0번 시트 읽음
	CellStyle style = workbook.createCellStyle();				// 테이블 타이틀 스타일
	style.setBorderTop(BorderStyle.THIN);						// 테두리 위쪽
	style.setBorderBottom(BorderStyle.THIN);					// 테두리 아래쪽
	style.setBorderLeft(BorderStyle.THIN);						// 테두리 왼쪽
	style.setBorderRight(BorderStyle.THIN);						// 테두리 오른쪽
		
	for(int i=0; i<list.size(); i++) {
		JSONObject item = list.get(i);
		XSSFRow row = dataSheet.getRow(i+3);					// 행 (1,2,3,4..)으로 i번 부터 시작 (getCell은 엑셀 위의 알파벳, 열(A,B,C,D..)로 똑같이 i번 부터 시작한다.)
		if(row == null) row = dataSheet.createRow(i+3);
			for(int j=0; j<cells.length; j++) {
				XSSFCell cell = row.createCell(j);
				cell.setCellStyle(style);
				String value = item.optString(cells[j]);
				if(value != null) {
					cell.setCellValue(value);
				}
		}

	}
	SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
	String filename = "PART_LIST_"+sdf.format(new Date())+".xlsx";
	filename = URLEncoder.encode(filename,"UTF-8").replace("+", "%20");
	response.setContentType("application/octer-stream");
    response.setHeader("Content-Transfer-Encoding", "binary;");
    response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
                
    OutputStream os = response.getOutputStream();
    workbook.write(os);
        
    fis.close();
    os.close();
		
	}

@SuppressWarning란?

정적 분석 시에 사용자가 의도적으로 설계한 코드를 컴파일러가 오류라고 판단하여 노란색 줄을 띄우는 경우가 있다.
@SuppressWarnings는 이 경우에, "해당 사항이 오류가 아니다"라는 포스트잇을 붙이는 역할이다.

@SuppressWarnings("resource") : resource 닫기 가능 유형의 자원 사용에 관련된 경고 억제

엑셀 업로드 샘플

public class XlsxListParser {
	
	private Workbook workbook;
	private XSSFSheet dataSheet;
	private int INDEX_START_ROW;
	private int INDEX_START_COL=0;
	
	public XlsxListParser(String filePath, int INDEX_START_ROW) {
		this.INDEX_START_ROW = INDEX_START_ROW;
		File xlsFile = new File(filePath);
		if (xlsFile.exists()) {
			FileInputStream fis = null;
			try {
				fis = new FileInputStream(xlsFile);
				workbook = new XSSFWorkbook(fis);
				fis.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			dataSheet = (XSSFSheet) workbook.getSheetAt(0);
		}
	}
	
	public XlsxListParser(String filePath, int INDEX_START_ROW, int INDEX_START_COL, int sheetNo) {
		this.INDEX_START_ROW = INDEX_START_ROW;
		this.INDEX_START_COL = INDEX_START_COL;
		
		File xlsFile = new File(filePath);
		if (xlsFile.exists()) {
			FileInputStream fis = null;
			try {
				fis = new FileInputStream(xlsFile);
				workbook = new XSSFWorkbook(fis);
				fis.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			dataSheet = (XSSFSheet) workbook.getSheetAt(sheetNo);
		}
	}
	
	public JSONArray getDataSet() {
		JSONArray result = new JSONArray();
    	for(int i=INDEX_START_ROW; i<=dataSheet.getLastRowNum(); i++) {
    		Row row = dataSheet.getRow(i);
    		JSONObject json = new JSONObject();
    		for(int j=INDEX_START_COL; j<row.getLastCellNum(); j++) {
    			Cell cell = row.getCell(j);
    			json.put("F"+(j+1), this.getValueString(cell).trim());
    		}
    		result.add(json);
    		
    	}
    	return result;
    }
	
	private String getValueString(Cell cell) {
		String result = "";
		
		try {
			result = cell.getRichStringCellValue().toString();
			
		}catch(IllegalStateException e) {
			System.out.println("cellType : "+cell.getCellType());
			try {
				if(cell.getCellType()==CellType.FORMULA){
					result = String.valueOf(cell.getNumericCellValue());
					
					if (result.substring(result.length() -2, result.length()).equals(".0")) {
						result = result.substring(0, result.length() - 2);
					}
					
				}else if( DateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					result = new SimpleDateFormat("yyyy-MM-dd").format(date);
					
				}else if(cell.getCellType()==CellType.NUMERIC) {
					DataFormatter fmt = new DataFormatter();
					result = fmt.formatCellValue(cell);
					
				}else {
					result = String.valueOf(cell.getNumericCellValue());
					
				}	
				
			}catch(IllegalStateException e1) {
				System.out.println(e1.getLocalizedMessage());
			}
		}catch(NullPointerException e1) {
			result = "";
		}
		if(result.equals("-")) result = "";
		return result;
	}
	

}

@Override
public JSONObject batchPartNo(JSONObject param) throws Exception {
	// TODO Auto-generated method stub
	String LANG = SessionUtil.getLangcode();
	JSONObject result = new JSONObject();
	String CMPY_SN = param.getString("CMPY_SN");
	String fileseq = param.optJSONArray("TEMP_FILE_LIST").getString(0);
	param.put("fileseq", fileseq);		
    List<JSONObject> fileList = commonMapper.selectSysFileTempInfo(param);
	if(fileList.size() > 0) {
		JSONObject file = fileList.get(0);
		String filePath = file.getString("FULLFILENAME");
		XlsxListParser parser = new XlsxListParser(filePath, 4);
		JSONArray list = parser.getDataSet();
			
		JSONObject cParam = new JSONObject();
		cParam.put("LANG", LANG);
		cParam.put("CODE_SEQ", PdmConstants.MAT_TYPE_CD_LIST);
		List<JSONObject> MAT_TYPE_CD_LIST = commonService.getCodeList(cParam);
		cParam.put("CODE_SEQ", PdmConstants.SLB_PART_ATTR1);
		List<JSONObject> SLB_PART_ATTR1 = commonService.getCodeList(cParam);
		cParam.put("CODE_SEQ", PdmConstants.SLB_PART_ATTR2);
		List<JSONObject> SLB_PART_ATTR2 = commonService.getCodeList(cParam);			
		cParam.put("CODE_SEQ", PdmConstants.UNIT_CD_LIST);
		List<JSONObject> UNIT_CD_LIST = commonService.getCodeList(cParam);
		cParam.put("CODE_SEQ", PdmConstants.SLB_PART_ATTR6);
		List<JSONObject> SLB_PART_ATTR6 = commonService.getCodeList(cParam);
		cParam.put("CODE_SEQ", PdmConstants.SLB_BOM_LEVEL_CD);
		List<JSONObject> SLB_BOM_LEVEL_CD = commonMapper.selectCodeTreeListFromJSON(cParam);
		cParam.put("CODE_SEQ", PdmConstants.SLB_PART_ATTR12);
		List<JSONObject> SLB_PART_ATTR12 = commonService.getCodeList(cParam);
		cParam.put("CODE_SEQ", PdmConstants.SLB_PART_ATTR15);
		List<JSONObject> SLB_PART_ATTR15 = commonService.getCodeList(cParam);
			
			
		for(int i=0; i<list.size(); i++) {
			JSONObject xlsx = list.getJSONObject(i);
			String PART_NO = xlsx.optString("F2");
			if(PART_NO.equals("")) continue;
				
			JSONObject item = new JSONObject();
			String MAT_TYPE_CD = this.getItgrCodeByMgt2(MAT_TYPE_CD_LIST, xlsx.optString("F1"));
			String ATTR28 = PART_NO.substring(0, 1);
			String MAT_MGT1 = PART_NO.substring(1, 2);
			String ATTR1_MGT1 = PART_NO.substring(2, 3);
			String ATTR2_MGT1 = PART_NO.substring(3, 4);
				
			//사업장코드
			item.put("CMPY_SN", CMPY_SN);
			//자재유형
			item.put("MAT_TYPE_CD", MAT_TYPE_CD);
			//품번
			item.put("PART_NO", PART_NO);
			//도번
			item.put("DRAW_NO", PART_NO);
			//품명
			item.put("PART_NM", xlsx.optString("F3"));
			//제품군1
			item.put("ATTR1", this.getItgrCodeByMgt1Mgt2(SLB_PART_ATTR1, ATTR1_MGT1, MAT_MGT1));
			//세부분류
			item.put("ATTR2", this.getItgrCodeByMgt1Mgt2(SLB_PART_ATTR2, ATTR2_MGT1, MAT_MGT1+ATTR1_MGT1));
			/플랜트
			item.put("ATTR4", xlsx.optString("F4"));
			//단위
			item.put("ATTR5", this.getItgrCodeByName(UNIT_CD_LIST, xlsx.optString("F5")));
			//제품군2
			item.put("ATTR6", this.getItgrCodeByMgt1(SLB_PART_ATTR6, xlsx.optString("F6")));
			//제품계층구조
			item.put("ATTR7", this.getItgrCodeByMgt1Tree(SLB_BOM_LEVEL_CD, xlsx.optString("F8")));
			//기존자재번호
			item.put("ATTR8", xlsx.optString("F9"));
			//외부자재그룹
			item.put("KIND_CD", xlsx.optString("F10"));
			//일반품목범주
			item.put("ATTR9", xlsx.optString("F11"));
			//총중량
			item.put("ATTR10", xlsx.optString("F12"));
			//순중량
			item.put("ATTR11", xlsx.optString("F13"));
			//중량단위
			item.put("ATTR12", this.getItgrCodeByName(SLB_PART_ATTR12, xlsx.optString("F14")));
			//재질
			item.put("ATTR13", xlsx.optString("F15"));
			//회로파트타입
			item.put("ATTR14", xlsx.optString("F16"));
			//자재상태
			item.put("ATTR15", this.getItgrCodeByMgt1(SLB_PART_ATTR15, xlsx.optString("F17")));
			//제품/반제품:차종
			item.put("ATTR16", xlsx.optString("F18"));
			//제품/반제품:출하검사(Y:N)
			item.put("ATTR17", this.getYn(xlsx.optString("F19")));
			//제품/반제품:양산여부(Y:N)
			item.put("ATTR18", this.getYn(xlsx.optString("F20")));
			//제품/반제품:2nd고객코드
			item.put("ATTR19", xlsx.optString("F21"));
			//기종
			item.put("ATTR20", xlsx.optString("F22"));
			//원/부자재:수입검사(Y:N)
			item.put("ATTR21", this.getYn(xlsx.optString("F23")));
			//원/부자재:개별바코드여부(Y:N)
			item.put("ATTR22", this.getYn(xlsx.optString("F24")));
			//ERP관리여부(Y:N)
			item.put("ATTR27", "Y");
				
			//회사코드
			item.put("ATTR28", ATTR28);
			mapper.insertPartInfo(item);
		}
	}
		
	result.put("RESULT", true);
	return result;
}