●index.html
|
<!DOCTYPE html> <html> <head> <meta charset="EUC-KR"> <title>엑셀 다운</title> </head> <body> <a href="testxls.jsp">엑셀 다운</a> </body> </html> |
●testxls.jsp
|
<%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR"%> <%@ page import="java.util.*,com.sun.org.apache.xalan.internal.xsltc.compiler.Parser, java.io.*, java.util.List, java.util.HashMap, org.apache.poi.hssf.usermodel.*, org.apache.poi.hssf.*, org.apache.poi.hssf.util.HSSFColor, org.apache.poi.hssf.util.Region" %> <% /* jsp 로직 Start*/ String sFileName = "엑셀파일" + ".xls"; sFileName = new String ( sFileName.getBytes("KSC5601"), "8859_1"); out.clear(); out = pageContext.pushBody(); response.reset(); // 이 문장이 없으면 excel 등의 파일에서 한글이 깨지는 문제 발생. String strClient = request.getHeader("User-Agent"); String fileName = sFileName; if (strClient.indexOf("MSIE 5.5") > -1) { //response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "filename=" + fileName + ";"); } else { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ";"); } OutputStream fileOut = null; //워크북 생성 HSSFWorkbook objWorkBook = new HSSFWorkbook(); //워크시트 생성 HSSFSheet objSheet = objWorkBook.createSheet(); //시트 이름 objWorkBook.setSheetName(0 , "1번시트" , HSSFWorkbook.ENCODING_UTF_16 ); //행생성 HSSFRow objRow = objSheet.createRow((short)0); //셀 생성 HSSFCell objCell = null; //------------------------------------------------------------------------------------ //병합 //제목 objSheet.addMergedRegion(new Region(0,(short)0,0,(short)7)); //제목 //상단 objSheet.addMergedRegion(new Region(1,(short)0,3,(short)0)); //구분 objSheet.addMergedRegion(new Region(1,(short)1,3,(short)1)); //2012년 폐기수량 objSheet.addMergedRegion(new Region(1,(short)2,3,(short)2)); //2013년 폐기수량 objSheet.addMergedRegion(new Region(1,(short)3,1,(short)7)); //평가전 objSheet.addMergedRegion(new Region(2,(short)3,3,(short)3)); //2013년 평가대상 objSheet.addMergedRegion(new Region(2,(short)4,2,(short)7)); //보존기간 objSheet.addMergedRegion(new Region(3,(short)4,3,(short)4)); //10년 objSheet.addMergedRegion(new Region(3,(short)5,3,(short)5)); //5년 objSheet.addMergedRegion(new Region(3,(short)6,3,(short)6)); //3년 objSheet.addMergedRegion(new Region(3,(short)7,3,(short)7)); //1년 //----------------------------------------------------------------- //스타일 설정 //스타일 객체 생성 HSSFCellStyle styleHd = objWorkBook.createCellStyle(); //제목 스타일 HSSFCellStyle styleSub = objWorkBook.createCellStyle(); //상단 스타일 HSSFCellStyle styleCon = objWorkBook.createCellStyle(); //내용 스타일 HSSFCellStyle styleBody = objWorkBook.createCellStyle(); //왼쪽 스타일 HSSFCellStyle stylesum = objWorkBook.createCellStyle(); //소계 스타일 //제목 폰트 HSSFFont font = objWorkBook.createFont(); font.setFontHeightInPoints((short)15); font.setBoldweight((short)font.BOLDWEIGHT_BOLD); //제목 스타일에 폰트 적용, 정렬 styleHd.setFont(font); styleHd.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHd.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER); //상단 폰트 HSSFFont font2 = objWorkBook.createFont(); font2.setBoldweight((short)font.BOLDWEIGHT_BOLD); //상단 스타일 설정 styleSub.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleSub.setBottomBorderColor(HSSFColor.BLACK.index); styleSub.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSub.setLeftBorderColor(HSSFColor.BLACK.index); styleSub.setBorderRight(HSSFCellStyle.BORDER_THIN); styleSub.setRightBorderColor(HSSFColor.BLACK.index); styleSub.setBorderTop(HSSFCellStyle.BORDER_THIN); styleSub.setTopBorderColor(HSSFColor.BLACK.index); //셀에 색 넣기 styleSub.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// not BackgroundColor styleSub.setFillForegroundColor(HSSFColor.YELLOW.index); //글자 속성 styleSub.setFont(font2); styleSub.setAlignment (HSSFCellStyle.ALIGN_CENTER); styleSub.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER); styleSub.setWrapText(true); //왼쪽 스타일 설정 styleBody.setAlignment (HSSFCellStyle.ALIGN_LEFT); styleBody.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER); styleBody.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// not BackgroundColor styleBody.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); styleBody.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBody.setBottomBorderColor(HSSFColor.GREY_50_PERCENT.index); styleBody.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBody.setLeftBorderColor(HSSFColor.GREY_50_PERCENT.index); styleBody.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBody.setRightBorderColor(HSSFColor.GREY_50_PERCENT.index); styleBody.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBody.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index); //내용 스타일 styleCon.setAlignment (HSSFCellStyle.ALIGN_LEFT); styleCon.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER); styleCon.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCon.setBottomBorderColor(HSSFColor.GREY_50_PERCENT.index); styleCon.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCon.setLeftBorderColor(HSSFColor.GREY_50_PERCENT.index); styleCon.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCon.setRightBorderColor(HSSFColor.GREY_50_PERCENT.index); styleCon.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCon.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index); //합계 스타일 stylesum.setAlignment (HSSFCellStyle.ALIGN_LEFT); stylesum.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// not BackgroundColor stylesum.setFillForegroundColor(HSSFColor.SKY_BLUE.index); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBottomBorderColor(HSSFColor.GREY_50_PERCENT.index); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setLeftBorderColor(HSSFColor.GREY_50_PERCENT.index); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setRightBorderColor(HSSFColor.GREY_50_PERCENT.index); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index); //------------ //----------------------------------------------------------------------------------- //1행 objRow = objSheet.createRow((short)0); objRow.setHeight ((short) 0x300); objCell = objRow.createCell((short)0); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("제목"); objCell.setCellStyle(styleHd); //병합에 스타일 주기 for(int i=0;i<8;i++){ objRow = objSheet.createRow((short)1); objCell = objRow.createCell((short)i); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue(""); objCell.setCellStyle(styleSub); objRow = objSheet.createRow((short)2); objCell = objRow.createCell((short)i); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue(""); objCell.setCellStyle(styleSub); objRow = objSheet.createRow((short)3); objCell = objRow.createCell((short)i); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue(""); objCell.setCellStyle(styleSub); } //상단 objRow = objSheet.createRow((short)1); objCell = objRow.createCell((short)0); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("구분"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)1); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("A"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)2); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("B"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)3); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("C"); objCell.setCellStyle(styleSub); objRow = objSheet.createRow((short)2); objCell = objRow.createCell((short)3); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("a"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)4); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("b"); objCell.setCellStyle(styleSub); objRow = objSheet.createRow((short)3); objCell = objRow.createCell((short)4); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("1"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)5); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("2"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)6); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("3"); objCell.setCellStyle(styleSub); objCell = objRow.createCell((short)7); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("4"); objCell.setCellStyle(styleSub); //---------------------------------------------------------------------------------------- //길이 설정 objSheet.setColumnWidth((short)0,(short)4500); objSheet.setColumnWidth((short)1,(short)4000); objSheet.setColumnWidth((short)2,(short)4000); objSheet.setColumnWidth((short)3,(short)4000); objSheet.setColumnWidth((short)4,(short)2500); objSheet.setColumnWidth((short)5,(short)2500); objSheet.setColumnWidth((short)6,(short)2500); objSheet.setColumnWidth((short)7,(short)2500); //-------------------------------------------------------------------------------------- //내용 objRow = objSheet.createRow((short)4); objRow.setHeight ((short) 0x150); objCell = objRow.createCell((short)0); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("A"); //구분 objCell.setCellStyle(styleBody); objCell = objRow.createCell((short)1); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("1"); objCell.setCellStyle(styleCon); objCell = objRow.createCell((short)2); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("2"); objCell.setCellStyle(styleCon); objCell = objRow.createCell((short)3); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("3"); objCell.setCellStyle(styleCon); objCell = objRow.createCell((short)4); //10년 objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("4"); objCell.setCellStyle(styleCon); objCell = objRow.createCell((short)5); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("5"); objCell.setCellStyle(styleCon); objCell = objRow.createCell((short)6); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("6"); objCell.setCellStyle(styleCon); objCell = objRow.createCell((short)7); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("7"); objCell.setCellStyle(styleCon);
//합계 objRow = objSheet.createRow((short)5); objRow.setHeight ((short) 0x150); objCell = objRow.createCell((short)0); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("소계"); //구분 objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)1); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("1"); objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)2); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("2"); objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)3); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("3"); objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)4); //10년 objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("4"); objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)5); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("5"); objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)6); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("6"); objCell.setCellStyle(stylesum); objCell = objRow.createCell((short)7); objCell.setEncoding(HSSFCell.ENCODING_UTF_16); objCell.setCellValue("7"); objCell.setCellStyle(stylesum);
fileOut = response.getOutputStream(); objWorkBook.write(fileOut); fileOut.close();
%>
|
'프로그래밍 > JSP' 카테고리의 다른 글
| poi error java.lang.OutOfMemoryError: GC overhead limit exceeded (0) | 2013.12.04 |
|---|---|
| poi xlsx 다운 (0) | 2013.12.02 |
| jsp 개발환경 세팅 이클립스 톰켓 플러그인 (0) | 2013.10.28 |
| jsp poi 엑셀 다운받기 (0) | 2013.09.04 |
| jsp poi 폰트, 스타일 적용 (0) | 2013.09.03 |
댓글