React Excel 다운로드

FRONNT

AXIOS

헤더에 정보값을 받을 수 있게 설정하여 api를 작성

const axiosInstanceDownload = axios.create({
  headers: {
    Accept: 'application/json',
    Authorization: '',
  },
});

export const downloadApi = async ({ url, method, data }) => {
  return await axiosInstanceDownload({
    headers: {
      'Content-Type': 'application/json;charset=UTF-8',
    },
    responseType: 'blob',
    url,
    method,
    data,
  });
};

export const cltDownLoadExcel = async (data) => {
  return await downloadApi({
    url: '/api/excel',
    method: 'POST',
    data: { param: data },
    responseType: 'blob',
  })
    .then((response) => {
      const url = window.URL.createObjectURL(new Blob([response.data]));
      const link = document.createElement('a');
      link.href = url;

      const extractDownloadFilename = (_response) => {
        const disposition = _response.headers['content-disposition'];
        const fileName = decodeURI(disposition.match(/filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/)[1].replace(/['"]/g, ''));
        return fileName;
      };

      link.setAttribute('download', extractDownloadFilename(response));
      document.body.appendChild(link);
      link.click();

      // Cleanup
      link.remove();
      window.URL.revokeObjectURL(url);
    })
    .catch((error) => {
      message.warning('다운로드를 받을 수 없습니다.');
      console.debug('error', error);
    });
};

---

BACK

dependency

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

해당 의존성으로는 오류를 발생 할 수있다

🚨org.apache.commons.io.output.unsynchronizedbytearrayoutputstream.builder()lorg/apache/commons/io/output/unsynchronizedbytearrayoutputstream$builder;

+dependency

		<dependency>
		    <groupId>commons-io</groupId>
		    <artifactId>commons-io</artifactId>
		    <version>2.14.0</version>
		</dependency>

하나 더 추가하자

---

controller

import

import java.text.SimpleDateFormat;
import java.sql.Timestamp;

import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;

body

List<DBObject> list = stdReportService.selectRprtListNopagiNation(param);

		// 엑셀 파일 내 최대 행 수
		int maxRowsPerSheet = 10000;
		// 전체 데이터 수
		int totalRowCount = list.size();
		// 시트 개수 계산
		int sheetCount = (int) Math.ceil((double) totalRowCount / maxRowsPerSheet);

		try (Workbook workbook = new XSSFWorkbook()) {
		    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
		        Sheet sheet = workbook.createSheet("신고 목록_" + (sheetIndex + 1));

		        Row headerRow = sheet.createRow(0);
		        String[] excelHeaders = {"신고번호", "문서번호", "문서원본번호", "제목", "조치결과", "신고자",  "부서", "요청일", "조치일"};
		        for (int i = 0; i < excelHeaders.length; i++) {
		            Cell cell = headerRow.createCell(i);
		            cell.setCellValue(excelHeaders[i]);
		        }

		        // 시트 내 시작 행과 종료 행 계산
		        int startIndex = sheetIndex * maxRowsPerSheet;
		        int endIndex = Math.min(startIndex + maxRowsPerSheet, totalRowCount);

		        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
		        for (int i = startIndex; i < endIndex; i++) {
		            DBObject report = list.get(i);
		            Row row = sheet.createRow(i - startIndex + 1); // 헤더행을 제외하고 1부터 시작

		            row.createCell(0).setCellValue((Integer) report.get("REPORT_ID"));
		            row.createCell(1).setCellValue(report.get("STD_ID") != null ? (Integer) report.get("STD_ID") : 0);
		            row.createCell(2).setCellValue(report.get("ORG_STD_ID") != null ? (Integer) report.get("ORG_STD_ID") : 0);
		            row.createCell(3).setCellValue((String) report.get("REPORT_TITLE"));

		            int status = report.get("STATUS") != null ? Integer.parseInt(report.get("STATUS").toString()) : 0;
		            String statusString = status == 1 ? "답변완료" : "대기중";
		            row.createCell(4).setCellValue(statusString);

		            row.createCell(5).setCellValue((String) report.get("REG_USER_NM"));
		            row.createCell(6).setCellValue((String) report.get("REG_DEPT_NM"));

		            if (report.get("REG_DTTM") instanceof Timestamp) {
		                Timestamp regDttm = (Timestamp) report.get("REG_DTTM");
		                row.createCell(7).setCellValue(sdf.format(regDttm));
		            } else {
		                row.createCell(7).setCellValue("");
		            }

		            if (report.get("COMP_DTTM") instanceof Timestamp) {
		                Timestamp compDttm = (Timestamp) report.get("COMP_DTTM");
		                row.createCell(8).setCellValue(sdf.format(compDttm));
		            } else {
		                row.createCell(8).setCellValue("");
		            }
		        }
		    }

		    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		    workbook.write(outputStream);

		    byte[] bytes = outputStream.toByteArray();

		    HttpHeaders httpHeaders = new HttpHeaders();
		    httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
		    httpHeaders.setContentDispositionFormData("attachment", "reportList.xlsx");
		    httpHeaders.setContentLength(bytes.length);

		    return new ResponseEntity<>(bytes, httpHeaders, HttpStatus.OK);

		} catch (IOException e) {
		    e.printStackTrace();
		    return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("Failed to generate Excel file");
		}
		
	}

DB에서 가져온 정보를 List에 담아서 엑셀의 헤더와 값들을 넣어준다.

DATE 양식이 안맞는 오류가 발생할 수 있으므로 Timestamp 를 사용하자

 if (report.get("REG_DTTM") instanceof Timestamp) {
	 Timestamp regDttm = (Timestamp) report.get("REG_DTTM"); 
     row.createCell(7).setCellValue(sdf.format(regDttm));
} else {
	 row.createCell(7).setCellValue("");
}