이번 포스팅에서는 Apache POI 라이브러리를 사용해서 엑셀 데이터를 읽고 쓰는 작업에 대해서 알아보려고 합니다.

🔖 Apache POI란?

Apache POI는 아파치 소프트 웨어 제단에서 만든 라이브러리로써 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로써 읽고 쓰는 기능을 제공해줍니다.

🧾 사용하는 클래스

다음은 Apache POI에서 엑셀 파일을 다루기 위해 사용하는 클래스에 대해 알아보도록 하겠습니다.

1. HSSF
Excel 87(-2007) 파일 포맷일 때 사용하는 클래스입니다. HSSFWorkbook, HSSFSheet 클래스를 사용하여 엑셀 데이터를 다룰 수 있습니다.

2. XSSF
Excel 2007 OOXML (.xlsx) 파일 포맷일 때 사용하는 클래스입니다. XSSFWorkbook, XSSFSheet 클래스를 사용하여 엑셀 데이터를 다룰 수 있습니다.

3. Workbook, Sheet, Row, Cell
Workbook 클래스는 하나의 엑셀 파일을 나타내며, Sheet 클래스는 엑셀 파일에서 하나의 시트를 나타냅니다. Row, Cell은 각각 Sheet 안에 있는 행과 열을 나타냅니다.

🧾 Gradle 의존성 주입

Spring에서 Apache POI를 사용하려면 maven repository에서 아래의 두 의존성에 대해 동일한 버전으로 추가하면 됩니다.

dependencies {

    implementation group: 'org.apache.poi', name: 'poi', version: '5.3.0'
    implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.3.0'
    
}

🔖 엑셀 파일 읽기

이제 엑셀 파일을 읽어오는 작업을 구현해보도록 하겠습니다.

🧾 Controller에서 Multipart 타입으로 엑셀 파일 받기

@PreAuthorize("hasAuthority('관리자')")
@PostMapping("/places")
public ResponseEntity savePlaces(@RequestParam("excel")MultipartFile file) {
    placeService.savePlaces(file);
    return new ResponseEntity(ApiResponse.success(), HttpStatus.OK);
}

MultipartFile 클래스는 사용자가 업로드한 File을 핸들러에서 손쉽게 다룰 수 있게 도와주는 매개변수입니다. 매개변수를 사용하기 위해서는 MultipartResolver가 등록되어있어야 하는데 Spring Boot에서는 자동으로 등록해줍니다.

MultipartFile 매개변수로 받은 파일은 메모리나 임시 저장소에 저장됩니다.

🧾 받은 엑셀 파일의 데이터 읽기

컨트롤러에서 Multipart 매개변수로 받은 파일을 getInputStream() 메서드를 통해 InputStream 형태로 반환하여 파일의 바이너리 데이터를 바이트 단위로 읽을 수 있도록 합니다.

이후 해당 파일의 내용을 읽어 아래의 메서드들로 Apache POI 라이브러리에서 제공하는 자바 클래스들로 변환하여 엑셀 데이터를 읽을 수 있습니다.

• new XSSWorkbook(InputStream)
  - Apache POI에서 XSSWorkbook 클래스는 엑셀 파일(.xlsx)을 다룰 수 있는 클래스입니다.
  - InputStream을 인자로 받아서 엑셀 파일의 내용을 읽고 워크북 객체를 생성합니다.
  - 내부적으로 InputStream의 데이터를 파싱하여 Excel 파일의 구조와 데이터를 메모리에 로드합니다.
  
  
• workBook.getSheet("sheetName")
  - 읽어온 워크북 객체(엑셀 파일)에서 엑셀 시트 객체를 불러옵니다.
  
  
• workSheet.getRow(i)
  - 읽어온 엑셀 시트에 i번째 행을 불러옵니다.
  
  
• workSheet.getLastRowNum()
  - 읽어온 엑셀 시트의 마지막 행의 번호를 읽어옵니다.
  
  
• row.getCell(i)
  - 읽어온 행에서 i번째 Cell을 읽어옵니다.
  
  
• cell.getCellType()
  - 읽어온 Cell의 타입을 반환합니다.
  
  
• cell.getStringCellValue()
  - 읽어온 Cell의 String 값을 반환합니다.
  
  
• cell.getNumericCellValue()
  • 읽어온 Cell의 Number 값을 반환합니다.

🧾 예시 코드

  /**
     * 액셀 파일의 플레이스 정보 저장 테스트
     */
    public void savePlaces(MultipartFile file) {
        try {
            // 엑셀 파일 읽어서 여러 정보들 바탕으로 엔티티 목록 만들기
            XSSFWorkbook workBook = new XSSFWorkbook(file.getInputStream());

            // 엑셀 파일에서 플레이스 시트 불러오기
            XSSFSheet workSheet = workBook.getSheet("플레이스");

            // 저장할 엔티티 목록
            List<Place> placeList = new ArrayList<>();

            for (int i=3;i<workSheet.getLastRowNum();i++) {
                // ----------- 기본 정보 세팅하기 -------------------
                Place place = new Place();

                Row row = workSheet.getRow(i);

                // 상호명
                Cell nameCell = row.getCell(1);
                if (nameCell == null || nameCell.getCellType() == CellType.BLANK) {
                    break;
                }

                // 상호명
                if (StringUtils.hasText(nameCell.getStringCellValue())) {
                    place.setPlaceName(nameCell.getStringCellValue());
                }

                // 대표 번호(주소 정보)
                if (StringUtils.hasText(row.getCell(2).getStringCellValue())) {
                    place.setPhoneNumber(row.getCell(2).getStringCellValue());
                }

						     ...

 
        } catch (IOException e) {
            throw new ApplicationException(ErrorCode.FILE_INPUT_OUTPUT_ERROR);
        }

🔖 엑셀 파일 쓰기

이번에는 엑셀 파일을 생성하여 클라이언트에게 반환하는 작업을 구현하도록 하겠습니다.

🧾 Controller에서 엑셀 파일을 반환하는 응답 만들기

먼저 엑셀 파일을 반환하기 위해서는 알맞은 컨텐츠 타입 및 헤더를 설정해줘야합니다.

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 타입은 브라우저에게 전달하는 데이터가 엑셀 파일 형식임을 알리는 컨텐츠 타입입니다. 또한 Content-Disposition 헤더를 설정하여 파일이 다운로드 되도록 설정할 수 있습니다.

다음으로 HTTP 응답에 대한 출력 스트림을 가져오는 HttpServletResponse.getOutputStream() 메서드를 통해 HTTP 응답의 본문(Body)에 데이터를 작성해야합니다.

이후 Workbook.write(OutputStream) 메서드를 호출하여 메모리에 만들어진 워크북 객체(엑셀 파일 데이터)를 HTTP 응답의 본문에 엑셀 파일 형식으로 변환하여 출력합니다.

@GetMapping("/엑셀다운로드")
    public void exportToExcel(HttpServletResponse response) throws IOException {
        List<Place> placeList = placeRepository.findAll();
        // 엑셀 파일의 컨텐츠 타입 및 헤더 설정
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=place_export.xlsx");

        // 엑셀 워크북과 시트 생성
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("플레이스");
				....

        

        // 파일을 HTTP 응답으로 출력
        workbook.write(response.getOutputStream());
        workbook.close();
        
    }

🧾 엑셀 파일 만들기

Apache POI 라이브러리는 아래와 같이 엑셀 데이터의 데이터 타입, 형식, 디자인에 관해 다양하게 설정할 수 있는 기능을 제공해줍니다.

1. 스타일 설정(가운데 정렬, 폰트 적용)

   • 정렬 : HorizontalAlignment와 VerticalAlignment를 사용해 가로 및 세로 정렬 설정.
   • 폰트 : createFont()를 통해 글꼴, 굵기, 크기 설정.

   CellStyle style = workbook.createCellStyle();
   
   // 가운데 정렬 설정
   style.setAlignment(HorizontalAlignment.CENTER);
   style.setVerticalAlignment(VerticalAlignment.CENTER);
   
   // 폰트 설정
   Font font = workbook.createFont();
   font.setBold(true);
   font.setFontName("맑은 고딕");
   font.setFontHeightInPoints((short) 12); // 글꼴 크기 설정
   style.setFont(font);
   
   cell.setCellStyle(style);
   

2. 셀 병합 (세로/가로 병합)

   • CellRangeAddress(startRow, endRow, startCol, endCol)를 사용해 병합 영역을 지정합니다.

   // 세로 병합: (0, 2) 행, (0, 0) 열 병합
   sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
   
   // 가로 병합: (0, 0) 행, (1, 3) 열 병합
   sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));

   
   

3. 셀 너비 및 높이 지정

   • 열 너비 : setColumnWidth()는 단위가 1/256입니다.
   • 행 높이 : setHeightInPoints()로 포인트 단위 설정.

   // 열 너비 설정 (단위: 1/256)
   sheet.setColumnWidth(5, 5000); // 5번째 열 너비 설정
   
   // 행 높이 설정
   Row row = sheet.createRow(0);
   row.setHeightInPoints(30); // 행 높이를 포인트 단위로 설정

   
   

4. RGB 색상 설정

   • RGB 값을 사용하려면 XSSFColor와 java.awt.Color를 활용합니다.
   • FillPatternType.SOLID_FOREGROUND를 사용해 색상 채우기.

   XSSFColor color = new XSSFColor(new java.awt.Color(255, 200, 200), null); // RGB 설정
   style.setFillForegroundColor(color);
   style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   cell.setCellStyle(style);

   
   

5. 셀 테두리 추가

   • 각 방향의 테두리를 BorderStyle 을 통해 설정.
   • setTopBorderColor()를 통해 테두리 색상도 설정 가능.

   style.setBorderTop(BorderStyle.THIN);     // 위쪽 테두리
   style.setBorderBottom(BorderStyle.THIN);  // 아래쪽 테두리
   style.setBorderLeft(BorderStyle.THIN);    // 왼쪽 테두리
   style.setBorderRight(BorderStyle.THIN);   // 오른쪽 테두리
   style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 테두리 색상 설정

   
   

6. 드롭다운 리스트 (데이터 유효성 검사)

   • createExplicitListConstraint: 리스트 값을 설정합니다.
   • CellRangeAddressList를 통해 드롭다운이 표시될 범위를 지정합니다.
   • setSuppressDropDownArrow(true)로 드롭다운 화살표를 표시.

   DataValidationHelper validationHelper = sheet.getDataValidationHelper();
   DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(new String[]{"O", "X"});
   
   // 적용 범위 설정: (4번째 행부터 10번째 행까지, 3번째 열)
   CellRangeAddressList addressList = new CellRangeAddressList(3, 9, 3, 3);
   DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
   
   // 드롭다운 화살표 표시
   dataValidation.setSuppressDropDownArrow(true);
   // 에러 박스 표시
   dataValidation.setShowErrorBox(true); 
   sheet.addValidationData(dataValidation);

   
   

7. 셀 값 입력 및 타입 설정

   • setCellValue() 를 사용해 문자열, 숫자, Boolean 타입 데이터를 셀에 설정할 수 있습니다.

   // 문자열 데이터 입력
   cell.setCellValue("Hello World");
   
   // 숫자 데이터 입력
   cell.setCellValue(1234.56);
   
   // Boolean 데이터 입력 (TRUE/FALSE)
   cell.setCellValue(true);