[Spring Boot] Java 엑셀 라이브러리, Apache POI 사용법

# 서론

프로젝트를 진행하다 보니 디비에있는 데이터를 불러와서 엑셀 파일로 저장해야하는 기능을 구현해야하는 일이 생겼다.

그래서 조사를 해보고 구현완료된걸 나중에 다시 삽질하지 않기 위해서 기록한다.

 

자바에서 엑셀을 다루기위해선 아파치 재단에서 만든 POI라는 라이브러리를 사용한다.

 

# 환경 설정

Gradle 의존성

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

구현할 기능의 흐름도는 다음과 같다.

엑셀 파일 불러오기 -> DB에서 데이터 읽어와서 엑셀에 쓰기 -> 해당 엑셀파일 다운로드

 

# 엑셀 파일 불러오기

public static final String FILE_PATH = "C:\\dev\\estimate.xlsx";

// 엑셀 파일 불러오기
OPCPackage opcPackage = OPCPackage.open(new File(FILE_PATH));
XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);
String sheetName = workbook.getSheetName(0);
Sheet sheet = workbook.getSheet(sheetName);

엑셀 파일을 불러오는 방법은 여러개가 있는데, 여기서는 OPCPackage를 통해 가져오는걸로 구현했다.

XSSFWorkbook과 HSSFWorkbook 두 가지 타입이 존재하는데, XSSFWorkbook이 최신 엑셀 까지 지원해주기 때문에 이걸로 한다.

 

파일 경로를 입력하고 workbook 객체를 생성하면 엑셀 파일이 불러와진다.

 

sheet는 엑셀 하단에 있는 그 시트로, 0번째 시트가 메인 시트다. 이름을 불러와서 sheet를 가져온다.

 

// 특정 셀에 특정 값 넣기
private void setValue(Sheet sheet, String position, String value) {
    CellReference ref = new CellReference(position);
    Row r = sheet.getRow(ref.getRow());
    if (r != null) {
        Cell c = r.getCell(ref.getCol());
        c.setCellValue(value);
    }
}

나는 C19와 같은 특정 셀에 데이터를 쓰고 싶어서 구글링을 해본 결과, 해당 코드를 통해서 특정셀에 접근할 수 있다.

position에 "C19"와 같은 위치를 넣고 원하는 데이터를 입력하면 setCellValue로 값을 설정할 수 있다.

사실 이 포스팅의 핵심은 이 메소드이다.

 

다른 포스팅을 봐도, 여러개의 데이터들을 하나의 엑셀파일로 저장하는 기능만 구현해놨었는데, 내가 필요했던 기능은 특정 엑셀 파일을 불러와서 특정 값을 변경하는 기능이였기 때문이다.

 

해당 코드는 아래 스택오버플로 답변을 참고했다.

https://stackoverflow.com/questions/14155423/get-cell-by-a-cell-reference-letter-identifier-with-apache-poi

Get Cell by a Cell reference letter identifier with Apache POI

 

// 데이터 세팅
setValue(sheet, "C4", estimate.getName()); // 수신
setValue(sheet, "C5", estimate.getEmail()); // 이메일
setValue(sheet, "C6", convertPhone(estimate.getPhone())); // 연락처
setValue(sheet, "C8", convertDateWithYear(estimate.getCreatedDate())); // 견적일
setValue(sheet, "C9", convertDateWithYear(estimate.getValidDate())); // 유효일

// 차량-내용
String date = convertDate(estimate.getDepartDate()) + "~" + convertDate(estimate.getArrivalDate());
setValue(sheet, "C14", date);
String content = estimate.getArrivalPlace() + " ~ " + estimate.getArrivalPlace();
setValue(sheet, "F14", content);
setValue(sheet, "L14", estimate.getVehicleType()); // 규격
setValue(sheet, "N14", Integer.toString(estimate.getVehicleNumber())); // 댓수
setValue(sheet, "O14", "대");

구현한 메소드를 활용해서 DB에서 가져온 데이터를 집어넣는다. (여기서 estimate는 JPA로 가져온 엔티티다)

convertXX메소드는 그냥 포맷 변경 메소드라 딱히 중요하지 않다.

 

// 다운로드
response.setContentType("ms-vnd/excel");
response.setHeader("Content-Disposition", "attachment;filename=estimate.xlsx");
response.setStatus(200);
workbook.write(response.getOutputStream());
workbook.close();

그리고 다운로드를 한다.

다운로드 방식은 response를 통해서 웹브라우저 하단 다운로드를 활용하게 된다. (따라서 포스트맨으로 테스트하면 안 된다.)

contentType과 header를 꼭 설정해 줘야한다.

 

따라서 전체적인 코드는 다음과 같다. (해당 기능을 모두 하나의 Service로 제작했다.)

@Service
@RequiredArgsConstructor
public class ExcelService {

    private final EstimateRepository repository;

    public static final String FILE_PATH = "C:\\dev\\estimate.xlsx";

    // 특정 셀에 특정 값 넣기
    private void setValue(Sheet sheet, String position, String value) {
        CellReference ref = new CellReference(position);
        Row r = sheet.getRow(ref.getRow());
        if (r != null) {
            Cell c = r.getCell(ref.getCol());
            c.setCellValue(value);
        }
    }

    // 엑셀 파일 불러와서 값 수정
    public void createExcel(Long estimateId, HttpServletResponse response) throws InvalidFormatException, IOException {
        // 견적 가져오기
        Estimate estimate = repository.findById(estimateId).orElseThrow();

        // 엑셀 파일 불러오기
        OPCPackage opcPackage = OPCPackage.open(new File(FILE_PATH));
        XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);
        String sheetName = workbook.getSheetName(0);
        Sheet sheet = workbook.getSheet(sheetName);
        
        // 데이터 세팅
        setValue(sheet, "C4", estimate.getName()); // 수신
        setValue(sheet, "C5", estimate.getEmail()); // 이메일
        setValue(sheet, "C6", convertPhone(estimate.getPhone())); // 연락처
        setValue(sheet, "C8", convertDateWithYear(estimate.getCreatedDate())); // 견적일
        setValue(sheet, "C9", convertDateWithYear(estimate.getValidDate())); // 유효일

        // 차량-내용
        String date = convertDate(estimate.getDepartDate()) + "~" + convertDate(estimate.getArrivalDate());
        setValue(sheet, "C14", date);
        String content = estimate.getArrivalPlace() + " ~ " + estimate.getArrivalPlace();
        setValue(sheet, "F14", content);
        setValue(sheet, "L14", estimate.getVehicleType()); // 규격
        setValue(sheet, "N14", Integer.toString(estimate.getVehicleNumber())); // 댓수
        setValue(sheet, "O14", "대");
        
        // 다운로드
        response.setContentType("ms-vnd/excel");
        response.setHeader("Content-Disposition", "attachment;filename=estimate.xlsx");
        response.setStatus(200);
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    // 06-29 형식
    private String convertDate(String date){
        return date.substring(5, 10);
    }

    // 2022-06-29 형식
    private String convertDateWithYear(String date) {
        return date.substring(0, 10);
    }

    // 010-1234-1234 형식
    private String convertPhone(String phone) {
        String top = phone.substring(0, 3);
        String mid = phone.substring(3, 7);
        String bottom = phone.substring(7, 11);
        return top + "-" + mid + "-" + bottom;
    }
}

Service 객체로 만들고 Controller에서 가져다 사용하면 된다.

 

# 다운로드시 엑셀 한글 깨짐 해결

// 다운로드
response.setContentType("ms-vnd/excel");
String fileName = "견적서_" + estimate.getName() + "님_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
// 엑셀 다운로드시 한글 깨짐 처리
String outputFileName = new String(fileName.getBytes("KSC5601"), "8859_1");
response.setHeader("Content-Disposition", "attachment;filename=" + outputFileName + ".xlsx");
response.setStatus(200);
workbook.write(response.getOutputStream());
workbook.close();

엑셀 파일 제목에 한글을 그냥 넣으면 _____ 이런식으로 깨지는데, 다음과 같이 설정해주면 깨지지 않고 잘 된다.