Jxls

김삼현·2022년 1월 10일

개요

Jxls 는 java 에서 특유의 표기법을 사용해서 만들어진 엑셀 템플릿을 이용하여 동적으로 엑셀을 생성할 수 있게 해주는 라이브러리이다.
템플릿을 작성하는 문법 자체가 그리 어렵지 않기 때문에 보다 쉽게 동적으로 엑셀 문서를 작성 할 수 있다.

기본적인 사용법

위 이미지는 Jxls 에서 사용되는 템플릿이다.

A:1 셀의 코멘트에 작성된 jx:area(lastCell="E5") 은 E5 셀 까지가 이 sheet 에서 사용되는 템플릿 영역이라는 것을 나타내고
A:4 셀의 코멘트에 작성된 jx:each(items="employees" var="employee" lastCell="E4") 는 A:4-E:4 까지의 영역을 employees 라는 배열을 이용한 loop 를 통해 반복해서 뿌리겠다는 의미이다.

A:4 ~ E:4 셀에 ${employee.name} 처럼 Jsp EL(Expression Language) 와 같은 형태로 작성하게 되면 위에 반복문에서 var 에 할당하는 employee 객체의 값을 출력할 수 있다.

위의 템플릿을 사용하여 생성된 결과물이다.

사용자 정의 커맨드

위의 예시에서 본 jx:area, jx:each 와 같이 코멘트에 작성된 것들을 Jxls 커맨드라고한다.

jx: 뒤에 붙은 area, each 와 같은 값이 커맨드 네임인데 실제 jxls 에서는 템플릿 파일을 읽어와 코멘트에 작성된 커맨드를 확인후 해당 커맨드 이름에 해당하는 커맨드 클래스의
applyAt 메소드를 통해 커맨드에 작성된 기능들이 수행 된다.

jx:area, jx:each 외에도 다양한 커맨드 들이 있고 jxls-documentation 에 사용 방법과 예시가 나와있다.

기본적으로 제공되는 커맨드 클래스 외에 직접 커맨드 클래스를 작성해서 적용할 수 있는데 간단한 예시를 통해 어떤식으로 작성하고 적용하는지 알아보자.

위 이미지는 회사-부서-직원 구조의 데이터를 이용한 보고서이다. 부서는 직원 수 만큼 셀 병합을 해야하고 회사는 하위 부서의 시작 로우부터 마지막 로우 까지 병합이 필요하다.

아래 클래스는 반복문을 돌면서 셀 병합을 수행해주는 커맨드 클래스이다. 기본적으로 반복문기능을 사용해야 하므로 EachCommand 를 상속하여 구현했다.
커맨드 클래스는 Command interface 를 implements 하여 구현하거나 혹은 Command interface 를 implements 한 클래스를 상속하여 작성해야한다.

EachMergeCommand.java

import org.jxls.area.Area;
import org.jxls.command.EachCommand;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.common.Size;

import java.util.List;
import java.util.stream.Collectors;

public class EachMergeCommand extends EachCommand {

    public static final String COMMAND_NAME = "each-merge";

    @Override
    public Size applyAt(CellRef cellRef, Context context) {
        // each-merge 셀 의 하위 셀 영역 목록을 가져온다.
        List<Area> childAreas = this.getAreaList().stream()
                .flatMap(area -> area.getCommandDataList().stream())
                .flatMap(commandData -> commandData.getCommand().getAreaList().stream())
                .collect(Collectors.toList());

        // 셀 병합을 수행하는 MergeAreaListener instance 생성
        MergeAreaListener listener = new MergeAreaListener(this.getTransformer(), cellRef);

        // each-merge comment 가 작성된 cell area 에 MergeAreaListener 추가
        this.getAreaList().get(0).addAreaListener(listener);

        //  하위 영역에 MergeAreaListener 추가
        childAreas.forEach(childArea -> {
            childArea.addAreaListener(listener);
        });

        // each 커맨드 수행         
        return super.applyAt(cellRef, context);
    }
}

위의 클래스에서 사용되는 MergeAreaListener 는 cell 영역에 작업이 수행될때 호출되는 함수들을 가진 클래스이다.
기본적으로 AreaListener 를 implements 해서 작성해야한다.

아래는 실제 병합작업을 수행하는 MergeAreaListener 클래스 이다.

MergeAreaListener.java


import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressBase;
import org.jxls.common.AreaListener;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;

@Slf4j
public class MergeAreaListener implements AreaListener {

    private final CellRef commandCell;

    private final Sheet sheet;

    private CellRef lastRowCellRef;

    public MergeAreaListener(Transformer transformer, CellRef cellRef) {
        this.commandCell = cellRef;
        this.sheet = ((PoiTransformer) transformer).getXSSFWorkbook().getSheet(cellRef.getSheetName());
    }


    @Override
    public void afterApplyAtCell(CellRef cellRef, Context context) {
        // 호출 시의 cellRef 가 하위 영역일 경우 lastRowCellRef 를 설정해주고 아닐 경우 셀 병합 수행
        if (commandCell.getCol() != cellRef.getCol()) {
            this.setLastRowCellRef(cellRef);
        } else {
            // 해당 영역이 이미 병합된 영역일 경우 return
            if (existMerged(cellRef)) {
                return;
            }
            merge(cellRef);
        }
    }

    private void merge(CellRef cellRef) {
        // 설정된 lastRowCellRef 가 없을경우 return
        if(this.lastRowCellRef == null) return;

        // 병합이 시작될 지점
        int from = cellRef.getRow();

        // 벙합이 끝나는 지점 - 하위 영역이 병합된 영역일 경우 병합된 영역의 끝나는 row 를 사용하고 아닐 경우 해당 cell 의 row 를 사용
        int lastRow = sheet.getMergedRegions().stream()
                .filter(address -> address.isInRange(this.lastRowCellRef.getRow(), this.lastRowCellRef.getCol()))
                .mapToInt(CellRangeAddressBase::getLastRow).findFirst().orElse(this.lastRowCellRef.getRow());

        log.debug("this :{}, merged start row : {} | end row : {} | col :{} ", this.toString(), from, lastRow, cellRef.getCol());

        // 병합 cell 생성
        CellRangeAddress region = new CellRangeAddress(from, lastRow, cellRef.getCol(), cellRef.getCol());

        // sheet 에 병합된 셀 추가
        sheet.addMergedRegion(region);

        // 스타일 적용
        applyStyle(sheet.getRow(cellRef.getRow()).getCell(cellRef.getCol()));
    }

    private void setLastRowCellRef(CellRef cellRef) {
        if (this.lastRowCellRef == null || this.lastRowCellRef.getRow() < cellRef.getRow()) {
            this.lastRowCellRef = cellRef;
        }
    }

    private void applyStyle(Cell cell) {
        CellStyle cellStyle = cell.getCellStyle();

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    }


    private boolean existMerged(CellRef cell) {
        return sheet.getMergedRegions().stream()
                .anyMatch(address -> address.isInRange(cell.getRow(), cell.getCol()));
    }

    @Override
    public void beforeApplyAtCell(CellRef cellRef, Context context) {
    }

    @Override
    public void beforeTransformCell(CellRef srcCell, CellRef targetCell, Context context) {
    }

    @Override
    public void afterTransformCell(CellRef srcCell, CellRef targetCell, Context context) {
    }

}

AreaListener 을 implements 하면 beforeApplyAtCell, afterApplyAtCell, beforeTransformCell, afterTransformCell 이 4가지의 메소드를 오버라이드 하게된다.

위에 EachMergeCommand 에 applyAt 을 살펴보면 자식 영역에 MergeAreaListener 가 수행되도록 추가한 것을 볼 수 있다.

이렇게 될경우 AreaListener 의 메소드가 호출되는 순서는 아래와 같다.

parent call beforeApplyAtCell -> parent call beforeTransformCell -> parent call afterTransformCell -> child call beforeApplyAtCell
-> child call beforeTransformCell -> child call afterTransformCell -> child call afterApplyAtCell -> parent call afterApplyAtCell

병합할 마지막 로우의 정보가 필요하므로 자식 영역이 afterApplyAtCell 을 호출 할 경우 lastRowCellRef 에 마지막으로 호출한 cell 의 정보를 담는다(순차적으로 호출되기 때문에 마지막에 호출한 셀의 정보를 저장)

부모영역이 afterApplyAtCell 을 호출 한 시점에는 자식영역에서의 모든 작업들이 완료 된 후 이므로 저장된 lastRowCellRef 의 정보를 가지고 병합을 수행한다.

이렇게 작성된 사용자 정의 커맨드 클래스는 아래와 같이 추가해서 사용한다.


XlsCommentAreaBuilder.addCommandMapping(EachMergeCommand.COMMAND_NAME, EachMergeCommand.class);

이렇게 만들어진 커맨드를 이용해서 실제 레포트를 뽑아보자.

ExcelGenerator.java


import com.excel.EachMergeCommand;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;

import java.io.*;

public class ExcelGenerator {

    private final String templatePath;

    private final Context context;

    public ExcelGenerator(String templatePath) {
        this.templatePath = templatePath;
        this.context = new Context();
        XlsCommentAreaBuilder.addCommandMapping(EachMergeCommand.COMMAND_NAME, EachMergeCommand.class);
    }

    public void addMappingValue(String varName, Object mappingValue) {
        this.context.putVar(varName, mappingValue);
    }

    public void generate(String outputPath) {
        try {
            InputStream is = this.getClass().getClassLoader().getResourceAsStream(this.templatePath);
            OutputStream os = new FileOutputStream(outputPath);
            try {
                JxlsHelper.getInstance().processTemplate(is, os, this.context);
            } catch (IOException e) {
                throw new RuntimeException("occurred error in jxls process template", e);
            }
        } catch (FileNotFoundException e) {
            throw new RuntimeException("not found template file", e);
        }
    }
}

ExcelGeneratorTest.java


import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.Month;
import java.time.ZoneId;
import java.util.Date;
import java.util.List;

public class ExcelGeneratorTest {

    private ExcelGenerator generator;

    private List<Company> companies;

    @BeforeEach
    void setUp() {
        List<Department> departments = List.of(
                Department.builder()
                        .name("개발 1 팀")
                        .employees(List.of(
                                Employee.builder()
                                        .name("홍길동")
                                        .birthDate(Date.from(LocalDate.of(1978, Month.JANUARY, 1).atStartOfDay(ZoneId.systemDefault()).toInstant()))
                                        .bonus(BigDecimal.valueOf(10000))
                                        .payment(BigDecimal.valueOf(200000))
                                        .build(),
                                Employee.builder()
                                        .name("이순신")
                                        .birthDate(Date.from(LocalDate.of(1989, Month.DECEMBER, 22).atStartOfDay(ZoneId.systemDefault()).toInstant()))
                                        .bonus(BigDecimal.valueOf(120000))
                                        .payment(BigDecimal.valueOf(2200000))
                                        .build()
                        ))
                        .build(),
                Department.builder()
                        .name("개발 2 팀")
                        .employees(List.of(
                                Employee.builder()
                                        .name("차범근")
                                        .birthDate(Date.from(LocalDate.of(1955, Month.AUGUST, 4).atStartOfDay(ZoneId.systemDefault()).toInstant()))
                                        .bonus(BigDecimal.valueOf(410000))
                                        .payment(BigDecimal.valueOf(2000000))
                                        .build(),
                                Employee.builder()
                                        .name("박지성")
                                        .birthDate(Date.from(LocalDate.of(1981, Month.JUNE, 30).atStartOfDay(ZoneId.systemDefault()).toInstant()))
                                        .bonus(BigDecimal.valueOf(320000))
                                        .payment(BigDecimal.valueOf(1500000))
                                        .build(),
                                Employee.builder()
                                        .name("손흥민")
                                        .birthDate(Date.from(LocalDate.of(1992, Month.MARCH, 11).atStartOfDay(ZoneId.systemDefault()).toInstant()))
                                        .bonus(BigDecimal.valueOf(120000))
                                        .payment(BigDecimal.valueOf(1000000))
                                        .build()
                        ))
                        .build()
        );

        companies = List.of(
                Company.builder()
                        .name("좋은 회사")
                        .departments(departments)
                        .build(),
                Company.builder()
                        .name("나쁜 회사")
                        .departments(departments)
                        .build(),
                Company.builder()
                        .name("이상한 회사")
                        .departments(departments)
                        .build()
        );

    }

    @Test
    public void nested_each_merge_generate_test() {
        this.generator = new ExcelGenerator("nested-each-merge-template.xlsx");
        this.generator.addMappingValue("companies", companies);

        Assertions.assertDoesNotThrow(() -> {
            generator.generate("nested-output.xlsx");
        });
    }

}