Jakarta POI III탄 Cell편

Jakarta POI

IX. Cell을 좀더 유연하게!

1. Date타입 셀 만들기

① 소스

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short)0);

//처음셀은 style없이 그냥 new Date()로 입력

HSSFCell cell = row.createCell((short)0);
cell.setCellValue(new Date());

//두번째 셀은 "m/d/yy h:mm"으로 포맷하여 날짜를 입력

HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell = row.createCell((short)1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

② 결과

③ HSSFDateFormat이 지원하는 날짜 포맷

cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 에서 다음과 같이 포맷을 정할 수 있다 (현재시간은 2005년 3월 14일 0시 52분 17초.. 헛 화이트데이 --;)

2. Cell의 Align속성

① 소스

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 2);

//HSSFCellStyle의 여러가지 align속성
createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

 

public void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)
{
    HSSFCell cell = row.createCell(column);
    cell.setCellValue("Align It");
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(align);
    cell.setCellStyle(cellStyle);
}

② 결과

3. Cell의 Border 속성

① 소스

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue(4);

// Cell의 Border 속성
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.GREEN.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLUE.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(HSSFColor.BLACK.index);
cell.setCellStyle(style);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

② 결과

③ HSSFCellStyle

HSSFCellStyle에는 다음과 같은 static 멤버변수가 존재합니다

ALIGN_CENTER	center horizontal alignment
ALIGN_CENTER_SELECTION	center-selection? horizontal alignment
ALIGN_FILL	fill? horizontal alignment
ALIGN_GENERAL	general (normal) horizontal alignment
ALIGN_JUSTIFY	justified horizontal alignment
ALIGN_LEFT	left-justified horizontal alignment
ALIGN_RIGHT	right-justified horizontal alignment
ALT_BARS	Wide dots
BIG_SPOTS	Large spots
BORDER_DASH_DOT	dash-dot border
BORDER_DASH_DOT_DOT	dash-dot-dot border
BORDER_DASHED	dash border
BORDER_DOTTED	hair-line border
BORDER_DOUBLE	double-line border
BORDER_HAIR	dot border
BORDER_MEDIUM	Medium border
BORDER_MEDIUM_DASH_DOT	medium dash-dot border
BORDER_MEDIUM_DASH_DOT_DOT	medium dash-dot-dot border
BORDER_MEDIUM_DASHED	Medium dashed border
BORDER_NONE	No border
BORDER_SLANTED_DASH_DOT	slanted dash-dot border
BORDER_THICK	Thick border
BORDER_THIN	Thin border
BRICKS	Brick-like layout
DIAMONDS	Diamonds
FINE_DOTS	Small fine dots
NO_FILL	No background
SOLID_FOREGROUND	Solidly filled
SPARSE_DOTS	Sparse dots
SQUARES	Squares
THICK_BACKWARD_DIAG	Thick backward facing diagonals
THICK_FORWARD_DIAG	Thick forward facing diagonals
THICK_HORZ_BANDS	Thick horizontal bands
THICK_VERT_BANDS	Thick vertical bands
THIN_BACKWARD_DIAG	Thin backward diagonal
THIN_FORWARD_DIAG	Thin forward diagonal
THIN_HORZ_BANDS	Thin horizontal bands
THIN_VERT_BANDS	Thin vertical bands
VERTICAL_BOTTOM	bottom-aligned vertical alignment
VERTICAL_CENTER	center-aligned vertical alignment
VERTICAL_JUSTIFY	vertically justified vertical alignment
VERTICAL_TOP	top-aligned vertical alignment

4. Cell의 색갈 채우기

① 소스

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);

HSSFCellStyle style = wb.createCellStyle();
// 아쿠아색을 배경으로 하고

style.setFillBackgroundColor(HSSFColor.AQUA.index);

//채움 스타일은 큰 점으로 한다
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);

style = wb.createCellStyle();

// 오렌지색으로 전경으로 하고
style.setFillForegroundColor(HSSFColor.ORANGE.index);

// 채움 스타일은 SOLID_FOREGROUND로 한다
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X");
cell.setCellStyle(style);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

② 결과

③ HSSFColor 정리!

5. Cell 병합

① 소스

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);
HSSFCell cell = row.createCell((short) 1);

cell.setCellValue("This is a test of merging");

sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

② 결과

③ Region

특정셀을 합칠 때는 HSSFSheet의 addMergedRegion(Region region)와

합칠 셀의 영역을 나타내는 Region을 사용한다.

Region region = new (int 시작ROW, short 시작COL, int 종료ROW, short 종료COL);

6. Cell에 폰트 설정하기

① 소스

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");

HSSFRow row = sheet.createRow((short) 1);

// 폰트 높이는 24, 폰트 종류는 Courier New, 이탈릭체로 설정한다
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);

// 설정한 폰트를 스타일에 적용한다
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);

// Cell에 스타일을 적용한다
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of fonts");
cell.setCellStyle(style);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

② 결과

=============================================

본문서는 자유롭게 배포/복사 할수 있지만

이문서의 저자에 대한 언급을 삭제하시면 안됩니다

저자 : GoodBug (unicorn@jakartaproject.com)

최초 : http://www.jakartaproject.com 

=============================================