[Java] Apache POI Excel 작업

build.gradle

    // excel Apache POI
    implementation 'org.apache.poi:poi:4.1.2'
    implementation 'org.apache.poi:poi-ooxml:4.1.2'

Excel 읽기

• 예시

public static void main(String[] args) {
      
        try {
            FileInputStream file = new FileInputStream(new File("example.xlsx"));
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);
            DataFormatter formatter = new DataFormatter();
            for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.print(formatter.formatCellValue(cell) + "\t");   // 탭으로 구분
                }
                System.out.println();   // 줄바꿈
            }
            file.close();
            System.out.println("엑셀에서 데이터 읽어오기 끝");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

• 셀 타입을 고려하여 읽기(업그레이드)

    public static void main(String[] args) {
       
               try {
            FileInputStream file = new FileInputStream(new File("example.xlsx"));
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                Date dateValue = cell.getDateCellValue();
                                DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                String formattedDate = dateFormat.format(dateValue);
                                System.out.print(formattedDate);
                            } else {
                                double numericValue = cell.getNumericCellValue();
                                //  정수인지 실수인지 구분
                                // 정수
                                if (numericValue == Math.floor(numericValue)) {
                                    int intValue = (int) numericValue;
                                    System.out.print(intValue);
                                    // 실수
                                } else {
                                    System.out.print(numericValue + "\t");
                                }
                            }
                            break;
                        case STRING:
                            DataFormatter formatter = new DataFormatter();
                            String stringValue = formatter.formatCellValue(cell);
                            System.out.print(stringValue + "\t");
                            break;
                        case BOOLEAN:
                            boolean booleanValue = cell.getBooleanCellValue();
                            System.out.print(booleanValue + "\t");
                            break;
                        case FORMULA:
                            String formulaValue = cell.getCellFormula();
                            System.out.print(formulaValue + "\t");
                            break;
                        case BLANK:
                            System.out.print("\t");
                            break;
                        default:
                            System.out.print("\t");
                            break;
                    }
                }
                System.out.println();   // 줄바꿈
            }
            file.close();
            System.out.println("엑셀에서 데이터 읽어오기 끝");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

• 결과

Excel 생성 및 쓰기

• 예시

public class ExcelWriter {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        List<MemberVO> members = new ArrayList<>();

        while (true) {
            System.out.print("이름을 입력하세요:");
            String name = scanner.nextLine();
            if (name.equals("quit")) {
                //System.exit(-1);
                break;
            }

            System.out.print("나이를 입력하세요:");
            int age = scanner.nextInt();
            scanner.nextLine(); // 개행문자 제거

            System.out.print("생년월일을 입력하세요:");
            String birthdate = scanner.nextLine();

            System.out.print("전화번호를 입력하세요:");
            String phone = scanner.nextLine();

            System.out.print("주소를 입력하세요:");
            String address = scanner.nextLine();

            System.out.print("결혼여부를 입력하세요 (true/false):");
            boolean isMarried = scanner.nextBoolean();
            scanner.nextLine(); // 개행문자 제거

            MemberVO member = new MemberVO(name, age, birthdate, phone, address, isMarried);
            members.add(member);
        }
        scanner.close();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("회원 정보");

            // 헤더 생성
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("이름");
            headerRow.createCell(1).setCellValue("나이");
            headerRow.createCell(2).setCellValue("생년월일");
            headerRow.createCell(3).setCellValue("전화번호");
            headerRow.createCell(4).setCellValue("주소");
            headerRow.createCell(5).setCellValue("결혼여부");

            // 헤더 색깔 변경
            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 헤더 가운데 정렬
            headerStyle.setAlignment(HorizontalAlignment.CENTER);

            // 글자 굵기 진하게 변경
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);
            // 테두리 전체 적용
            headerStyle.setBorderTop(BorderStyle.THIN);
            headerStyle.setBorderBottom(BorderStyle.THIN);
            headerStyle.setBorderLeft(BorderStyle.THIN);
            headerStyle.setBorderRight(BorderStyle.THIN);

            for (Cell cell : headerRow) {
                cell.setCellStyle(headerStyle);
            }

            // 데이터 생성
            for (int i = 0; i < members.size(); i++) {
                MemberVO member = members.get(i);
                Row row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(member.getName());
                row.createCell(1).setCellValue(member.getAge());
                row.createCell(2).setCellValue(member.getBirthdate());
                row.createCell(3).setCellValue(member.getPhone());
                row.createCell(4).setCellValue(member.getAddress());
                Cell marriedCell = row.createCell(5);
                marriedCell.setCellValue(member.isMarried());
            }
            // 엑셀 파일 저장
            String filename = "members.xlsx";
            FileOutputStream outputStream = new FileOutputStream(new File(filename));
            workbook.write(outputStream);
            workbook.close();
            System.out.println("엑셀 파일이 저장되었습니다: " + filename);
        } catch (IOException e) {
            System.out.println("엑셀 파일 저장 중 오류가 발생했습니다.");
            e.printStackTrace();
        }
    }
}

결과