티스토리 뷰

Web Development/Spring

Excel Download ( ExcelView)

dev ms 2015. 6. 26. 14:03
반응형



출처 : http://snoopy81.tistory.com/327

☆ 전체소스 :  spring304.zip

 

■ 스프링은 엑셀 다운로드를 생성할 수 있도록 다음의 두 View 클래스를 제공

  ● AbstractExcelView : POI API를 이용하여 엑셀 응답을 생성

    ▶ 별도의 LIB 필요 ( poi-3.8-20120326.jar) : http://poi.apache.org/

  ● AbstractJExcelView : JExcel API를 이용하여 엑셀 응답을 생성

 

 

FileDownloadController.java

package sp.mvc.controller;

import java.util.ArrayList;
import java.util.List;

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

 

@Controller
public class FileDownloadController {


 @RequestMapping(value="/filedown.sp")
 public ModelAndView fileDownload(){
  System.out.println("----- FileDownloadController.fileDownload() -----");
  
  ModelAndView modelAndView = new ModelAndView("filedownload");
   
  return modelAndView;
 }
 
 @RequestMapping("/exceldown.sp")
 public String excelDownload(Model model){
  System.out.println("----- FileDownloadController.excelDownload() -----");
  
  List<String> list = new ArrayList<String>();
  list.add("사이트 관리");
  list.add("관리자 관리");
  list.add("공통 코드 관리");
  list.add("접속 이력");
  
  model.addAttribute("menuList", list);
  
  return "excelDownload";
 }
}

 

ExcelDownloadView.java

package sp.mvc.view;

import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

 

public class ExcelDownloadView extends AbstractExcelView {

 

 @SuppressWarnings("unchecked")
 @Override
 protected void buildExcelDocument(Map<String, Object> model,
   HSSFWorkbook workbook, HttpServletRequest req, HttpServletResponse res)
   throws Exception {
  // TODO Auto-generated method stub
  System.out.println("---- ExcelDownloadView.buildExcelDocument() ----");
  
  String userAgent = req.getHeader("User-Agent");
  String fileName = "test.xls";
  
  if(userAgent.indexOf("MSIE") > -1){
   fileName = URLEncoder.encode(fileName, "utf-8");
  }else{
   fileName = new String(fileName.getBytes("utf-8"), "iso-8859-1");
  }
  
  res.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\";");
  res.setHeader("Content-Transfer-Encoding", "binary");
    
  HSSFSheet sheet = createFirstSheet(workbook);
  createColumnLabel(sheet);
  
  List<String> menuList = (List<String>)model.get("menuList");

  for(int i=0; i <= menuList.size()-1; i++){
   createPageRow(sheet, menuList, i);
  }
 }
  
 private HSSFSheet createFirstSheet(HSSFWorkbook workbook){
  HSSFSheet sheet = workbook.createSheet();
  workbook.setSheetName(0, "테스트");
  sheet.setColumnWidth(1, 256*30);
  return sheet;
 }
 
 private void createColumnLabel(HSSFSheet sheet){
  HSSFRow firstRow = sheet.createRow(0);
  
  HSSFCell cell = firstRow.createCell(0);
  cell.setCellValue("순위");
  
  cell = firstRow.createCell(1);
  cell.setCellValue("페이지");
 }

 

 private void createPageRow(HSSFSheet sheet, List<String> menuList, int rowNum){
  HSSFRow row = sheet.createRow(rowNum + 1);
  
  HSSFCell cell = row.createCell(0);
  cell.setCellValue(rowNum + 1);
  
  cell = row.createCell(1);
  cell.setCellValue(menuList.get(rowNum)); 
 }
}

 

main_config.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:context="http://www.springframework.org/schema/context" 
 xmlns:p="http://www.springframework.org/schema/p"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd">

 

 <context:annotation-config/>
 <context:component-scan base-package="sp.mvc.controller"/>
 
 <!-- bean class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping" p:alwaysUseFullPath="true"/ -->
 <!-- bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" p:alwaysUseFullPath="true"/ --> 
 
  
 <!-- ViewResolver 설정 -->
 <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1"/>
 
 <bean id="internalResourceViewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"
  p:order="2" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp"></bean>


 
 <!-- 각종 bean 설정 -->
 <bean id="excelDownload" class="sp.mvc.view.ExcelDownloadView"></bean>
 
</beans>

 

 

결과

요청 URL

   http://localhost:8090/spring304/exceldown.sp

 

----- FileDownloadController.excelDownload() -----
---- ExcelDownloadView.buildExcelDocument() ----

 

 

 

 

 

[참고자료] Spring 3.0 프로그래밍-최범균



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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
------------ excel view 시작 -------------------

@Component
public class UserListExcelView extends AbstractExcelView{

 @Override
 protected void buildExcelDocument(Map<String, Object> model,
   HSSFWorkbook wb, HttpServletRequest request,
   HttpServletResponse response) throws Exception {
  // TODO Auto-generated method stub
  
  HSSFSheet sheet = wb.createSheet();
  int rowIdx = 0;


  HSSFCellStyle titleStyle = wb.createCellStyle();
  titleStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
  titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  
  // 타이틀 설정
  String[] titleList = { "아이디", "이름", "EMAIL","연락처","등록일","최종접속"};
 
  HSSFRow titleRow = sheet.createRow(rowIdx++);
  for (int i = 0; i < titleList.length; i++) {
   HSSFCell cell = titleRow.createCell(i);
   cell.setCellValue(new HSSFRichTextString(titleList[i]));
   cell.setCellStyle(titleStyle);
  }

  // 엑셀 서식 설정
  HSSFCellStyle numStyle = wb.createCellStyle();
  
  numStyle.setDataFormat(wb.createDataFormat().getFormat("#,##0"));

  HSSFCellStyle percentStyle = wb.createCellStyle();
  percentStyle.setDataFormat(wb.createDataFormat().getFormat("0%"));

  HSSFCellStyle dateStyle = wb.createCellStyle();
  dateStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy/mm/dd"));

  SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

  // 데이터 추가
  List<AdminVO> list = (List)model.get("list");
  
  for (AdminVO vo : list) {
   HSSFRow dataRow = sheet.createRow(rowIdx++);
   
    HSSFCell idxCell = dataRow.createCell(0);
    idxCell.setCellValue(new HSSFRichTextString(vo.getAm_id()));
    
    HSSFCell nameCell = dataRow.createCell(1);
    
    nameCell.setCellValue(new HSSFRichTextString(vo.getAm_name()));
    
    HSSFCell statusCell = dataRow.createCell(2);
    statusCell.setCellValue(new HSSFRichTextString(vo.getAm_status()));

    HSSFCell emailCell = dataRow.createCell(3);
    emailCell.setCellValue(new HSSFRichTextString(vo.getAm_email()));
    
    HSSFCell phoneCell = dataRow.createCell(4);
    phoneCell.setCellValue(new HSSFRichTextString(vo.getAm_phone()));
    
    HSSFCell curdateCell = dataRow.createCell(5);
    curdateCell.setCellValue(new HSSFRichTextString(vo.getCurdate()));
  

  }

  for (int i = 0; i < titleList.length; i++) {
   sheet.autoSizeColumn((short)i);
  }

  // 파일 다운로드 시작
  String fileInfo = String.format("attachment; filename=\"" + createFileName() + "\"");
  response.setHeader("Content-Disposition", fileInfo);
  
 }
 
 private String createFileName() {
  SimpleDateFormat fileFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
  return new StringBuilder("User")
    .append("-").append(fileFormat.format(new Date())).append(".xls").toString();
 }

}
------------ excel view 끝 -------------------

------------ controller ----------------------

public ModelAndView excelDownLoad (HttpServletRequest request, HttpServletResponse response ,
     ModelMap modelMap,
      @ModelAttribute AdminVO vo,
      UserListExcelView excelView)

....

    resultMap = adminMemeberService.selAdminMemberList(vo);
    list = (List<AdminVO>)resultMap.get("list");
    
    mav.addObject("list",list);
    mav.setView(excelView);


------------ controller 끝----------------------


ModelAndView 에 addObject 한 객체 값은

setView에서 excelView 로 경로를 잡아주면


 protected void buildExcelDocument(Map<String, Object> model,
 HSSFWorkbook wb, HttpServletRequest request,
 HttpServletResponse response) throws Exception 


에서 Map<String, Object> model 에서 get으로 해당 객체 변수명해서 받아올 수 있다.


대신 poi를 사용하기 때문에 Maven에서는

  <!--  Poi  -->
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.9</version>
  </dependency>  
   

선언해 주시고, 그외에서는 

jar파일을 받아서 셋팅해주면 된다.




반응형
저작자표시

'Web Development > Spring' 카테고리의 다른 글

Spring AOP 예제 및 정리  (2) 2016.04.19
CSRF prevention 관련 설정 내용 입니다.  (0) 2016.01.01
Spring 3.0 사용중 Controller 에서 @ModelAttribute 일때 이유 없이 진행 안될 때  (0) 2015.06.13
ibatis insert 후 nullPoint 에러는 반환값이 없기 때문 ㅡㅡ  (0) 2015.06.13
스프링에서 프로퍼티 파일을 사용  (0) 2015.06.13