[egov] 전자정부프레임워크 XSSFWorkbook 으로 엑셀 다운받기

[egov] 전자정부프레임워크 XSSFWorkbook 으로 엑셀 다운받기

 

 

전자정부 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);
}

}

 

 

출처 : http://www.incree.com/tc/incree/entry/%EC%A0%84%EC%9E%90%EC%A0%95%EB%B6%80%ED%94%84%EB%A0%88%EC%9E%84%EC%9B%8C%ED%81%AC-XSSFWorkbook-%EC%9C%BC%EB%A1%9C-%EC%97%91%EC%85%80-%EB%8B%A4%EC%9A%B4%EB%B0%9B%EA%B8%B0