apache poi를 이용한 엑셀생성시 몇가지 옵션 정리(XSSF중심 예제)

사용하면서 정리한 몇가지 옵션이다.

주로 사용자가 다운로드받은 엑셀의 프린트출력을 바로 할수 있도록 하기 위한 내용 중심이다.

HSSF보다는 최근의 excel은 대부분 xlsx를 사용하므로 XSSF를 사용.

 

/*
 * POI Library 용어 정리
	HSSF : 엑셀 파일을 다루는데 사용 (엑셀 97 이후~)
	XSSF : 엑셀 2007 이후의 xlsx 파일을 다루는데 사용
	HPSF : 오피스 파일의 문서요약 정보를 다루는데 사용
	HWPF : 워드 파일을 다루는데 사용
	HSLF : 파워포인트 파일을 다루는데 사용
	HDGF : 비지오 파일을 다루는데 사용
	HPBF : 퍼블리셔 파일을 다루는데 사용
	HSMF : 아웃룻의 *.msg 파일을 다루는데 사용
	DDF : 아웃룩의 이미지 파일을 다루는데 사용
 */
package com.test.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;


/**
 *
 *  상세내용 엑셀 변환용 
 * 1. 타이틀은 회색, 페이지별로 반복
 * 2. 좌우여백 : 0.5 inch
 * 3. 페이지 레이아웃 : 가로는 1페이지로, 세로는 자동으로
 * 4. font size : 10, 타이틀은 11
 * 5. footer 중앙에 페이지번호 추가
 * 6. footer 좌우에 내용추가
 * 7. Horizontal center 정렬
 * 8. 각 컬럼사이즈는 고정으로 처리 (내용에 따라 일부 변경 필요)
 * 9. cell 정렬은 Vertical은 Center
 * 10. Cell내용은 줄바뀜 제거 ( wordWrap )
 */
@SpringBootTest
public class TestWriteExcel {

	@Test
	public void mainTest() {
		TestWriteExcel rww = new TestWriteExcel();

		try {

			rww.createExcel();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	void createExcel() {

		XSSFWorkbook xlsxWB = new XSSFWorkbook();

		// Sheet 이름 설정하기
		XSSFSheet sheet1 = xlsxWB.createSheet("202103_상세내역");
		sheet1.setFitToPage(true);
		sheet1.setAutobreaks(true);
			
//		double leftMarginInches = sheet.getMargin(Sheet.LeftMargin);
		sheet1.setMargin(Sheet.LeftMargin, 0.5 /* inches */ );
		sheet1.setMargin(Sheet.RightMargin, 0.5 /* inches */ );
		sheet1.setRepeatingRows(CellRangeAddress.valueOf("1"));
		
//		Header header = sheet1.getHeader();
		
		//page 번호 추가
		Footer footer = sheet1.getFooter();
        footer.setCenter( HeaderFooter.page() + "/" + HeaderFooter.numPages() );  
        footer.setLeft("회사명or 프로젝트명");
        footer.setRight("202103_상세내역");
		       
		
		XSSFPrintSetup ps = sheet1.getPrintSetup();
		ps.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
		
		//확대/축소비율
//		ps.setScale((short)90); // 아래옵션이 있다면 설정 필요없음.
		ps.setFitWidth((short)1);  //가로는 1페이지에
		ps.setFitHeight((short)0); //세로는 자동으로
		
		sheet1.setHorizontallyCenter(true); //출력시 가로정렬 Center로
		
		// 컬럼의 너비 설정
//		sheet1.autoSizeColumn(1); // 행 번호
		
		//타이틀 Cell 스타일
		XSSFCellStyle cellStyle = xlsxWB.createCellStyle();
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);

		//타이틀 Cell 색상
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 밝은 Grey
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 
		
		cellStyle.setWrapText(true); //줄바뀜처리
		cellStyle.setAlignment(HorizontalAlignment.CENTER);  //가로정렬
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //세로정렬


		//내용 Cell 스타일
		XSSFCellStyle cellStyle2 = xlsxWB.createCellStyle();
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //세로정렬
		
		cellStyle2.setBorderBottom(BorderStyle.THIN);
		cellStyle2.setBorderTop(BorderStyle.THIN);
		cellStyle2.setBorderRight(BorderStyle.THIN);
		cellStyle2.setBorderLeft(BorderStyle.THIN);
		
        //내용은 크기에 따른 줄 바꿈 필요없음.(Default)
		//cellStyle2.setWrapText(true);
		Font font = xlsxWB.createFont();
		font.setFontHeightInPoints((short) 10);
		cellStyle2.setFont(font);
		

		XSSFRow row = null;
		XSSFCell cell = null;
		String[] titles = {
				"학교명", 
				"ID",
				"이름",
				"등록일시",
				"출석\n일시",
				"비고",
				"검사명",
				"담당자"
				};
		
		int i = 0;
		
        //각 컬럼별 사이즈 지정
		sheet1.setColumnWidth(i++, 3300);//
		sheet1.setColumnWidth(i++, 1700);//
		sheet1.setColumnWidth(i++, 2500);// 이름
		sheet1.setColumnWidth(i++, 3200);// 등록일시
		sheet1.setColumnWidth(i++, 3200);// 출석일시
		sheet1.setColumnWidth(i++, 3000);// 비고
		sheet1.setColumnWidth(i++, 3000);// 검사명
		sheet1.setColumnWidth(i++, 2200);// 담당자

		// 첫 행 만들기
		row = sheet1.createRow(0);
		
		int j=0;
		for (String string : titles) {			
			cell = row.createCell(j++);
			cell.setCellValue(string);
			cell.setCellStyle(cellStyle);
		}		

		String[] cellValues = {
				"데이터제목", 
				"A234567890",
				"홍길동입니다",
				"2020-12-23 14:55",
				"2020-12-25 14:55",
				"Test",
				"신장, 몸무게",
				"김선생"
				};
		
		CellType[] cellTypes = {
				CellType.STRING,
				CellType.STRING,
				CellType.STRING,
				CellType.STRING,
				CellType.STRING,
				CellType.STRING,
				CellType.STRING,
				CellType.STRING
		};
		
        //100개의 Row를 테스트로 입력하여 페이지 넘어가는 것 확인
		for(int l=1;l<100;l++) {
			// 두번째 행부터 내용입력됨
			row = sheet1.createRow(l);
			
			for (int k=0;k < cellValues.length;k++) {
				
				String string = cellValues[k];
				cell = row.createCell(k);
				cell.setCellValue(string);
				cell.setCellStyle(cellStyle2);
				cell.setCellType(cellTypes[k]);
			}			
		}
	
		// 파일저장
		String filename = "sample_" + Calendar.getInstance().getTimeInMillis() + ".xlsx";
		try {
			File xlsFile = new File("d:\\" + filename);
			FileOutputStream fileOut = new FileOutputStream(xlsFile);
			xlsxWB.write(fileOut);
			
			System.out.println(filename + " is created");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}