참고:
이 글은 제가 이전에 관리하던 블로그에서 작성했던 글입니다. velog에도 게시하고 싶어서 뱃겨왔습니다. 원문은 여기서 보시면 됩니다.

🍀 Apache POI?

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

🍀 JDK 호환성

참고: https://poi.apache.org/devel/

• POI 4.0 and later require JDK version 1.8 or later.

• POI 3.11 and later 3.x versions require JDK version 1.6 or later.

• POI 3.5 to 3.10 required the JDK version 1.5 or later. Versions prior to 3.5 required JDK 1.4+.

나의 경우는 java 11 버전을 쓰기 때문에 POI 4.0 이상의 버전을 사용할 것이다.

🍀 Maven 프로젝트 생성

Maven 프로젝트 생성시 archetype는 maven-archetype-quickstart을 선택한다.

그렇게 생성된 maven 프로젝트의 pom.xml에 poi-ooxml dependency 하나만 추가한다.

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <!-- jdk는 11버전 사용중입니다. -->
    <maven.compiler.source>11</maven.compiler.source>
    <maven.compiler.target>11</maven.compiler.target>
  </properties>

  <dependencies>
    <!-- poi-ooxml 을 추가합니다 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>4.1.2</version>
    </dependency>
    <!-- 나머지 의존성 생략... -->  
  </dependencies>
  

이 의존성 하나만 추가해도 Maven의 전이성 의존 덕분에
poi를 사용하기 위한 필요한 모든 jar 가 추가된다.

IDE에서 본 의존성 목록

🍀 실습 전에 알아둘 핵심 용어

1. apache POI 의 주요 클래스들은 주로 HSSF, XSSF 로 시작한다. 차이점은 아래와 같다.

• HSSF
  • Excel 97(-2007) 파일 포맷을 사용할 때 사용
  • ex) HSSFWorkbook, HSSFSheet

• XSSF
  • Excel 2007 OOXML (*.xlsx) 파일 포맷을 사용할 때 사용
  • ex) XSSFWorkbook, XSSFSheet

2. Workbook, Sheet, Row, Cell

   • Workbook은 하나의 엑셀 파일을 의미
   • Sheet는 엑셀파일(= Workbook)의 시트를 의미
   • Row, Cell 은 Sheet 안에 있는 행과 열을 의미

🍀 엑셀 파일 생성하기

apache poi를 사용한 엑셀 파일을 생성은 아래와 같은 순서로 코드가 진행한다.

1. workbook 을 생성한다.

2. workbook 내에 sheet를 생성한다.

3. sheet 내에 row를 생성한다.

4. 하나의 row에 여러개의 cell을 생성한다. (= 하나의 행에 여러 열을 생성한다)

5. 3과 4의 과정을 계속해서 반복한다.

코드

package practice.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

public class PoiMakeExcel {

    public static String filePath = "C:\\poi_temp";
    public static String fileNm = "poi_making_file_test.xlsx";

    public static void main(String[] args) {

        // 빈 Workbook 생성
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 빈 Sheet를 생성
        XSSFSheet sheet = workbook.createSheet("employee data");

        // Sheet를 채우기 위한 데이터들을 Map에 저장
        Map<String, Object[]> data = new TreeMap<>();
        data.put("1", new Object[]{"ID", "NAME", "PHONE_NUMBER"});
        data.put("2", new Object[]{"1", "cookie", "010-1111-1111"});
        data.put("3", new Object[]{"2", "sickBBang", "010-2222-2222"});
        data.put("4", new Object[]{"3", "workingAnt", "010-3333-3333"});
        data.put("5", new Object[]{"4", "wow", "010-4444-4444"});

        // data에서 keySet를 가져온다. 이 Set 값들을 조회하면서 데이터들을 sheet에 입력한다.
        Set<String> keyset = data.keySet();
        int rownum = 0;

        // 알아야할 점, TreeMap을 통해 생성된 keySet는 for를 조회시, 키값이 오름차순으로 조회된다.
        for (String key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object[] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if (obj instanceof String) {
                    cell.setCellValue((String)obj);
                } else if (obj instanceof Integer) {
                    cell.setCellValue((Integer)obj);
                }
            }
        }

        try (FileOutputStream out = new FileOutputStream(new File(filePath, fileNm))) {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

콘솔 출력

🍀 엑셀 파일 읽기

apache poi를 사용한 엑셀 파일을 읽는 것은 아래와 같은 순서로 코드가 진행한다.

1. 이미 있는 엑셀 파일을 사용해서 workbook 인스턴스를 생성한다.

2. 해당 workbook에서 원하는 sheet를 가져온다.

3. sheet내에서 읽고자 하는 행 번호를 지정한다.

4. row에 있는 모든 cell을 순회하면서 읽는다.

5. 3과 4의 과정을 sheet 내의 모든 행을 읽을 때까지 반복한다.

코드

package practice.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

public class PoiReadExcel {

    public static String filePath = "C:\\poi_temp";
    public static String fileNm = "poi_reading_test.xlsx";

    public static void main(String[] args) {

        try (FileInputStream file = new FileInputStream(new File(filePath, fileNm))){

            // 엑셀 파일로 Workbook instance를 생성한다.
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            // workbook의 첫번째 sheet를 가저온다.
            XSSFSheet sheet = workbook.getSheetAt(0);

            // 만약 특정 이름의 시트를 찾는다면 workbook.getSheet("찾는 시트의 이름");
            // 만약 모든 시트를 순회하고 싶으면
            // for(Integer sheetNum : workbook.getNumberOfSheets()) {
            //      XSSFSheet sheet = workbook.getSheetAt(i);
            // }
            // 아니면 Iterator<Sheet> s = workbook.iterator() 를 사용해서 조회해도 좋다.

            // 모든 행(row)들을 조회한다.
            for (Row row : sheet) {
                // 각각의 행에 존재하는 모든 열(cell)을 순회한다.
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    // cell의 타입을 하고, 값을 가져온다.
                    switch (cell.getCellType()) {

                        case NUMERIC:
                            //getNumericCellValue 메서드는 기본으로 double형 반환
                            System.out.print((int) cell.getNumericCellValue() + "\t");
                            break;

                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }
                System.out.println();
            }
            
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

테스트할 엑셀 파일 내용

콘솔 출력

🍀 엑셀 수식 넣기

엑셀이 복잡해질 수록 수식을 자주 사용하게 된다.
수식을 읽거나, 쓰는 법을 익혀본다.

코드

package practice.test;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class PoiFormulaCell {

    public static String filePath = "C:\\poi_temp";
    public static String fileNm = "poi_formula_test.xlsx";

    public static void main(String[] args) {
        
		XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("example");

        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("width");
        header.createCell(1).setCellValue("height");
        header.createCell(2).setCellValue("area");


        Row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue(100);
        dataRow.createCell(1).setCellValue(100);
        dataRow.createCell(2).setCellFormula("A2*B2");

        try (FileOutputStream out = new FileOutputStream(new File(filePath, fileNm))) {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }


    }
}

콘솔 출력

🍀 엑셀 수식값 읽기

앞서 만든 파일(poi_formula_test.xlsx)의 수식이 적용된 area 값을 읽어 본다.

package practice.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

public class PoiReadFormula {


    public static String filePath = "C:\\poi_temp";
    public static String fileNm = "poi_formula_test.xlsx";

    public static void main(String[] args) {

        try (FileInputStream file = new FileInputStream(new File(filePath, fileNm))) {

            XSSFWorkbook workbook = new XSSFWorkbook(file);

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            XSSFSheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                if(row.getRowNum() == 0 ) { continue; }

                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();
                    System.out.print("변환전: " + cell);
                    System.out.print(" / ");
                    System.out.println("변환후: " + evaluator.evaluateInCell(cell));

                    // 굳이 타입을 확인하고 싶다면 아래처럼 할 수도 있다.
                    /*
                    switch (evaluator.evaluateInCell(cell).getCellType()) {

                        case NUMERIC:
                            System.out.println(cell.getNumericCellValue() + "\t");
                            break;
                        case STRING:
                            System.out.println(cell.getStringCellValue() + "\t");
                            break;
                        case FORMULA:
                            //
                            break;
                    }
                    */
                }
                System.out.println("");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }


    }
}

evaluateInCell 메소드는 파라미터로 들어오는 cell이 수식이면 이를 평가하여(evaluate) 후 수정하여 다시 cell에 넣는다. 그래서 다음과 같은 결과가 나온다.

콘솔 출력

✨ 참고

• https://poi.apache.org/components/spreadsheet/quick-guide.html

• https://stackoverflow.com/questions/14315943/iteration-through-multiple-sheet-of-excel-in-java