java excel poi (눈금선,병합,배경색,테두리,글씨체,사이즈,금액,천단위)

java에서 exel 파일 만들때 병합이라든지 폰트등 세세한 설정을 보여주는 예제 입니다.

일단 아래와 같은 파일을 생성하는 소스 입니다.

스프링에 엑셀 다운 받는 기능 추가는 다음 링크 참조 http://huskdoll.tistory.com/570

배경색 종류는 다음 링크 참조하세요. http://huskdoll.tistory.com/607

내용은 소스의 주석을 참고하여 주시기 바랍니다.

Row row = null;
Cell cell = null;
int rowCount = 0;
int cellCount = 0;
 
//1.셀 스타일 및 폰트 설정
CellStyle styleOfBoardFillFontBlackBold16 = workbook.createCellStyle();
//정렬
styleOfBoardFillFontBlackBold16.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
styleOfBoardFillFontBlackBold16.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
//배경색
styleOfBoardFillFontBlackBold16.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
styleOfBoardFillFontBlackBold16.setFillPattern(CellStyle.SOLID_FOREGROUND);
//테두리 선 (우,좌,위,아래)
styleOfBoardFillFontBlackBold16.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontBlackBold16.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontBlackBold16.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontBlackBold16.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//폰트 설정
Font fontOfGothicBlackBold16 = workbook.createFont();
fontOfGothicBlackBold16.setFontName("나눔고딕"); //글씨체
fontOfGothicBlackBold16.setFontHeight((short)(16*20)); //사이즈
fontOfGothicBlackBold16.setBoldweight(Font.BOLDWEIGHT_BOLD); //볼드 (굵게)
styleOfBoardFillFontBlackBold16.setFont(fontOfGothicBlackBold16);
 
//2.셀 스타일 및 폰트 설정
CellStyle styleOfBoardFillFontRedBold14 = workbook.createCellStyle();
//정렬
styleOfBoardFillFontRedBold14.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
styleOfBoardFillFontRedBold14.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
//배경색
styleOfBoardFillFontRedBold14.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
styleOfBoardFillFontRedBold14.setFillPattern(CellStyle.SOLID_FOREGROUND);
//테두리 선 (우,좌,위,아래)
styleOfBoardFillFontRedBold14.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontRedBold14.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontRedBold14.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontRedBold14.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//폰트 설정
Font RedBold14 = workbook.createFont();
RedBold14.setFontName("나눔고딕"); //글씨체
RedBold14.setColor(HSSFColor.RED.index);
RedBold14.setFontHeight((short)(14*20)); //사이즈
RedBold14.setBoldweight(Font.BOLDWEIGHT_BOLD); //볼드 (굵게)
styleOfBoardFillFontRedBold14.setFont(RedBold14);
 
//3.셀 스타일 및 폰트 설정
CellStyle styleOfBoardFillFontBlack11 = workbook.createCellStyle();
//정렬
styleOfBoardFillFontBlack11.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
styleOfBoardFillFontBlack11.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
//배경색
styleOfBoardFillFontBlack11.setFillForegroundColor(IndexedColors.LAVENDER.getIndex());
styleOfBoardFillFontBlack11.setFillPattern(CellStyle.SOLID_FOREGROUND);
//테두리 선 (우,좌,위,아래)
styleOfBoardFillFontBlack11.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontBlack11.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontBlack11.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleOfBoardFillFontBlack11.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//폰트 설정
Font Black11 = workbook.createFont();
Black11.setFontName("나눔고딕"); //글씨체
Black11.setFontHeight((short)(11*20)); //사이즈
styleOfBoardFillFontBlack11.setFont(Black11);
 
//4.셀 스타일 및 폰트 설정(일반 텍스트)
CellStyle styleOfBoardFontBlack11 = workbook.createCellStyle();
//정렬
styleOfBoardFontBlack11.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
styleOfBoardFontBlack11.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
//테두리 선 (우,좌,위,아래)
styleOfBoardFontBlack11.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleOfBoardFontBlack11.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleOfBoardFontBlack11.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleOfBoardFontBlack11.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//폰트 설정 (위 폰트 사용)
styleOfBoardFontBlack11.setFont(Black11);
 
//4.셀 스타일 및 폰트 설정(금액)
CellStyle styleOfBoardMoneyFontBlack11 = workbook.createCellStyle();
//정렬
styleOfBoardMoneyFontBlack11.setAlignment(CellStyle.ALIGN_RIGHT); //우측 정렬
styleOfBoardMoneyFontBlack11.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
//테두리 선 (우,좌,위,아래)
styleOfBoardMoneyFontBlack11.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleOfBoardMoneyFontBlack11.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleOfBoardMoneyFontBlack11.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleOfBoardMoneyFontBlack11.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//폰트 설정 (위 폰트 사용)
styleOfBoardMoneyFontBlack11.setFont(Black11);
//천단위 쉼표, 금액
styleOfBoardMoneyFontBlack11.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
            
//Sheet 생성
Sheet sheet = workbook.createSheet("도서정보");
 
//눈금선 없애기
sheet.setDisplayGridlines(false);
 
//첫줄 개행
rowCount++;
 
//열 생성
row = sheet.createRow(rowCount++);
cellCount = 0; //셀 카운트 초기화
 
//셀 병합
sheet.addMergedRegion(new CellRangeAddress(1,4,0,1)); //열시작, 열종료, 행시작, 행종료 (자바배열과 같이 0부터 시작)
sheet.addMergedRegion(new CellRangeAddress(1,1,2,6));
 
//셀 생성
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16); //위에서 설정한셀 스타일 셋팅
cell.setCellValue("A문고 상태");
 
//라인을 그리기 위해 실행 (병합이 되어도 테두리 라인을 그리기 위해 셀을 생성하고 스타입을 셋팅)
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontRedBold14);
cell.setCellValue("도서 정보");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontRedBold14);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontRedBold14);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontRedBold14);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontRedBold14);
 
//2열 작성
row = sheet.createRow(rowCount++);
cellCount = 0;
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlack11);
cell.setCellValue("ID");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlack11);
cell.setCellValue("도서명");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlack11);
cell.setCellValue("작가");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlack11);
cell.setCellValue("가격");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlack11);
cell.setCellValue("수량");
 
//3열 작성
row = sheet.createRow(rowCount++);
cellCount = 0;
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFontBlack11);
cell.setCellValue("b1");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFontBlack11);
cell.setCellValue("레미제라블");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFontBlack11);
cell.setCellValue("빅토르 위고");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardMoneyFontBlack11);
cell.setCellValue(3000);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardMoneyFontBlack11);
cell.setCellValue(32);
 
//4열 작성
row = sheet.createRow(rowCount++);
cellCount = 0;
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFillFontBlackBold16);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFontBlack11);
cell.setCellValue("a32");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFontBlack11);
cell.setCellValue("홍길동");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardFontBlack11);
cell.setCellValue("허균");
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardMoneyFontBlack11);
cell.setCellValue(8000);
 
cell = row.createCell(cellCount++);
cell.setCellStyle(styleOfBoardMoneyFontBlack11);
cell.setCellValue(15);

추가: 셀 표시형식(날짜)

CellStyle style = workbook.createCellStyle();

style.setDataFormat((short) 0xe);