Apache POI 사용한 엑셀 파일 생성

Apache POI 란?

아파치 POI(Apache POI)는 아파치 소프트웨어 재단에서 만든 라이브러리로서 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로서 읽고 쓰는 기능을 제공한다. 주로 워드, 엑셀, 파워포인트와 파일을 지원하며 최근의 오피스 포맷인 Office Open XML File Formats [1] (OOXML, 즉 xml 기반의 *.docx, *.xlsx, *.pptx 등) 이나 아웃룩, 비지오, 퍼블리셔 등으로 지원 파일 포맷을 늘려가고 있다.

url : https://poi.apache.org/

종류

• HSSF : 엑셀 97~2003 버전
• XSSF : 엑셀 2007 이상
• SXSSF : XSSF 성능 개선

사용 방법

예

public class ExcelWriteService {

    public void excelWrite() throws IOException {
        Workbook wb = null;
        FileOutputStream fout = null;
        String outputFilePath = "/Users/soon/Desktop/poi/out.xlsx";

        try {
            wb = new SXSSFWorkbook();

            Row row;
            Cell cell;
            Sheet sheet;

            String sheetName = WorkbookUtil.createSafeSheetName("sheet1");                        

            sheet = wb.createSheet(sheetName);

            Font fontTitle = wb.createFont();
            fontTitle.setFontHeightInPoints((short) 20);

            CellStyle styleTitle = wb.createCellStyle();            
            styleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
            styleTitle.setAlignment(HorizontalAlignment.CENTER);
            styleTitle.setFont(fontTitle);

            row = sheet.createRow(0);            
            cell = row.createCell(0);
            cell.setCellStyle(styleTitle);
            cell.setCellValue("내역서");           

            sheet.addMergedRegion(new CellRangeAddress(
                    0, //first row (0-based)
                    0, //last row  (0-based)
                    0, //first column (0-based)
                    7  //last column  (0-based)
            ));            

            row = sheet.createRow(2);
            row.createCell(0).setCellValue("수 신 : ");

            row = sheet.createRow(3);
            row.createCell(0).setCellValue("참 조 : ");

            row = sheet.createRow(4);
            row.createCell(0).setCellValue("T E L : ");

            row = sheet.createRow(5);
            row.createCell(0).setCellValue("F A X : ");

            row = sheet.createRow(6);
            row.createCell(0).setCellValue("작성일자 : ");

            row = sheet.createRow(7);
            row.createCell(0).setCellValue("처리일 : ");

            //

            Font fontHeader = wb.createFont();
            fontHeader.setFontHeightInPoints((short) 12);

            CellStyle styleHeader = wb.createCellStyle();
            styleHeader.setBorderTop(BorderStyle.THIN);
            styleHeader.setBorderBottom(BorderStyle.THIN);
            styleHeader.setBorderLeft(BorderStyle.THIN);
            styleHeader.setBorderRight(BorderStyle.THIN);
            styleHeader.setVerticalAlignment(VerticalAlignment.CENTER);
            styleHeader.setAlignment(HorizontalAlignment.CENTER);
            styleHeader.setFont(fontHeader);

            row = sheet.createRow(9);

            cell = row.createCell(0);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("처리일자");

            cell = row.createCell(1);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("오류구분");

            cell = row.createCell(2);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("요청사항");

            cell = row.createCell(3);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("처리사항");

            cell = row.createCell(4);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("요청자");

            cell = row.createCell(5);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("처리자");

            cell = row.createCell(6);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("소요시간");

            cell = row.createCell(7);
            cell.setCellStyle(styleHeader);
            cell.setCellValue("처리상태");

            Font fontBody = wb.createFont();
            fontBody.setFontHeightInPoints((short) 12);

            CellStyle styleBody = wb.createCellStyle();
            styleBody.setBorderTop(BorderStyle.THIN);
            styleBody.setBorderBottom(BorderStyle.THIN);
            styleBody.setBorderLeft(BorderStyle.THIN);
            styleBody.setBorderRight(BorderStyle.THIN);
            styleBody.setVerticalAlignment(VerticalAlignment.CENTER);           
            styleBody.setFont(fontBody);

            for(int j=10 ; j<20 ; j++) {
                row = sheet.createRow(j);

                cell = row.createCell(0);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(1);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(2);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(3);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(4);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(5);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(6);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");

                cell = row.createCell(7);
                cell.setCellStyle(styleBody);
                cell.setCellValue("test");
            }


            fout = new FileOutputStream(outputFilePath);
            wb.write(fout);


        }catch(Exception e) {
            log.error("excelWrite error : {}", e.getMessage());
        }finally {
            fout.close();
            ((SXSSFWorkbook)wb).dispose();
        }
    }

    public static void main(String[] args) throws IOException {
        System.out.println("ex start");
        ExcelWriteService ew = new ExcelWriteService();
        ew.excelWrite();
        System.out.println("ex end");
    }

}

참고

• https://poi.apache.org/components/spreadsheet/quick-guide.html#NewWorkbook
• https://qiita.com/tool-taro/items/4b3c802bb114a9110ecb