[JAVA] 엑셀 파일(.xlsx) 쓰기 / 스타일 적용

 

▶ previous

1 POI 라이브러리 다운로드 & 프로젝트에 적용
2 엑셀파일(.xlsx) 읽어오기 & list에 저장

 

[ 엑셀파일(.xlsx) 쓰기(생성)/저장/스타일 적용 ]

 

1 Font (글꼴)

XSSFFont Font = workbook.createFont();
→ 폰트 스타일 생성
Font.setFontHeightInPoints((short)14);
→ 글자 크기
Font.setFontName("맑은 고딕");
→ 글꼴 지정
Font.setBold(true);
→ 진하게(true)

Style.setFont(Font);
→ 만들어놓은 CellStyle에 폰트 적용

 

---

 

2 CellStyle

CellStyle Style = workbook.createCellStyle();
→ 셀 스타일 생성
Style.setAlignment(HorizontalAlignment.CENTER);
→ 가운데정렬
Style.setVerticalAlignment(VerticalAlignment.CENTER);
→ 세로 기준 가운데정렬
Style.setBorderTop(BorderStyle.THIN);
Style.setBorderBottom(BorderStyle.THIN);
Style.setBorderLeft(BorderStyle.THIN);
Style.setBorderRight(BorderStyle.THIN);
→ 가는선 테두리

Style.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
Style.setFillPattern(FillPAtternType.SOLID_FOREGROUND);
→ 셀 배경색 지정 (얘네 둘은 쎗뚜쎗뚜)

cell.setCellStyle(Style);
→ 셀에 스타일 적용

 

---

 

3 writeExcel() 엑셀 작성, 저장

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
→ workbook & sheet 생성

curRow = sheet.createRow(n);
→ n 행 생성
cell = curRow.createCell(n);
→ 생성된 n행(curRow)에 셀 생성
cell.setCellValue("...");
→ 생성된 셀에 값 넣기

sheet.setColumnWidth(index, 너비);
→ 너비 지정

workbook.write(fileout);
fileout.close();
→ 작성 후 저장, 종료

public void writeExcel(ArrayList<exData> list) {
	String path = Work4.class.getResource("").getPath();	//저장할 파일 경로
		
	try {
		File file = new File(path + "ExcelExFile.xlsx");
		FileOutputStream fileout = new FileOutputStream(file);
			
		XSSFWorkbook xworkbook = new XSSFWorkbook();
			
		XSSFSheet xsheet = xworkbook.createSheet("메가박스");		//시트 생성
		XSSFRow curRow;
			
		int row = list.size();		//리스트 크기
		Cell cell = null;
			
		//Title
		curRow = xsheet.createRow(0);
		cell = curRow.createCell(0);
		cell.setCellValue("전국 메가박스 지점");
			
		//Head
		curRow = xsheet.createRow(1);
		cell = curRow.createCell(0);
		cell.setCellValue("이름");
			
		cell = curRow.createCell(1);
		cell.setCellValue("비율");
			
		cell = curRow.createCell(2);
		cell.setCellValue("%");
			
		//Body
		for(int i=2;i<row;i++){
			curRow = xsheet.createRow(i); // row 생성
				
			cell = curRow.createCell(0);
			cell.setCellValue(list.get(i).getName());
                
			//이름이 '동탄'지점의 행에 PointStyle 적용
			if(list.get(i).getName().equals("동탄")) {
				cell.setCellStyle(PointStyle);
			} else{ cell.setCellStyle(BodyStyle); }
                
			cell = curRow.createCell(1);
			cell.setCellValue(list.get(i).getNum());
                
			//셀스타일
			if(list.get(i).getName().equals("동탄")) {
				cell.setCellStyle(PointStyle);
			} else{ cell.setCellStyle(BodyStyle); }
                
			cell = curRow.createCell(2);
			cell.setCellValue(list.get(i).getPer());
                
			//셀스타일
			if(list.get(i).getName().equals("동탄")) {
				cell.setCellStyle(PointStyle);
			} else{ cell.setCellStyle(BodyStyle); }
		}
			
		//열 너비 설정
		for(int i=0;i<3;i++) {
			xsheet.autoSizeColumn(i);
			xsheet.setColumnWidth(i, (xsheet.getColumnWidth(i))+256);
		}
			
		xworkbook.write(fileout);
		fileout.close();
			
	} catch(FileNotFoundException e) {
		e.printStackTrace();
	} catch(IOException e) {
		e.printStackTrace();
	}
}

 

---

wirteExcel() 전체 코드

public void writeExcel(ArrayList<exData> list) {
	String path = Work4.class.getResource("").getPath();	//저장할 파일 경로
		
	try {
		File file = new File(path + "ExcelExFile.xlsx");
		FileOutputStream fileout = new FileOutputStream(file);
			
		XSSFWorkbook xworkbook = new XSSFWorkbook();
			
		//Title
		XSSFFont TitleFont = xworkbook.createFont();
		TitleFont.setFontHeightInPoints((short)14);
		TitleFont.setFontName("맑은 고딕");
		TitleFont.setBold(true);
			
		CellStyle TitleStyle = xworkbook.createCellStyle();
		TitleStyle.setAlignment(HorizontalAlignment.CENTER);
		TitleStyle.setFont(TitleFont);
			
		//Body
		XSSFFont BodyFont = xworkbook.createFont();
		BodyFont.setFontHeightInPoints((short)11);
		BodyFont.setFontName("맑은 고딕");
			
		CellStyle BodyStyle = xworkbook.createCellStyle();
		BodyStyle.setAlignment(HorizontalAlignment.CENTER);
		BodyStyle.setBorderTop(BorderStyle.THIN);
		BodyStyle.setBorderBottom(BorderStyle.THIN);
		BodyStyle.setBorderLeft(BorderStyle.THIN);
		BodyStyle.setBorderRight(BorderStyle.THIN);
		BodyStyle.setFont(BodyFont);
			
		//Point
		CellStyle PointStyle = xworkbook.createCellStyle();
		PointStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
		PointStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		PointStyle.setAlignment(HorizontalAlignment.CENTER);
		PointStyle.setBorderTop(BorderStyle.THIN);
		PointStyle.setBorderBottom(BorderStyle.THIN);
		PointStyle.setBorderLeft(BorderStyle.THIN);
		PointStyle.setBorderRight(BorderStyle.THIN);
		PointStyle.setFont(BodyFont);
			
			//Header
		XSSFFont HeadFont = xworkbook.createFont();
		HeadFont.setBold(true);
		HeadFont.setFontHeightInPoints((short)11);
		HeadFont.setFontName("맑은 고딕");
			
		CellStyle HeadStyle = xworkbook.createCellStyle();
		HeadStyle.setAlignment(HorizontalAlignment.CENTER);
		HeadStyle.setFillForegroundColor(HSSFColorPredefined.GREY_25_PERCENT.getIndex());
		HeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		HeadStyle.setBorderTop(BorderStyle.THIN);
		HeadStyle.setBorderBottom(BorderStyle.THIN);
		HeadStyle.setBorderLeft(BorderStyle.THIN);
		HeadStyle.setBorderRight(BorderStyle.THIN);
		HeadStyle.setFont(HeadFont);
			
		XSSFSheet xsheet = xworkbook.createSheet("메가박스");		//시트 생성
		XSSFRow curRow;
			
		int row = list.size();		//리스트 크기
		Cell cell = null;
			
		//Title
		curRow = xsheet.createRow(0);
		cell = curRow.createCell(0);
		cell.setCellValue("전국 메가박스 지점");
		cell.setCellStyle(TitleStyle);
			
		//Head
		curRow = xsheet.createRow(1);
		cell = curRow.createCell(0);
		cell.setCellValue("이름");
		cell.setCellStyle(HeadStyle);
			
		cell = curRow.createCell(1);
		cell.setCellValue("비율");
		cell.setCellStyle(HeadStyle);
			
		cell = curRow.createCell(2);
		cell.setCellValue("%");
		cell.setCellStyle(HeadStyle);
			
		//Body
		for(int i=2;i<row;i++){
			curRow = xsheet.createRow(i); // row 생성
				
			cell = curRow.createCell(0);
			cell.setCellValue(list.get(i).getName());
                
			//셀스타일
			if(list.get(i).getName().equals("동탄")) {
				cell.setCellStyle(PointStyle);
			} else{ cell.setCellStyle(BodyStyle); }
                
			cell = curRow.createCell(1);
			cell.setCellValue(list.get(i).getNum());
                
			//셀스타일
			if(list.get(i).getName().equals("동탄")) {
				cell.setCellStyle(PointStyle);
			} else{ cell.setCellStyle(BodyStyle); }
                
			cell = curRow.createCell(2);
			cell.setCellValue(list.get(i).getPer());
                
			//셀스타일
			if(list.get(i).getName().equals("동탄")) {
				cell.setCellStyle(PointStyle);
			} else{ cell.setCellStyle(BodyStyle); }
		}
			
		//열 너비 설정
		for(int i=0;i<3;i++) {
			xsheet.autoSizeColumn(i);
			xsheet.setColumnWidth(i, (xsheet.getColumnWidth(i))+256);
		}
			
		xworkbook.write(fileout);
		fileout.close();
			
	} catch(FileNotFoundException e) {
		e.printStackTrace();
	} catch(IOException e) {
		e.printStackTrace();
	}
}