전자정부 3.6.0에 포함된 spring 에서 기본으로 제공하는 엑셀 다운로드는

HSSFWorkbook 을 사용하기 때문에 65536 레코드가 넘어가면 에러가 발생한다.


XSSFWorkbook 형태로 다운로드 받기 위해서는 AbstractView를 직접 구현해 주어야 한다.



1. pom.xml에 poi, poi-ooxml dependency 추가


<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>


2. AbstractView 구현

package com.incree.excel;

import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

@Component("listExcelView")
public class ListExcelView extends AbstractView  {
private static final String CONTENT_TYPE = "application/vnd.ms-excel";

public ListExcelView() {
setContentType(CONTENT_TYPE);
}

  /**
   * 스프링 AbstractExcelView 에 있는 함수 편리하게 쓰기 위해 추가
   */
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
XSSFRow sheetRow = sheet.getRow(row);
if (sheetRow == null) {
sheetRow = sheet.createRow(row);
}
XSSFCell cell = sheetRow.getCell(col);
if (cell == null) {
cell = sheetRow.createCell(col);
}
return cell;
}

/**
   * 스프링 AbstractExcelView 에 있는 함수 편리하게 쓰기 위해 추가
   */
protected void setText(XSSFCell cell, String text) {
cell.setCellType(CellType.STRING); // 스프링엔 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 로 돼 있지만 deprecated 됐으므로 수정
cell.setCellValue(text);
}

@Override
protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = (XSSFSheet) wb.createSheet("쉬트이름");

        XSSFCell cell = null;

        XSSFDataFormat format = wb.createDataFormat();
        XSSFCellStyle style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,###"));

int cellStartIndex = 2;

// set header information
setText(getCell(sheet, 1, 0), "셀내용");
setText(getCell(sheet, 2, 1), "셀내용2");
setText(getCell(sheet, 3, 2), "셀내용3");

               // List resultList =  (List) model.get("resultList");

..............
..............
..............


ServletOutputStream out = response.getOutputStream();
wb.write(out);

        if (out != null) out.close();
        if (wb != null) wb.close();
}
}

3. controller 구현

package com.incree.excel;

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

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class ListController {
@RequestMapping("/listExcelDownload.do")
public ModelAndView listExcelDownload(Model model, HttpServletResponse response) throws Exception{
// List resultList = getList();

// Map<String, Object> map = new HashMap<String, Object>();
// map.put("resultList", resultList);

response.setHeader("Content-Disposition", "attachment; fileName=\"list_excel.xlsx\";");
response.setHeader("Content-Transfer-Encoding", "binary");

return new ModelAndView("listExcelView", map);
}

}

Posted by incree

2017/12/19 14:51 2017/12/19 14:51
, ,
Response
0 Trackbacks , 0 Comments
RSS :
http://www.incree.com/tc/incree/rss/response/384

Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다

« Previous : 1 : ... 10 : 11 : 12 : 13 : 14 : 15 : 16 : 17 : 18 : ... 147 : Next »

Notices

Archives

Authors

  1. incree

Recent Trackbacks

Calendar

«   2018/11   »
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  

Site Stats

Total hits:
497750
Today:
27
Yesterday:
263