스프링부트 apache poi 이용하여 엑셀 작업하기

일을 하다보면 엑셀파일을 전달하거나 엑셀파일은 다운로드 받을 수 있는 기능을 제공해야 할때가 있다.

그리고 엑셀 파일을 업로드하여 읽어서 처리해야 하는 일도 종종 발생한다.

 

어떻게 하면 보다 쉽게 수정하거나 재활용할 수 있는 방법이 없을까 고민하다가 샘플로 한번 만들어보게 되었다.

apache poi를 이용하여 엑셀 파일 작성 결과

 

소스는 아래 주소에서 받아 볼 수 있다.

https://github.com/wifi-java/spring-poi-excel

 

엑셀 데이터를 그릴 추상 클래스를 작성하고 해당 추상클래스를 상속받아 출력할 양식에 따라 구현해주면 된다.

import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;

public abstract class Excel<T> {
  private final String SHEET_NAME = "Sheet";

  // 시트에 최대한 쓸 수 있는 row 수
  private final int MAX_ROW = 1040000;
  private List<String> header = null;
  private List<T> data = null;

  public Excel(List<String> header, List<T> data) {
    this.header = header;
    this.data = data;
  }

  public void write(SXSSFWorkbook workbook, int rowNum) {
    // 매개변수로 받은 rowNo부터 이어서 작성
    int rowNo = rowNum % MAX_ROW;
    int rowDataNo = rowNo;

    if (ObjectUtils.isNotEmpty(data)) {
      for (T t : data) {
        String sheetName = SHEET_NAME + (rowNo / MAX_ROW + 1);
        boolean isNeedHeader = this.isNeedHeader(workbook, sheetName);
        SXSSFSheet sheet = this.getSheet(workbook, sheetName);

        if (isNeedHeader) {
          drawHeader(workbook, sheet, header);
          rowDataNo = (rowNo + 1) % MAX_ROW;
        }

        drawData(workbook, sheet, rowDataNo, t);
        rowNo = rowNo + 1;
        rowDataNo = rowDataNo + 1;
      }
    } else {
      // 데이터가 없을 경우 헤더라도 그릴 수 있도록 처리.
      String sheetName = SHEET_NAME + (rowNo / MAX_ROW + 1);

      boolean isNeedHeader = this.isNeedHeader(workbook, sheetName);
      SXSSFSheet sheet = this.getSheet(workbook, sheetName);

      if (isNeedHeader) {
        drawHeader(workbook, sheet, header);
        rowNo = rowNo + 1;
      }
    }
  }

  protected abstract void drawHeader(SXSSFWorkbook workbook, SXSSFSheet sheet, List<String> header);

  protected abstract void drawData(SXSSFWorkbook workbook, SXSSFSheet sheet, int rowNo, T data);

  // 헤더 셀 스타일
  protected CellStyle makeHeadStyle(SXSSFWorkbook workbook) {
    CellStyle style = makeBorder(workbook);
    style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);

    Font font = workbook.createFont();
    font.setBold(true);

    style.setWrapText(true);
    style.setFont(font);
    return style;
  }


  private CellStyle makeBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderTop(BorderStyle.THIN);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderRight(BorderStyle.THIN);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setAlignment(HorizontalAlignment.CENTER);

    return style;
  }

  // 셀 스타일 밑줄
  protected CellStyle makeBottomBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderBottom(BorderStyle.THIN);

    return style;
  }

  // 셀 스타일 왼쪽 줄
  protected CellStyle makeLeftBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);

    return style;
  }

  // 셀 스타일 오른쪽 줄
  protected CellStyle makeRightBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderRight(BorderStyle.THIN);

    return style;
  }

  // 셀 스타일 윗줄
  protected CellStyle makeTopBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderTop(BorderStyle.THIN);

    return style;
  }

  // 셀 스타일 왼쪽, 밑줄
  protected CellStyle makeLeftBottomBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);
    style.setBorderBottom(BorderStyle.THIN);

    return style;
  }

  // 셀 스타일 오른쪽, 밑줄
  protected CellStyle makeRightBottomBorder(SXSSFWorkbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderRight(BorderStyle.THIN);
    style.setBorderBottom(BorderStyle.THIN);

    return style;
  }


  private SXSSFSheet getSheet(SXSSFWorkbook workbook, String sheetName) {
    SXSSFSheet sheet = workbook.getSheet(sheetName);

    if (ObjectUtils.isEmpty(sheet)) {
      sheet = workbook.createSheet(sheetName);
    }

    sheet.trackAllColumnsForAutoSizing();

    return sheet;
  }

  private boolean isNeedHeader(SXSSFWorkbook workbook, String sheetName) {
    SXSSFSheet sheet = workbook.getSheet(sheetName);
    return ObjectUtils.isEmpty(sheet);
  }

 

그리고 해당 추상 클래스를 상속받아 실제로 엑셀을 그릴 클래스를 구현한다.

drawHeader, drawData 메서드는 추상클래스인 Excel 클래스에서 write 할때 호출하고 있다. 

다른 엑셀 파일을 작성해야 한다면 Excel를 상속받아 엑셀 양식에 맞게 drawHeader, drawData안에 내용을 작성하면 된다.

import com.example.excel.common.excel.Excel;
import com.example.excel.model.User;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.util.List;

public class ExcelUser extends Excel<User> {

  public ExcelUser(List<String> header, List<User> data) {
    super(header, data);
  }

  @Override
  protected void drawHeader(SXSSFWorkbook workbook, SXSSFSheet sheet, List<String> header) {
    if (ObjectUtils.isNotEmpty(header)) {
      CellStyle headStyle = makeHeadStyle(workbook);
      Row row = sheet.createRow(0);

      for (int i = 0; i < header.size(); i++) {
        String title = header.get(i);

        Cell cell = row.createCell(i);
        cell.setCellStyle(headStyle);
        cell.setCellValue(title);
      }
    }
  }

  @Override
  protected void drawData(SXSSFWorkbook workbook, SXSSFSheet sheet, int rowNo, User data) {
    Row row = sheet.createRow(rowNo);

    Cell nameCell = row.createCell(0, CellType.STRING);
    nameCell.setCellValue(data.getName());

    Cell emailCell = row.createCell(1, CellType.STRING);
    emailCell.setCellValue(data.getEmail());

    CellStyle cellLeftStyle = makeLeftBottomBorder(workbook);
    CellStyle cellRightStyle = makeRightBottomBorder(workbook);
    nameCell.setCellStyle(cellLeftStyle);
    emailCell.setCellStyle(cellRightStyle);

    // 셀 자동 사이즈 조절
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);

    // 셀 여백 조절
    sheet.setColumnWidth(0, (sheet.getColumnWidth(0)) + (short) 512);
    sheet.setColumnWidth(1, (sheet.getColumnWidth(1)) + (short) 512);
  }
}

 

추가로 엑셀 파일을 업로드하여 읽는것은 샘플 소스를 보면 된다.