Apache poi 4.0.x 라이브러리 Spring excel 다운로드 (응용1)

jsp는 똑같음

$('#btnExcelDown').click(function () {
            let searchForm = setBeforeSubmit();
            if (confirm("다운로드 하시겠습니까?") == false) {
                return;
            }

            if (!searchForm) {
                alert("일시적인 오류입니다. 새로고침 후 재시도하시기 바랍니다.");
                return;
            }

            location.href = "/emp/excelDown?" + searchForm;
        });

 

controller 는 변경됨

 

    @RequestMapping("/excelDown")
    public ResponseEntity<String> excelDown(HttpServletRequest request, HttpServletResponse response, Model model)  throws Exception {
        model.addAttribute("request", request);
        model.addAttribute("response", response);
        return CommonUtil.createFileEntity(empExcelDownService, model);
    }

 

CommonUtil Class

public class CommonUtil {
    private static Logger logger = LoggerFactory.getLogger(CommonUtil.class);

    public static ResponseEntity<String> createFileEntity(IService service, Model model) throws Exception {

        HttpHeaders responseHeaders = new HttpHeaders();
        responseHeaders.add("Content-Type", "application/download; charset=UTF-8");

        return new ResponseEntity<String>(service.execute(model), responseHeaders, HttpStatus.CREATED);
    }
}

 

완성된 Service 부분

여기서 이제 내용만 넣어서 셋팅 하면 끝

브라우저 파일 이름 인코딩 작업은 따로 클래스로 빼도 될듯 하다. 

중요한건 response에 header 작업, file 생성시 dir, name 둘다 넘겨야 제대로 뜬다는것. 

@Service
public class EmpExcelDownService implements IService {
    private final Logger logger = LoggerFactory.getLogger(EmpUpdatePersonalService.class);

    @Inject
    private SqlSession sqlSession;


    @Override
    public String execute(Model model) throws Exception {

        EmpDAO dao = sqlSession.getMapper(EmpDAO.class);

        HttpServletRequest request = (HttpServletRequest) model.asMap().get("request");
        HttpServletResponse response = (HttpServletResponse) model.asMap().get("response");

        HttpSession session = request.getSession();
        LoginDTO loginUserInfo = (LoginDTO)session.getAttribute("loginUserInfo");

        String fileDir ="C:\\Users\\user\\Desktop\\";
        String fileName = "down_emp_"+CommonUtil.getCurrentDate("yyyyMMddHHmmss") + ".xlsx";

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("My Sheet");
        sheet.setColumnWidth(0, 2560);
        sheet.setColumnWidth(1, 2560);

        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Hello");

        // 여기서부터는 각 브라우저에 따른 파일이름 인코딩작업
        String browser = request.getHeader("User-Agent");
        if (browser.indexOf("MSIE") > -1) {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        } else if (browser.indexOf("Trident") > -1) {       // IE11
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        } else if (browser.indexOf("Firefox") > -1) {
            fileName = "\"" + new String(fileName.getBytes("UTF-8"), "8859_1") + "\"";
        } else if (browser.indexOf("Opera") > -1) {
            fileName = "\"" + new String(fileName.getBytes("UTF-8"), "8859_1") + "\"";
        } else if (browser.indexOf("Chrome") > -1) {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < fileName.length(); i++) {
                char c = fileName.charAt(i);
                if (c > '~') {
                    sb.append(URLEncoder.encode("" + c, "UTF-8"));
                } else {
                    sb.append(c);
                }
            }
            fileName = sb.toString();
        } else if (browser.indexOf("Safari") > -1){
            fileName = "\"" + new String(fileName.getBytes("UTF-8"), "8859_1")+ "\"";
        } else {
            fileName = "\"" + new String(fileName.getBytes("UTF-8"), "8859_1")+ "\"";
        }


        response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ";");
        response.setHeader("Content-Transfer-Encoding", "binary");

        File file = new File(fileDir,fileName);
        file.createNewFile();

        FileOutputStream outputStream = new FileOutputStream(file, false);
        wb.write(outputStream);
        if(outputStream!=null) {
            outputStream.close();
        }

        return null;
    }
}