[java] Spring Excel down하기

[java] Spring Excel down하기

 

 

 

pom.xml

1 2 3 4 5

<dependency>             <groupId>org.apache.poi</groupId>             <artifactId>poi</artifactId>             <version>3.17</version>         </dependency>

cs

 

HomeController.java가 있는 패키지에 ExcelController.java를 생성해 주세요.

ExcelController.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

@Controller public class ExcelController {       private static final Logger logger = LoggerFactory.getLogger(ExcelController.class);          //Service 연동     @Resource(name = "homeService")     private HomeService homeService;          @RequestMapping(value = "/excelDown.do", method = RequestMethod.POST)     public void ExcelDown(HttpServletResponse response){         logger.info("@@@@@@@@@@@@@@@ExcelDown START@@@@@@@@@@@@@@@");                  homeService.getExcelDown(response);                  logger.info("@@@@@@@@@@@@@@@ExcelDown END@@@@@@@@@@@@@@@");              } } Colored by Color Scripter

cs

 

HomeService.java

1

public void getExcelDown(HttpServletResponse response);

cs

 

HomeServiceImpl.java

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

@Override     public void getExcelDown(HttpServletResponse response) {         Map<String, Object> map = new HashMap<String, Object>();         List<?> list = homeMapper.testDbList(map);                  try{             //Excel Down 시작             Workbook workbook = new HSSFWorkbook();             //시트생성             Sheet sheet = workbook.createSheet("게시판");                          //행, 열, 열번호             Row row = null;             Cell cell = null;             int rowNo = 0;                          // 테이블 헤더용 스타일             CellStyle headStyle = workbook.createCellStyle();                  // 가는 경계선을 가집니다.             headStyle.setBorderTop(BorderStyle.THIN);             headStyle.setBorderBottom(BorderStyle.THIN);             headStyle.setBorderLeft(BorderStyle.THIN);             headStyle.setBorderRight(BorderStyle.THIN);                  // 배경색은 노란색입니다.             headStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());             headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);                  // 데이터는 가운데 정렬합니다.             headStyle.setAlignment(HorizontalAlignment.CENTER);                  // 데이터용 경계 스타일 테두리만 지정             CellStyle bodyStyle = workbook.createCellStyle();             bodyStyle.setBorderTop(BorderStyle.THIN);             bodyStyle.setBorderBottom(BorderStyle.THIN);             bodyStyle.setBorderLeft(BorderStyle.THIN);             bodyStyle.setBorderRight(BorderStyle.THIN);                  // 헤더 생성             row = sheet.createRow(rowNo++);                  cell = row.createCell(0);             cell.setCellStyle(headStyle);             cell.setCellValue("번호");                  cell = row.createCell(1);             cell.setCellStyle(headStyle);             cell.setCellValue("이름");                  cell = row.createCell(2);             cell.setCellStyle(headStyle);             cell.setCellValue("제목");                  // 데이터 부분 생성             for(Object map1 : list) {                 Map<String, Object> mapValue = (Map<String, Object>) map1;                                  logger.info("DB DATA : "+mapValue.toString());                                  row = sheet.createRow(rowNo++);                 cell = row.createCell(0);                 cell.setCellStyle(bodyStyle);                 cell.setCellValue(""+mapValue.get("IDCOL"));                 cell = row.createCell(1);                 cell.setCellStyle(bodyStyle);                 cell.setCellValue(""+mapValue.get("NAMECOL"));                 cell = row.createCell(2);                 cell.setCellStyle(bodyStyle);                 cell.setCellValue(""+mapValue.get("VALUECOL"));             }                  // 컨텐츠 타입과 파일명 지정             response.setContentType("ms-vnd/excel");             response.setHeader("Content-Disposition", "attachment;filename=test.xls");               // 엑셀 출력             workbook.write(response.getOutputStream());             workbook.close();         } catch (IOException e) {             e.printStackTrace();         }              } Colored by Color Scripter

cs

 

마지막으로

home.jsp

1 2 3

<form name="excelForm" id="excelForm" method="POST" action="./excelDown.do">     <input type="submit" id="excelDown" value="EXCEL 다운"/> </form> Colored by Color Scripter

cs

 

후에 실행해보면 ExcelFile을 Down 받아서 보여줍니다.

 

감사합니다.

출처: https://jdkblog.tistory.com/130 [JDK's blog]