Java POI 를 이용하여 Excel 파일 생성 및 쓰기

Java에서 POI를 이용하여 Excel 파일을 생성하는 예제이다.

ExcelUtil.java

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.HashMap;
import java.util.Map;

public class ExcelUtil {

    /**
     * Create a library of cell styles.
     */
    public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
        Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
        XSSFDataFormat fmt = wb.createDataFormat();

        XSSFCellStyle style1 = wb.createCellStyle();
        style1.setAlignment(HorizontalAlignment.RIGHT);
        style1.setDataFormat(fmt.getFormat("0.0%"));
        styles.put("percent", style1);

        XSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setDataFormat(fmt.getFormat("0.0X"));
        styles.put("coeff", style2);

        XSSFCellStyle style3 = wb.createCellStyle();
        style3.setAlignment(HorizontalAlignment.RIGHT);
        style3.setDataFormat(fmt.getFormat("$#,##0.00"));
        styles.put("currency", style3);

        XSSFCellStyle style4 = wb.createCellStyle();
        style4.setAlignment(HorizontalAlignment.RIGHT);
        style4.setDataFormat(fmt.getFormat("mmm dd"));
        styles.put("date", style4);

        XSSFCellStyle style5 = wb.createCellStyle();
        style5.setAlignment(HorizontalAlignment.RIGHT);
        style5.setDataFormat(fmt.getFormat("yyyy-MM-dd hh:mm:ss.000"));
        styles.put("datetime", style5);

        XSSFCellStyle style6 = wb.createCellStyle();
        XSSFFont headerFont = wb.createFont();
        headerFont.setFontName("맑은 고딕");
        headerFont.setBold(true);
        style6.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style6.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style6.setAlignment(HorizontalAlignment.CENTER);
        style6.setFont(headerFont);
        styles.put("header", style6);

        return styles;
    }


    /**
     * Column Name으로 Column Index 얻기 --> AA 같은 것을 읽을 수 있도록 수정해야 함
     * @param columnName
     * @return
     */
    public static int getColumnIndexByName(String columnName) {

        int columnIndex = 0;
        char[] tempCharArray = columnName.toCharArray();
        for(int i=0; i<tempCharArray.length; i++) {
            int tempIndex = (int)tempCharArray[i] - 65;
            columnIndex = columnIndex + 26*(tempCharArray.length-i-1) + tempIndex;
        }

        return columnIndex;
    }



    // Sheet로 부터 Row를 취득, 생성하기
    public static Row getRow(Sheet sheet, int rownum) {
        Row row = sheet.getRow(rownum);
        if (row == null) {
            row = sheet.createRow(rownum);
        }
        return row;
    }

    public static Cell getCell(Row row, int cellnum) {
        return getCell(row, cellnum, CellType.STRING, null) ;
    }

    public static Cell getCell(Row row, int cellnum, CellStyle defaultCellStyle) {
        return getCell(row, cellnum, CellType.STRING, defaultCellStyle) ;
    }

    // Row로 부터 Cell를 취득, 생성하기
    public static Cell getCell(Row row, int cellnum, CellType defaultCellType, CellStyle defaultCellStyle) {
        Cell cell = row.getCell(cellnum);
        if (cell == null) {
            cell = row.createCell(cellnum, defaultCellType);
            if(defaultCellStyle != null) {
                cell.setCellStyle(defaultCellStyle);
            }
        }

        return cell;
    }

    public static Cell getCell(Row row, String columnName) {
        return getCell(row, columnName, CellType.STRING, null);
    }

    public static Cell getCell(Row row, String columnName, CellStyle defaultCellStyle) {
        return getCell(row, columnName, CellType.STRING, defaultCellStyle);
    }

    /**
     * Row와 Colum Name으로 Cell 얻기
     * @param row
     * @param columnName
     * @return
     */
    public static Cell getCell(Row row, String columnName, CellType defaultCellType, CellStyle defaultCellStyle) {
        int columIndex = getColumnIndexByName(columnName);
        Cell cell = getCell(row, columIndex, defaultCellType, defaultCellStyle);

        return cell;
    }

    public static Cell getCell(Sheet sheet, int rownum, int cellnum) {
        return getCell(sheet, rownum, cellnum, CellType.STRING, null);
    }

    public static Cell getCell(Sheet sheet, int rownum, int cellnum, CellType defaultCellType) {
        return getCell(sheet, rownum, cellnum, defaultCellType, null);
    }

    public static Cell getCell(Sheet sheet, int rownum, int cellnum, CellType defaultCellType, CellStyle defaultCellStyle) {
        Row row = getRow(sheet, rownum);
        return getCell(row, cellnum, defaultCellType, defaultCellStyle);
    }

    public static Cell getCell(Sheet sheet, String nameBoxValue) {
        return getCell(sheet, nameBoxValue, CellType.STRING, null);
    }

    public static Cell getCell(Sheet sheet, String nameBoxValue, CellType defaultCellType) {
        return getCell(sheet, nameBoxValue, defaultCellType, null);
    }

    public static Cell getCell(Sheet sheet, String nameBoxValue, CellStyle defaultCellStyle) {
        return getCell(sheet, nameBoxValue, CellType.STRING, defaultCellStyle);
    }

    /**
     * Cell Name Box Value로 Cell 얻기
     * @param sheet
     * @param nameBoxValue
     * @return
     */
    public static Cell getCell(Sheet sheet, String nameBoxValue, CellType defaultCellType, CellStyle defaultCellStyle) {
        int tempCount = 0;
        for(int i=0; i<nameBoxValue.length(); i++) {
            char tempChar = nameBoxValue.charAt(i);
            if( (tempChar >= 'a' && tempChar <= 'z') || (tempChar >= 'A' && tempChar <= 'Z')) {
                tempCount++;
            }
        }

        int rowIndex = Integer.parseInt(nameBoxValue.substring(tempCount)) - 1;
        int columIndex = getColumnIndexByName(nameBoxValue.substring(0,tempCount));

        return getCell(sheet, rowIndex, columIndex, defaultCellType, defaultCellStyle);
    }


    /**
     * Cell 각종 타입을 String Value로 변경
     * @param cell
     * @return
     */
    public static String convertCellTypeStringValue(Cell cell) {
        String returnValue = "";

        if(cell != null) {
            CellType cellType = cell.getCellType();
            if(cellType == CellType.BLANK) {
                returnValue = "";
            }
            else if(cellType == CellType.BOOLEAN) {
                returnValue = String.valueOf(cell.getBooleanCellValue());
            }
            else if(cellType == CellType.FORMULA) {
                returnValue = cell.getCellFormula();
            }
            else if(cellType == CellType.NUMERIC) {
                returnValue = String.valueOf(cell.getNumericCellValue());
            }
            else if(cellType == CellType.STRING) {
                returnValue = cell.getStringCellValue();
            }
        }

        return returnValue.trim();
    }
}

ApplicationService.java

public String actionPrintExcel(String conditionWhere) {

	String outputFilePath = null;

	FileOutputStream fos = null;
	XSSFWorkbook workbook = null;

	try {
		Path tempPath = Paths.get("c:\\temp");
		if (!Files.exists(tempPath)) {
			Files.createDirectory(tempPath);
		}

		outputFilePath = tempPath + "\\" + System.currentTimeMillis();

		workbook = new XSSFWorkbook();
		Map<String, XSSFCellStyle> styleMap = ExcelUtil.createStyles(workbook);

		XSSFSheet sheet = workbook.createSheet();

		//Header
		Cell productCodeHeaderCell = ExcelUtil.getCell(sheet, "A1", styleMap.get("header")); //반제품 코드
		productCodeHeaderCell.setCellValue("반제품 코드");
		Cell productNameHeaderCell = ExcelUtil.getCell(sheet, "B1", styleMap.get("header")); //반제품 명
		productNameHeaderCell.setCellValue("반제품 명");
		Cell rawMaterialCodeHeaderCell = ExcelUtil.getCell(sheet, "C1", styleMap.get("header")); //원자재 코드
		rawMaterialCodeHeaderCell.setCellValue("원자재 코드");
		Cell lotNoHeaderCell = ExcelUtil.getCell(sheet, "D1", styleMap.get("header")); //Lot No.
		lotNoHeaderCell.setCellValue("Lot No.");
		Cell inputOutputHeaderCell = ExcelUtil.getCell(sheet, "E1", styleMap.get("header")); //타입
		inputOutputHeaderCell.setCellValue("입고/출고");
		Cell qtyHeaderCell = ExcelUtil.getCell(sheet, "F1", styleMap.get("header")); //수량
		qtyHeaderCell.setCellValue("수량");
		Cell cncHeaderCell = ExcelUtil.getCell(sheet, "G1", styleMap.get("header")); //호기
		cncHeaderCell.setCellValue("호기");
		Cell workerHeaderCell = ExcelUtil.getCell(sheet, "H1", styleMap.get("header")); //작업자
		workerHeaderCell.setCellValue("작업자");
		Cell createDateHeaderCell = ExcelUtil.getCell(sheet, "I1", styleMap.get("header")); //생성일
		createDateHeaderCell.setCellValue("생성일");

		//Data
		List<FRMMAction> actionList = getActionList(conditionWhere, 1, 1000000);
		for(int i=0; i<actionList.size(); i++) {
			FRMMAction action = actionList.get(i);

			Cell productCodeCell = ExcelUtil.getCell(sheet, i+1, 0, CellType.STRING); //반제품 코드
			productCodeCell.setCellValue(action.getProductCode());
			Cell productNameCell = ExcelUtil.getCell(sheet, i+1, 1, CellType.STRING); //반제품 명
			productNameCell.setCellValue(action.getProductName());
			Cell rawMaterialCodeCell = ExcelUtil.getCell(sheet, i+1, 2, CellType.STRING); //원자재 코드
			rawMaterialCodeCell.setCellValue(action.getMaterialCode());
			Cell lotNoCell = ExcelUtil.getCell(sheet, i+1, 3, CellType.STRING); //Lot No.
			lotNoCell.setCellValue(action.getLotNo());
			Cell inputOutputCell = ExcelUtil.getCell(sheet, i+1, 4, CellType.STRING); //타입
			inputOutputCell.setCellValue(action.getIoType());
			Cell qtyCell = ExcelUtil.getCell(sheet, i+1, 5, CellType.NUMERIC); //수량
			qtyCell.setCellValue(action.getQty());
			Cell cncCell = ExcelUtil.getCell(sheet, i+1, 6, CellType.STRING); //호기
			cncCell.setCellValue(action.getCncName());
			Cell workerCell = ExcelUtil.getCell(sheet, i+1, 7, CellType.STRING); //작업자
			workerCell.setCellValue(action.getWorker());
			Cell createDateCell = ExcelUtil.getCell(sheet, i+1, 8, CellType.STRING, styleMap.get("datetime")); //생성일
			createDateCell.setCellValue(action.getCreateDate());
		}

		// Column Auto Size
		for(int colNum = 0; colNum < ExcelUtil.getRow(sheet, 0).getLastCellNum(); colNum++)
			sheet.autoSizeColumn(colNum);

		fos = new FileOutputStream(outputFilePath);
		workbook.write(fos);

	}
	catch (Exception e) {
		outputFilePath = null;
		e.printStackTrace();
	}
	finally {

		if( workbook!= null) {
			try {
				workbook.close();
			}
			catch (Exception e) {
				outputFilePath = null;
				e.printStackTrace();
			}
		}

		if(fos!= null) {
			try {
				fos.close();
			}
			catch (Exception e) {
				outputFilePath = null;
				e.printStackTrace();
			}
		}
	}

	return outputFilePath;
}