Spring 엑셀 데이터 추출 및 차트 만들기 : jxls Excel View , Chart

Spring 엑셀 데이터 추출 및 차트 만들기 : jxls Excel View , Chart

개발환경

Java 1.6
Spring 3.2.7
jxls 1.x
IntelliJ 14.x

Spring MVC 에서 데이터를 엑셀로 추출하기 위한 방법과 엑셀의 데이터를 이용해 차트를 생성하는 부분을 설명한다.
엑셀을 추출하기 위해 아주 심플한 jxls 라이브러를 사용한다.

jxls 를 사용하게 되면 엑셀 파일을 템플릿으로 사용할 수 있어 레이아웃을 구성하기가 쉽다.
일반적인 라이브러리는 직접 자바로 레이아웃을 구성해야하지만 jxls 는 엑셀에 레이아웃을 구성하고 파일로 저장하면 jxls 에서 해당 템플릿 엑셀을 로드하여 데이터를 알맞게 채워주는 역활을 한다.

크리티컬한 부분까지 구현될지 모르지만 일반적인 엑셀 리포팅으로 쓰기에는 적당한것 같다.

현재 jxls 는 2.x 버전이 제공되지만 나는 1.x 버전을 사용한다. http://jxls.sourceforge.net/1.x

jxls 사용하기 위한 필수 라이브러리

POI 3.7+
Commons BeanUtils
Commons Collections
Commons JEXL
Commons Logging
Commons Digester

자바 1.6을 사용하기 때문에 poi 는 3.9 버전을 사용하였다. 최신 버전을 오류가 발생한다.

메이븐 의존성 설정

<properties>
    <spring.version>3.2.7.RELEASE</spring.version>
    <poi.version>3.9</poi.version>
</properties>

<dependencies>

    <!-- apache poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>${poi.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-contrib</artifactId>
        <version>3.7-beta3</version>
    </dependency>
    <!-- apache poi -->

    <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.9.2</version>
    </dependency>

    <dependency>
        <groupId>commons-collections</groupId>
        <artifactId>commons-collections</artifactId>
        <version>3.2.1</version>
    </dependency>

    <dependency>
        <groupId>commons-digester</groupId>
        <artifactId>commons-digester</artifactId>
        <version>2.0</version>
    </dependency>

    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-jexl</artifactId>
        <version>2.1.1</version>
    </dependency>

    <dependency>
        <groupId>net.sf.jxls</groupId>
        <artifactId>jxls-core</artifactId>
        <version>1.0.5</version>
    </dependency>
</dependencies>

엑셀을 추출하기 위한 UI를 만들기 위해 컨트롤러를 개발하였다.

HelloController.java

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.View;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class HelloController {

    @RequestMapping(value ="/", method = RequestMethod.GET)
    public String dispHomeView(ModelMap model) {
        return "home";
    }

    // 데이터를 엑셀로 추출하여 프론트엔드에 전달한다.
    @RequestMapping(value = "/excel", method = RequestMethod.GET)
    public View viewExcel(Model model) {

        // 임의의 데이터를 만듬.
        List<String> listData = new ArrayList<String>();
        listData.add("홍길동");
        listData.add("나그네");
        listData.add("홍길동");
        listData.add("홍길동");
        listData.add("홍길동");
        listData.add("홍길동");
        listData.add("홍길동");

        // 차트를 만들기 위한 통계자료도 구한다.
        List<Map<String, Object>> listStat = new ArrayList<Map<String, Object>>();

        Map<String, Object> mapStat = new HashMap<String, Object>();
        mapStat.put("name", "홍길동");
        mapStat.put("count", 6);
        listStat.add(mapStat);

        mapStat = new HashMap<String, Object>();
        mapStat.put("name", "나그네");
        mapStat.put("count", 1);
        listStat.add(mapStat);

        // 데이터를 담는 다.
        model.addAttribute("data", listData);
        model.addAttribute("stat", listStat);

        // 엑셀을 출력한다.
        return new ExcelView();
    }
}

뷰 화면을 제공하기 위한 JSP 를 개발한다.

home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<html>
<body>
    <h1>엑셀받기</h1>

    <a href="/excel">다운</a>
</body>
</html>

데이터를 엑셀로 추출하기 위한 엑셀뷰 클래스를 개발한다.

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Map;

public class ExcelView extends AbstractExcelView {

    @Override
    protected void buildExcelDocument(Map<String, Object> modal, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
        response.setHeader("Content-Type", "application/octet-stream");
        response.setHeader("Content-Disposition", "attachment; filename=excel99.xls");

        OutputStream os = null;
        InputStream is = null;

        try {
            // 엑셀 템플릿 파일이 존재하는 위치 (classpath 하위)
            is = new ClassPathResource("/excel.xls").getInputStream();
            os = response.getOutputStream();

            XLSTransformer transformer = new XLSTransformer();

            Workbook excel = transformer.transformXLS(is, modal);
            excel.write(os);
            os.flush();

        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            if(os != null) try { os.close(); } catch (IOException e) { }
            if(is != null) try { is.close(); } catch (IOException e) { }
        }
    }
}

마지막으로 템플릿으로 사용될 엑셀 파일을 생성하면 된다. 엑셀의 태그와 자바 데이터가 어떻게 바인딩되는지 설명한다.

위 사진에서 ${data}는 HelloController에서 viewExcel 메서드의 model.addAttribute("data", listData); 소스를 의미한다.

그리고 ${stat.name} 와 ${stat.count} 는 model.addAttribute("stat", listStat); 의미한다.

데이터 형식은 자동으로 인식하여 알아서 반복문으로 해당 영역에 삽입된다.

생성된 데이터를 이용하여 엑셀 차트를 생성한다.
엑셀 삽입에서 원하는 차트를 선택하고, 차트를 떠블클릭하면 상단에 데이터 선택이라는 메뉴가 있다.

사진과 같이 선택하면 된다. 자세한 설명은 첨부된 엑셀 템플릿을 확인한다. 

엑셀 차트는 엑셀 데이터를 이용하여 엑셀에 제공되는 기능으로 차트를 만들기때문에 엑셀을 잘 아는 사람이라면 쉽게 다양한 차트를 만들 수 있을 것이다. 다시 말해 차트는 자바에서 구현하는 것이 아니라 엑셀에서 구현되는 것이다.

excel.xls

정적인 영역의 데이터를 차트로 표시하였다. 하지만 동적이 영역의 데이터를 차트로 표시하려면 다음과 같이 하면된다. 

엑셀에서 수식 탭에서 이름 관리자를 클릭한다.

새로 만들기 하여 위와같이 두개를 생성한다.
값은 =OFFSET(Sheet1!$G$3,0,0,COUNTA(Sheet1!$G:$G),1)
$G는 필드(열) $3은 행 이다. 시작지점을 설정한다. COUNT 차트를 만들 데이터 , DATE 는 차트 표에 노출될 이름이다. 적당한 위치를 설정한다.

그리고 데이터 선택을 눌러 범례 항목 계열 값에 =Sheet1!COUNT (이름 관리자에 등록한 이름 입력)
가로 축 레이블에는 =Sheet1!DATE 를 입력하면 동적으로 모든 행을 표시할 수 있다.

excel (40).xls

참고자료

http://jxls.sourceforge.net/1.x/samples/chartsample.html

Syaku Blog by Seok Kyun. Choi. 최석균.