아파치 POI, 스프링에서 엑셀 만들기

 

 

 

엑셀 파일의 데이터를 추철해서 DB에 넣고 싶을 때, 혹은 반대로 DB에 있는 데이터를 엑셀 파일로 만들고 싶을 때, 우리는 POI를 찾게 된다. POI를 사용하는 방법을 정리해본다.

 

https://poi.apache.org/

Apache POI - the Java API for Microsoft Documents

 

 

지금부터 진행할 실습 코드는 필자의 Github에도 있다. 자세한 코드를 알고 싶다면 아래 링크를 참고하시라.

https://github.com/conquerex/WhatTheSpringDataJpa/commit/b1d3bdfa0294c65a13c6037117368ad185220359

POI 학습 · conquerex/WhatTheSpringDataJpa@b1d3bdf

 

 

 

 

---

 

 

 

📊 Apache POI

the Java API for Microsoft Documents. 즉 마이크로소프트 문서(MS 오피스로 만들어진 파일)를 위한 Java API라는 뜻. Poor Obfuscation Implementation의 준말이다. 위키백과에는 어원과 관련해서 아래와 같이 설명하고 있다.

기존의 마이크로소프트 오피스의 파일 포맷(OLE 2 Compund Document Format : OLE2)이 일부러 해독하기 힘들게 만들어 놓은것 같음에도 불구하고 실제로 리버스 엔지니어링되어 사용할 수 있게 되었음을 의미한다. POI 프로젝트 내부에서 사용하는 각 모듈의 이름들 또한 이와 비슷하게 유머섞인 이름들로 되어있다.

익숙한 아파치 소프트웨어 재단에서 제공하고 있다. POI를 스프링에 추가하는 것부터 엑셀을 만들고 읽는 것까지 진행해보도록 하겠다.

 

 

🏄‍♂️ 1. 스프링에 POI 추가

본 예제는 POI를 Gradle에 추가하였다. 최신 버전이 궁금하다면 아래 링크를 참고하시길.

https://mvnrepository.com/artifact/org.apache.poi/poi
https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml

 

// build.gradle

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'com.h2database:h2'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'

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

 

여기에 나오는 모든 실습 코드는 아래 자료에서 가지고 왔음을 알린다.

https://www.baeldung.com/java-microsoft-excel

 

 

 

🏄‍♂️ 2. Excel에 들어갈 data와 파일 만들기

Excel을 만들 때 다음과 같은 개념이 생성되어야 한다. 이 개념을 갖고 있으면 코드를 이해하기가 수월하다.

엑셀 파일 - 엑셀 시트 - 개별 행(Row) - 개별 셀(Cell)

 

POI에서는 Workbook을 생성해서 원하는 엑셀 포맷을 읽거나 쓸 수 있다. Workbook은 엑셀 통합 문서이다. 그 내부에 Sheet가 여러개 존재할 수 있다. 이 Sheet는 페이지와 유사한 개념이다. MS 오피스 엑셀 하단에 시트를 구분한 UI를 볼 수 있다. Workbook은 인터페이스이며 이를 구현한 XSSFWorkbook을 생성하여 엑셀 데이터를 담을 준비를 한다. XSSF는 엑셀 파일 포맷 중 하나이다. 아래 표를 보면 알 수 있듯이 Excel '97(-2007) file format인 xls 파일은 HSSF, Excel 2007 OOXML file format인 xlsx 파일은 XSSF라고 부른다.

 

 

 

엑셀 파일을 읽고 쓰기위한 유틸리티성 클래스를 만들기로 했다. 그리고 엑셀파일을 만드는 것부터 해보았다. 먼저 통합문서 개념인 Workbook을 만들고 그 내부에 Sheet를 생성한다.

public class ExcelPOIHelper {

    public void writeExcel() throws IOException {
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet("Persons");
        sheet.setColumnWidth(0, 6000);
        sheet.setColumnWidth(1, 4000);

    }
}

 

이제 Sheet 내부에 Row(행)를 생성한다. Row 내부에는 POI의 최소 단위인 Cell을 넣을 수 있다. 이 때 Cell이나 Workbook 등에는 원하는 스타일을 씌울 수 있다. 아래 코드에는 스타일을 만들고 원하는 데이터를 Cell 단위에 담아서 적용하는 사례이다.

Row header = sheet.createRow(0);

CellStyle headerStyle = workbook.createCellStyle();
// 생략

XSSFFont font = ((XSSFWorkbook) workbook).createFont();
// 생략

Cell headerCell = header.createCell(0);
headerCell.setCellValue("Name");
headerCell.setCellStyle(headerStyle);

headerCell = header.createCell(1);
headerCell.setCellValue("Age");
headerCell.setCellStyle(headerStyle);

// Next, let's write the content of the table with a different style:
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);

Row row = sheet.createRow(2);
Cell cell = row.createCell(0);
cell.setCellValue("John Smith");
cell.setCellStyle(style);

cell = row.createCell(1);
cell.setCellValue(20);
cell.setCellStyle(style);

 

원하는 데이터를 만들었다면 이제 파일 형태로 저장해보자. write 후에 close하는 것도 잊지 말자.

// Finally, let's write the content to a ‘temp.xlsx' file in the current directory and close the workbook:
File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";

FileOutputStream outputStream = new FileOutputStream(fileLocation);
workbook.write(outputStream);
workbook.close();

 

 

 

엑셀의 가능성은 무궁무진하다. 드래곤퀘스트를 만들수 있을 정도로

 

 

🏄‍♂️ 3. Excel 파일 읽기

ExcelPOIHelper 내부에 읽기를 위한 함수를 만들어보자. 파일을 읽어야 하기 때문에 FileInputStream이 필요하다. 또한 통합문서와 Sheet 영역을 읽어와야 하기 때문에 파일에서 읽은 데이터를 Workbook과 Sheet에 담도록 하자.

public Map<Integer, List<String>> readExcel(String fileLocation) throws IOException {
  FileInputStream file = new FileInputStream(fileLocation);
  Workbook workbook = new XSSFWorkbook(file);

  // Next, let's retrieve the first sheet of the file and iterate through each row:
  Sheet sheet = workbook.getSheetAt(0);

}

 

 

Sheet로 읽어들인 데이터를 다시 Row와 Cell로 나눈다. 개별 Cell에 접근할 때 CellType을 확인하는 이유는 적절한 객체 타입으로 저장하기 위해서다.

Map<Integer, List<String>> data = new HashMap<>();
int i = 0;
for (Row row : sheet) {
    data.put(i, new ArrayList<String>());
    for (Cell cell : row) {
        switch (cell.getCellType()) {
			// case 문이 들어갈 자리
		}
	}
	i++;
}
return data;

 

 

POI에서 제공하는 CellType(예. STRING, NUMERIC 등)을 확인한 다음, 적절한 형태로 data에 저장한다. 여기까지하면 엑셀 파일을 만들고 읽는 것이 완성된다.

case STRING: {
    data.get(i).add(cell.getRichStringCellValue().getString());
    break;
}
case NUMERIC: {
    if (DateUtil.isCellDateFormatted(cell)) {
	    data.get(i).add(cell.getDateCellValue() + "");
    } else {
    	data.get(i).add(cell.getNumericCellValue() + "");
    }
    break;
}
case BOOLEAN: {
    data.get(i).add(cell.getBooleanCellValue() + "");
    break;
}
case FORMULA: {
    data.get(i).add(cell.getCellFormula() + "");
    break;
}
default:
    data.get(i).add(" ");

 

 

 

🏄‍♂️ 4. 마지막으로 테스트

마지막으로 제대로 저장을 하고 읽을 수 있는지 테스트를 해보자. generateExcelFile에서 엑셀 파일을 만들고 실제 테스트는 whenParsingPOIExcelFile_thenCorrect에서 진행할 수 있도록 분리하였다. 4개의 assertEquals 중 "20.0"이 반환되는 부분에서 의문을 가질 수 있다. 이는 ExcelPOIHelper에서 NUMERIC 케이스를 보면 원인을 확인할 수 있다. getNumericCellValue 함수는 double 형태로 반환해주기 때문에 소수점이 포함된 형태로 출력이 된다.

 

private ExcelPOIHelper excelPOIHelper;
private static String FILE_NAME = "temp.xlsx";
private String fileLocation;

@BeforeEach
public void generateExcelFile() throws IOException {
    File currDir = new File(".");
    String path = currDir.getAbsolutePath();
    fileLocation = path.substring(0, path.length() - 1) + FILE_NAME;

    excelPOIHelper = new ExcelPOIHelper();
    excelPOIHelper.writeExcel();
}

@Test
public void whenParsingPOIExcelFile_thenCorrect() throws IOException {
    Map<Integer, List<String>> data = excelPOIHelper.readExcel(fileLocation);

    assertEquals("Name", data.get(0).get(0));
    assertEquals("Age", data.get(0).get(1));

    assertEquals("John Smith", data.get(1).get(0));
    // cell.getDateCellValue()은 double 타입으로 반환하기 때문에 "20"이 아닌 "20.0"으로 출력
    assertEquals("20.0", data.get(1).get(1));
}

 

 

이렇게 엑셀 파일을 쓰고 읽는 법을 튜토리얼 수준에서 학습해 보았다. 이런게 될 줄이야. 신기하도다.

 

이번 포스팅의 마무리는 엑셀 아트로. (뭐야 이거... 무서워.)

 

 

 

 

👩‍🎨