[JAVA] apache POI 사용한 엑셀 다운로드 기능

apache POI를 사용하여 엑셀 다운로드 기능을 구현해 보기!

 

 

 

[ FRONT ] vue.js

java에서 엑셀 파일을 생성하고 front에서는  response 을 받아서

Blob을 가지고 엑셀 파일을 다운로드합니다. 이때, 다운로드하는 방법은 브라우저에 따라 다를 수 있으니 주의.

 downloadExcel() {
    fetch('/download-excel', {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json',
        },
        body: JSON.stringify(this.AnnualList),
    })
    .then(response => {
        // 응답이 성공적으로 받아졌을 때 엑셀 파일 다운로드
        if (response.ok) {
            return response.blob();
        }
        throw new Error('엑셀 통신 실패');
    })
    .then(blob => {
        // Blob을 가지고 엑셀 파일을 다운로드하는 방법은 브라우저에 따라 다를 수 있음.
        // 여기서는 브라우저 기본 다운로드 방식을 사용.

        //오늘 날짜 추가를 위한 dateString 생성.
        const today = new Date();
        const year = today.getFullYear();
        const month = String(today.getMonth() + 1).padStart(2, '0'); // 월은 0부터 시작하므로 +1 해줌
        const day = String(today.getDate()).padStart(2, '0');        // 일자
        const dateString = `${year}${month}${day}`;

        const url = window.URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = `연차관리목록_${dateString}.xlsx`;
        document.body.appendChild(a);
        a.click();
        window.URL.revokeObjectURL(url);
    })
    .catch(error => {
        console.error('에러 발생 : ', error);
    })
}

 

 

 

 

[BACK] JAVA

main 함수에서 data를 받아와서 listToExcelByte() 메소드로 엑셀 파일을 생성합니다.

main함수 하단에는 기능별로 메소드를 분개처리해서 작성하였습니다.

 

▼main

// 엑셀 다운로드 - main
@PostMapping(value="/download-excel", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
public ResponseEntity<byte[]> downloadExcel(@RequestBody @Valid List<AnnualVO> dataList, Errors errors) throws Exception {

    // 데이터 변환
    List<LinkedHashMap<String, Object>> dbData = convertDataListToMap(dataList);

    // 엑셀 파일 생성
    byte[] excelBytes = listToExcelByte(dbData, "Annual Data");

    // HTTP 응답에 엑셀 파일을 첨부하여 전송
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("filename", "annual_data.xlsx");
    headers.setContentLength(excelBytes.length);

    return new ResponseEntity<>(excelBytes, headers, HttpStatus.OK);
}

 

 

▼convertDataListToMap()

: 여기서 value에 따른 표출 text를 설정할 수 있습니다.

예를 들어, DB에서는 Y/N 값으로 체크하던 val를 사용자가 인식하기 편리한 텍스트로 변환이 가능합니다.

// AnnualVO 리스트를 LinkedHashMap 리스트로 변환하는 메소드
private List<LinkedHashMap<String, Object>> convertDataListToMap(List<AnnualVO> dataList) {
    List<LinkedHashMap<String, Object>> mapList = new ArrayList<>();
    for (AnnualVO annualVO : dataList) {
        LinkedHashMap<String, Object> map = new LinkedHashMap<>();
        map.put("docNo", annualVO.getDocNo());  
        map.put("empName", annualVO.getEmpName());
        map.put("empNo", annualVO.getEmpNo());

        // 신청상태 분개처리
        map.put("annualYn", "Y".equals(annualVO.getAnnualYn()) ? "신청" : "취소");

        map.put("annualType", annualVO.getAnnualType());
        map.put("startDate", annualVO.getStartDate());
        map.put("endDate", annualVO.getEndDate());
        map.put("useDays", annualVO.getUseDays());

        mapList.add(map);
    }
    return mapList;
}

 

 

▼ listToExcelByte()

: List를 받아서 excel로 만든 뒤 byte로 변환하여 리턴합니다.

protected  static  ObjectMapper JSON_MAPPER = new ObjectMapper().configure(MapperFeature.ACCEPT_CASE_INSENSITIVE_PROPERTIES, true);
    
 /**
 * listToExcelByte
 * <pre>List 를 받아서 excel로 만든뒤 byte 로 변환하여 리턴</pre>
 */
public static byte[] listToExcelByte(List<LinkedHashMap<String, Object>> dbData, String sheetName) throws Exception {
    // 파일 생성
    XSSFWorkbook wb = new XSSFWorkbook();

    // 시트 생성
    XSSFSheet sheet = wb.createSheet(StringUtils.isEmpty(sheetName) ? "Sheet1" : sheetName);

    // 헤더 명칭 설정
    String[] headerNames = { "문서번호", "사원명", "사원번호", "신청상태", "휴가종류", "시작일", "종료일", "사용일수" };
    XSSFRow headerRow = sheet.createRow(0);
    for (int i = 0; i < headerNames.length; i++) {
        XSSFCell headerCell = headerRow.createCell(i);
        headerCell.setCellValue(headerNames[i]);
        setHeaderStyle(wb, headerCell);
    }

    // 값 넣기
    for (int i = 0; i < dbData.size(); i++) {
        XSSFRow row = sheet.createRow(i + 1);
        LinkedHashMap<String, Object> map = dbData.get(i);
        int cellCnt = 0;
        for (Object value : map.values()) {
            XSSFCell cell = row.createCell(cellCnt++);
            setCellStyleByValue(wb, sheet, row.getRowNum(), cell.getColumnIndex(), value);
            setCellValue(cell, value);
        }
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    wb.write(os);
    os.close();

    return os.toByteArray();
}

 

 

▼ setStyle()

: data의 Type별로 style을 적용합니다.

/**
 *  flag   
 * 1 : Hearder 
 * 2 : String
 * 3 : Integer
 * 4 : Boolean
 * 5 : Date
 */
public static void setStyle(XSSFWorkbook wb, XSSFSheet sheet, int row, int cel, int flag ) {
    XSSFCellStyle style = wb.createCellStyle();
    XSSFDataFormat format = wb.createDataFormat();
    XSSFFont font = wb.createFont();

    switch (flag) {
        case 1:
            font.setBold(true);
            style.setFont(font);
            break;
        case 2:
            style.setDataFormat(format.getFormat("@"));
            style.setAlignment(HorizontalAlignment.LEFT);
            break;
        case 3:
            style.setAlignment(HorizontalAlignment.RIGHT);
            break;
        case 4:
            style.setAlignment(HorizontalAlignment.CENTER);
            break;
        case 5:
            style.setDataFormat(format.getFormat("yyyy-mm-dd h:mm"));
            style.setAlignment(HorizontalAlignment.RIGHT);
            break;
    }
    sheet.setColumnWidth(cel, Math.min(4000, sheet.getColumnWidth(cel) + 4000));
    XSSFRow sheetRow = sheet.getRow(row);   
    sheetRow.getCell(cel).setCellStyle(style);
}

 

 

▼ setHeaderStyle()

: Header 스타일을 설정합니다.

public static void setHeaderStyle(XSSFWorkbook wb, XSSFCell cell) {
    XSSFCellStyle style = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    style.setFont(font);
    cell.setCellStyle(style);
}

 

 

▼ setCellStyleByValue()

: value에 따른 특정한 Cell 이벤트를 적용합니다. (ex. 배경색)

 public static void setCellStyleByValue(XSSFWorkbook wb, XSSFSheet sheet, int rowCnt, int cellCnt, Object value) {
    XSSFRow sheetRow = sheet.getRow(rowCnt);
    if (sheetRow == null) {
        sheetRow = sheet.createRow(rowCnt); // 행이 없으면 새로 생성
    }

    XSSFCell cell = sheetRow.getCell(cellCnt);
    if (cell == null) {
        cell = sheetRow.createCell(cellCnt); // 셀이 없으면 새로 생성
    }

    XSSFCellStyle style = wb.createCellStyle();
    // 값에 따라서 배경색 설정
    if (value instanceof String && "신청".equals(value)) {
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    } else if (value instanceof String && "취소".equals(value)) {
        style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    } else if (value instanceof Double && (Double)value >= 5.0) {
        style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }
    cell.setCellStyle(style);
}

 

 

▼ setHeaderStyle()

: 셀에 값을 채우는 메서드입니다. 

숫자의 경우 Integer, Long, Float, Double 등.. 다양한 Type이 있으니 data의 Type에 따라 유의하여 적용해야 합니다.

public static void setCellValue(XSSFCell cell, Object value) {
    if (value instanceof String) {
        cell.setCellValue((String) value);
    } else if (value instanceof Integer || value instanceof Long || value instanceof Float || value instanceof Double) {
        cell.setCellValue(Double.parseDouble(value.toString())); // double로 형변환 후 셀에 값을 채움
        XSSFCellStyle style = cell.getCellStyle();
        XSSFDataFormat format = cell.getSheet().getWorkbook().createDataFormat();
        style.setDataFormat(format.getFormat("0.0")); // 숫자 형식으로 설정 (소수점 한자리까지)
        cell.setCellStyle(style);
    } else if (value instanceof Boolean) {
        cell.setCellValue((Boolean) value);
    } else if (value instanceof Date) {
        cell.setCellValue((Date) value);
    } else {
        cell.setCellValue(value.toString()); // 기타 형식은 문자열로 처리
    }
}

 

 

 

 

 

 

 

 

 ▽ [ 전체코드 ] 

package com.vms.controller;


import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.Errors;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.fasterxml.jackson.databind.MapperFeature;
import com.fasterxml.jackson.databind.ObjectMapper;
import cohttp://m.vms.vo.AnnualVO;

import io.micrometer.common.util.StringUtils;
import jakarta.validation.Valid;


@RestController
public class ExcelContoller {

private final Logger logger = LoggerFactory.getLogger(this.getClass());

// 엑셀 다운로드
@PostMapping(value="/download-excel", produces="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
public ResponseEntity<byte[]> downloadExcel(@RequestBody @Valid List<AnnualVO> dataList, Errors errors) throws Exception {

    // 데이터 변환
    List<LinkedHashMap<String, Object>> dbData = convertDataListToMap(dataList);

    // 엑셀 파일 생성
    byte[] excelBytes = listToExcelByte(dbData, "Annual Data");

    // HTTP 응답에 엑셀 파일을 첨부하여 전송
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    headers.setContentDispositionFormData("filename", "annual_data.xlsx");
    headers.setContentLength(excelBytes.length);

    return new ResponseEntity<>(excelBytes, headers, HttpStatus.OK);
}


// AnnualVO 리스트를 LinkedHashMap 리스트로 변환하는 메소드
private List<LinkedHashMap<String, Object>> convertDataListToMap(List<AnnualVO> dataList) {
    List<LinkedHashMap<String, Object>> mapList = new ArrayList<>();
    for (AnnualVO annualVO : dataList) {
        LinkedHashMap<String, Object> map = new LinkedHashMap<>();
        map.put("docNo", annualVO.getDocNo());  
        map.put("empName", annualVO.getEmpName());
        map.put("empNo", annualVO.getEmpNo());
        
        // 신청상태 분개처리
        map.put("annualYn", "Y".equals(annualVO.getAnnualYn()) ? "신청" : "취소");
        
        map.put("annualType", annualVO.getAnnualType());
        map.put("startDate", annualVO.getStartDate());
        map.put("endDate", annualVO.getEndDate());
        map.put("useDays", annualVO.getUseDays());

        mapList.add(map);
    }
    return mapList;
}

protected  static  ObjectMapper JSON_MAPPER = new ObjectMapper().configure(MapperFeature.ACCEPT_CASE_INSENSITIVE_PROPERTIES, true);
    
 
 /**
     * listToExcelByte
     * <pre>List 를 받아서 excel로 만든뒤 byte 로 변환하여 리턴</pre>
     */
    public static byte[] listToExcelByte(List<LinkedHashMap<String, Object>> dbData, String sheetName) throws Exception {
     // 파일 생성
        XSSFWorkbook wb = new XSSFWorkbook();
        
        // 시트 생성
        XSSFSheet sheet = wb.createSheet(StringUtils.isEmpty(sheetName) ? "Sheet1" : sheetName);
        
        // 헤더 명칭 설정
        String[] headerNames = { "문서번호", "사원명", "사원번호", "신청상태", "휴가종류", "시작일", "종료일", "사용일수" };
        XSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < headerNames.length; i++) {
            XSSFCell headerCell = headerRow.createCell(i);
            headerCell.setCellValue(headerNames[i]);
            setHeaderStyle(wb, headerCell);
        }
        
        // 값 넣기
        for (int i = 0; i < dbData.size(); i++) {
            XSSFRow row = sheet.createRow(i + 1);
            LinkedHashMap<String, Object> map = dbData.get(i);
            int cellCnt = 0;
            for (Object value : map.values()) {
                XSSFCell cell = row.createCell(cellCnt++);
                setCellStyleByValue(wb, sheet, row.getRowNum(), cell.getColumnIndex(), value);
                setCellValue(cell, value);
            }
        }

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        os.close();

        return os.toByteArray();
    }
    
    
    /**
     * setStyle
     * <pre>style 을 입힌다.</pre>
     *  flag   
     * 1 : Hearder 
     * 2 : String
     * 3 : Integer
     * 4 : Boolean
     * 5 : Date
     */
    public static void setStyle(XSSFWorkbook wb, XSSFSheet sheet, int row, int cel, int flag ) {
        XSSFCellStyle style = wb.createCellStyle();
        XSSFDataFormat format = wb.createDataFormat();
        XSSFFont font = wb.createFont();
        
        switch (flag) {
            case 1:
                font.setBold(true);
                style.setFont(font);
                break;
            case 2:
                style.setDataFormat(format.getFormat("@"));
                style.setAlignment(HorizontalAlignment.LEFT);
                break;
            case 3:
                style.setAlignment(HorizontalAlignment.RIGHT);
                break;
            case 4:
                style.setAlignment(HorizontalAlignment.CENTER);
                break;
            case 5:
                style.setDataFormat(format.getFormat("yyyy-mm-dd h:mm"));
                style.setAlignment(HorizontalAlignment.RIGHT);
                break;
        }
        sheet.setColumnWidth(cel, Math.min(4000, sheet.getColumnWidth(cel) + 4000));
        XSSFRow sheetRow = sheet.getRow(row);   
        sheetRow.getCell(cel).setCellStyle(style);
    }
    
    
    // Header 스타일 설정 메서드
    public static void setHeaderStyle(XSSFWorkbook wb, XSSFCell cell) {
        XSSFCellStyle style = wb.createCellStyle();
        XSSFFont font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        cell.setCellStyle(style);
    }

    
    public static void setCellStyleByValue(XSSFWorkbook wb, XSSFSheet sheet, int rowCnt, int cellCnt, Object value) {
        XSSFRow sheetRow = sheet.getRow(rowCnt);
        if (sheetRow == null) {
            sheetRow = sheet.createRow(rowCnt); // 행이 없으면 새로 생성
        }

        XSSFCell cell = sheetRow.getCell(cellCnt);
        if (cell == null) {
            cell = sheetRow.createCell(cellCnt); // 셀이 없으면 새로 생성
        }

        XSSFCellStyle style = wb.createCellStyle();
        // 값에 따라서 배경색 설정
        if (value instanceof String && "신청".equals(value)) {
            style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        } else if (value instanceof String && "취소".equals(value)) {
            style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        } else if (value instanceof Double && (Double)value >= 5.0) {
            style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        cell.setCellStyle(style);
    }
    
    
    // 셀에 값 채우는 메서드
    public static void setCellValue(XSSFCell cell, Object value) {
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer || value instanceof Long || value instanceof Float || value instanceof Double) {
            cell.setCellValue(Double.parseDouble(value.toString())); // double로 형변환 후 셀에 값을 채움
            XSSFCellStyle style = cell.getCellStyle();
            XSSFDataFormat format = cell.getSheet().getWorkbook().createDataFormat();
            style.setDataFormat(format.getFormat("0.0")); // 숫자 형식으로 설정 (소수점 한자리까지)
            cell.setCellStyle(style);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else {
            cell.setCellValue(value.toString()); // 기타 형식은 문자열로 처리
        }
    }
}