POI 라이브러리를 이용하여 Excel 파일 다운로드(ajax)

화면에 보이는 DB 테이블 목록 중 선택하여 다운로드를 클릭하면 해당 테이블 딕셔너리를 조회하여 데이터를 엑셀 파일로 다운로드 하는 기능이다.

 

 

 

1. 의존성 추가

pom.xml에 poi 라이브러리 dependency를 추가한다.

과정은 생략한다.

 

 

2. controller

@RequestMapping("/downloadExcel.do")
public void downloadExcelMetaList(HttpServletRequest request, HttpServletResponse response,
                                  @RequestBody TableVO data) {
    try {
      // Excel File 생성
      XSSFWorkbook workbook = excelService.excelDownload(data);
 
      // 컨텐츠 타입과 헤더 지정
      response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      response.setHeader("Content-Disposition", "attachment; filename=\"list.xlsx\"");
      
      // Excel File 쓰기
      OutputStream outputStream = response.getOutputStream();
      workbook.write(outputStream);
      outputStream.close();
      
    } catch (Exception e) {
      log.error("[ERR] downloadExcel IOException : {} ", e.getMessage());
    }
}

 

전송받은 데이터를 활용하여 엑셀 파일을 생성하고 내보내는 코드를 작성한다.

 

 

3. service

public XSSFWorkbook excelDownload(TableVO data) throws Exception {
    /* 워크북 생성 */
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Sheet");
    
    /* 글꼴 스타일 생성 */
    Font font = workbook.createFont();
    font.setFontHeight((short)260); // 글자크기 13point
    
    /* 헤더 셀 스타일 생성 */ 
    XSSFCellStyle headerCellStyle = workbook.createCellStyle();
    // RGB를 이용하여 셀 바탕색 설정
    headerCellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(237, 237, 237)));
    headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    // 셀 테두리 설정
    headerCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headerCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headerCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headerCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    // 텍스트 가운데 정렬
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    // 셀너비에 맞춰 줄바꿈
    headerCellStyle.setWrapText(true);
    // 글꼴 스타일 적용
    headerCellStyle.setFont(font);
 
    // 2A-3A 병합
    CellRangeAddress region1 = new CellRangeAddress(1, 2, 0, 0);
    sheet.addMergedRegion(region1);
    // 2B-3B 병합
    CellRangeAddress region2 = new CellRangeAddress(1, 2, 1, 1);
    sheet.addMergedRegion(region2);
    
    // 헤더 로우 생성
    XSSFRow headerRow1 = sheet.createRow(1);
    headerRow1.setHeightInPoints(39.00f);
    XSSFCell headerCell;
    headerCell = headerRow1.createCell(0);
    headerCell.setCellValue("No.");
    headerCell = headerRow1.createCell(1);
    headerCell.setCellValue("구분");
    headerCell = headerRow1.createCell(2);
    headerCell.setCellValue("기관명");
    headerCell = headerRow1.createCell(3);
 
    XSSFRow headerRow = sheet.createRow(2);
    headerRow.setHeightInPoints(39.00f);
    headerCell = headerRow.createCell(4);
    headerCell.setCellValue("논리DB명");
    headerCell = headerRow.createCell(5);
    headerCell.setCellValue("물리DB명");
    headerCell = headerRow.createCell(6);
    headerCell.setCellValue("DB설명");
    headerCell = headerRow.createCell(7);
 
    // 모든 헤더 셀에 스타일 적용
    for (int rowNum = 1; rowNum <= 2; rowNum++) {
      XSSFRow row = sheet.getRow(rowNum);
      for (int colNum = 0; colNum <= 41; colNum++) {
        XSSFCell cell = row.getCell(colNum);
          if (cell == null) {
              cell = row.createCell(colNum);
          }
          cell.setCellStyle(headerCellStyle);
      }
    }
    
    
    // 데이터 셀 스타일 생성
    XSSFCellStyle dataCellStyle = workbook.createCellStyle();
    dataCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    dataCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    dataCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    dataCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    dataCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    dataCellStyle.setWrapText(true);
    dataCellStyle.setFont(font);
    
    // 데이터 로우 생성
    XSSFRow dataRow;
    XSSFCell dataCell;
 
    
    ArrayList<String> tableNames = data.getTableNames();
    
    int rowNo = 1;
    for (String tableName : tableNames) {
        // DB 조회 및 데이터를 가져오는 과정
        List<TableVO> tableDetail = retrieveTblDetail(data);
        ...
      
        for (TableVO table : tableDetail) {
            dataRow = sheet.createRow(rowNo);
            dataRow.setHeightInPoints(39.00f);
            dataCell = dataRow.createCell(0);
            dataCell.setCellValue(rowNo);
            dataCell.setCellStyle(dataCellStyle);
            dataCell = dataRow.createCell(1);
            dataCell.setCellValue(table.getOwner);
            dataCell.setCellStyle(dataCellStyle);
            ...
            rowNo++;
        }
    }
    
    //모든 셀 너비 맞춤
    for(int i = 0; i < 42; i++){
      sheet.autoSizeColumn(i); 
      sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 2048);
    }
    
    return workbook;
      
  }

 

엑셀 양식에 맞게 셀 스타일을 생성하여 createRow - createCell - setCellValue - setCellStyle 과정을 반복한다.

 

 

3. js function

function fnDownloadExcel(dbName) {
    let checkList = $("input[name='checkList']:checked");
    if (checkList.length == 0) {
        alert("다운로드할 테이블을 선택해주세요.");
        return;
    }
    
    let checkListArr = [];
    $(checkList).each(function() {
        checkListArr.push($(this).val());
    })
    
    let checkData = {
        dbName: dbName,
        tableNames: checkListArr
    };
 
    loadingImgShow();
    $.ajax({
        url: "/downloadExcel.do",
        type: "POST",
        data: JSON.stringify(checkData),
        contentType: 'application/json',
        xhrFields: {
        responseType: 'blob' // 응답 데이터 타입 설정
    },
        success: function (blob, status, xhr) {
        if (xhr.status === 200) {
         var url = window.URL.createObjectURL(blob);
          var a = document.createElement('a');
          a.href = url;
          a.download = 'meta_list.xlsx';
          document.body.appendChild(a);
          a.click();
          window.URL.revokeObjectURL(url);
        } else {
          console.error('POST request failed with status', xhr.status);
        }
    },
        error: function(jqxhr, status, error) {
            let msg = jqxhr.status + " , " + status + " , " + error;
            alertBox(msg + "\n에러가 발생 했습니다. 관리자에게 문의해 주세요.");
        },
        complete: function() {
            loadingImgHide();
        }
    })
}

 

화면 button의 onclick 이벤트로 fnDownloadExcel 을 발생시킨다.

db를 조회하는데 필요한 데이터를 json 형식으로 전송하고 응답 데이터를 blob 으로 설정 후 url 생성 및 다운로드하면 정상적으로 저장되는 것을 확인할 수 있다.