파일 다운로드 (excel, csv)

1. build.gralde

// csv
compile 'org.apache.commons:commons-csv:1.5'
// xlsx poi
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.0'

2. Controller (api)

import kr.co.careercare.highlight.services.exports.IExportsService;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;

/**
 * @author yunsd
 * @date   2018. 12. 04.
 * @Desc   
 */
@Controller
@RequestMapping("/exports")
@CrossOrigin
public class ExportsController {

   private static final Logger logger = Logger.getLogger(ExportsController.class);

   @Autowired
   IExportsService exportsService;


   @GetMapping(value = "/csv")
   public ResponseEntity<byte[]> csv() {

      HashMap<String,String> params = new HashMap<String, String>();
      params.put("USER_MAIL", "yunsd@xxxxxx.co.kr");
      params.put("URL_KEY", "e83db13911b9baa90bb04db734303b0f");

      HttpHeaders header = new HttpHeaders();
      header.setContentType(MediaType.valueOf("plain/text"));
      header.set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=csvFile.csv");
      header.setContentLength((exportsService.getCSVbyByte(params)).length);

      return new ResponseEntity<byte[]>(exportsService.getCSVbyByte(params), header, HttpStatus.OK);
   }

   @GetMapping(value = "/excel" )
   public ResponseEntity<byte[]> excel() {

      HashMap<String,String> params = new HashMap<String, String>();
      params.put("USER_MAIL", "yunsd@zzzzzz.co.kr");
      params.put("URL_KEY", "e83db13911b9baa90bb04db734303b0f");

      HttpHeaders header = new HttpHeaders();
      header.setContentType(MediaType.valueOf("text/excel"));
      header.set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=excelFile.xlsx");
      header.setContentLength((exportsService.getExcelbyByte(params)).length);

      return new ResponseEntity<byte[]>(exportsService.getExcelbyByte(params), header, HttpStatus.OK);
   }


}

3. Service (api)

import java.util.HashMap;

/**
 * @author yunsd
 * @date   2018. 12. 04.
 * @Desc   
 */
public interface IExportsService {
    byte[] getCSVbyByte(HashMap<String, String> parameter);
    byte[] getExcelbyByte(HashMap<String, String> parameter);
}

4. ServiceImpl (api)

package kr.co.careercare.highlight.services.exports;

import kr.co.careercare.highlight.dao.exports.ExportsDao;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

/**
 * @author yunsd
 * @date 2018. 12. 04
 * @Desc  DB데이터를 파일로 byte로 생성해서 controller로 넘긴다.
 */
@Service
public class ExportsServiceImpl implements IExportsService {

   @Autowired ExportsDao exportsDao;

    //추출 컬럼 타이틀 설정
   String[] HEADER = {"DATE_CREATE", "PRINT_TEXT", "URL", "MEMO", "IMAGE", "COLOR", "URL_TYPE"};
   int[] SHEET_SIZE = {3000, 10500, 10500, 10500, 10500, 2000, 2000};

   /*CVS 파일 생성*/
   @Override
   public byte[]  getCSVbyByte(HashMap<String, String> parameter) {

       //추출 데이터 가져오기
       List<HashMap<String, String>> exportData = new ArrayList<HashMap<String, String>>();
       exportData = exportsDao.getExportInfo(parameter);

       //저장 위치 및 파일명
       StringWriter sw = new StringWriter();
       CSVPrinter csvPrinter = null;

       try {
           csvPrinter = new CSVPrinter(sw, CSVFormat.DEFAULT.withHeader(HEADER));

           for (HashMap<String, String> res : exportData) {
               List<String> data  = Arrays.asList(
                       res.get("DATE_CREATE"),
                       res.get("PRINT_TEXT").replaceAll(",",""),
                       res.get("URL"),
                       res.get("MEMO"),
                       res.get("IMAGE"),
                       res.get("COLOR"),
                       res.get("URL_TYPE")
               );
               csvPrinter.printRecord(data);
           }
           sw.flush();
           return sw.toString().getBytes("UTF-8");

       } catch (Exception e) {
           e.printStackTrace();
       } finally {
           try {
               csvPrinter.close();
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
       return null;
   }


   /*excel로 파일을 추출한다.*/
    @Override
   public byte[] getExcelbyByte(HashMap<String, String> parameter) {
        String sheetName = "Sheet1";//name of sheet

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName) ;
        sheet.autoSizeColumn(2);
        //추출 데이터 가져오기
        List<HashMap<String, String>> exportData = null;
        exportData = exportsDao.getExportInfo(parameter);

        //컬럼 스타일 지정하기 - 헤드 스타일 지정하기
        XSSFCellStyle headStyle = wb.createCellStyle();
        // 컬러지정
        headStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(228, 228, 228), new DefaultIndexedColorMap()));
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 가운데정렬
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        // 폰트설정
        XSSFFont headFont = wb.createFont();
        // 굵게
        headFont.setBold(true);

        //컬럼명 설정
        XSSFRow headRow = sheet.createRow(0);
        for(int cell = 0; cell < HEADER.length; cell++){
            XSSFCell cellVal = headRow.createCell(cell);
            XSSFRichTextString rt = new XSSFRichTextString(HEADER[cell]);
            // 폰드 적용
            rt.applyFont(headFont);
            cellVal.setCellValue(rt);
            //백그라운드 컬러 적용
            cellVal.setCellStyle(headStyle);
            //컬럼 사이즈 설정
            sheet.setColumnWidth(cell, SHEET_SIZE[cell]);
        }

        //컬럼(cell) 값 설정
        int cellNum = 0;
        for (HashMap<String, String> res : exportData) {
            //첫 번째 라인: 제목, 두 번째 라인부터: 값
            XSSFRow row = sheet.createRow(cellNum+1);

            //컬럼 생성
            for(int cell = 0; cell < HEADER.length; cell++){
                XSSFCell cellVal = row.createCell(cell);
                //컬럼 스타일 지정하기 - 펜 컬러 지정
                XSSFCellStyle penColor = wb.createCellStyle();

                if(cell == 5){
                    switch (res.get(HEADER[cell])){
                        case "hltcolor-1" :
                            penColor.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 175, 174), new DefaultIndexedColorMap()));
                            break;
                        case "hltcolor-2" :
                            penColor.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 205, 134), new DefaultIndexedColorMap()));
                            break;
                        case "hltcolor-3" :
                            penColor.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 238, 124), new DefaultIndexedColorMap()));
                            break;
                        case "hltcolor-4" :
                            penColor.setFillForegroundColor(new XSSFColor(new java.awt.Color(140, 249, 128), new DefaultIndexedColorMap()));
                            break;
                        case "hltcolor-5" :
                            penColor.setFillForegroundColor(new XSSFColor(new java.awt.Color(117, 219, 255), new DefaultIndexedColorMap()));
                            break;
                        case "hltcolor-6" :
                            penColor.setFillForegroundColor(new XSSFColor(new java.awt.Color(  231, 178, 255), new DefaultIndexedColorMap()));
                            break;
                    }
                    penColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                }else{
                    cellVal.setCellValue(res.get(HEADER[cell]));
                }

                cellVal.setCellStyle(penColor);
            }
            //다음 row
            cellNum++;
        }

        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        try {
            wb.write(baos);
            return baos.toByteArray();

        } catch (IOException e) {
            System.out.println("Flushing/closing error!");
            e.printStackTrace();
        }finally {
            try {
                baos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return null;
    }
}

선임에게 넘기기위해 백단 기능 구현함.

localhost:8080/api/exports/csv

localhost:8080/api/exports/excel