엑셀 생성 + 저장위치 설정 다운로드(리스트)

개요

리스트 엑셀 출력

/**
     * 엑셀 다운로드
     * @param adminBuildEnergyVo
     * @return
     * @throws Throwable 
     */
    @RequestMapping(value="/admin/stats/excelCreate.do")
    @ResponseBody
    public void adminBuildEenrgyExcel(@ModelAttribute("loginStatsVo") LoginStatsVo loginStatsVo, HttpServletRequest request, HttpServletResponse    response) throws Throwable{
        try{
            response.setContentType("text/html;charset=UTF-8");
            PrintWriter out = response.getWriter();
            
            String rdmStr = CommonUtils.getRandomString();
            Calendar todayCal = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyMd");
            int today = Integer.parseInt(sdf.format(todayCal.getTime()));
            String homedir = "D:/eGovFrameDev-3.5.1-32bit/workspace/greencity/src/main/webapp/excel/"+today+"/"; //엑셀 생성
            
            // Workbook 생성
            Workbook xlsWb = new HSSFWorkbook(); // Excel 2007 이전 버전
            
                Workbook xlsxWb = new XSSFWorkbook(); // Excel 2007 이상
                 
                // *** Sheet-------------------------------------------------
                // Sheet 생성
                Sheet sheet = xlsxWb.createSheet("로그인 통계"); //시트이름
         
                // 컬럼 너비 설정
                sheet.setColumnWidth(0, 3000);
                sheet.setColumnWidth(1, 3000);
                sheet.setColumnWidth(2, 3000);
                sheet.setColumnWidth(3, 3000);
                sheet.setColumnWidth(4, 3000);
                // ----------------------------------------------------------
                 
                // *** Style--------------------------------------------------
                // Cell 스타일 생성
                CellStyle cellStyle = xlsxWb.createCellStyle();
                 
                // 줄 바꿈
                cellStyle.setWrapText(true);
                 
                // Cell 색깔, 무늬 채우기
                cellStyle.setFillForegroundColor(HSSFColor.LIME.index);
                cellStyle.setFillPattern(CellStyle.ALIGN_CENTER);
                 
                Row row = null;
                Cell cell = null;
                //----------------------------------------------------------
                
                // 첫 번째 줄
                row = sheet.createRow(0);
                 
                // 첫 번째 줄에 Cell 설정하기-------------
                cell = row.createCell(0);
                cell.setCellValue("번호");
                cell.setCellStyle(cellStyle); // 셀 스타일 적용
                 
                cell = row.createCell(1);
                cell.setCellValue("ID");
                cell.setCellStyle(cellStyle); // 셀 스타일 적용
                 
                cell = row.createCell(2);
                cell.setCellValue("IP");
                cell.setCellStyle(cellStyle); // 셀 스타일 적용
                
                cell = row.createCell(3);
                cell.setCellValue("접속시간");
                cell.setCellStyle(cellStyle); // 셀 스타일 적용
                
                cell = row.createCell(4);
                cell.setCellValue("접속종료시간");
                cell.setCellStyle(cellStyle); // 셀 스타일 적용
                //---------------------------------
                 
                // 두 번째 줄
                row = sheet.createRow(1);                                
                
                List<LoginStatsVo> list = statsService.selectExcelDown(loginStatsVo); //로그인 통계 목록 조회
                
                System.out.println("size:"+list.size());
                
                //객체 json 변환
//                JSONArray jsonArray = new JSONArray();
//                jsonArray.add(loginStatsVo);
//                out.println(jsonArray);
                
                for(int i=0; i < list.size(); i++){
                    
                    System.out.println("list"+list.get(i).toString());
                    
                    row = sheet.createRow(i+1);
                 
                    // 두 번째 줄에 Cell 설정하기-------------
                    cell = row.createCell(0);
                    cell.setCellValue(list.get(i ).getLoginstatsno());
                     
                    cell = row.createCell(1);
                    cell.setCellValue(list.get(i ).getUserid());
                     
                    cell = row.createCell(2);
                    cell.setCellValue(list.get(i ).getUserip());
                    
                    cell = row.createCell(3);
                    cell.setCellValue(list.get(i ).getConnecttime());
                    
                    cell = row.createCell(4);
                    cell.setCellValue(list.get(i ).getLogouttime());
                
                }
                //---------------------------------
                
                // excel 파일 저장
                
                try {
                    
                    File file = new File(homedir+rdmStr+"/");
                    if(file.exists() == false){
                        file.mkdirs();
                    }
                    
                    File xlsFile = new File(homedir+rdmStr+"_로그인 통계.xls");
                    System.out.println("2222222222"+xlsFile.toString());
                    FileOutputStream fileOut = new FileOutputStream(xlsFile);
                    xlsxWb.write(fileOut);
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                                 
                
//            out.println(jsonArray);
//            String zipName = homedir+rdmStr+".zip";
//            Zip.compress(homedir+rdmStr+"/");
            out.println(homedir+rdmStr+"_로그인 통계.xls");
            
            }catch(Exception e){
                System.err.println(e.toString());
            }
            
//        return "y";
    }

@RequestMapping(value="/admin/stats/excelDownLoad.do")
    public void adminBuildEenrgyExcel2(HttpServletRequest request, HttpServletResponse    response){
        try{
//            String root = "D:/eGovFrameDev-3.5.1-32bit/workspace/greencity/src/main/webapp/";
            String root = "D:/eGovFrameDev-3.5.1-32bit/workspace/greencity/src/main/webapp/";
            
//            String rootPath = root + "/";
//            byte fileByte[] = FileUtils.readFileToByteArray(new File("D:/eGovFrameDev-3.5.1-32bit/workspace/test2/src/main/webapp"+saveFileName));
            System.out.println("path : "+request.getParameter("path"));
            byte fileByte[] = FileUtils.readFileToByteArray(new File(request.getParameter("path")));
    
            response.setContentType("application/octet-stream");
            response.setContentLength(fileByte.length);
 
            response.setHeader("Content-Disposition", "attachment; fileName=\"" + URLEncoder.encode(request.getParameter("path").substring(request.getParameter("path").lastIndexOf("/")),"UTF-8").replace("+", "%20")+"\";");
            response.setHeader("Content-Transfer-Encoding", "binary");
            response.getOutputStream().write(fileByte);
            
            response.getOutputStream().flush();
            response.getOutputStream().close();
        }catch(Exception e){
            System.out.println(e.toString());
        }

function fn_execlView() {
         
//             $(".loginstatsno").attr("name", "loginstatsno"); //name값 삽입
 
            console.log($("form[name=frm]").serialize());
            
             $.ajax({
                    url : "/admin/stats/excelCreate.do",
                    type : "POST",
                    data : $("form[name=frm]").serialize(),
                    dataType : "text",
    
                    success : function(response) {
//                         $(".loginstatsno").attr("name", ""); //name 삭제
                        location.href = "/admin/stats/excelDownLoad.do?path="+response;
                    },
    
                    error : function(request, status, error) {
                        alert("서버 통신에 실패하였습니다.");
                    }
    
                });
            
        }