어셈블링블링우당탕: [java] spring poi 를 활용한 excel 파일 업로드 및 다운로드 (제약 조건 및 서식 변경)

pom.xml

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

java (service)

SXSSFWorkbook wb = new SXSSFWorkbook();

SXSSFSheet sheet = wb.createSheet("셀이름");

1) excel 다운로드 ( 셀 병합, 스타일 , 제약조건 등)

Row row = null;

Cell cell = null;

int rowNo = 0;

//상단 설명문구 스타일

CellStyle descriptionStyle = wb.createCellStyle();

// 가는 경계선

descriptionStyle.setBorderTop(BorderStyle.MEDIUM);

descriptionStyle.setBorderBottom(BorderStyle.MEDIUM);

descriptionStyle.setBorderLeft(BorderStyle.MEDIUM);

descriptionStyle.setBorderRight(BorderStyle.MEDIUM);

// 데이터는 왼쪽 정렬

descriptionStyle.setAlignment(HorizontalAlignment.LEFT);

descriptionStyle.setLocked(true);

descriptionStyle.setWrapText(true);

// 테이블 헤더용 스타일

CellStyle headStyle = wb.createCellStyle();

// 가는 경계선

headStyle.setBorderTop(BorderStyle.MEDIUM);

headStyle.setBorderBottom(BorderStyle.MEDIUM);

headStyle.setBorderLeft(BorderStyle.MEDIUM);

headStyle.setBorderRight(BorderStyle.MEDIUM);

// 데이터는 가운데 정렬

headStyle.setAlignment(HorizontalAlignment.CENTER);

headStyle.setLocked(true);

CellStyle headRedStyle = wb.createCellStyle();

headRedStyle.setBorderTop(BorderStyle.MEDIUM);

headRedStyle.setBorderBottom(BorderStyle.MEDIUM);

headRedStyle.setBorderLeft(BorderStyle.MEDIUM);

headRedStyle.setBorderRight(BorderStyle.MEDIUM);

// 데이터는 가운데 정렬

headRedStyle.setAlignment(HorizontalAlignment.CENTER);

headRedStyle.setLocked(true);

Font headRedStyleFont = wb.createFont();

headRedStyleFont.setColor(IndexedColors.RED.index);

headRedStyle.setFont(headRedStyleFont);

//폰트 설정

Font headerFont = wb.createFont();

headerFont.setBold(true);

headStyle.setFont(headerFont);

//설명 문구 \n 으로엔터 값을 입력한다.

row = sheet.createRow(rowNo++);

cell = row.createCell(0);

cell.setCellStyle(descriptionStyle);

cell.setCellValue("" +

"* 한번에 최대 " + Constant.MAX_EXCEL_UPLOAD_STUDENT_COUNT + " 명 까지 업로드 가능 합니다." +

"\n* 원생유형,원생학년 항목은 콤보박스 에서 선택 하여 입력 합니다.(셀 복사 가능)" +

"\n* 원생 학년 정보는 설정 > 선택항목설정 페이지에서 설정된 학년정보를 선택할수 있습니다." +

"\n* 빨간색으로 표시된 항목은 필수 항목입니다." +

"\n* 입력되지 않은 사항은 원생관리 > 원생 상세 > 원생등록 페이지에서 수정 가능 합니다."

);

//CellRangeAddress firstRow, lastRow, firstCol, lastCol 을 범위로 설정한다.

//셀 병합

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));

//병합한 열의 높이 설정

row.setHeight((short) 1500);

// 헤더 생성

row = sheet.createRow(rowNo++);

cell = row.createCell(0);

cell.setCellStyle(headRedStyle);

cell.setCellValue("*원생 유형");

cell = row.createCell(1);

cell.setCellStyle(headStyle);

cell.setCellValue("원생 학년");

cell = row.createCell(2);

cell.setCellStyle(headStyle);

cell.setCellValue("원생 학교명");

cell = row.createCell(3);

cell.setCellStyle(headStyle);

cell.setCellValue("원생 휴대폰번호(ex:01011112222)");

cell = row.createCell(4);

cell.setCellStyle(headRedStyle);

cell.setCellValue("*원생 이름");

cell = row.createCell(5);

cell.setCellStyle(headStyle);

cell.setCellValue("성 별");

cell = row.createCell(6);

cell.setCellStyle(headStyle);

cell.setCellValue("생년월일 8자(ex:20010507)");

cell = row.createCell(7);

cell.setCellStyle(headStyle);

cell.setCellValue("출결번호4자리");

cell = row.createCell(8);

cell.setCellStyle(headRedStyle);

cell.setCellValue("*보호자1 성함");

cell = row.createCell(9);

cell.setCellStyle(headRedStyle);

cell.setCellValue("*보호자1 이메일");

cell = row.createCell(10);

cell.setCellStyle(headRedStyle);

cell.setCellValue("*보호자1 휴대폰번호");

cell = row.createCell(11);

cell.setCellStyle(headStyle);

cell.setCellValue("보호자2 성함");

cell = row.createCell(12);

cell.setCellStyle(headStyle);

cell.setCellValue("보호자2 이메일");

cell = row.createCell(13);

cell.setCellStyle(headStyle);

cell.setCellValue("보호자2 휴대폰번호");

// 1열과 2열의 기본 셀 넓이 지정 (글자수 비례)

for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) {

sheet.setColumnWidth(x, ((256 * sheet.getRow(0).getCell(x).getStringCellValue().length()) + 2500));

}

for (int x = 0; x < sheet.getRow(1).getPhysicalNumberOfCells(); x++) {

sheet.setColumnWidth(x, ((256 * sheet.getRow(1).getCell(x).getStringCellValue().length()) + 2500));

}

/**

제약조건을 설정 하는 부분이다

쿼리로 제약조건을 설정해야하는 값 리스트를 가져와서

문자열 리스트로 변환후 DataValidation 객체를 사용하여

콤보박스를 이용한 선택만 가능 하게끔 설정한다.

**/

ArrayList < StudentVo > studentTypeList = studentDao.selectStudentTypeList();

ArrayList < StudentVo > studentGradeTypeList = studentDao.selectStudentGradeTypeList(studentVo);

//데이터 없을시 json return

if (StringUtils.isEmpty(studentTypeList) ||

studentTypeList.size() == 0 ||

StringUtils.isEmpty(studentGradeTypeList) ||

studentGradeTypeList.size() == 0

) {

JsonObject result = new JsonObject();

result.addProperty("result", Constant.XHR_REQUEST_FAIL);

result.addProperty("msg", "원생 타입 또는 원생 학년 타입 값이 설정되지 않았습니다.");

PrintWriter out = res.getWriter();

res.setContentType("text/html");

res.setCharacterEncoding("UTF-8");

out.write(result.toString());

out.flush();

return;

}

ArrayList < String > studentTypeStringList = new ArrayList < String > ();

ArrayList < String > studentGradeTypeStringList = new ArrayList < String > ();

//디비에서 가져온 원생 타입 리스트 를 필요한 데이터만 뽑아서 문자열 리스트로 저장

for (StudentVo s: studentTypeList) {

studentTypeStringList.add(s.getStudentTypeName() + ":" + s.getStudentTypeId());

}

//디비에서 가져온 학년정보 타입 리스트도 마찬가지

for (StudentVo s: studentGradeTypeList) {

studentGradeTypeStringList.add(s.getStudentGradeTypeName() + ":" + s.getStudentGradeTypeId());

}

//현재 시트에 데이터 밸류데이션 헬퍼 객체 생성

DataValidationHelper validationHelper = sheet.getDataValidationHelper();

DataValidationConstraint descriptionConstraint = validationHelper.createExplicitListConstraint(new String[] {

});

//1번째 열의 모든 컬럼에 대해 변경 불가 처리

CellRangeAddressList descriptionRegions = new CellRangeAddressList(0, 0, -1, -1);

DataValidation descriptionValidation = validationHelper.createValidation(descriptionConstraint, descriptionRegions);

descriptionValidation.setSuppressDropDownArrow(false);

descriptionValidation.createErrorBox("tip", "변경이 불가 합니다.");

descriptionValidation.setShowErrorBox(true);

descriptionValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);

descriptionValidation.setEmptyCellAllowed(false);

DataValidationConstraint headerConstraint = validationHelper.createExplicitListConstraint(new String[] {

});

//2번째 열의 모든 컬럼에 대해 변경 불가 처리 (1과 같은 내용이지만 따로관리)

CellRangeAddressList headerRegions = new CellRangeAddressList(1, 1, -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);

//제약 조건 데이터 생성 - 문자열 리스트를 배열로 변환 하여 인자로 전달함

DataValidationConstraint studentTypeConstraint = validationHelper.createExplicitListConstraint(studentTypeStringList.toArray(new String[studentTypeStringList.size()]));

DataValidationConstraint studentGradeTypeConstraint = validationHelper.createExplicitListConstraint(studentGradeTypeStringList.toArray(new String[studentGradeTypeStringList.size()]));

CellRangeAddressList studentTypeRegions = new CellRangeAddressList(rowNo, rowNo + Constant.MAX_EXCEL_UPLOAD_STUDENT_COUNT, 0, 0);

DataValidation studentTypeValidation = validationHelper.createValidation(studentTypeConstraint, studentTypeRegions);

studentTypeValidation.setSuppressDropDownArrow(true);

studentTypeValidation.createErrorBox("tip", "입력값이 바르지 않습니다.");

studentTypeValidation.setShowErrorBox(true);

studentTypeValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);

studentTypeValidation.setEmptyCellAllowed(false);

//위와 같은 방식으로 벨류데이션 객체 생성

CellRangeAddressList studentGradeTypeRegions = new CellRangeAddressList(rowNo, rowNo + Constant.MAX_EXCEL_UPLOAD_STUDENT_COUNT, 1, 1);

DataValidation studentGradeTypeValidation = validationHelper.createValidation(studentGradeTypeConstraint, studentGradeTypeRegions);

studentGradeTypeValidation.setSuppressDropDownArrow(true);

studentGradeTypeValidation.createErrorBox("tip", "입력값이 바르지 않습니다.");

studentGradeTypeValidation.setShowErrorBox(true);

studentGradeTypeValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);

studentGradeTypeValidation.setEmptyCellAllowed(false);

CellRangeAddressList studentGenderRegions = new CellRangeAddressList(rowNo, rowNo + Constant.MAX_EXCEL_UPLOAD_STUDENT_COUNT, 5, 5);

DataValidationConstraint studentGenderConstraint = validationHelper.createExplicitListConstraint(new String[] {

"남자:M",

"여자:W"

});

DataValidation studentGenderValidation = validationHelper.createValidation(studentGenderConstraint, studentGenderRegions);

studentGenderValidation.setSuppressDropDownArrow(true);

studentGenderValidation.createErrorBox("tip", "입력값이 바르지 않습니다.");

studentGenderValidation.setShowErrorBox(true);

studentGenderValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);

studentGenderValidation.setEmptyCellAllowed(false);

//휴대폰번호 입력시 정수로 인식하여 앞에자리 0 이 사라지는걸 막기위한 서식 변경

//모든 열의 3,7,19,13 컬럼은 숫자를 입력해도 모두 문자로 인식한다.

XSSFCellStyle phoneCellStyle = (XSSFCellStyle) wb.createCellStyle();

XSSFDataFormat phoneDataFormat = (XSSFDataFormat) wb.createDataFormat();

phoneCellStyle.setDataFormat(phoneDataFormat.getFormat("@"));

sheet.setDefaultColumnStyle(3, phoneCellStyle);

sheet.setDefaultColumnStyle(7, phoneCellStyle);

sheet.setDefaultColumnStyle(10, phoneCellStyle);

sheet.setDefaultColumnStyle(13, phoneCellStyle);

//위에서 만들어놓은 밸류데이션 객체들 시트에 적용

sheet.addValidationData(descriptionValidation);

sheet.addValidationData(headerValidation);

sheet.addValidationData(studentTypeValidation);

sheet.addValidationData(studentGradeTypeValidation);

sheet.addValidationData(studentGenderValidation);

// 컨텐츠 타입과 파일명 지정

res.setHeader("Set-Cookie", "fileDownload=true; path=/");

res.setHeader("Content-Disposition", String.format("attachment; filename=\"" + new String(("원생등록").getBytes("KSC5601"), "8859_1") + ".xlsx\""));

wb.write(res.getOutputStream());

wb.dispose();

2) excel 업로드

jsp

<label>찾아보기

</label>

</form>

</div>

function excelUpload(excelFileName , formId, targetUrl){

if(!$("input[name=excelFile]").val()){

alert("선택된 파일이 없습니다");

return;

}

var fileFormat = $("input[name="+excelFileName+"]").val().split(".");

if (fileFormat.indexOf("xlsx") > -1 || fileFormat.indexOf("xls") > -1) {

var excelFile = new FormData(document.getElementById(formId));

$.ajax({

url: targetUrl,

data: excelFile,

processData: false,

contentType: false,

type: "POST",

success: function(data){

alert(data.msg);

locaion.reload();

}

});

} else {

alert("excel 파일만 업로드 가능 합니다.");

}

java (controller)

@ResponseBody

@RequestMapping(value = "/student/excel/upload", method = RequestMethod.POST)

public HashMap < String, Object > clientListExcelProc(MultipartHttpServletRequest req, Locale locale) throws Exception {

return studentService.uploadStudentExcelForm(req, locale);

}

java (service)

MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) req;

MultipartFile excelFile = multipart.getFile("excelFile");

if (excelFile == null || excelFile.isEmpty()) {

resultMap.put("result", Constant.XHR_REQUEST_FAIL);

resultMap.put("msg", "파일이 올바르지 않습니다.");

return resultMap;

}

//로컬 파일 저장 경로

String uploadRealPath = env.getProperty("excel.upload.realPath");

//엑셀파일을 읽기 위해서 서버에 저장

File destFile = new File(uploadRealPath + File.separator + studentVo.getCompanyId() + File.separator + excelFile.getOriginalFilename());

try {

if (!destFile.exists()) {

destFile.mkdirs();

}

excelFile.transferTo(destFile);

} catch (Exception e) {

resultMap.put("result", Constant.XHR_REQUEST_FAIL);

resultMap.put("msg", "업로드중 오류가 발생 하였습니다. 관리자에게 문의 하세요.");

return resultMap;

}

//데이터를 가져올 컬럼을 지정

ExcelReadOption excelReadOption = new ExcelReadOption();

excelReadOption.setFilePath(destFile.getAbsolutePath());

excelReadOption.setOutputColumns("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N");

//읽어 드리기 시작할 열

int startRow = 3;

excelReadOption.setStartRow(startRow);

//읽어드리기

List<Map<String, String>> excelContent = ExcelUtil.excelRead(excelReadOption);

try {

for(Map<String, String> content : excelContent) {

//열의 컬럼값들

content.get("A");

content.get("B");

content.get("C");

content.get("D");

...

}

어셈블링블링우당탕

2020년 9월 2일 수요일

[java] spring poi 를 활용한 excel 파일 업로드 및 다운로드 (제약 조건 및 서식 변경)

댓글 없음:

댓글 쓰기

[lunux]리눅스 폴더별 용량 확인