Java Apache POI로 엑셀(Excel) 파일 조작하기

Apache POI

Apache POI는 Microsoft Office 파일들을 읽고 쓸 수 있게 하는 라이브러리입니다. MS Word, MS PowerPoint, MS Excel를 조작할 수 있는데 이번 포스팅에서는 엑셀 파일을 Apache POI를 활용하여 자바 코드로 손쉽게 다룰 수 있는 방법을 알아보겠습니다. 

---

Apache POI 라이브러리 추가

gradle

dependencies {
    implementation 'org.apache.poi:poi:4.1.2'
    implementation 'org.apache.poi:poi-ooxml:4.1.2'
}

maven

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

---

엑셀 파일 생성하기

빈 엑셀 파일을 생성하는 방법은 다음과 같습니다. 맨 위 행에 이름, 나이, 이메일이라는 값을 차례로 넣어준 코드입니다.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriter {
    public static void main(String[] args) throws Exception {
        try (Workbook workbook = new XSSFWorkbook()) {
        
            // 시트 생성
            Sheet sheet = workbook.createSheet("ExampleSheet");
			
            // 시트의 첫번째 행 생성 (헤더 행)
            Row headerRow = sheet.createRow(0);
            
            // 헤더 행에 셀 생성 및 값 설정
            headerRow.createCell(0).setCellValue("이름");
            headerRow.createCell(1).setCellValue("나이");
            headerRow.createCell(2).setCellValue("이메일");

            // 엑셀 파일 저장
            try (FileOutputStream outputStream = new FileOutputStream("excel_file.xlsx")) {
                workbook.write(outputStream);
                System.out.println("엑셀 파일이 생성되었습니다.");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---

기존 엑셀 파일에 데이터 쓰기

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriter {
    public static void main(String[] args) throws IOException {
        try (FileInputStream fileInputStream = new FileInputStream("excel_file.xlsx");
             Workbook workbook = new XSSFWorkbook(fileInputStream)) {

            Sheet sheet = workbook.getSheet("ExampleSheet");

            // 데이터 쓰기
            Row dataRow = sheet.createRow(1);
            dataRow.createCell(0).setCellValue("Yeo");
            dataRow.createCell(1).setCellValue(30);
            dataRow.createCell(2).setCellValue("dutmdcjf@example.com");

            // 엑셀 파일 저장
            try (FileOutputStream outputStream = new FileOutputStream("excel_file.xlsx")) {
                workbook.write(outputStream);
            }

            System.out.println("데이터가 엑셀 파일에 추가되었습니다.");
        }
    }
}

---

엑셀 파일의 데이터 읽기

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {
    public static void main(String[] args) {
        try (FileInputStream fileInputStream = new FileInputStream("excel_file.xlsx");
             Workbook workbook = new XSSFWorkbook(fileInputStream)) {

            Sheet sheet = workbook.getSheet("ExampleSheet");

            for (Row row : sheet) {
                 // cell 값들 출력
                 //for (Cell cell : row) {
                 //    System.out.print(cell.toString() + "/t");
                 //}
                 
                Cell nameCell = row.getCell(0);
                Cell ageCell = row.getCell(1);
                Cell emailCell = row.getCell(2);

                String name = nameCell.getStringCellValue();
                int age = (int) ageCell.getNumericCellValue();
                String email = emailCell.getStringCellValue();

                System.out.println("이름: " + name);
                System.out.println("나이: " + age);
                System.out.println("이메일: " + email);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 

💁Cell 데이터 읽어올 때 데이터를 String으로 받아오고 양쪽 공백을 제거하고 싶다면??

	//  Cell 타입 String으로 + 양쪽 공백 제거
	private String getCellValueAsString(Cell cell) {
		String cellValue = "";

		if (cell != null) {
			switch (cell.getCellType()) {
			case STRING:
				cellValue = cell.getStringCellValue();
				break;
			case NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					Date dateValue = cell.getDateCellValue();
					DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
					cellValue = dateFormat.format(dateValue);
				} else {
					double numbericValue = cell.getNumericCellValue();
					if (numbericValue == Math.floor(numbericValue)) {
						int intValue = (int) numbericValue;
						cellValue = String.valueOf(intValue);
					} else {
						cellValue = String.valueOf(numbericValue);
					}
				}
				break;
			case BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			default:
				cellValue = "";
			}
		}
		return StringUtils.trimWhitespace(cellValue);
	}

 

---

대량 데이터 처리

Apache POI에서 엑셀 파일을 처리할 때 XSSF 방식과 SXSSF 방식을 사용합니다. 지금까지 예제 코드에선 XSSF 방식을 사용했지만, 대량의 데이터를 다룰 때는 SXSSF를 사용하는 것이 더욱 효율적인 방법입니다. XSSF와 SXSSF 방식 차이에 대해 간단하게 설명하고 예시를 보여드리겠습니다.

 

XSSF (XML Spreadsheet Format) :

• XML 기반의 파일 포맷인 .xlsx 파일을다루는데 사용됩니다. .xlsx는 엑셀 2007 버전부터 사용되는 파일 형식입니다.
• XSSF는 전체 데이터를 메모리에 로드하여 처리하는 방식입니다.
• 작은 크기의 엑셀 파일에 적합하고, 메모리 사용량이 중요하지 않을 때 사용할 수 있습니다.    

SXSSF (Streaming Usermodel API for XSSF) :   

• SXSSF는 대량의 데이터를 처리할 때 사용되는 XSSF의 호환 스트리밍 확장입니다.
• SXSSF는 엑셀 파일을작은부분으로 나누어 처리하고, 작업하는 동안에만 일부 데이터에 메모리에 유지하기 때문에 대량의 데이터를 다룰 때 메모리 사용량을 줄여줍니다.
• 데이터가 엑셀 파일에 쓰여지면서 동시에 디스크에 flush 되기 때문에, 메모리 공간에 제한이 있는 경우에 유용합니다.      

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriter {
    public static void main(String[] args) {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream("excel_file.xlsx")) {

            Sheet sheet = workbook.createSheet("ExampleSheet");

            // 대량 데이터 처리
            for (int i = 0; i < 10000; i++) {
                Row row = sheet.createRow(i);
                for (int j = 0; j < 10; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(i + "행 " + j + "열");
                }
            }

            // 엑셀 파일 저장
            workbook.write(outputStream);
            System.out.println("대량 데이터가 엑셀 파일에 저장되었습니다.");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---

Apache POI 주요 메서드

Workbook:

• createSheet(String sheetName): 새로운 시트를 생성합니다. 시트 이름을 인수로 전달하여 생성할 수 있습니다.
• getSheet(String sheetName): 주어진 이름의 시트를 반환합니다.
• getSheetAt(int index): 주어진 인덱스에 해당하는 시트를 반환합니다. 
• getSheetName(int index): 주어진 인덱스에 해당하는 시트의 이름을 반환합니다. 
• setActiveSheet(int sheetIndex): 주어진 인덱스에 해당하는 시트를 활성화합니다. 
• removeSheetAt(int index): 주어진 인덱스에 해당하는 시트를 삭제합니다.
• getNumberOfSheets(): 시트의 개수를 반환합니다.
• createFont(): 폰트 객체를 생성하여 반환합니다.
• createCellStyle(): 셀 스타일을 생성하여 반환합니다.
• close(): Workbook 객체와 관련된 리소스를 해제합니다.

 

Sheet:

• createRow(int rownum): 새로운 행을 생성합니다. 행 번호를 인수로 전달하여 생성할 수 있습니다.
• getRow(int rownum): 주어진 행 번호에 해당하는 행을 반환합니다.
• removeRow(Row row): 주어진 행을 삭제합니다.
• getLastRowNum(): 마지막 행의 인덱스를 반환합니다.
• getPhysicalNumberOfRows(): 물리적으로 존재하는 행의 개수를 반환합니다.
• shiftRows(int startRow, int endRow, int n): 주어진 범위의 행을 지정된 수만큼 이동합니다.
• autoSizeColumn(int column): 주어진 열의 너비를 자동으로 조정하여 셀 내용을 보여줍니다.
• createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow): 시트에서 특정 행과 열을 고정하여 보여줍니다.
• protectSheet(String password): 시트를 보호합니다.

 

Row:

• createCell(int column): 새로운 셀을 생성합니다. 열 번호를 인수로 전달하여 생성할 수 있습니다.
• getCell(int column): 주어진 열 번호에 해당하는 셀을 반환합니다.
• removeCell(Cell cell): 주어진 셀을 삭제합니다.
• getLastCellNum(): 마지막 셀의 열 인덱스를 반환합니다.
• getPhysicalNumberOfCells(): 물리적으로 존재하는 셀의 개수를 반환합니다.
• createCellStyle(): 셀 스타일 객체를 생성하여 반환합니다.
• getHeight(): 행의 높이를 반환합니다.
• setHeight(short height): 행의 높이를 설정합니다.

 

Cell:

• setCellValue(String value): 셀에 문자열 값을 설정합니다.
• setCellValue(double value): 셀에 숫자 값을 설정합니다.
• setCellValue(boolean value): 셀에 불리언 값을 설정합니다.
• getStringCellValue(): 문자열 값을 가져옵니다.
• getNumericCellValue(): 숫자 값을 가져옵니다.
• getBooleanCellValue(): 불리언 값을 가져옵니다.
• getCellType(): 셀의 타입을 반환합니다.
• getCellStyle(): 셀의 스타일을 반환합니다.
• setCellStyle(CellStyle style): 셀에 스타일을 적용합니다.
• setCellFormula(String formula): 셀에 수식을 설정합니다.
• setCellComment(Comment comment): 셀에 주석을 추가합니다.
• removeCellComment(): 셀의 주석을 제거합니다.
• setHyperlink(Hyperlink hyperlink): 셀에 하이퍼링크를 설정합니다.

 

DataFormat:

• getFormat(String format): 주어진 형식 문자열에 대한 포맷 인덱스를 가져옵니다.
• getFormat(Cell cell): 주어진 셀의 형식 문자열을 가져옵니다.
• createCellStyle(): 셀 스타일 객체를 생성합니다.

 

CellStyle:

• setFont(Font font): 셀의 폰트를 설정합니다.
• setDataFormat(short format): 셀의 데이터 형식을 설정합니다.
• setAlignment(HorizontalAlignment align): 셀의 가로 정렬을 설정합니다.
• setVerticalAlignment(VerticalAlignment align): 셀의 세로 정렬을 설정합니다.

 

Font:

• setBold(boolean bold): 폰트를 굵게 설정합니다.
• setItalic(boolean italic): 폰트를 이탤릭체로 설정합니다.
• setUnderline(FontUnderline underline): 폰트에 밑줄을 설정합니다.
• setColor(short color): 폰트의 색상을 설정합니다.

 

Hyperlink:

• createHyperlink(int type): 지정된 유형의 하이퍼링크 객체를 생성합니다.
• setAddress(String address): 하이퍼링크의 주소를 설정합니다.
• setTooltip(String tooltip): 하이퍼링크의 툴팁을 설정합니다.

 

Apache POI에는 위와 같은 기능들 말고도 DataValidationHelper, ConditionalFormatting, Drawing 등을 제공합니다.