Spring excel download xlsx (엑셀 2007 이후버전)

엑셀 2007 이후 버전은 확장자가 xlsx 로 생성 됩니다.

Spring에서 엑셀 다운로드 xls (2003버전) 예제가 많은데 xlsx는 깔끔하게 정리 된 게 없어서 블로그에 적어 봅니다.

많은 도움이 되면 좋겠습니다.

일단 프로젝트 구조는 아래와 같습니다. (DB연결은 http://huskdoll.tistory.com/192 를 참고하세요.)

붉은 상자로 표시한 부분만 확인해 주세요. (wedding 부분은 다른 테스트입니다. 무시 하세요.)

테이블에서 데이터를 읽어와서 excel 파일로 생성하는 예제 입니다.

테이블 정보는 다음과 같습니다.

[books]

[books_detail]

1. 일단 pom.xml에 poi 라이브러리를 추가해주세요.

<!-- http://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10.1</version>
</dependency>

2. servlet-context.xml 에 뷰 이름과 동일한 이름의 빈 객체를 뷰 객체로 사용하기 위해 BeanNameViewResolver 를 설정해주고 excelView bean도 추가해줍니다.

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
 
    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
    
    <!-- Enables the Spring MVC @Controller programming model -->
    <annotation-driven />
 
    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
    <resources mapping="/resources/**" location="/resources/" />
 
    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views/" />
        <beans:property name="suffix" value=".jsp" />
        <beans:property name="order" value="2" />
    </beans:bean>
    
    <beans:bean class="org.springframework.web.servlet.view.BeanNameViewResolver">
        <beans:property name="order" value="1" />
    </beans:bean>
    
    <beans:bean class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver"> 
        <beans:property name="order" value="0"/> 
        <beans:property name="defaultErrorView" value="error"/> 
        <beans:property name="exceptionMappings"> 
            <beans:props> 
                <beans:prop key="RuntimeException">error</beans:prop> 
            </beans:props> 
        </beans:property>
    </beans:bean>
    
    <beans:bean name="excelView" class="kr.co.myapp.util.ExcelView"/>
    
    <context:component-scan base-package="kr.co.myapp" />
    
</beans:beans>

3. ExcelController 를 작성해주세요. (자세한 설명은 주석을 확인하세요.)

package kr.co.myapp.excel.controller;
 
import java.util.List;
import java.util.Map;
 
import javax.servlet.http.HttpServletResponse;
 
import kr.co.myapp.excel.service.ExcelService;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
 
@Controller
public class ExcelController {
 
    @Autowired
    ExcelService excelService;
 
    @RequestMapping("/excelDownload")
    public String excelTransform(@RequestParam Map<String, Object> paramMap, Map<String, Object> ModelMap, HttpServletResponse response) throws Exception{
        
        /*
        excelDownload?target=books&id=b2
        
        위와 같은 형식으로 파라미터가 온다고 가정
        target에 따라서 가져올 리스트를 선택
         */
        String target = paramMap.get("target").toString();
        
        response.setHeader("Content-disposition", "attachment; filename=" + target + ".xlsx"); //target명을 파일명으로 작성
 
        //엑셀에 작성할 리스트를 가져온다.
        List<Object> excelList= excelService.getAllObjects(target, paramMap); 
        
        //ExcelView(kr.co.myapp.util.ExcelView) 에 넘겨줄 값 셋팅
        ModelMap.put("excelList", excelList); 
        ModelMap.put("target", target);
 
        return "excelView"; //servlet-context.xml 에서 name이 excelView(kr.co.myapp.util.ExcelView)인것 호출
 
    }
 
}

4. ExcelService 를 작성해주세요.

package kr.co.myapp.excel.service;
 
import java.util.List;
import java.util.Map;
 
 
public interface ExcelService {
 
    public List<Object> getAllObjects(String target, Map<String, Object> searchMap);
 
}

5. ExcelServiceImpl 를 작성해주세요.

package kr.co.myapp.excel.service.impl;
 
import java.util.List;
import java.util.Map;
 
import javax.annotation.Resource;
 
import kr.co.myapp.excel.dao.ExcelDao;
import kr.co.myapp.excel.service.ExcelService;
 
import org.springframework.stereotype.Service;
 
@Service(value = "excelService")
public class ExcelServiceImpl implements ExcelService {
 
    @Resource(name = "excelDao")
    private ExcelDao excelDao;
 
 
    @Override
    public List<Object> getAllObjects(String target, Map<String, Object> searchMap){
        
        //controller에서 넘어온 target에 따라서 dao 실행을 구분
        if(target.equals("books")){
            return excelDao.getBooks(searchMap);//검색조건 searchMap를 넘겨줌
        }
 
        if(target.equals("booksDetail")){
            return excelDao.getBooksDetail(searchMap);//검색조건 searchMap를 넘겨줌
        }
 
        return null;
    }
 
}

6. ExcelDao 를 작성해주세요.

package kr.co.myapp.excel.dao;
 
import java.util.List;
import java.util.Map;
 
import kr.co.myapp.wedding.domain.Wedding;
 
public interface ExcelDao {
    
    public List<Object> getBooks(Map<String, Object> searchMap);
    
    public List<Object> getBooksDetail(Map<String, Object> searchMap);
    
}

7. ExcelDaoImpl 를 작성해주세요.

package kr.co.myapp.excel.dao.impl;
 
import java.util.List;
import java.util.Map;
 
import kr.co.myapp.excel.dao.ExcelDao;
import kr.co.myapp.util.PageableUtil;
 
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
 
@Repository("excelDao")
public class ExcelDaoImpl implements ExcelDao{
 
    @Autowired
    private SqlSession sqlSession;
 
    public void setSqlSession(SqlSession sqlSession){
        this.sqlSession = sqlSession;
    }
 
    @Override
    public List<Object> getBooks(Map<String, Object> searchMap){
        return sqlSession.selectList("selectBooks", searchMap);
    }
 
    @Override
    public List<Object> getBooksDetail(Map<String, Object> searchMap){
        return sqlSession.selectList("selectBooksDetail", searchMap);
    }
 
}

8. books.xml 를 작성해주세요.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="kr.co.myapp.excel.dao.ExcelDao"><!-- namespace에서 interface로 정의된 DAO를 정확히 명시해주어야 한다 -->
    <select id="selectBooks" resultType="kr.co.myapp.excel.domain.Books">
        select
            id,
            name,
            writer,
            price,
            genre,
            publisher,
            discount_yn,
            discount_rate,
            cnt,
            sale_date
        from
            books
        <where>
            <if test='id != null and !id.equals("")'>
                and id = #{id}
            </if>
        </where>
    </select>
    <select id="selectBooksDetail" resultType="kr.co.myapp.excel.domain.BooksDetail">
        select
            detail_id,
            id,
            publish_date,
            best_seller_yn
        from
            books_detail
        <where>
            <if test='detail_id != null and !detail_id.equals("")'>
                and detail_id = #{detail_id}
            </if>
        </where>
    </select>
</mapper>

9. AbstractExcelPOIView 를 작성해주세요.

package kr.co.myapp.util;
 
import java.util.Map;
 
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.servlet.view.AbstractView;
 
public abstract class AbstractExcelPOIView extends AbstractView {
    // https://jira.spring.io/browse/SPR-6898
 
    /** The content type for an Excel response */
    private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 
    /**
     * Default Constructor. Sets the content type of the view for excel files.
     */
    public AbstractExcelPOIView() {
    }
 
    @Override
    protected boolean generatesDownloadContent() {
        return true;
    }
 
    /**
     * Renders the Excel view, given the specified model.
     */
    @Override
    protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
 
        Workbook workbook = createWorkbook();
 
        setContentType(CONTENT_TYPE_XLSX);
 
        buildExcelDocument(model, workbook, request, response);
 
        // Set the content type.
        response.setContentType(getContentType());
 
        // Flush byte array to servlet output stream.
        ServletOutputStream out = response.getOutputStream();
        out.flush();
        workbook.write(out);
        out.flush();
        if (workbook instanceof SXSSFWorkbook) {
            ((SXSSFWorkbook) workbook).dispose();
        }
 
        // Don't close the stream - we didn't open it, so let the container
        // handle it.
    }
 
    /**
     * Subclasses must implement this method to create an Excel Workbook.
     * HSSFWorkbook, XSSFWorkbook and SXSSFWorkbook are all possible formats.
     */
    protected abstract Workbook createWorkbook();
 
    /**
     * Subclasses must implement this method to create an Excel HSSFWorkbook
     * document, given the model.
     * 
     * @param model
     *            the model Map
     * @param workbook
     *            the Excel workbook to complete
     * @param request
     *            in case we need locale etc. Shouldn't look at attributes.
     * @param response
     *            in case we need to set cookies. Shouldn't write to it.
     */
    protected abstract void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception;
}

10. ExcelView 를 작성해주세요.

package kr.co.myapp.util;
 
import java.util.List;
import java.util.Map;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import kr.co.myapp.excel.domain.Books;
import kr.co.myapp.excel.domain.BooksDetail;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ExcelView extends AbstractExcelPOIView {
 
    @SuppressWarnings("unchecked")
    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
 
        String target = model.get("target").toString();
 
        //target에 따라서 엑셀 문서 작성을 분기한다.
        if(target.equals("books")){
            //Object로 넘어온 값을 각 Model에 맞게 형변환 해준다.
            List<Books> listBooks = (List<Books>) model.get("excelList");
 
            //Sheet 생성
            Sheet sheet = workbook.createSheet(target);
            Row row = null;
            int rowCount = 0;
            int cellCount = 0;
 
            // 제목 Cell 생성
            row = sheet.createRow(rowCount++);
 
            row.createCell(cellCount++).setCellValue("id");
            row.createCell(cellCount++).setCellValue("name");
            row.createCell(cellCount++).setCellValue("writer");
 
            // 데이터 Cell 생성
            for (Books book : listBooks) {
                row = sheet.createRow(rowCount++);
                cellCount = 0;
                row.createCell(cellCount++).setCellValue(book.getId()); //데이터를 가져와 입력
                row.createCell(cellCount++).setCellValue(book.getName());
                row.createCell(cellCount++).setCellValue(book.getWriter());
 
            }
 
        }
 
        if(target.equals("booksDetail")){
 
            List<BooksDetail> booksDetailList = (List<BooksDetail>) model.get("excelList");
 
            //Sheet 생성
            Sheet sheet = workbook.createSheet(target);
            Row row = null;
            int rowCount = 0;
            int cellCount = 0;
 
            // 제목 Cell 생성
            row = sheet.createRow(rowCount++);
 
            row.createCell(cellCount++).setCellValue("detail_id");
            row.createCell(cellCount++).setCellValue("id");
            row.createCell(cellCount++).setCellValue("publish_date");
            row.createCell(cellCount++).setCellValue("best_seller_yn");
 
            // 데이터 Cell 생성
            for (BooksDetail bookDetail : booksDetailList) {
                row = sheet.createRow(rowCount++);
                cellCount = 0;
                row.createCell(cellCount++).setCellValue(bookDetail.getDetail_id());
                row.createCell(cellCount++).setCellValue(bookDetail.getId());
                row.createCell(cellCount++).setCellValue(bookDetail.getPublish_date());
                row.createCell(cellCount++).setCellValue(bookDetail.getBest_seller_yn());
 
            }
 
        }
 
    }
 
    @Override
    protected Workbook createWorkbook() {
        return new XSSFWorkbook();
    }
 
}

위 프로젝트 구조와 주석을 잘 보시고 따라하시면 될 듯 합니다.

url호출 예제 : http://localhost:8080/excelDownload?target=books&id=b1 (URL은 각각 설정에 맞게 변경하세요. jsp 페이지에서 url을 호출 하도록 링크를 걸면 됩니다.)

[결과]