{"id":1025,"date":"2024-04-16T19:48:46","date_gmt":"2024-04-16T10:48:46","guid":{"rendered":"http:\/\/inwelly.com\/?p=1025"},"modified":"2024-04-21T13:22:23","modified_gmt":"2024-04-21T04:22:23","slug":"java-spring-poi-excel-download-sample-on-controller-java","status":"publish","type":"post","link":"https:\/\/inwelly.com\/?p=1025","title":{"rendered":"JAVA Spring – POI Excel download sample on Controller.java"},"content":{"rendered":"\n
\n

import org.apache.poi.hssf.usermodel.HSSFCellStyle;<\/p>\n\n\n\n

import org.apache.poi.hssf.util.HSSFColor;<\/p>\n\n\n\n

import org.apache.poi.ss.usermodel.CellStyle;<\/p>\n\n\n\n

import org.apache.poi.xssf.streaming.SXSSFCell;<\/p>\n\n\n\n

import org.apache.poi.xssf.streaming.SXSSFRow;<\/p>\n\n\n\n

import org.apache.poi.xssf.streaming.SXSSFSheet;<\/p>\n\n\n\n

import org.apache.poi.xssf.streaming.SXSSFWorkbook;<\/p>\n\n\n\n

import java.io.OutputStream;<\/p>\n\n\n\n

import java.text.SimpleDateFormat;<\/p>\n\n\n\n

  @RequestMapping(value = “\/test\/test\/excel.do”)<\/p>\n\n\n\n

public void excel(HttpServletResponse response) throws Exception {<\/p>\n\n\n\n

List<SomereportVO> list = statisticsService.selectExcelList();<\/p>\n\n\n\n

SimpleDateFormat format1 = new SimpleDateFormat (“yyyyMMddHHmmssSSS”);<\/p>\n\n\n\n

Date time = new Date();<\/p>\n\n\n\n

String TODAYTXT = format1.format(time);<\/p>\n\n\n\n

SXSSFWorkbook wb = new SXSSFWorkbook(100);<\/p>\n\n\n\n

SXSSFSheet  sheet = wb.createSheet(“TEST_”+TODAYTXT);<\/p>\n\n\n\n

SXSSFRow  row = sheet.createRow(0);<\/p>\n\n\n\n

SXSSFCell cell = row.createCell(1);<\/p>\n\n\n\n

\/\/SXSSFWorkbook wb = new SXSSFWorkbook(100);<\/p>\n\n\n\n

\/\/Sheet sheet = wb.createSheet(“TEST”); \/\/ Sheet Name<\/p>\n\n\n\n

\/\/Row row = null;<\/p>\n\n\n\n

\/\/Cell cell = null;<\/p>\n\n\n\n

int rowNo = 0;<\/p>\n\n\n\n

String[] columns_head = new String[]{“\ub9e4\ucd9c\uc77c\uc790”, “\uc0c1\ud488 ID”, “\uace0\uac1d ID”,”TEST1″,”TEST2″,”TEST3″,”TEST4″,”TEST5″};<\/p>\n\n\n\n

int[] columns_width = new int[]{1000, 5000, 3000, 1000 , 2000, 3000,1000 , 500};<\/p>\n\n\n\n

try {<\/p>\n\n\n\n

\/\/ \ud14c\uc774\ube14 \ud5e4\ub354\uc6a9 \uc2a4\ud0c0\uc77c<\/p>\n\n\n\n

CellStyle headStyle = wb.createCellStyle();<\/p>\n\n\n\n

headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

headStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);<\/p>\n\n\n\n

headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);<\/p>\n\n\n\n

headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);<\/p>\n\n\n\n

\/\/ \ub370\uc774\ud130\uc6a9 \uacbd\uacc4 \uc2a4\ud0c0\uc77c<\/p>\n\n\n\n

CellStyle bodyStyle = wb.createCellStyle();<\/p>\n\n\n\n

bodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

bodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);<\/p>\n\n\n\n

\/\/ \ud5e4\ub354 \ubd80\ubd84 \uc0dd\uc131<\/p>\n\n\n\n

row = sheet.createRow(rowNo++);<\/p>\n\n\n\n

for(int i = 0; i < columns_head.length; i++) <\/p>\n\n\n\n

{<\/p>\n\n\n\n

sheet.setColumnWidth(i, (sheet.getColumnWidth(i)) + columns_width[i]);<\/p>\n\n\n\n

cell = row.createCell(i);<\/p>\n\n\n\n

cell.setCellStyle(headStyle);<\/p>\n\n\n\n

cell.setCellValue(columns_head[i]);<\/p>\n\n\n\n

}<\/p>\n\n\n\n

\/\/ \ub370\uc774\ud130 \ubd80\ubd84 \uc0dd\uc131<\/p>\n\n\n\n

for(SaleFigureMDreportVO vo : list) {<\/p>\n\n\n\n

row = sheet.createRow(rowNo++);<\/p>\n\n\n\n

for(int j = 0; j < columns_head.length ; j++) <\/p>\n\n\n\n

{<\/p>\n\n\n\n

cell = row.createCell(j);<\/p>\n\n\n\n

cell.setCellStyle(bodyStyle);<\/p>\n\n\n\n

if( j == 0 ) { cell.setCellValue(vo.getSDt()); }<\/p>\n\n\n\n

if( j == 1 ) { cell.setCellValue(vo.getGId()); }<\/p>\n\n\n\n

if( j == 2 ) { cell.setCellValue(vo.getNo()); }<\/p>\n\n\n\n

if( j == 3 ) { cell.setCellValue(vo.getBKo()); }<\/p>\n\n\n\n

if( j == 4 ) { cell.setCellValue(vo.getCNo()); }<\/p>\n\n\n\n

if( j == 5 ) { cell.setCellValue(vo.getCNm()); }<\/p>\n\n\n\n

if( j == 6 ) { cell.setCellValue(vo.getONo()); }<\/p>\n\n\n\n

if( j == 7 ) { cell.setCellValue(vo.getINm()); }<\/p>\n\n\n\n

}<\/p>\n\n\n\n

}<\/p>\n\n\n\n

\/\/ \uc5d1\uc140 \ucd9c\ub825<\/p>\n\n\n\n

response.setHeader(“Set-Cookie”, “fileDownload=true; path=\/”);<\/p>\n\n\n\n

response.setHeader(“Content-Disposition”, String.format(“attachment; filename=\\”test_”+TODAYTXT+”.xlsx\\””)); \/\/ \ud30c\uc77c\uba85<\/p>\n\n\n\n

wb.write(response.getOutputStream());<\/p>\n\n\n\n

}   catch(Exception e) {<\/p>\n\n\n\n

response.setHeader(“Set-Cookie”, “fileDownload=false; path=\/”);<\/p>\n\n\n\n

response.setHeader(“Cache-Control”, “no-cache, no-store, must-revalidate”);<\/p>\n\n\n\n

response.setHeader(“Content-Type”,”text\/html; charset=utf-8″);<\/p>\n\n\n\n

OutputStream out = null;<\/p>\n\n\n\n

try {<\/p>\n\n\n\n

out = response.getOutputStream();<\/p>\n\n\n\n

byte[] data = new String(“fail..”).getBytes();<\/p>\n\n\n\n

out.write(data, 0, data.length);<\/p>\n\n\n\n

} catch(Exception ignore) {<\/p>\n\n\n\n

ignore.printStackTrace();<\/p>\n\n\n\n

} finally {<\/p>\n\n\n\n

if(out != null) try { out.close(); } catch(Exception ignore) {}<\/p>\n\n\n\n

}<\/p>\n\n\n\n

} finally {<\/p>\n\n\n\n

\/\/ \ub514\uc2a4\ud06c \uc801\uc5c8\ub358 \uc784\uc2dc\ud30c\uc77c\uc744 \uc81c\uac70\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n

wb.dispose();<\/p>\n\n\n\n

try { wb.close(); } catch(Exception ignore) {}<\/p>\n\n\n\n

}<\/p>\n\n\n\n

}<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"

import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import java.io.OutputStream; import java.text.SimpleDateFormat;   @RequestMapping(value […]<\/p>\n","protected":false},"author":1,"featured_media":1280,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[104],"tags":[],"class_list":["post-1025","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-java"],"_links":{"self":[{"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/posts\/1025","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/inwelly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1025"}],"version-history":[{"count":1,"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/posts\/1025\/revisions"}],"predecessor-version":[{"id":1026,"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/posts\/1025\/revisions\/1026"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/inwelly.com\/index.php?rest_route=\/wp\/v2\/media\/1280"}],"wp:attachment":[{"href":"https:\/\/inwelly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1025"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/inwelly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1025"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/inwelly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1025"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}