본문 바로가기
프로그래밍/JSP

jsp poi 엑셀 다운 예제

by -현's- 2013. 11. 26.
반응형

●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();

%>








 























반응형

댓글