1. Apache POI 라이브러리 소개 및 설치
자바에서 엑셀 파일을 다루기 위해 가장 널리 사용되는 라이브러리는 Apache POI입니다. 이 강력한 라이브러리를 사용하면 .xls(구 버전)와 .xlsx(최신 버전) 두 가지 형식의 엑셀 파일 모두 다룰 수 있습니다.
Maven 설정
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
Gradle 설정
implementation 'org.apache.poi:poi:5.2.3'
implementation 'org.apache.poi:poi-ooxml:5.2.3'
💡 TIP: poi는 구 버전 엑셀(.xls) 파일을 위한 것이고, poi-ooxml은 최신 엑셀(.xlsx) 파일을 위한 것입니다. 대부분의 경우 두 가지 모두 추가하는 것이 좋습니다.
2. 새 엑셀 파일 생성하기
가장 기본적인 작업부터 시작해볼까요? 새 엑셀 파일을 만들고 데이터를 입력해 보겠습니다.
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelCreator {
public static void main(String[] args) {
// 새 워크북(엑셀 파일) 생성
XSSFWorkbook workbook = new XSSFWorkbook();
// 새 시트 생성
XSSFSheet sheet = workbook.createSheet("직원 정보");
// 데이터 준비 (2차원 배열로 표현)
Object[][] data = {
{"이름", "부서", "직급", "입사일"}, // 헤더 행
{"김자바", "개발팀", "선임 개발자", "2020-01-15"},
{"이엑셀", "기획팀", "과장", "2019-03-20"},
{"박데이터", "데이터팀", "팀장", "2018-11-05"}
};
// 데이터를 시트에 쓰기
int rowNum = 0;
for (Object[] rowData : data) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : rowData) {
Cell cell = row.createCell(colNum++);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}
// 열 너비 자동 조정
for (int i = 0; i &amp;lt; data[0].length; i++) {
sheet.autoSizeColumn(i);
}
// 파일로 저장
try (FileOutputStream outputStream = new FileOutputStream("직원정보.xlsx")) {
workbook.write(outputStream);
System.out.println("엑셀 파일이 성공적으로 생성되었습니다!");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
위 코드는 간단한 직원 정보를 담은 엑셀 파일을 생성합니다. 중요한 부분을 살펴볼까요?
XSSFWorkbook: .xlsx 형식의 워크북(엑셀 파일)을 생성합니다.createSheet(): 새로운 시트를 만듭니다.createRow(),createCell(): 행과 열을 생성하고 데이터를 입력합니다.autoSizeColumn(): 데이터 길이에 맞게 열 너비를 자동 조정합니다.- 마지막으로
FileOutputStream을 통해 파일로 저장합니다.
3. 엑셀 파일 읽기
이제 기존 엑셀 파일에서 데이터를 읽어오는 방법을 알아볼까요?
import org.apache.poi.ss.usermodel.*;
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 ExcelReader {
public static void main(String[] args) {
try (FileInputStream file = new FileInputStream(new File("직원정보.xlsx"))) {
// 워크북 객체 생성
Workbook workbook = new XSSFWorkbook(file);
// 첫 번째 시트 가져오기
Sheet sheet = workbook.getSheetAt(0);
// 모든 행 순회
for (Row row : sheet) {
// 행의 모든 셀 순회
for (Cell cell : row) {
// 셀 타입에 따라 다르게 처리
switch (cell.getCellType()) {
case STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print(cell.getDateCellValue() + "\t");
} else {
System.out.print(cell.getNumericCellValue() + "\t");
}
break;
case BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
case FORMULA:
System.out.print(cell.getCellFormula() + "\t");
break;
default:
System.out.print("\t");
}
}
System.out.println();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
여기서 주목할 점은:
FileInputStream으로 파일을 열고,XSSFWorkbook으로 워크북 객체를 생성합니다.getSheetAt()으로 특정 인덱스의 시트를 가져옵니다.getCellType()으로 셀의 데이터 타입을 확인하고, 타입에 맞는 메서드로 값을 추출합니다.DateUtil.isCellDateFormatted()로 날짜 형식인지 확인합니다.
4. 기존 엑셀 파일 수정하기
이미 있는 엑셀 파일에 데이터를 추가하거나 수정하는 방법도 알아봅시다.
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 ExcelModifier {
public static void main(String[] args) {
String filePath = "직원정보.xlsx";
try (FileInputStream inputStream = new FileInputStream(filePath)) {
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
// 새 행 추가 (마지막 행 다음에)
int lastRowNum = sheet.getLastRowNum();
Row newRow = sheet.createRow(lastRowNum + 1);
// 셀에 데이터 입력
newRow.createCell(0).setCellValue("최신입");
newRow.createCell(1).setCellValue("마케팅팀");
newRow.createCell(2).setCellValue("사원");
newRow.createCell(3).setCellValue("2023-05-10");
// 특정 셀 수정하기 (김자바의 직급을 수석 개발자로 변경)
Row row1 = sheet.getRow(1); // 두 번째 행 (인덱스는 0부터 시작)
Cell cell = row1.getCell(2); // 세 번째 열 (직급)
cell.setCellValue("수석 개발자");
// 파일 저장
try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
workbook.write(outputStream);
System.out.println("엑셀 파일이 성공적으로 수정되었습니다!");
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
이 코드에서는:
- 기존 파일을
FileInputStream으로 열고 워크북을 생성합니다. getLastRowNum()으로 마지막 행의 인덱스를 얻어 새 행을 추가합니다.- 특정 행과 열을 지정해서 셀 값을 수정합니다.
- 수정된 내용을
FileOutputStream으로 저장합니다.
5. 스타일과 서식 적용하기
엑셀 파일의 모양을 더 보기 좋게 만들기 위해 스타일을 적용해 봅시다.
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelStyler {
public static void main(String[] args) {
// 새 워크북 생성
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("스타일 예제");
// 헤더 행 생성
Row headerRow = sheet.createRow(0);
// 헤더 스타일 생성
CellStyle headerStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(IndexedColors.WHITE.getIndex());
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
// 헤더 셀 생성 및 스타일 적용
String[] headers = {"상품명", "가격", "수량", "합계"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(headerStyle);
}
// 데이터 행 생성
Object[][] data = {
{"노트북", 1200000, 2, "=B2*C2"},
{"스마트폰", 800000, 3, "=B3*C3"},
{"태블릿", 500000, 1, "=B4*C4"}
};
// 숫자 형식 스타일 생성
CellStyle numberStyle = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
numberStyle.setDataFormat(format.getFormat("#,##0"));
// 데이터 입력 및 스타일 적용
for (int i = 0; i < data.length; i++) {
Row row = sheet.createRow(i + 1);
for (int j = 0; j < data[i].length; j++) {
Cell cell = row.createCell(j);
if (data[i][j] instanceof String) {
String value = (String) data[i][j];
if (value.startsWith("=")) {
cell.setCellFormula(value.substring(1));
cell.setCellStyle(numberStyle);
} else {
cell.setCellValue(value);
}
} else if (data[i][j] instanceof Integer) {
cell.setCellValue((Integer) data[i][j]);
if (j == 1 || j == 3) { // 가격과 합계 열에만 스타일 적용
cell.setCellStyle(numberStyle);
}
}
}
}
// 열 너비 자동 조정
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}
// 파일 저장
try (FileOutputStream outputStream = new FileOutputStream("스타일_예제.xlsx")) {
workbook.write(outputStream);
System.out.println("스타일이 적용된 엑셀 파일이 생성되었습니다!");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
위 코드에서 주목할 부분은:
CellStyle과Font객체를 사용해 셀 스타일과 폰트를 정의합니다.- 배경색 설정(
setFillForegroundColor,setFillPattern) - 숫자 형식 설정(
setDataFormat) - 수식 입력(
setCellFormula)
6. 여러 시트 작업하기
하나의 엑셀 파일에 여러 시트를 생성하고 관리하는 방법을 알아봅시다.
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class MultiSheetExample {
public static void main(String[] args) {
// 새 워크북 생성
Workbook workbook = new XSSFWorkbook();
// 여러 시트 생성
Sheet summarySheet = workbook.createSheet("요약");
Sheet salesSheet = workbook.createSheet("판매 데이터");
Sheet inventorySheet = workbook.createSheet("재고 현황");
// 첫 번째 시트 (요약) 작성
Row summaryHeader = summarySheet.createRow(0);
summaryHeader.createCell(0).setCellValue("분기별 판매 요약");
Row summaryRow1 = summarySheet.createRow(1);
summaryRow1.createCell(0).setCellValue("분기");
summaryRow1.createCell(1).setCellValue("총 판매액");
Row summaryRow2 = summarySheet.createRow(2);
summaryRow2.createCell(0).setCellValue("Q1");
summaryRow2.createCell(1).setCellValue(10500000);
Row summaryRow3 = summarySheet.createRow(3);
summaryRow3.createCell(0).setCellValue("Q2");
summaryRow3.createCell(1).setCellValue(12600000);
// 두 번째 시트 (판매 데이터) 작성
Row salesHeader = salesSheet.createRow(0);
salesHeader.createCell(0).setCellValue("제품");
salesHeader.createCell(1).setCellValue("월");
salesHeader.createCell(2).setCellValue("판매량");
// 데이터 행 추가 (예시)
String[] products = {"노트북", "스마트폰", "태블릿"};
String[] months = {"1월", "2월", "3월"};
int rowNum = 1;
for (String product : products) {
for (String month : months) {
Row row = salesSheet.createRow(rowNum++);
row.createCell(0).setCellValue(product);
row.createCell(1).setCellValue(month);
row.createCell(2).setCellValue(100 + (int)(Math.random() * 900)); // 랜덤 판매량
}
}
// 시트 탐색 및 작업
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
System.out.println("시트 이름: " + sheet.getSheetName());
}
// 파일 저장
try (FileOutputStream outputStream = new FileOutputStream("멀티시트_예제.xlsx")) {
workbook.write(outputStream);
System.out.println("여러 시트가 포함된 엑셀 파일이 생성되었습니다!");
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
여러 시트 작업에서 중요한 점:
createSheet()로 여러 시트를 생성합니다.getNumberOfSheets()로 시트 개수를 확인합니다.getSheetAt()로 특정 시트에 접근합니다.getSheetName()으로 시트 이름을 가져옵니다.
7. 엑셀 파일을 CSV로 변환하기
때로는 엑셀 파일을 CSV 형식으로 변환해야 할 필요가 있습니다.
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.Iterator;
public class ExcelToCsvConverter {
public static void main(String[] args) {
String excelFilePath = "직원정보.xlsx";
String csvFilePath = "직원정보.csv";
try (FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream);
BufferedWriter writer = new BufferedWriter(new FileWriter(csvFilePath))) {
Sheet sheet = workbook.getSheetAt(0); // 첫 번째 시트 선택
// 모든 행을 순회
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// 행의 셀을 CSV 형식으로 변환
StringBuilder csvLine = new StringBuilder();
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
String cellValue = getCellValueAsString(cell);
// CSV 형식에 맞게 처리 (쌍따옴표 이스케이프 등)
if (cellValue.contains(",") || cellValue.contains("\"") || cellValue.contains("\n")) {
cellValue = cellValue.replace("\"", "\"\"");
csvLine.append("\"").append(cellValue).append("\"");
} else {
csvLine.append(cellValue);
}
// 마지막 셀이 아니면 쉼표 추가
if (i < row.getLastCellNum() - 1) {
csvLine.append(",");
}
}
// CSV 파일에 행 쓰기
writer.write(csvLine.toString());
writer.newLine();
}
System.out.println("엑셀 파일이 CSV로 성공적으로 변환되었습니다!");
} catch (IOException e) {
e.printStackTrace();
}
}
// 셀 값을 문자열로 변환하는 헬퍼 메서드
private static String getCellValueAsString(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue().toString();
} else {
// 숫자를 문자열로 변환 (과학적 표기법 방지)
return String.valueOf(cell.getNumericCellValue());
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
try {
return String.valueOf(cell.getNumericCellValue());
} catch (Exception e) {
return cell.getStringCellValue();
}
default:
return "";
}
}
}
이 코드의 주요 부분:
- 각 행의 셀 값을 순회하면서 CSV 형식에 맞게 변환합니다.
- 쉼표, 줄바꿈, 따옴표 등 특수 문자가 포함된 셀은 적절히 처리합니다.
- 다양한 셀 타입(문자열, 숫자, 날짜 등)에 대응하는
getCellValueAsString()메서드를 구현합니다.
8. 자주 발생하는 문제와 해결 방법
메모리 문제 해결 (대용량 파일 처리)
대용량 엑셀 파일을 처리할 때 메모리 부족 문제가 발생할 수 있습니다. 이를 해결하기 위한 방법을 알아봅시다.
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import java.io.FileOutputStream;
import java.io.IOException;
public class LargeExcelGenerator {
public static void main(String[] args) {
// SXSSF 워크북 생성 (스트리밍 모드, 메모리에 100행만 유지)
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
SXSSFSheet sheet = workbook.createSheet("대용량 데이터");
// 많은 행 생성 (예: 100,000 행)
for (int i = 0; i < 100_000; i++) {
Row row = sheet.createRow(i);
for (int j = 0; j < 10; j++) {
Cell cell = row.createCell(j);
cell.setCellValue("데이터 " + i + "," + j);
}
// 진행 상황 로깅 (10,000행마다)
if (i % 10_000 == 0) {
System.out.println(i + "행 생성 완료");
}
}
// 파일 저장
try (FileOutputStream outputStream = new FileOutputStream("대용량_파일.xlsx")) {
workbook.write(outputStream);
System.out.println("대용량 엑셀 파일이 생성되었습니다!");
} catch (IOException e) {
e.printStackTrace();
} finally {
// 임시 파일 정리
workbook.dispose();
try {
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
핵심 포인트:
SXSSFWorkbook을 사용하여 스트리밍 모드로 파일을 생성합니다.- 메모리에 유지할 행 수를 제한하여 메모리 사용량을 줄입니다.
- 작업 완료 후
dispose()를 호출하여 임시 파일을 정리합니다.
비밀번호로 보호된 엑셀 파일 작업
비밀번호로 보호된 엑셀 파일을 생성하는 방법을 알아봅시다.
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import java.io.FileOutputStream;
import java.io.IOException;
import java.security.GeneralSecurityException;
public class PasswordProtectedExcel {
public static void main(String[] args) {
try {
// 새 워크북 생성
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("보안 데이터");
// 몇 가지 데이터 추가
sheet.createRow(0).createCell(0).setCellValue("비밀번호로 보호된 문서입니다");
// 암호화를 위한 임시 파일 생성
String tempFile = "temp.xlsx";
try (FileOutputStream out = new FileOutputStream(tempFile)) {
workbook.write(out);
}
workbook.close();
// 비밀번호 설정 및 암호화
String password = "mySecretPassword";
POIFSFileSystem fs = new POIFSFileSystem();
EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
Encryptor enc = info.getEncryptor();
enc.confirmPassword(password);
// 암호화 및 저장
try (FileOutputStream fos = new FileOutputStream("보안_문서.xlsx");
org.apache.poi.openxml4j.opc.OPCPackage opc = org.apache.poi.openxml4j.opc.OPCPackage.open(tempFile)) {
try (java.io.OutputStream os = enc.getDataStream(fs)) {
opc.save(os);
}
fs.writeFilesystem(fos);
System.out.println("비밀번호로 보호된 엑셀 파일이 생성되었습니다!");
}
} catch (IOException | GeneralSecurityException e) {
e.printStackTrace();
}
}
}
여기서 중요한 점:
POIFSFileSystem과EncryptionInfo를 사용하여 파일을 암호화합니다.- 임시 파일을 생성하고 암호화한 후 최종 파일로 저장합니다.
이상으로 자바를 사용한 엑셀 파일 컨트롤의 기본 방법을 살펴보았습니다. Apache POI 라이브러리를 사용하면 엑셀 파일 생성, 읽기, 수정 등 다양한 작업을 효율적으로 수행할 수 있습니다. 이 포스트가 조금이나마 도움이 될 수 있으면 좋겠네요. 🙂