poi와 poi-ooxml 라이브러리를 사용하여 자바에서 엑셀을 다룰 수 있다

implementation 'org.apache.poi:poi'
implementation 'org.apache.poi:poi-ooxml'

업로드

Front-end

Back-end

다운로드

Front-end

<div>
      <button class="btn" onclick="/event/excel">엑셀 추출</button>
</div>

Back-end

  • HttpServletResponse 와 ResponseEntity를 사용 하는 2가지 방법이 있다
  • ResponseEntity 를 통한 방법을 사용할 것이다
@RequestMapping("/event")
@Controller
@AllArgsConstructor
public class eventController {
      private final EventService eventService;
      private final ExcelMethod excelMethod;

      ...

      @GetMapping("/excel")
      @ResponseBody
      public ResponseEntity<Resource> excelDownload(HttpServletRequest request) throws IOException {

         // IE여부 판별하여 fileName 설정
         String userAgent = request.getHeader("User-Agent");
         boolean ie = (userAgent.indexOf("MSIE") > -1 || userAgent.indexOf("Trident") > -1);

         List<EventDTO> list = eventService.getAllList();

         ResponseEntity<Resource> responseEntity = excelMethod.excelDownload(list, ie);

         return responseEntity;
      
      ...
}

@Log4j2
@Component
public class ExcelMethod {

      @Value("${custom.path.upload-file}")
      private String path;

      public ResponseEntity<Resource> excelDownload(List<EventDto> list, boolean ie) throws IOException {
         String now = LocalDateTime.now().toString();      
         String fileName = path + now + ".xlsx";   

         if (ie) {
            fileName = URLEncoder.encode(fileName, "utf-8").replaceAll("\\+", "%20");
         } else {
            fileName = new String(fileName.getBytes("utf-8"), "iso-8859-1");
         }

         File file = new File(fileName);

         // excel 생성
         makeExcel(file, list);

         // 헤더 설정
         HttpHeaders header = new HttpHeaders();
         header.add(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + fileName + "\"");
         header.add("Cache-Control", "no-cache, no-store, must-revalidate");
         header.add("Pragma", "no-cache");
         header.add("Expires", "0");

         // 바디 설정(파일)
         InputStreamResource resource = new InputStreamResource(new FileInputStream(file));

         return ResponseEntity.ok()
                     .headers(header)        // 헤더 삽입
                     .contentLength(file.length())
                     .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
                     .body(resource);        // 바디 삽입
      }


      public void makeExcel(File file, List<EventDTO> events) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();      // Sheet 생성

        // Font 설정
        Font font = workbook.createFont();         
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());

        // Style 설정
        CellStyle style = workbook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFont(font);

        Row row;
        Cell cell;
        int rowNum = 0;

        String[] headerKeys = {"ID", "종류", "상태", "제목", "일시"};

        // Header
        row = sheet.createRow(rowNum++);
        for (int i=0; i<headerKeys.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headerKeys[i]);
            cell.setCellStyle(style);     // Style 적용
        }

        // Row 입력
        for (EventDTO event : events) {
            row = sheet.createRow(rowNum++);

            cell = row.createCell(0);
            cell.setCellValue(event.getId());
            cell = row.createCell(1);
            cell.setCellValue(event.getType());
            cell = row.createCell(2);
            cell.setCellValue(event.getState());
            cell = row.createCell(3);
            cell.setCellValue(event.getTitle());
            cell = row.createCell(4);
            cell.setCellValue(event.getDate().toString());
        }

        // 셀 너비 자동 조정
        for (int i=0; i<headerKeys.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // 파라미터로 넘어온 file에 엑셀 데이터 입력
        try {
            workbook.write(new FileOutputStream(file));
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

→ makeExcel()에서 workbook.write(new FileOutputStream(file))을 통해 파라미터로 넘어온 file에 엑셀 데이터를 입력한다
→ excelDownload()에서는 엑셀 데이터가 입력된 file을 읽어와 HTTP Response Body에 넣는다