Spring-JSP

[Spring-JSP] 엄청쉬운 Apache Poi Excel 양식다운로드 기능구현 / 테이블 참조

Jeong Jeon
반응형

오늘은 excel 다운로드를 만들어보려고 한다.

본인은 양식을 만들어서 사용자들이 양식에 맞게 사용할 수 있도록 제공하려고했다.

 

그와중에 다른 시트에 있는 데이터를 참조해서 드롭다운메뉴로 사용할 수 있게 설정해 주고 싶었는데(사용자 편의성) 찾아봐도 잘나오지 않았다.

 

다음에도 사용할 수 있으니 기록해둬야지

 

1). TEST.JSP

multipart/form-data로 form을 하나 만들어준다. => multipart 설명은 생략하겠다 ㅎ;

 

<button title="양식다운" class="btn btn-primary" type="button" onclick="javascript:excelDownLoad();">양식다운</button>

<!-- 엑셀다운로드 -->
<form id="excelUpload" method="POST" action="/excel/excelDownload" enctype="multipart/form-data"></form>

 

2). TEST.Js

이래저래 하다가 그냥 form만들어서 Submit 날리는것으로 만들었다.

function excelDownLoad(){
	$("#deviceExcelUpload").submit();
}

 

3). TEST.Controller

@RequestMapping(value = "/excel/excelDownload", method = RequestMethod.POST)
	public void excelDownload(HttpServletRequest req, HttpServletResponse resp, Locale locale) throws Exception{
		testService.testExcelDownloadTms(req, resp, locale);
	}

 

4). Service.Java

여러가지 필요한 정보들이 있는데, 일단 Full Code와 나눠서 설명해놔야겠다.

 

  • 셀별 제약조건 걸기 및 다른 Sheet의 Data Table 참조

1. DataValidationHelper :  셀 값 안에 들어갈 정규식을 만드는것을 도와준다.

2. CellRangeAddressList : 셀의 범위를 지정한다. (firstRow, firstCol, lastRow, lastCol)

3. DataValidation : 만들어놓은 정규식을 정해진 범위에 적용시킨다.

 

나머지 Option은 아래 코드 설명 과 함께 Apache Poi API 문서를 참조

//시트1 의 Address를 시트2의 Address 리스트로 뿌려주기 => 데이터 참조
DataValidationConstraint addressConstraint = validationHelper.createFormulaListConstraint("테스트1!$E$2:$E$10000");
//Constraint를 적용시킬 범위를 설정한다. (시작row,끝row,시작column,마지막column)
CellRangeAddressList addressRegions = new CellRangeAddressList(1,10000,0,0);
//Validation에 만든 Constraint와 Regions(범위)를 옵션으로 넣어준다.
DataValidation addressValidation = validationHelper.createValidation(addressConstraint,addressRegions);
//드롭다운 사용 여부
addressValidation.setSuppressDropDownArrow(true);
//에러 메세지 설정
addressValidation.createErrorBox("tip","입력값이 바르지 않습니다.");
//에러박스 노출 설정
addressValidation.setShowErrorBox(true);
//에러 스타일 설정
addressValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
//공백 유효 설정
addressValidation.setEmptyCellAllowed(false);

여기서 어려웠던 것들이 있다. 그냥 드롭다운을 만들때 DB에 있는것을 가져와서 뿌리는것은 쉬웠는데,

다른 시트의 데이터를 참조하는 유효성을 넣어주는것이 어려웠다.

내가 원하는 상황은 시트1에서 1컬럼에 데이터를 여러개 넣었고, 시트2에 다른 작업을 할때 한 컬럼에 대해 시트1에 기입한 데이터를 참조하고 싶었다.

하지만 검색해도 잘 나오지않아, 진짜 Excel을 보고 유효성값을 찾아 해결하였다.

 

해결방안 : 

DataValidationConstraint addressConstraint = validationHelper.createFormulaListConstraint("테스트1!$E$2:$E$10000");

(시트!$시작컬럼$끝컬럼$행)

 

엑셀 숫자만 입력 가능 유효성입력

DataValidationConstraint numConstraint = validationHelper.createCustomConstraint("ISNUMBER(OFFSET($A$1,ROW()-1,COLUMN()-1))");

 

  • FullCode
@Override
	public void testExcelDownloadTms(HttpServletRequest req, HttpServletResponse resp, Locale locale) throws Exception {
		UserVo userVo = (UserVo) req.getAttribute("user");
		//엑셀 양식 만들기
		int companyId = userVo.getCompanyId();
		//저장파일명
		String saveFileName = "등록양식";
		//Sheet 갯수
		String[] sheetNameArr = {"테스트1","테스트2"};

		//Header 임시
		String[] headerNameTmp= {};
		String[] headerName1 = {"TEST_ADDRESS"};
		String[] headerName2 = {"TEST_NAME","TEST_PHONE","TEST_ADDRESS","TEST_ID","TEST_EMAIL"};

		//엑셀 만들기 시작
		@SuppressWarnings("resource")
		SXSSFWorkbook wb = new SXSSFWorkbook();
		//Sheet별 만들기 for
		for(int i=0; i<sheetNameArr.length; i++) {

			SXSSFSheet sheet = wb.createSheet(sheetNameArr[i]);

			Row row = null;
			Cell cell = null;

		/*HEADER 만들기 시작*/
			//테이블 헤더용 스타일
			CellStyle headStyle = wb.createCellStyle();
			//가는 경계선
			headStyle.setBorderTop(BorderStyle.THIN);
			headStyle.setBorderBottom(BorderStyle.THIN);
			headStyle.setBorderLeft(BorderStyle.THIN);
			headStyle.setBorderRight(BorderStyle.THIN);
			//데이터는 가운데 정렬
			headStyle.setAlignment(HorizontalAlignment.CENTER);
			headStyle.setLocked(true);
			//폰트 설정
			Font headerFont = wb.createFont();
			headerFont.setBold(true);
			headStyle.setFont(headerFont);
			// 헤더 생성
			row = sheet.createRow(0);

			//Sheet별 조건 걸기
			if(i==0) {
				headerNameTmp = headerName1;
			}else if(i==1){
				headerNameTmp = headerName2;
				TESTVo testVo = new TESTVo();
				TESTVo.setCompanyId(companyId);
				ArrayList<TESTVo> testPhoneList = testDao.selectPhoneList(testVo);
				ArrayList<TESTVo> testEmailList = testDao.selectEmailList(testVo);
                
				if(StringUtils.isEmpty(testPhoneList) || testEmailList.size() == 0 ) {
					JsonObject result = new JsonObject();
					result.addProperty("result", Constant.XHR_REQUEST_FAIL);
					result.addProperty("msg", "없습니다.");
					PrintWriter out = resp.getWriter();
			        resp.setContentType("text/html");
			        resp.setCharacterEncoding("UTF-8");
			        out.write(result.toString());
			        out.flush();
			        return;
				}
			}

			//Header 넣어주기
			for(int j = 0; j < headerNameTmp.length; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(headStyle);
				cell.setCellValue(headerNameTmp[j]);
			}

			// 기본 셀 넓이 지정 (글자수 비례)
			for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) {
				sheet.setColumnWidth(x, ((256 * sheet.getRow(0).getCell(x).getStringCellValue().length()) + 1500));
	        }

		/* 각 셀별 제약조건 걸어주기 */
			DataValidationHelper validationHelper = sheet.getDataValidationHelper();

			//공통 Header 제약조건 걸기 => 셀 변경 불가
			DataValidationConstraint headerConstraint = validationHelper.createExplicitListConstraint(new String[] {""});
			CellRangeAddressList headerRegions = new CellRangeAddressList(0,0,-1,-1);
			DataValidation headerValidation = validationHelper.createValidation(headerConstraint, headerRegions);
			headerValidation.setSuppressDropDownArrow(false);
			headerValidation.createErrorBox("tip","변경이 불가 합니다.");
			headerValidation.setShowErrorBox(true);
			headerValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
			headerValidation.setEmptyCellAllowed(false);


			//첫번째 시트
			if(i==0) {
            	//컬럼별 조건 넣기
				ArrayList<TESTVo> testNameList =testDao.selectTestNameList(new TESTVo());
				
				//원하는 형식으로 데이터 가공-Start
				ArrayList<String> testNameListTmp = new ArrayList<String>();
				
				for(TESTVo testVo : testNameList) {
					testNameListTmp.add(testVo.getTestAddress());
				}
				//원하는 형식으로 데이터 가공 -End


			/* Constraints of Column-TEST_Address */
				//시트1 의 Address를 시트2의 Address 리스트로 뿌려주기 => 데이터 참조
				DataValidationConstraint addressConstraint = validationHelper.createFormulaListConstraint("테스트1!$E$2:$E$10000");
				//Constraint를 적용시킬 범위를 설정한다. (시작row,끝row,시작column,마지막column)
				CellRangeAddressList addressRegions = new CellRangeAddressList(1,10000,0,0);
				//Validation에 만든 Constraint와 Regions(범위)를 옵션으로 넣어준다.
				DataValidation addressValidation = validationHelper.createValidation(addressConstraint,addressRegions);
				//드롭다운 사용 여부
				addressValidation.setSuppressDropDownArrow(true);
				//에러 메세지 설정
				addressValidation.createErrorBox("tip","입력값이 바르지 않습니다.");
				//에러박스 노출 설정
				addressValidation.setShowErrorBox(true);
				//에러 스타일 설정
				addressValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
				//공백 유효 설정
				addressValidation.setEmptyCellAllowed(false);

			/* Constraints of Column-TEST_ID */
				DataValidationConstraint testNameConstraint = validationHelper.createExplicitListConstraint(testNameListTmp.toArray(new String[testNameListTmp.size()]));
				
                CellRangeAddressList testNameRegions = new CellRangeAddressList(1,10000,1,1);
				DataValidation testNameValidation = validationHelper.createValidation(testNameConstraint, testNameRegions);
				testNameValidation.setSuppressDropDownArrow(true);
				testNameValidation.createErrorBox("tip","입력값이 바르지 않습니다.");
				testNameValidation.setShowErrorBox(true);
				testNameValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
				testNameValidation.setEmptyCellAllowed(false);

			/* Constraints of Number Format */
				DataValidationConstraint numConstraint = validationHelper.createCustomConstraint("ISNUMBER(OFFSET($A$1,ROW()-1,COLUMN()-1))");

				//SensorSeq
				CellRangeAddressList testIdRegions = new CellRangeAddressList(1,10000,2,2);
				DataValidation testIdValidation = validationHelper.createValidation(numConstraint, testIdRegions);
				testIdValidation.setSuppressDropDownArrow(false);
				testIdValidation.createErrorBox("tip","숫자만 입력 가능 합니다.");
				testIdValidation.setShowErrorBox(true);
				testIdValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
				testIdValidation.setEmptyCellAllowed(false);

				//시트에 만들어놓은 설정값 적용
				sheet.addValidationData(addressValidation);
				sheet.addValidationData(testNameValidation);
				sheet.addValidationData(testIdValidation);
			}

			//모든 Sheet 공통 Validation
			sheet.addValidationData(headerValidation);
		}

		// 컨텐츠 타입과 파일명 지정
		resp.setHeader("Set-Cookie", "fileDownload=true; path=/");
		resp.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((saveFileName).getBytes("KSC5601"),"8859_1")+".xlsx\""));
		wb.write(resp.getOutputStream());
		wb.dispose();
	}

 

2021-06-07

+ 추가

엄청쉬운 데이터 엑셀 다운로드 글

https://zara49.tistory.com/150

 

반응형

'Spring-JSP' 카테고리의 다른 글

[Spring-JSP] Thumbnailator-이미지/jcodec-비디오 업로드시 썸네일 만들고 저장하기  (0) 2021.04.09
[Spring-JSP] Rest API 사용하기  (0) 2021.03.03
[Spring-JSP] 엄청쉬운Poi Excel 업로드 기능 구현  (0) 2021.02.17
[Spring-JSP] Custom Annotation만드는 방법 정리  (0) 2021.02.09
[Spring-JSP] 파일업로드 처리 / 파일(단,다중) + 추가정보 @ModelAttribute  (0) 2021.01.29

'Spring-JSP'의 다른글

  • 현재글[Spring-JSP] 엄청쉬운 Apache Poi Excel 양식다운로드 기능구현 / 테이블 참조

관련글

댓글

티스토리툴바