반응형
APACHE POI를 활용한 엑셀 다운로드 기능을 구현
1 . POM.xml 에 POI 관련 dependency 등록
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency>
- 만약 행이 엄청 많아서 xlsx로 다운받고 싶을때는
<!--xlsx로 받고싶을때--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency>
2 . 컨트롤러에서 excelView 로 리턴시키면 ExcelView.java로 가게하는 설정
dispatcher-servlet.xml
<bean class = "org.springframework.web.servlet.view.BeanNameViewResolver"> <property name = "order" value = "1" /> </bean> <!--컨트롤러 리턴시 다른것들보다 먼저 읽혀야 하기 때문에 order를 0과 1로 지정--> <bean class = "org.springframework.web.servlet.handler.SimpleMappingExceptionResolver"> <property name = "order" value = "0" /> <property name = "defaultErrorView" value = "error" /> <property name = "exceptionMappings"> <props> <props key = "RuntimeException">error</prop> </props> </property> </bean> <bean name = "excelView" class = "net.yg.cmmn.excel.ExcelView" />
3 . ExcelView가 상속받을 AbstractExcelView.java 작성
public class AbstractExcelView extends AbstractView { private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; public AbstractExcelView(){} @Override protected boolean generatesDownloadContent(){ return true; } @Override protected final void renderMergedOutputModel(Map<String,Object> model , HttpServletRequest request , HttpServletResponse response) throws Exception{ Workbook workbook = createWorkbook(); setContentType("CONTENT_TYPE_XLSX"); buildExcelDocument(model , workbook , request , response); response.setContentType(getContentType()); ServletOutputStream out = response.getOutputStream(); out.flush(); workbook.write(out); out.flush(); if(workbook instanceof SXSSFWorkbook){ ((SXSSFWorkbook) workbook).dispose(); } } protected abstract Workbook createWorkbook(); protected abstract void buildExcelDocument(Map<String,Object> model, Workbook workbook , HttpServletRequest request , HttpServletResponse response) throws Exception; }
4 . ExcelView.java 작성
public class ExcelView extends AbstractExcelView{ @Override protected void buildExcelDocument(Map<String,Object> model , Workbook workbook , HttpServletRequest request , HttpServletResponse response) throws Exception{ String type = model.get("excelType").toString(); if(type.equals("board")){ List<BoardVO> excelList = (List<BoardVO>)model.get("excelList"); Sheet sheet = workbook.createSheet(type); Row row = null; int rowCount = 0; int cellCount = 0; row = sheet.createRow(rowCount++); row.createCell(cellCount++).setCellValue("번호"); row.createCell(cellCount++).setCellValue("제목"); row.createCell(cellCount++).setCellValue("작성자"); row.createCell(cellCount++).setCellValue("작성일"); row.createCell(cellCount++).setCellValue("조회수"); for(BoardVO vo : excelList){ row = sheet.createRow(rowCount++); cellCount = 0; row.createCell(cellCount++).setCellValue(vo.getBdSq()); row.createCell(cellCount++).setCellValue(vo.getBdTitl()); row.createCell(cellCount++).setCellValue(vo.getBdWrtr()); row.createCell(cellCount++).setCellValue(vo.getRgDt()); row.createCell(cellCount++).setCellValue(vo.getBdCnt()); } } } @Override protected Workbook createWorkbook(){ return new XSSFWorkbook(); } }
5 . ExcelController.java 작성
public class ExcelController{ @Resource(name = "excelService") private ExcelService excelService; @RequestMapping(value = "/getBoardByExcel") public String getBoardList(BoardVO vo , Map<String,Object> modelMap , HttpServletResponse response) throws Exception{ response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel"); response.setHeader("Pragma","public"); response.setHeader("Expires","0"); response.setHeader("Content-Disposition","attachment; filename = test.xls"); List<Object> excelList = excelService.getBoardList(vo); modelMap.put("excelList",excelList); modelMap.put("excelType",board); return "excelView"; } }
반응형
'Web Development > Spring' 카테고리의 다른 글
multipart filter 적용하기 (0) | 2018.10.17 |
---|---|
[Spring+DB]방문자정보 저장, 방문자수 조회 (9) | 2018.01.25 |
전자정부프레임워크 마이바티스 설정 (0) | 2017.12.29 |
[Spring]CKEditor로 이미지 첨부하기 (2) | 2017.12.21 |
[Spring]http->https 이동간 세션 복사 (2) | 2017.10.31 |