JXLS을 이용해 엑셀 파일 만들기

JXLS는 개발자가 엑셀 템플릿을 만든 후, 데이터를 엑셀 템플릿에 바인딩하여 엑셀로 만드는 JAVA 라이브러리이다. 

메이븐 설정

JXLS 라이브러리를 사용하기 위해, pom.xml에 아래 dependency를 추가해준다.

<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.8.0</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>2.8.0</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-reader</artifactId>
    <version>2.0.6</version>
</dependency>

• jxls: JXLS 라이브러리의 핵심 기능을 제공하는 라이브러리이다. Excel 템플릿과 데이터를 사용하여 엑셀 파일을 생성하는 데 필요한 주요 기능이 포함되어 있다.
• jxls-poi: Apache POI 라이브러리와 함께 사용되는 JXLS 확장 라이브러리이다. 엑셀 파일을 생성할 때 POI의 API를 활용하여 더 많은 기능을 사용할 수 있다. 예를 들어, 글꼴, 테두리, 셀 스타일, 그리고 다양한 차트 기능을 사용할 수 있다.
• jxls-reader: 엑셀 파일에서 데이터를 읽어오는 기능을 제공하는 라이브러리이다. JXLS 라이브러리의 Excel 템플릿에 데이터를 삽입하는 것과는 반대로, 이 라이브러리는 Excel 파일에서 데이터를 읽어와서 Java 객체로 변환할 수 있다. 이를 통해, 엑셀 파일에서 생성된 데이터를 Java 애플리케이션에서 쉽게 활용할 수 있다.

Java 소스

Controller

    @PostMapping("/order/excel")
    public ResponseEntity<Object> createTestExcel(@RequestBody List<Order> orderList){
        try {
            ByteArrayOutputStream byteArrayOutputStream = documentService.createJxlsExcelFile(orderList);
            return getResponseEntity(byteArrayOutputStream, "Order Excel.xlsx");
        }catch (IOException e){
            log.error("Excel File Creation Fail");
            return ResponseEntity.noContent().build();
        }
    }

    private ResponseEntity<Object> getResponseEntity(ByteArrayOutputStream baos, String fileName) {
        ByteArrayResource resource = new ByteArrayResource(baos.toByteArray());
        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Transfer-Encoding", "Binary");
        headers.add(HttpHeaders.ETAG, "ERMS");
        headers.add(HttpHeaders.CACHE_CONTROL, "private");
        headers.add(HttpHeaders.EXPIRES, "-1;");
        headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + fileName + "\"");
        return ResponseEntity.ok()
            .headers(headers)
            .contentLength(resource.contentLength())
            .contentType(MediaType.APPLICATION_OCTET_STREAM)
            .body(resource);
    }

Service

    public ByteArrayOutputStream createJxlsExcelFile(List<Order> orderList) throws IOException {

        // 엑셀 생성할 때 사용하는 엑셀 템플릿이다.
        String excelTemplateFilePath = "/static/template/test/jxlsExcelTemplate.xlsx";
        // 엑셀 템플릿을 InputStream에 답는다.
        InputStream inputStream = new ClassPathResource(excelTemplateFilePath).getInputStream();

        // 생성된 엑셀 파일을 담은 OutputStream이다.
        final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

        final Context context = new Context();
        // orderDetails는 엑셀 템플릿에서 order 데이터를 가르키는 key이다.
        context.putVar("orderDetails", orderList);

        //엑셀 템플릿(inputStream)에 데이터(context)를 바인딩해서, 엑셀 파일(outputStream)을 생성한다.
        JxlsHelper.getInstance().processTemplate(inputStream, outputStream, context);

        return outputStream;
    }

DTO

엑셀에 나타내주기 위한 데이터를 Order이라는 DTO에 담았다.

@Data
public class Order {
    private OrderBaseData baseData;
    private List<OrderProduct> productList;
}

@Data
public class OrderBaseData {
    private String currency;
    private String customerName;
}

@Data
public class OrderProduct {
    private String orderId;
    private String productName;
    private BigDecimal amount;
    private BigDecimal discountAmount;
    private Integer unit;
}

엑셀 템플릿

엑셀 템플릿은 /static/template/test 위치에 만들었다.

 

작성한 Excel Template이다.

노트에 jxls 수식을 쓴다. 셀에는 ${}을 이용해 출력하고자 하는 데이터의 필드를 지정한다.

참고로 메모는 셀을 오른쪽 클릭하고 '새 노트'를 누르면 된다.

노트 보이는 여부는 '메모 표시/숨기기'를 사용하면 된다.

 

예시로 만든 Excel Template을 첨부했다.

jxlsExcelTemplate.xlsx

0.01MB

jx:area

엑셀을 생성할 영역을 지정해준다.

last Cell에 엑셀에서 가장 마지막 영역 셀의 번호를 넣어주면 된다.

multisheet

jx:each를 이용해 multisheet로 만들 수 있다.

jx:each는 Java에서 forEach 구문을 생각하면 된다. for(var orderDetail: orderDetails) 와 같은거다.

• items: for문 돌릴 필드
• var: for 문 안의 item을 나타낼 변수
• lastCell: 적용될 엑셀 양식의 마지막 셀 번호.
• multisheet: sheet 명

 

sheet가 orderDetails에 있는 Order 개수만큼 생성된다. 

참고로, items에 들어가는 orderDetails는 JAVA에서 데이터를 바인딩할 때 준 Key 값이다.

 

var에 지정된 변수명(orderDetail)으로 엑셀 양식에서 사용된다.

 

데이터 표시

기본적으로 ${}을 이용해, 바인딩할 데이터의 필드를 나타내면 된다.

리스트에 있는 특정 값도 배열처럼 꺼내서 사용할 수 있다. ex) ${orderDetail.productList[0].orderId}

필드에 있는 값들을 더하거나 곱하는 등 특정 수식을 이용해 계산하고 싶다면, $[]로 감싸주면 된다.

 

화폐 마다 다른 포맷 형식은, 엑셀의 사용자 지정을 이용해 표시해줄 수 있다. 

 

물론, 엑셀의 수식들도 다 사용할 수 있다.

 

jx:if

 

jx:if는 condition에 있는 조건문에 따라, 어떤 영역을 선택해서 보여줄 지 결정할 때 사용한다.

currency가 KRW이면, 빨간색 영역을, 아니면 파란색 영역을 사용하겠다는 뜻이다.

 

참고로, 통화마다 다른 형식을 나타내야한다면, 사용자 지정 형식과 jx:if를 같이 사용하길 권장한다.

엑셀 함수의 IF문을 사용해서, 원화이면 ₩1,234로, 달러이면 $1,234.00 으로 표시한다고 하자.

엑셀 함수를 이용해 JSLX 템플릿에 아래와 같이 표현할 수 있다.

$[=IF("${orderDetail.baseData.currency}"="KRW", "₩" & TEXT(${orderDetail.baseData.amount}, "0"), "$"&TEXT(${orderDetail.baseData.amount}, "#,##0.00"))]

 

엑셀을 열면 처음에 아래와 같이 수식 그대로 출력되고,

셀을 클릭해야 계산되어서 나온다.

 

jx:each

jx:each는 Java에서 forEach 구문을 생각하면 된다. for(var orderDetail: orderDetails) 와 같은거다.

• items: for문 돌릴 필드
• var: for 문 안의 item을 나타낼 변수
• lastCell: 적용될 엑셀 양식의 마지막 셀 번호.

productList에 있는 product들이 각 행마다 출력된다.

 

테스트 결과

JSON

[
  {
    "baseData": {
      "currency": "KRW",
      "customerName": "한국 고객"
    },
    "productList": [
      {
        "orderId": "주문 번호 1",
        "productName": "사과",
        "amount": 1000,
        "discountAmount": -200,
        "unit": 10
      },
      {
        "orderId": "주문 번호 1",
        "productName": "귤",
        "amount": 100,
        "discountAmount": 0,
        "unit": 20
      }
    ]
  },
  {
    "baseData": {
      "currency": "USD",
      "customerName": "American Customer"
    },
    "productList": [
      {
        "orderId": "Order No 2",
        "productName": "Grape",
        "amount": 10.4,
        "discountAmount": -1.1,
        "unit": 20
      },
      {
        "orderId": "Order No 2",
        "productName": "Banana",
        "amount": 1.2,
        "discountAmount": -0.2,
        "unit": 30
      }
    ]
  }
]

 

생성된 엑셀 파일

Sheet명이 Currency로 나오고, Multisheet로 생성된 것을 확인할 수 있다.

 

아래는 생성된 엑셀 파일이다.

Order Excel.xlsx

0.01MB

 

참고 자료

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=rlagyska3319&logNo=221199546011 

Spring jxls ( Template ) 이용한 Excel 다운로드 만들기2 Muti Sheet 다중 시트 ( maven 용 )

https://blog.naver.com/rlagyska3319/221183713526

Spring jxls ( Template ) 이용한 Excel 다운로드 만들기 ( maven 용 )

 

https://jxls.sourceforge.net/reference/multi_sheets.html

JXLS -

 

https://truecode-95.tistory.com/138

[JXLS] jx:if(condition 원하는 셀 색상 변경하기

https://kim-jong-hyun.tistory.com/24

[Spring] - jxls로 간편하게 엑셀다운로드 구현하기