[Java] POI 라이브러리로 데이터 엑셀 다운받기 + 셀 병합

 

Apache POI(Poor Obfuscation Implementation)란

 

Apache POI는 아파치 소프트웨어 재단에 의해 운영되는 오픈소스 프로젝트 입니다. 순수 자바 라이브러리로서 Microsoft Office의 Word, PowerPoint, Excel 형식의 파일을 읽고 쓸 수 있게 해주며 최근의 오피스 포맷인 Office Open XML File Format도 지원해줍니다.

---

Apache POI 라이브러리 적용하기

maven Repository(https://mvnrepository.com/artifact/org.apache.poi/poi)에 방문해서 poi를 검색해서 나온 결과를 보면 아래와 같습니다.

일단 최신 버전 중 다운로드가 많은 5.0.x를 들어가서 Maven 혹은 Gradle에 넣을 소스를 복사하여 붙혀 넣습니다.

---

POI를 활용하여 xls 파일 만들기

 

 

1. Excel을 만들 Controller 생성

먼저 Exceldownload를 호출할 Controller를 만들고 url을 생성합니다.

그리고 엑셀에 들어갈 데이터를 임시로 만듭니다.(만일 데이터베이스에서 데이터를 가져올 수 있다면 데이터베이스 정보를 가져와도 됩니다.)

package core;

import java.io.IOException;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class excelExport {

	@GetMapping("/downloadExel")
	public void downloadExel(HttpServletResponse response) throws IOException {
		// 엑셀에 들어갈 데이터 생성
		exem[] list = {
				new exem(1,"문항 1","내용 1","데이터 1","설명 1"),
				new exem(2,"문항 2","내용 2","데이터 2","설명 2"),
				new exem(3,"문항 3","내용 3","데이터 3","설명 3"),
				new exem(4,"문항 4","내용 4","데이터 4","설명 4"),
				new exem(5,"문항 5","내용 5","데이터 5","설명 5"),
				new exem(6,"문항 6","내용 6","데이터 6","설명 6"),
				new exem(7,"문항 7","내용 7","데이터 7","설명 7"),
				new exem(8,"문항 8","내용 8","데이터 8","설명 8"),
				new exem(9,"문항 9","내용 9","데이터 9","설명 9"),
				new exem(0,"문항 10","내용 10","데이터 10","설명 10"),
				new exem(11,"문항 11","내용 11","데이터 11","설명 11"),
				new exem(12,"문항 12","내용 12","데이터 12","설명 12"),
				new exem(13,"문항 13","내용 13","데이터 13","설명 13"),
				new exem(14,"문항 14","내용 14","데이터 14","설명 14"),
				new exem(15,"문항 15","내용 15","데이터 15","설명 15")
		};
        
        
   public class exem {
    private int id;
    private String question;
    private String content;
    private String data;
    private String description;

    // 생성자
    public exem(int id, String question, String content, String data, String description) {
        this.id = id;
        this.question = question;
        this.content = content;
        this.data = data;
        this.description = description;
    }

    // getter 메소드들
    public int getId() {
        return id;
    }

    public String getQuestion() {
        return question;
    }

    public String getContent() {
        return content;
    }

    public String getData() {
        return data;
    }

    public String getDescription() {
        return description;
    }

    // 객체 정보를 출력하기 위한 toString() 메소드 재정의
    @Override
    public String toString() {
        return "collegeStudent{" +
                "id=" + id +
                ", question='" + question + '\'' +
                ", content='" + content + '\'' +
                ", data='" + data + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

		
	}
}

 

2. Excel과 Sheet, Header 만들기

다음으로 엑셀파일을 만들기 위해 WorkBook을 생성하고 Sheet의 이름을 셋팅합니다.

Sheet  이름은 엑셀파일 내부 하단의 seet 이름을 정할 수 있습니다 저는 시험 내용 이라고 시트 이름을 정했습니다.

 

다음으로 엑셀의 한 줄을 넣게 위해 Row라는 자료형을 선언하고 만들어둔 sheet에 하나의 row를 추가합니다.

이때 createRow 안에는 row의 순서를 의미하는 int 자료형이 들어갑니다 엑셀로 치면 1행 2 행 3 행...

0번부터 시작이므로 0을 넣어줍니다. 다음으로 생성한 Row에 Cell (createCell)을 만들어서 값을 입력합니다 또한 0번부터 시작 하며 엑셀로 치면 A 열 B 열 C 열 이 됩니다.

Workbook workbook = new HSSFWorkbook();
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("설명");

 

여기서 병합 기능을 추가할 수 있는데  Cell을 만들어서 Cell 에  컬럼명을 생성하고 sheet.addMergedRegion 로 병합 범위를 정할 수 있습니다 

 EX)

            Cell cell1 = headerRow1.createCell(0);
            cell1.setCellValue("문항");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    1,  // 마지막 행 (0-based)
                    0,  // 첫 번째 열 (0-based)
                    0   // 마지막 열 (0-based)
            ));

 

 

 

            Cell cell1 = headerRow1.createCell(0);
            cell1.setCellValue("문항");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    0,  // 마지막 행 (0-based)
                    0,  // 첫 번째 열 (0-based)
                    1   // 마지막 열 (0-based)
            ));

 

 

 

주의할 점은 위쪽기준 2행은 이미 병합된 로우이니 첫번째 행의  createRow (1) 가 아닌 createRow(2)로 셋팅을 하셔야 하고 아래쪽 기준  createCell 도 2 부터 시작하셔야 제대로 대이터가 들어갑니다

 

 

응용으로 가운데 정렬과 볼드체도 가능 합니다.

        // 헤더 셀 스타일 설정
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setBold(true);
        headerCellStyle.setFont(font);

        // 첫 번째 병합된 셀
        Cell cell1 = headerRow1.createCell(0);
        cell1.setCellValue("센터명");
        cell1.setCellStyle(headerCellStyle);

 

 

 

마지막으로 인코딩 설정이랑 리턴만 잘 해주면 엑셀 다운이 가능 합니다.

        try {

      			/// 엑셀 데이터 셋팅



			 // 응답 설정
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Allow-Methods", "GET, POST");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=\"example.xlsx\"");
        	response.setHeader("Content-Transfer-Encoding", "binary");
            
            // 엑셀 파일을 응답에 쓰기
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();  // 강제로 출력 버퍼 비우기
        } catch(Exception e) {
            e.printStackTrace();
            LOGGER.error(e.getMessage());
            data.put("result", false);
            data.put("msg", "엑셀 파일 생성 중 오류가 발생했습니다.");
        } finally {
            // 워크북 닫기
            workbook.close();
        }

        }

 

 

 

//예시코드


        Workbook workbook = new XSSFWorkbook();  // 엑셀 워크북 생성

        try {

            // 시트 생성
            Sheet sheet= workbook.createSheet("Sample Data");
//           if(qustnStatisticsVO.getCenterId().isEmpty()){
//               sheet = workbook.createSheet("설문통계 (센터)");
//           }else{
//               sheet = workbook.createSheet("설문통계 (상담원)");
//           }


            // 첫 번째 행: 헤더 1
            Row headerRow1 = sheet.createRow(0);
            Cell cell1 = headerRow1.createCell(0);
            cell1.setCellValue("센터명");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    1,  // 마지막 행 (0-based)
                    0,  // 첫 번째 열 (0-based)
                    0   // 마지막 열 (0-based)
            ));
            Cell cell2 = headerRow1.createCell(1);
            cell2.setCellValue("선택 항목1");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    0,  // 마지막 행 (0-based)
                    1,  // 첫 번째 열 (0-based)
                    2   // 마지막 열 (0-based)
            ));
            Cell cell3 = headerRow1.createCell(3);
            cell3.setCellValue("선택 항목2");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    0,  // 마지막 행 (0-based)
                    3,  // 첫 번째 열 (0-based)
                    4   // 마지막 열 (0-based)
            ));
            Cell cell4 = headerRow1.createCell(5);
            cell4.setCellValue("선택 항목3");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    0,  // 마지막 행 (0-based)
                    5,  // 첫 번째 열 (0-based)
                    6   // 마지막 열 (0-based)
            ));
            Cell cell5 = headerRow1.createCell(7);
            cell5.setCellValue("선택 항목4");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    0,  // 마지막 행 (0-based)
                    7,  // 첫 번째 열 (0-based)
                    8   // 마지막 열 (0-based)
            ));
            Cell cell6 = headerRow1.createCell(9);
            cell6.setCellValue("선택 항목5");
            // 병합된 셀 설정
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 첫 번째 행 (0-based)
                    0,  // 마지막 행 (0-based)
                    9,  // 첫 번째 열 (0-based)
                    10   // 마지막 열 (0-based)
            ));

            Cell cell7 = headerRow1.createCell(11);
            cell7.setCellValue("소개");
            sheet.addMergedRegion(new CellRangeAddress(
                    0,  // 시작 행
                    1,  // 종료 행
                    11,  // 시작 열
                    11   // 종료 열
            ));

            //설문 제목 해더값 추출
            List<QustnManageVO> qustnStatisticsHeaderList = smartOnQustnManageService.selectQustnManageList();

            QustnManageVO qustnManageVO = qustnStatisticsHeaderList.get(qustnStatisticsVO.getQustnTitleIndex()-1);


            Row row1 = sheet.createRow(1);
            Cell towcell1 = row1.createCell(1);
            towcell1.setCellValue(qustnManageVO.getQustnQesitmEx1());
            sheet.addMergedRegion(new CellRangeAddress(
                    1,  // 시작 행
                    1,  // 종료 행
                    1,  // 시작 열
                    2   // 종료 열
            ));

            Cell towcell2 = row1.createCell(3);
            towcell2.setCellValue(qustnManageVO.getQustnQesitmEx2());
            sheet.addMergedRegion(new CellRangeAddress(
                    1,  // 시작 행
                    1,  // 종료 행
                    3,  // 시작 열
                    4   // 종료 열
            ));

            Cell towcell3 = row1.createCell(5);
            towcell3.setCellValue(qustnManageVO.getQustnQesitmEx3());
            sheet.addMergedRegion(new CellRangeAddress(
                    1,  // 시작 행
                    1,  // 종료 행
                    5,  // 시작 열
                    6   // 종료 열
            ));

            Cell towcell4 = row1.createCell(7);
            towcell4.setCellValue(qustnManageVO.getQustnQesitmEx4());
            sheet.addMergedRegion(new CellRangeAddress(
                    1,  // 시작 행
                    1,  // 종료 행
                    7,  // 시작 열
                    8   // 종료 열
            ));

            Cell towcell5 = row1.createCell(9);
            towcell5.setCellValue(qustnManageVO.getQustnQesitmEx5());
            sheet.addMergedRegion(new CellRangeAddress(
                    1,  // 시작 행
                    1,  // 종료 행
                    9,  // 시작 열
                    10   // 종료 열
            ));

        if(!qustnStatisticsList.isEmpty()) {
            for (int i = 2; i < qustnStatisticsList.size() + 2; i++) {
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(qustnStatisticsList.get(i - 2).getCenterNm());
                row.createCell(1).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo1Cnt());
                row.createCell(2).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo1Per() + " %");
                row.createCell(3).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo2Cnt());
                row.createCell(4).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo2Per() + " %");
                row.createCell(5).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo3Cnt());
                row.createCell(6).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo3Per() + " %");
                row.createCell(7).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo4Cnt());
                row.createCell(8).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo4Per() + " %");
                row.createCell(9).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo5Cnt());
                row.createCell(10).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerNo5Per() + " %");
                row.createCell(11).setCellValue(qustnStatisticsList.get(i - 2).getQustnAnswerTotalCnt());
            }
        }

            // 응답 설정
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Allow-Methods", "GET, POST");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            if(qustnStatisticsVO.getCenterId().isEmpty()){
                response.setHeader("Content-Disposition", "attachment; filename=\"설문통계 (센터).xlsx\"");
            }else{
                response.setHeader("Content-Disposition", "attachment; filename=\"설문통계 (상담원).xlsx\"");
            }
            response.setHeader("Content-Transfer-Encoding", "binary");

            // 엑셀 파일을 응답에 쓰기
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();  // 강제로 출력 버퍼 비우기
        } catch(Exception e) {
            e.printStackTrace();
            LOGGER.error(e.getMessage());
            data.put("result", false);
            data.put("msg", "엑셀 파일 생성 중 오류가 발생했습니다.");
        } finally {
            // 워크북 닫기
            workbook.close();
        }

        }