[JXLS] JAVA SPRING 데이터 엑셀출력 및 셀병합, merge 하는 방법 에러 해결방법

[JXLS] JAVA SPRING 데이터 엑셀출력 및 셀병합, merge 하는 방법

웹프로젝트를 개발하다보면

테이블이나 어떤 데이터들을 엑셀로 다운받는 기능을 만들어야 하는 경우가 있다.

일단 엑셀로 만드는게 POI만 쓰면 진짜 더럽게 귀찮아진다.

그렇다고 제이쿼리 excel export를 쓰자니

페이징처리된 테이블의 데이터들을 뽑기가 애매하고

1만로우쯤 되었을때 그걸 다 테이블에 append 시키기도 오바같다

그래서 찾다찾다 찾아낸것이

JXLS

일단 jxls는 poi 기반으로 만들어졌다.

사용법은

정말 간단하다.

그냥 마이바티스에서 db데이터 뽑아서

페이지로 날려주는 그 모델

해쉬맵데이터 형식으로뽑은걸 그대로 사용하면 된다

엑셀에 위치 지정이나 반복되는것은 

미리 엑셀 템플릿을 만들어 놓으면

그대로 들어간다.

일단 셋팅 방법부터 알아보도록 하자.

전자정부 기준이로 설명 한다.

1. pom.xml  

pom.xml의 dependency 부분에 아래 코드를 넣어 주도록 하자.

<!-- 엑셀 다운로드 -->
        
        <dependency>
              <groupId>net.sf.jxls</groupId>
               <artifactId>jxls-core</artifactId>
             <version>1.0.6</version>
        </dependency>
       <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-poi</artifactId>
            <version>1.0.13</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls</artifactId>
            <version>2.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.jxls</groupId>
            <artifactId>jxls-jexcel</artifactId>
            <version>1.0.6</version>
        </dependency>

위에서 말했듯 jxls는 poi를 사용하기 때문에 poi도 받아주어야 한다.

2. 엑셀만들고 다운로드 받는 클래스

package 패키지 경로;
 
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Map;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
 
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
 
// MakeExcel이라는 클래스를 만들고 그 안에 downliad라는 메소드를 생성한다.
public class MakeExcel {
    public void download(HttpServletRequest request, HttpServletResponse response,
                    Map<String, Object> bean, String fileName, String templateFile, String string)
                    throws ParsePropertyException, InvalidFormatException {
 
        // 받아오는 매개변수 bean는 디비에서 뽑아온 데이터
        // fileName 은 다운로드 받을때 지정되는 파일명
        // templateFile 는 템플릿 엑셀 파일명이다.
        
        // tempPath는 템플릿 엑셀파일이 들어가는 경로를 넣어 준다.
        String tempPath = request.getSession().getServletContext().getRealPath("/WEB-INF/excel");
        
 
        // 별도로 다운로드 만들기 귀찮으까 이런식으로 만들어서 바로 엑셀 생성후 다운 받게 
        try {
 
            InputStream is = new BufferedInputStream(new FileInputStream(tempPath + "\\" + templateFile));
            XLSTransformer xls = new XLSTransformer();
            
            
            Workbook workbook = xls.transformXLS(is, bean);
            
            
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xlsx\"");
            
            OutputStream os = response.getOutputStream();
            
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3. 엑셀 다운로드 요청 및 데이터가져오는 메소드

 @RequestMapping(value = "/downExcel.do")
        public void listExcel(HttpServletRequest request,
                HttpServletResponse response, VO vo,
                ModelMap modelMap) throws Exception, Exception {
 
            
            // 그냥 평소에 마이바티스에서 데이터 뽑는 방법으로 데이터를 가져온다.
            List<VO> dataList = trs01Service.selectGroupList(groupVO);
            
            
            // 받은 데이터를 맵에 담는다.
            Map<String, Object> beans = new HashMap<String, Object>();
            beans.put("dataList", dataList);
            
            // 엑셀 다운로드 메소드가 담겨 있는 객체
            MakeExcel me = new MakeExcel();
 
            me.download(request, response, beans, "다운받을때지정될 엑셀파일명", "엑셀템플릿 파일 명.xlsx", "무시해도됨");
        }

이렇게 하면 자바에서 할 건 끝났다.

막 예전에 POI에서 셀위치 지정하고 했던것들은 하지 않아도 된다!!

저 템플릿위치에 아래 4번에서 만든 템플릿 엑셀파일을 넣어놓고

3번에서 만든 메소드를 호출하면 해당 데이터를 담은 엑셀을 다운로드 받게 된다.

정말 너무너무 간단하다.

4. 템플릿 엑셀 예제

위 그림과 같이 JSTL 쓰던것 처럼 적어주면 된다.

저렇게 적으면 콜렉션일 경우 자동으로 반복까지 해준다.

그냥 저렇게만 만들어 두면 저 위치부터 아래로 쭉쭉 엑셀데이터가 입력 되는 것이다.

엄청나다.

5. 셀병합, merge

이런식으로 셀병합을 하고 이름옆에 3줄로 다른 정보를 반복시켜야 하는 경우가 있다.

그냥 4번에 있는식으로 템플릿만들어서 적으면 에러난다.

java.lang.NullPointerException

at net.sf.jxls.util.Util.shiftColumnUp(Util.java:335) ~[jxls-core-1.0.6.jar:?]

at net.sf.jxls.util.Util.shiftUncoupledCellsUp(Util.java:315) ~[jxls-core-1.0.6.jar:?]

at net.sf.jxls.util.Util.duplicateRow(Util.java:246) ~[jxls-core-1.0.6.jar:?]

at net.sf.jxls.controller.SheetTransformationControllerImpl.duplicateRow(SheetTransformationControllerImpl.java:140) ~[jxls-core-1.0.6.jar:?]

at net.sf.jxls.transformer.CollectionRowTransformer.processRowCollections(CollectionRowTransformer.java:106) ~[jxls-core-1.0.6.jar:?]

at net.sf.jxls.transformer.CollectionRowTransformer.transform(CollectionRowTransformer.java:66) ~[jxls-core-1.0.6.jar:?]

이런 에러가 막 뿜어져 나올 것이다.

왜 에러가 나느냐..

템플릿을 잘못 만들었기 때문이다.

이걸 보는 여러분들도 템플릿으로 이것저것 에러뿜어가면서 테스트 하다보면

알수 있을 이유이다

그냥 해결방법만 간단하게 말하면

이런식으로 셀병합한 위치에 들어가는 데이터 옆에 //:숫자  를 넣어 주면 된다.

만약 데이터가 3칸을 병합해서 세줄단위로 넘어가야 하는 경우

0 , 1, 2  로 세서 숫자 2를 넣어주면 된다.

머지를 하지 않더라도

2줄단위로 반복하거나 3줄단위로 반복하게 하는 경우에도 동일하게 적용하면 된다.

JXLS의 가장 좋은 장점은 데이터 넣는곳 템플릿에 스타일 넣어주면 그 스타일도 같이 반복이 된다.

아주 훌륭하다