[SpringBoot] 엑셀 DB에 업로드하기

1. pom.xml

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

 

2. Excel Helper

Apache POI 클래스인 Workbook, Sheet, Row, Cell을 사용한다. 과정은 다음과 같다.

1. InputStream에서 Workbook을 생성한다.

2. Workbook에서 Sheet 이름으로 가져와서 Sheet 객체를 생성한다. 

3. Sheet.iterator()에서 Row 객체들을 순서대로 가져올 수 있다. 

4. Row에서 Cell 객체를 하나씩 가져온다.

간단하게 보면 이런 느낌이다.

Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet(SHEET);
Iterator<Row> rows = sheet.iterator();
while (rows.hasNext()) {
  Row currentRow = rows.next();
  Iterator<Cell> cellsInRow = currentRow.iterator();
  while (cellsInRow.hasNext()) {
     Cell currentCell = cellsInRow.next();
     // each cell case
     id = (long) currentCell.getNumericCellValue();	//숫자
     title = currentCell.getStringCellValue();		//문자열
     published = currentCell.getBooleanCellValue();	//Boolean
  }
     
workbook.close();

활용

Applicant Entity에 엑셀 내용을 담는 메서드다. 

빈 row인데 한번 작성되었다가 지워진 row는 rows에 포함되어서 자꾸 오류가 났다. 

그래서 가장 바깥쪽 while 반복문에 outer라는 이름을 붙여서 특정 cell이 null값일 때 outer반복을 끝냈다.

public static List<Applicant> excelToApplicants(InputStream is) {
   SHEET = "시트이름";
   try {
      Workbook workbook = new XSSFWorkbook(is);
      Sheet sheet = workbook.getSheet(SHEET);
      Iterator<Row> rows = sheet.iterator();
      List<Applicant> applicantList = new ArrayList<>();
      int rowNumber = 0;

      outer : while (rows.hasNext()) {
         Row currentRow = rows.next();
         // 제목부분 스킵
         if (rowNumber == 0) {
            rowNumber++;
            continue;
         }
         Iterator<Cell> cellsInRow = currentRow.iterator();
         Applicant applicant = new Applicant();
         int cellIdx = 0;
         while (cellsInRow.hasNext()) {
            Cell currentCell = cellsInRow.next();
            switch (cellIdx) {
               case 0:
                  //생략               
                  break;
               case 1:
                  String dateStr = currentCell.getStringCellValue();
                  SimpleDateFormat formatter = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
                  applicant.setApplicantDate(formatter.parse(dateStr).toInstant());//.minusSeconds(32400));
                  break;
               case 2:
                  applicant.setApplicantName(currentCell.getStringCellValue());
                  if(applicant.getApplicantName()==null) break outer;
                  break;
               case 3:
                  applicant.setApplicantEmail(currentCell.getStringCellValue());
                  break;
               //생략
               default:
                  break;
            }
            cellIdx++;
         }
         applicantList.add(applicant);
      }
      workbook.close();
      return applicantList;
   } catch (IOException | ParseException e) {
      throw new RuntimeException("엑셀 파일 파싱 실패: " + e.getMessage());
   }
}

 

3. Service

서비스에서는 단순히 2번의 메서드를 호출하면 된다. file.getInputStream을 보내면 된다.

converter는 내가 따로 만든 dto와 entity 매핑 클래스다. repository를 이용해 list를 한번에 저장하고 반환된 entity list를 dto list로 변경해 최종 반환한다.

@Override
public List<ApplicantDto> saveApplicants(MultipartFile file) throws Exception {
   List<Applicant> applicantList = ExcelHelper.excelToApplicants(file.getInputStream());
   return converter.toApplicantDtoList(applicantRepository.saveAll(applicantList));
}

 

4. Controller

1. RestController다. 따라서 @ResponseBody는 따로 추가하지 않았다.

2. PostMapping이다.

3. MultipartFile을 @RequestParam으로 받는다. 

4. 엑셀을 정상적으로 DB에 저장했다면 dto list와 성공 메세지를 같이 반환한다.

5. 예외가 발생했다면 실패 메세지와 에러 종류를 반환한다.

@ApiOperation(value = "지원자 일괄 추가", notes = "지원자 목록을 엑셀로 일괄 추가한다.", response = Map.class)
@PostMapping("/applicant/save")
public ResponseEntity<Map<String, Object>> saveApplicant(@RequestParam(name="file") MultipartFile file) {
   Map<String, Object> resultMap = new HashMap<>();
   HttpStatus status = HttpStatus.ACCEPTED;

   try{
      resultMap.put("list", interviewService.saveApplicants(file));
      resultMap.put("message",SUCCESS);
      status=HttpStatus.OK;
   }catch(Exception e){
      resultMap.put("message",FAIL);
      resultMap.put("error", e.getMessage());
   }

   return new ResponseEntity<>(resultMap, status);
}

 

 

참고 : https://www.bezkoder.com/spring-boot-upload-excel-file-database/