web

spring -db 에서 데이터 불러와서 엑셀 다운로드

늉_늉 2020. 12. 4. 21:46

view

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  <form id = "excelDownloadForm" name = "excelDownloadForm" method="post" enctype="multipart/form-data" action ="excelOutput" >
      <input type="hidden" id="excelOutput" name="excelOuput">
      <span class="btn_pack medium icon"><span class="excel"></span><a href="#" onclick="excelDownload()">엑셀다운로드</a></span>
   </form>    
             
 
 
 
 
 
 
<script>
 
                  function excelDownload(){
                    var f = document.excelDownloadForm;
                    f.action = "excelOutput";
                    f.submit();
                }
 </script>
 cs

 

 

xml 경로에

bean을 추가해주고

1
   <bean id="fileDownloadView" class="aaa.bbb.ccc.utils.DownExcelView"/>
 cs

 

DownExcelView를 만들어 준다.

 

 

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
142
143
144
145
146
147
148
149
150
151
152
153
154
 
 
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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.web.servlet.view.document.AbstractExcelView;
 
 
public class DownExcelView extends AbstractExcelView{
 
    @SuppressWarnings("unchecked")
    @Override
    protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        
        
        String userAgent = request.getHeader("User-Agent");
        String fileName ="엑셀다운로드.xls";
        
        
        if(userAgent.indexOf("MSIE") > -1) {
            fileName = URLEncoder.encode(fileName,"utf-8");
        }else {
            fileName = new String(fileName.getBytes("utf-8"),"iso-8859-1");
        }
        
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\";");
        response.setHeader("Content-Transfer-Encoding", "binary");
        
        
        //워크북 생성
//                workbook = new HSSFWorkbook();
                //워크시트 생성
                HSSFSheet sheet = workbook.createSheet();
                
                
                
                workbook.setSheetName(0, "엑셀다운로드");
                sheet.setColumnWidth(1, 256*30);
                
                
                //스타일
                HSSFPalette palette = workbook.getCustomPalette();
                HSSFColor co = palette.findSimilarColor(153, 204, 255);
                HSSFCellStyle style = workbook.createCellStyle(); 
                style.setFillForegroundColor(co.getIndex());
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                
                
                sheet.setColumnWidth(3, 4000);
                sheet.setColumnWidth(4, 8000);
                sheet.setColumnWidth(5, 12000);
                sheet.setColumnWidth(6, 8000);
                
                //행 생성
                HSSFRow row = sheet.createRow(0);
                //셀 생성
                HSSFCell cell;
                
                
                //헤더 정보 구성
                cell = row.createCell(0);
                cell.setCellValue("no"); //no
                cell.setCellStyle(style);
 
                cell = row.createCell(1); 
                cell.setCellValue("국가");
                cell.setCellStyle(style);
 
                cell = row.createCell(2);
                cell.setCellValue("구분");
                cell.setCellStyle(style);
 
                cell = row.createCell(3);
                cell.setCellValue("학년");
                cell.setCellStyle(style);
 
                cell = row.createCell(4);
                cell.setCellValue("주소");
                cell.setCellStyle(style);
 
                cell = row.createCell(5);
                cell.setCellValue("EMAIL");
                cell.setCellStyle(style);
 
                cell = row.createCell(6);
                cell.setCellValue("학부");
                cell.setCellStyle(style);
 
                cell = row.createCell(7);
                cell.setCellValue("재학유무");
                cell.setCellStyle(style);
        
        
        
                
                List<StatisticInfo> xlist = (List<StatisticInfo>)model.get("statisticList");
                
               StatisticInfo vo = new StatisticInfo+();
                for(int rowIdx = 0; rowIdx < xlist.size(); rowIdx++) {
                    vo = xlist.get(rowIdx);
                    
                    //행 생성
                    row = sheet.createRow(rowIdx+1);
                    
                    cell = row.createCell(0);
                    cell.setCellValue(vo.getNo());
                    
                    cell = row.createCell(1);
                    cell.setCellValue(vo.getCountry()); 
                    
                    cell = row.createCell(2);
                    cell.setCellValue(vo.getSection());//구분
                    
                    cell = row.createCell(3);
                    cell.setCellValue(vo.getGrade()); 
                    
                    cell = row.createCell(4);
                    cell.setCellValue(vo.getAddress());
                    
                    cell = row.createCell(5);
                    cell.setCellValue(vo.getEmail());
                    
                    cell = row.createCell(6);
                    cell.setCellValue(vo.getDepartment());
                    
                    cell = row.createCell(7);
                    cell.setCellValue(vo.getUseYN());
                }
                
    
    
    }
    
    
 
        
        
        
        
 
}
 
 cs

 

 

 

 

 

 

controller

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@RequestMapping(value= "/excelOutput",method= RequestMethod.POST)
    public String excelDownload(Model mv) {
        
        List<StatisticInfo> list = new ArrayList<StatisticInfo>();
        
        list = infoservice.getFormatStatisticListForExcel();
        
        mv.addAttribute("statisticList",list);
        return "fileDownloadView";
        
        
        
    }
    
 cs

 

 

 

 

 

service

 

 

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
import java.util.List;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
//엑셀 다운로드 서비스
@Service
public class StatisticInfoService {
 
    private static final Logger log = LoggerFactory.getLogger(StatisticInfoService.class);
    
    @Autowired
    StatisticInfoMapper infoMapper;
    
 
 
    public void excelUpload(StatisticInfo vo) {
            vo.setSubject(vo.getSubject().replaceAll(" ", "")); //공백제거
            infoMapper.excelUpload(vo);
            
            
    }
 
    
}
 
 cs

 

 

 

 

 

mapper

 

 

1
2
3
4
public void excelUpload(StatisticInfo vo) {    
        sqlSession.update(dbNameSpace + "excelUpload", vo); 
    }
 
 cs

 

 

 

 

 

xml

 

 

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
<update id="dgrant_excelUpload" parameterType="com.aaa.bbb.model.StatisticInfo">
    /* [dgrant_excelUpload] 엑셀파일 입력 및 수정 */
    MERGE INTO TB_STATISTICS_INFO I
    USING DUAL
    ON (I.NO=#{no})
    WHEN MATCHED THEN
        UPDATE SET 
                I.COUNTRYCODE =(SELECT COUNTRYCODE FROM TB_STATICS_COUNTRY tsc WHERE tsc.COUNTRYNAME=#{country}),
                I.SECTION =#{section},
                I.SUBJECTCODE =(SELECT SUBJECTCODE FROM TB_STATICS_SUBJECT tss WHERE tss.SUBJECTNAME=#{subject}),
                I.ORIGINTITLE =#{originTitle},
                I.EMAIL=#{email},
                I.ADDRESS=#{address},
                I.USEYN =#{useYN}
                WHERE NO = #{no}
                WHEN NOT MATCHED THEN
                INSERT
                (
                    I.NO,
                    I.COUNTRYCODE,
                    I.SECTION,
                    I.SUBJECTCODE,
                    I.ORIGINTITLE,
                    I.EMAIL,
                    I.ADDRESS,
                    I.USEYN
                )
                VALUES
                (
                    #{no},
                    (SELECT COUNTRYCODE FROM TB_STATICS_COUNTRY tsc WHERE tsc.COUNTRYNAME=#{country}),
                    #{section},
                    (SELECT SUBJECTCODE FROM TB_STATICS_SUBJECT tss WHERE tss.SUBJECTNAME=#{subject}),
                    #{originTitle},
                    #{email},
                    #{ADDRESS},
                    'Y'
                )
    </update>
 cs

 

 

 

 

pom.xml

 

 

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
       <!-- POI 추가 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.12</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.12</version>
        </dependency>
 
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>
 
    <!-- XLST Transformer -->
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
        </dependency>
 cs

 

 

 

 

저작자표시 (새창열림)

'web' 카테고리의 다른 글

코드  (0) 2024.10.16
소스트리 깃랩 클론 꺼짐현상  (0) 2021.08.13
spring - 엑셀파일 업로드db에 저장 poi 3.12  (0) 2020.11.22
Part3 기본적인 웹게시물 관리 - 코드로배우는 스프링 웹프로젝트  (0) 2020.10.29
코드로 배우는 스프링 웹프로젝트 - 파일 업로드 처리  (0) 2020.10.28

'web'의 다른글

  • 현재글spring -db 에서 데이터 불러와서 엑셀 다운로드

관련글

티스토리툴바