엑셀템플릿(Jxls)를 활용한 엑셀 다운로드 구현하기

1. 메이븐 POM 설정

---

    <!--JXLS  -->
    <dependency>
        <groupId>net.sf.jxls</groupId>
        <artifactId>jxls-core</artifactId>
        <version>1.0.6</version>
    </dependency> 

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls</artifactId>
        <version>2.3.0</version>
    </dependency>

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls-poi</artifactId>
        <version>1.0.9</version>
    </dependency>

    <dependency>
        <groupId>org.jxls</groupId>
        <artifactId>jxls-jexcel</artifactId>
        <version>1.0.6</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>

2. 함수코드

---

package com.sample.vue.common.utils;

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 org.apache.poi.ss.usermodel.Workbook;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

public class ExcelUtil {

public void download(HttpServletRequest request, HttpServletResponse response, Map<String , Object> beans, String filename, String templateFile) throws org.apache.poi.openxml4j.exceptions.InvalidFormatException {
    String tempPath = request.getSession().getServletContext().getRealPath("/WEB-INF/template") ;

    try {
        InputStream is = new BufferedInputStream(new FileInputStream(tempPath + "\\" + templateFile));
        XLSTransformer transformer = new XLSTransformer();
        Workbook resultWorkbook = transformer.transformXLS(is, beans);
        response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + ".xlsx\"");
        OutputStream os = response.getOutputStream();
        resultWorkbook.write(os);

    } catch (ParsePropertyException | IOException ex) {
        ex.printStackTrace();
    }
}

}

3. 사용방법

---

@RequestMapping(value = "/excelList")
public void excelList(HttpServletRequest request 
                          ,HttpServletResponse response
                          ,@RequestParam Map<String, Object> param
        ) throws InvalidFormatException {

    if( !AuthUtil.isAdmin()) {
        param.put("account", AuthUtil.getCurrentUserAccount());
    }
    List<Map<String, String>> slotEntityList = slotService.selectSlotExcelList(param);

    Map<String , Object> beans = new HashMap<String , Object>();
    beans.put("list", slotEntityList);

    //FIX: 한글 NAME 안되는 현상 해결하기
    ExcelUtil excelUtil = new ExcelUtil();
    excelUtil.download(request, response, beans, "SLOT_LIST",  "EL_0001.xlsx");
}

4. 폴더구조

---

5. 엑셀파일

---

EL_0001.xlsx