[Java] POI를 이용한 Excel 다루기

안녕하세요. 명월입니다.

이 글은 Java에서 POI를 이용해 Excel를 다루는 방법에 대한 글입니다.

예전에 제가 NPOI를 이용해 C#에서 Excel를 다루는 방법에 대한 글을 사용한 적이 있습니다.

링크 - [C#] NPOI를 이용한 엑셀 파일 만들기

링크 - [C#] NPOI를 이용하여 Excel를 읽어드리고 다시 출력하기

언어는 다르지만 똑같은 apache software 재단에서 만든 라이브러리이기 때문에 사용 방법이나 API는 매우 비슷합니다.

레포지토리 - https://mvnrepository.com/artifact/org.apache.poi/poi

레포지토리 - https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/

...
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.1</version>
</dependency>
...

import java.io.FileOutputStream;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Program {
  public static void main(String... args) {
    new Program();
  }

  public Program() {
    String version = "xls";
    // var version = "xlsx";
    // Workbook 생성.
    Workbook workbook = createWorkbook(version);
    
    // Workbook안에 시트 생성.
    Sheet sheet = workbook.createSheet("Test Sheet");
    
    // Sheet에서 셀 취득
    Cell cell = getCell(sheet, 0, 0);
    // 셀에 데이터 작성
    cell.setCellValue("TEST Result");
    
    cell = getCell(sheet, 0, 1);
    cell.setCellValue(100);
    
    cell = getCell(sheet, 0, 2);
    cell.setCellValue(Calendar.getInstance().getTime());

    // 셀에 데이터 포멧 지정
    CellStyle style = workbook.createCellStyle();
    // 날짜 포멧    
    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    // 정렬 포멧
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    // 셀 색지정
    style.setFillBackgroundColor(IndexedColors.GOLD.index);
    // 폰트 설정
    Font font = workbook.createFont();
    font.setColor(IndexedColors.RED.index);
    cell.setCellStyle(style);
    
    //셀 너비 자동 지정
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.autoSizeColumn(2);
    cell = getCell(sheet, 1, 0);
    cell.setCellValue(1);
    cell = getCell(sheet, 1, 1);
    cell.setCellValue(2);
    cell = getCell(sheet, 1, 2);
    
    //함수식
    cell.setCellFormula("SUM(A2:B2)");
    writeExcel(workbook, "d:\\work\\test." + version);
  }

  // Workbook 생성
  public Workbook createWorkbook(String version) {
    //표준 xls 버젼
    if ("xls".equals(version)) {
      return new HSSFWorkbook();
    //확장 xlsx 버젼
    } else if ("xlsx".equals(version)) {
      return new HSSFWorkbook();
    }
    throw new NoClassDefFoundError();
  }

  // Sheet로 부터 Row를 취득, 생성하기
  public Row getRow(Sheet sheet, int rownum) {
    Row row = sheet.getRow(rownum);
    if (row == null) {
      row = sheet.createRow(rownum);
    }
    return row;
  }

  // Row로 부터 Cell를 취득, 생성하기
  public Cell getCell(Row row, int cellnum) {
    Cell cell = row.getCell(cellnum);
    if (cell == null) {
      cell = row.createCell(cellnum);
    }
    return cell;
  }

  public Cell getCell(Sheet sheet, int rownum, int cellnum) {
    Row row = getRow(sheet, rownum);
    return getCell(row, cellnum);
  }

  public void writeExcel(Workbook workbook, String filepath) {
    try (FileOutputStream stream = new FileOutputStream(filepath)) {
      workbook.write(stream);
    } catch (Throwable e) {
      e.printStackTrace();
    }
  }
}

예전에 C#버전으로 만든 것과 비교했을 때, 함수의 대소문자(코딩 규약)만 다르고 거의 사용방법이 비슷하네요.

이번에는 excel를 읽어서 수정해서 다시 파일로 저장하는 예제를 작성하겠습니다.

예제 첨부 파일 - Test.xls

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Program {
  public static void main(String... args) {
    new Program();
  }

  public Program() {
    String version = "xls";
    // var version = "xlsx";
    // Workbook 취득.
    Workbook workbook = getWorkbook("d:\\work\\Test.xls", version);
    // Workbook안에 시트 취득.
    Sheet sheet = workbook.getSheetAt(0);
    // Sheet에서 셀 취득 후 데이터 설정
    // 1월 오전
    getCell(sheet, 1, 2).setCellValue(-5);
    // 1월 오후
    getCell(sheet, 1, 3).setCellValue(0);
    // 2월 오전
    getCell(sheet, 2, 2).setCellValue(-10);
    // 2월 오후
    getCell(sheet, 2, 3).setCellValue(-5);
    // 3월 오전
    getCell(sheet, 3, 2).setCellValue(0);
    // 3월 오후
    getCell(sheet, 3, 3).setCellValue(2);
    // 4월 오전
    getCell(sheet, 4, 2).setCellValue(4);
    // 4월 오후
    getCell(sheet, 4, 3).setCellValue(10);
    // 5월 오전
    getCell(sheet, 5, 2).setCellValue(10);
    // 5월 오후
    getCell(sheet, 5, 3).setCellValue(15);
    // 6월 오전
    getCell(sheet, 6, 2).setCellValue(18);
    // 6월 오후
    getCell(sheet, 6, 3).setCellValue(25);
    // 7월 오전
    getCell(sheet, 7, 2).setCellValue(23);
    // 7월 오후
    getCell(sheet, 7, 3).setCellValue(28);
    // 8월 오전
    getCell(sheet, 8, 2).setCellValue(25);
    // 8월 오후
    getCell(sheet, 8, 3).setCellValue(31);
    // 9월 오전
    getCell(sheet, 9, 2).setCellValue(25);
    // 9월 오후
    getCell(sheet, 9, 3).setCellValue(29);
    // 10월 오전
    getCell(sheet, 10, 2).setCellValue(15);
    // 10월 오후
    getCell(sheet, 10, 3).setCellValue(25);
    // 11월 오전
    getCell(sheet, 11, 2).setCellValue(11);
    // 11월 오후
    getCell(sheet, 11, 3).setCellValue(17);
    // 12월 오전
    getCell(sheet, 12, 2).setCellValue(5);
    // 12월 오후
    getCell(sheet, 12, 3).setCellValue(9);
    
    // 함수값 재 설정
    for (int i = 1; i <= 12; i++) {
      getCell(sheet, i, 1).setCellFormula(String.format("AVERAGE(C%d:D%d)", i + 1, i + 1));
    }
    writeExcel(workbook, "d:\\work\\result." + version);
  }
  // Workbook 읽어드리기
  public Workbook getWorkbook(String filename, String version) {
    try (FileInputStream stream = new FileInputStream(filename)) {
      //표준 xls 버젼
      if ("xls".equals(version)) {
        return new HSSFWorkbook(stream);
      //확장 xlsx 버젼
      } else if ("xlsx".equals(version)) {
        return new XSSFWorkbook(stream);
      }
      throw new NoClassDefFoundError();
    } catch (Throwable e) {
      e.printStackTrace();
      return null;
    }
  }

  // Sheet로 부터 Row를 취득, 생성하기
  public Row getRow(Sheet sheet, int rownum) {
    Row row = sheet.getRow(rownum);
    if (row == null) {
      row = sheet.createRow(rownum);
    }
    return row;
  }
  // Row로 부터 Cell를 취득, 생성하기
  public Cell getCell(Row row, int cellnum) {
    Cell cell = row.getCell(cellnum);
    if (cell == null) {
      cell = row.createCell(cellnum);
    }
    return cell;
  }

  public Cell getCell(Sheet sheet, int rownum, int cellnum) {
    Row row = getRow(sheet, rownum);
    return getCell(row, cellnum);
  }

  public void writeExcel(Workbook workbook, String filepath) {
    try (FileOutputStream stream = new FileOutputStream(filepath)) {
      workbook.write(stream);
    } catch (Throwable e) {
      e.printStackTrace();
    }
  }
}

NPOI처럼 엑셀 파일을 읽어서 다시 값을 재정의 하는 것으로 그래프도 자동으로 변하는 것을 확인할 수 있습니다. 실제 실무에서는 엑셀을 1부터 만들기 보다는 문서의 템플릿을 어느정도 작성한 상태에서 값을 재설정하여 엑셀의 데이터 수치등을 변하게 하는 방식으로 많이 사용됩니다. 그러는 편이 font나 스타일 설정에도 유리하고, 정적인 설정은 이미 템플릿을 설정하여 사용하는 방식으로 프로그램의 속도를 어느정도 향상시킬 수도 있습니다.

여기까지 Java에서 POI를 이용해 Excel를 다루는 방법에 대한 설명이었습니다.

궁금한 점이나 잘못된 점이 있으면 댓글 부탁드립니다.