목표! : DB 내용을 Excell 파일로 제공하자!

설문조사 사이트를 만들려고 하고있다...
그렇다면? 응답 결과를 엑셀로 보내줘야하잖아요!

어떻게 하는건데... 아직 구현에 시간이 남았기는 했지만 진심으로 궁금하기 때문에 한번 찾아보겠다!

찾아보기

역시 인터넷 세상 ~ 🙆‍♀️

우아한 기술 블로그
전자정부표준프레임워크
[[Java] 자바 Apache POI 라이브러리 예시 코드로 알아보기(https://unboundweekend.com/apache-poi-%EB%9D%BC%EC%9D%B4%EB%B8%8C%EB%9F%AC%EB%A : 6%AC/)

Apache POI는 Microsoft office 파일 형식을 수정하고 생성하는 데 사용되는 자바 라이브러리다.

이 라이브러리를 통해서 DB의 데이터를 엑셀로 변환하고 사용자가 다운로드 할 수 있도록 기능을 구현하겠다~

Apache POI 사용해보기

📄공식문서

엑셀 파일의 종류

엑셀 파일은 두 종류가 있다. 두번째 형식이 더 최신이고 용량도 크다.

• xls형식 : org.apache.poi.hssf
• xlsx형식 : org.apache.poi.xssf

cell과 row의 차이

• Row : 행
• Cell : 열

하나의 행에 여러가지의 열이 있다.

따라서 행을 순차적으로 돌면서 열을 넣는다. 다시 말해 두번째 행에 1,2,3열의 내용을 채우는 방식으로 동작한다.

어떻게 하면 엑셀 파일을 만들 수 있을까?

1. 엑셀 파일을 만들기
2. 엑셀 파일의 시트를 만들기
3. 해당 시트에 행을 만들어준다.
4. 행마다 열을 넣어즌다.
5. 다운로드하기!

의존성 추가하기

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<!-- jdk는 11버전 사용중입니다. -->
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>

동작 살펴보기

엑셀을 만들자

	Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(fileName);

• Workbook wb = new XSSFWorkbook() : 새로운 엑셀 파일을 만들자~

• XSSFWorkbook() : 위에서 봤던 엑셀 파일의 클래스
• Workbook인터페이스 : excel 문서의 상위 인터페이스, 사용자가 통합 문서를 읽거나 쓸 때 생성하는 첫번째 개체이다. 또한 시트를 생성하기 위한 최상위 개체이다.

• Sheet sheet = wb.createSheet(fileName) : 엑셀의 시트를 만들고, 이름 설정,시트는 여러개일 수 있지만 여기서는 하나의 시트만 사용한다.
  

엑셀 열의 제목을 만들자

	//제목 셀 스타일을 지정하는 코드 ...

    //vo의 제목 셋팅
    row = sheet.createRow(rowNum++);
    for(Field field : list.get(0).getClass().getDeclaredFields()){
      field.setAccessible(true);                //필드 접근 권한 설정
      Object value = field.get(list.get(0));    //객체의 첫번째 값을 가져온다.

      cell = row.createCell(columnNum);         //현재 행의 columnNum번째 셀을 생성한다.
      cell.setCellStyle(style1);                //셀의 스타일을 적용한다.
      cell.setCellValue(field.getName());       //셀의 값을 설정한다!

      columnNum += 1;                           //다음 셀로 이동
    }

코드 구조를 보면 다음과 같다.

1. 행을 만들기
2. 리스트의 첫 번째 요소의 클래스의 모든 필드를 Field객체에 담는다. 
3. 지금 행의 N번째 셀을 생성하고, 셀의 값을 담는다.
4. 다음 셀로 이동!!

이 코드의 결과는 list의 필드 명의 값으로 첫번째 행을 만든 것이다!
그러면 다음에는 두번째 행부터 열을 채워줘야겠지요?

엑셀 행을 만들자 (내용을 채운다)

    // vo의 리스트 셋팅
    for (int i=0; i<list.size(); i++) {               //리스트의 크기만큼!
      columnNum = 0;                                  //열 번호를 다시 초기화
      row = sheet.createRow(rowNum++);                //rowNum번째 행 만들기
      
      //통째로 안넣는 이유가 지금 내가 가진 객체가 리스트<객체>이런식으로 되어있음. 
      // 따라서 리스트의 객체의 속성을 또 하나하나 가져오는 코드가 필요한 것!
      for(Field field : list.get(i).getClass().getDeclaredFields()){  //리스트를 가져옵시다!
        field.setAccessible(true);                    //필드 접근권환 활성화 시켜주고
        Object value = field.get(list.get(i));        //리스트의 값을 가져와주고 

        cell = row.createCell(columnNum);             //셀을 만들어줍시다
        cell.setCellStyle(style2);                    //셀의 스타일을 넣어줍시다
        if (value != null) {
          cell.setCellValue(value.toString());        //셀이 int형인 경우도 있어서 안전하게 toString메서드 사용~
        } else {                                      //셀이 없는 경우에
          cell.setCellValue("");    
        }
        columnNum += 1;                               //다음 셀로 이동~!!    
      }
    }

1. 넣을 리스트의 갯수만큼의 행에 
2. 리스트의 user 객체의 값을 차례대로 열에 넣어준다~

엑셀 파일을 다운로드 하자~

    // 컨텐츠 타입과 파일명 지정
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    

    // 정규식 사용하여 파일명에 .@$^공백이 있을경우 _로 대체
    fileName = fileName.replaceAll("[.@$^\\s]", "_");
    // 헤더에 한글 셋팅
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename="+fileName+".xlsx");

    // Excel File Output
    wb.write(response.getOutputStream());
    wb.close();
  }

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); 

→ 응답은 HTTP로!! 따라서 헤더의 Content-Type을 엑셀 파일로 설정해 웹 브라우저에게 형식을 알려준다.

    // 정규식 사용하여 파일명에 .@$^공백이 있을경우 _로 대체
    fileName = fileName.replaceAll("[.@$^\\s]", "_");
    // 헤더에 한글 셋팅
    fileName = URLEncoder.encode(fileName, "UTF-8");

🤔 파일이름이 UTF-8로 인코딩하지 않으면? 다운로드 하다가 깨지겠죠?? 문제를 해결해주기 위해 파일이름을 UTF-8로 인코딩 해줍시다. 그런데 여기 코드에서는 시트이름이랑 동일하게 사용하고 있어요. 실제 코드에서는 시트명과 파일이름이 다르겠죠?

    response.setHeader("Content-Disposition", "attachment;filename="+fileName+".xlsx");

응답객체에 "Content-Disposition" 가 있으면 무엇일까요?

Content-Disposition 헤더는 : 웹페이지인지, 다운로드되어야하는지 를 알려준다.

• 기본값인 inline은 웹페이지임을, attachment은 다운로드 받아야하는 파일임을 알려준다.

• 여기서는 파일의 이름을 지정해서 다운로드 시켜준다!

  ✔️ 그런데 이 엑셀파일을 바로 던져주는게 아니고, 엑셀파일을 만든다음에~ HTTP 응답 스트림에 엑셀파일을 써주는 거에요!!

wb.write(response.getOutputStream());
wb.close();

HTTP의 응답 스트림이 무엇인지도 알아야겠죠?

• HTTP 2에서 요청과 응답을 위해 사용하는 길! 서버와 클라이언트가 여러개의 요청과 응답을 하나의 스트림으로 사용하여 더 유연하고, 전송할 수 있는 데이터의 크기도 늘어났다! 작성해주는 부분이 요기!
  

그러면 실제로 헤더 부분이 궁금하잖아요?

파일이름에 와라랄라 되어있는건 인코딩해서 그래요!!

다시 디코딩하면 잘됌🙆‍♀️

코드 전문

컨트롤러 부분

@RestController
public class excellTest {

  @Autowired
  UserRepository userRepository;

  @GetMapping("/user/excell")
  public void excellDownload(HttpServletResponse response) throws Exception {
    List<User> list = userRepository.findAll();
    String excelFileName = "고객 정보";

    ExcellUtil.excelDownload(response, list, excelFileName);

  }
}

실제 기능 부분

public class ExcellUtil {
  public static void excelDownload(HttpServletResponse response, List<?> list, @NonNull String fileName) throws Exception{
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(fileName);

    Row row = null;
    Cell cell = null;
    int rowNum = 0;
    int columnNum = 0;

    //제목 스타일 지정
    CellStyle style1 = wb.createCellStyle();
    style1.setAlignment(HorizontalAlignment.CENTER);                    //정렬 방식
    style1.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());  //배경 색상
    style1.setFillPattern(FillPatternType.BRICKS);                      //배경 패턴 설정
    style1.setBorderRight(BorderStyle.THIN);                            //테두리 선의 스타일 지정 (오른쪽,왼쪽,위,아래)
    style1.setBorderLeft(BorderStyle.THIN);
    style1.setBorderTop(BorderStyle.THIN);
    style1.setBorderBottom(BorderStyle.THIN);


    //vo의 제목 셋팅
    row = sheet.createRow(rowNum++);
    for(Field field : list.get(0).getClass().getDeclaredFields()){
      field.setAccessible(true);                //필드 접근 권한 설정
      Object value = field.get(list.get(0));    //객체의 첫번째 값을 가져온다.

      cell = row.createCell(columnNum);         //현재 행의 columnNum번째 셀을 생성한다.
      cell.setCellStyle(style1);                //셀의 스타일을 적용한다.
      cell.setCellValue(field.getName());       //셀의 값을 설정한다!

      columnNum += 1;                           //다음 셀로 이동
    }

    CellStyle style2 = wb.createCellStyle();
    style2.setAlignment(HorizontalAlignment.CENTER);
    style2.setBorderRight(BorderStyle.THIN);
    style2.setBorderLeft(BorderStyle.THIN);
    style2.setBorderTop(BorderStyle.THIN);
    style2.setBorderBottom(BorderStyle.THIN);

    // vo의 리스트 셋팅
    for (int i=0; i<list.size(); i++) {               //리스트의 크기만큼!
      columnNum = 0;                                  //열 번호를 다시 초기화
      row = sheet.createRow(rowNum++);                //rowNum번째 행 만들기

      //통째로 안넣는 이유가 지금 내가 가진 객체가 리스트<객체>이런식으로 되어있음.
      // 따라서 리스트의 객체의 속성을 또 하나하나 가져오는 코드가 필요한 것!
      for(Field field : list.get(i).getClass().getDeclaredFields()){  //리스트를 가져옵시다!
        field.setAccessible(true);                    //필드 접근권환 활성화 시켜주고
        Object value = field.get(list.get(i));        //리스트의 값을 가져와주고

        cell = row.createCell(columnNum);             //셀을 만들어줍시다
        cell.setCellStyle(style2);                    //셀의 스타일을 넣어줍시다
        if (value != null) {
          cell.setCellValue(value.toString());        //셀이 int형인 경우도 있어서 안전하게 toString메서드 사용~
        } else {                                      //셀이 없는 경우에
          cell.setCellValue("");
        }
        columnNum += 1;                               //다음 셀로 이동~!!
      }
    }

    // 컨텐츠 타입과 파일명 지정
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    // 정규식 사용하여 파일명에 .@$^공백이 있을경우 _로 대체
    fileName = fileName.replaceAll("[.@$^\\s]", "_");
    // 헤더에 한글 셋팅
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename="+fileName+".xlsx");

    // Excel File Output
    wb.write(response.getOutputStream());
    wb.close();
  }
}