SpringBoot ] 복붙만으로 EXCEL 대용량 다운로드 구현

1. bulid.gradle에 의존성 추가 

	compile 'org.apache.poi:poi:3.15'
	compile 'org.apache.poi:poi-ooxml:3.15'

 

2. Excel.java

@RestController
public class Excel {
	@Autowired
	ExcelService excelService;
	
	@RequestMapping(value = "/contentListDown")
	public void contentListDownload(HttpServletRequest request,	HttpServletResponse response){
		excelService.contentListDownload(request,response);
	}
	
}

 

3. ExcelHandler.java

 

 
 
public class ExcelHandler<T extends Map<String, Object>> implements ResultHandler<T> {
		public static final org.mariadb.jdbc.internal.logging.Logger LOGGER = LoggerFactory.getLogger(ExcelHandler.class);
		private T result;
		private String title;
		private String filename;
		private SXSSFWorkbook workbook;
		private SXSSFSheet sheet;
		private HttpServletResponse response;
		private ResultContext<? extends T> context;
		private List<String> columnTitleList;
		private int rownum;
		final int TITLE = 0;
		final int BODY = 1;

		private ExcelHandler() {
			super();
			rownum = 0;
		}

		public ExcelHandler(HttpServletResponse response, String filename) {
			this();
			this.response = response;
			this.title = filename;
			try {
				this.filename = URLEncoder.encode(filename.replace(" ", "_"), "UTF-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			;
			workbook = new SXSSFWorkbook(10000);
			sheet = workbook.createSheet(title);
		}

		public ExcelHandler(HttpServletResponse response, String filename, List<String> orderedColumnTitleList) {
			this(response, filename);
			this.columnTitleList = orderedColumnTitleList;
		}

		@Override 
		public void handleResult(ResultContext<? extends T> resultContext) { 
			if(resultContext.getResultObject() == null) { 
				return; 
				} 
			this.context = resultContext; 
			result = context.getResultObject(); 
			Font headerFont = workbook.createFont(); 
			headerFont.setFontName("맑은 고딕"); headerFont.
			setBold(true); CellStyle headerStyle = workbook.createCellStyle(); 
			headerStyle.setBorderTop(BorderStyle.THIN); 
			headerStyle.setBorderBottom(BorderStyle.THIN); 
			headerStyle.setBorderLeft(BorderStyle.THIN); 
			headerStyle.setBorderRight(BorderStyle.THIN); 
			headerStyle.setAlignment(HorizontalAlignment.CENTER); 
			headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); 
			headerStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); 
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
			headerStyle.setFont(headerFont); 
			Font bodyFont = workbook.createFont(); 
			bodyFont.setFontName("맑은 고딕"); 
			CellStyle bodyStyle= workbook.createCellStyle(); 
			bodyStyle.setBorderTop(BorderStyle.THIN); 
			bodyStyle.setBorderBottom(BorderStyle.THIN); 
			bodyStyle.setBorderLeft(BorderStyle.THIN); 
			bodyStyle.setBorderRight(BorderStyle.THIN); 
			bodyStyle.setAlignment(HorizontalAlignment.LEFT); 
			bodyStyle.setFont(bodyFont); 
		
			if(rownum == 0 ) { 
				write(TITLE, rownum,headerStyle);
				write(BODY, rownum+1,bodyStyle); 
				}
			else {  
				write(BODY, rownum+1,bodyStyle); 
				}
			rownum++; 
			}
 
		private void write(int type,int currentRow,CellStyle style) { 
			if (columnTitleList == null) { 
				columnTitleList = new ArrayList<String>(context.getResultObject().keySet()); 
				} 
			SXSSFRow row = sheet.createRow(currentRow); 
			if(columnTitleList.size() == 0 ) { 
				return; 
				} 
			for(int i = 0 ; i < columnTitleList.size() ; i++) { 
				SXSSFCell cell = row.createCell(i); 
				String tempValue = ""; 
				switch (type) { 
				case TITLE: 
					tempValue = columnTitleList.get(i);
					 cell.setCellValue(tempValue); 
				break; 
				case BODY: 
					if(context.getResultObject().containsKey(columnTitleList.get(i))) { 
						tempValue = context.getResultObject().get(columnTitleList.get(i)).toString(); 
						 cell.setCellValue(tempValue); 
						} 
					break; 
					} // 스타일 객체가 없다면 기본으로 if(style != null) { cell.setCellStyle(style); } cell.setCellValue(tempValue); } }
			}
		}
		
		public void download() throws IOException{ 

			LOGGER.debug("## start excel download : "+filename); 
			response.setHeader("Content-Disposition", "attachment; filename=" + filename.replaceAll(" ", "_") + ".xlsx;"); 
			response.setCharacterEncoding("UTF-8");
			ServletOutputStream stream = response.getOutputStream(); 
			OutputStream out = new BufferedOutputStream(stream); 
			try { 
				response.resetBuffer(); 
				response.setBufferSize(1024 * 4); 
				workbook.write(out); 
				} 
			catch (Exception e) { 
				out.flush(); 
				out.close(); 
				stream.close(); 
				} 
			finally { 
				out.flush(); 
				out.close(); 
				stream.close(); 
				} 
			if (workbook != null) { 
				try { workbook.dispose(); 
				} 
				catch (Exception e) {
					workbook.close(); 
					} 
				finally { 
					workbook.close(); 
					} 
				} 
			workbook.close(); 
			} 
		public void close() { 
			workbook.dispose(); 
			try { 
				workbook.close(); 
				} 
			catch (IOException e) { 
				e.printStackTrace(); 
				} 
			}

	}

 

 

4. ExcelService.java

public interface ExcelService { 
	public void contentListDownload( HttpServletRequest request, HttpServletResponse response);
	
	}

 

5. ExcelServiceImpl.java

 

@Service
public class ExcelServiceImpl implements ExcelService {
	@Autowired 
	 BoardMapper mapper;

	private ExcelHandler<Map<String, Object>> excelHandler;

	@Override
	public void contentListDownload(HttpServletRequest request, HttpServletResponse response) {
		excelHandler = new ExcelHandler<>(response,"테스트"); 
		mapper.contentListDownload(excelHandler);
 
		try {  
			excelHandler.download(); 
		} 
		catch (IOException e) { 
			e.printStackTrace(); 
			excelHandler.close(); 
		}
	}
 
}

 

6. ExcelMapper.java

 

@Mapper
@Repository
public interface ExcelMapper {
	public void contentListDownload(ResultHandler<Map<String,Object>> ExcelHander) ;	
	
}

 

 

7.  Excel.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC '-//mybatis.org//DTD Mapper 3.0//EN' 'http://mybatis.org/dtd/mybatis-3-mapper.dtd'>
<mapper namespace='Mapper경로'>
	<resultMap id="excelListDownloadMap" type="java.util.HashMap" >
    	<result column="NO"     	property="번호"    	jdbcType="VARCHAR" />
    	<result column="TITILE"     	property="제목"    	jdbcType="VARCHAR" /> 
    	<result column="EMAIL"     	property="메일"    	jdbcType="VARCHAR" /> 
    </resultMap>

    <select id="contentListDownload" resultMap="excelListDownloadMap">
		
			SELECT *
			FROM TB_INFO  
	
	</select>
</mapper>