boardAjax 만들기 5 (리스트 페이징2, 엑셀파일 다운로드)

@ boardAjax 만들기 5 (리스트 페이징2, 엑셀파일 다운로드)

1. 페이징
- MyUtil.java

- AccountService.java
/**
 * 데이터 목록
 * @param commandMap
 * @return
 */
public List<Map<String,Object>> selectList(int start,int end);

- AccountController.java
/**
 * account 목록 조회
 * @param searchVO - 조회할 정보가 담긴 SampleDefaultVO
 * @param model
 * @return /account/accountList.jsp
 * @exception Exception
 */
@RequestMapping(value = "/account/accountList.do")
public ModelAndView selectSampleList(HttpServletRequest request, ModelMap model,@RequestParam Map<String,Object> commandMap) throws Exception {
/*
egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationInfo paginationInfo = new egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationInfo();
 
        int pageNo = 1; //현재 페이지 번호
        int listScale = 10; //한 페이지에 나올 글 수
        int pageScale = 5; //페이지 개수
        
        try {
            if(commandMap.size() > 0) {
                pageNo = Integer.parseInt((String)commandMap.get("pageNo"));//현재 페이지 번호
            }
 
            paginationInfo.setCurrentPageNo(pageNo);
            paginationInfo.setRecordCountPerPage(listScale); 
            paginationInfo.setPageSize(pageScale); 
            
            int startPage = paginationInfo.getFirstRecordIndex(); //시작 페이지
            int lastPage = paginationInfo.getRecordCountPerPage(); //마지막 페이지
            
            commandMap.put("pageNo", pageNo);
            commandMap.put("startPage", startPage);
            commandMap.put("lastPage", lastPage);
            
            int totalList = accountService.selectDataCount(commandMap); //전체 글 개수
            paginationInfo.setTotalRecordCount(totalList);
 
            List<Map<String,Object>> boardList = accountService.selectList(commandMap); //커뮤니티 글 목록
            
            System.out.println(boardList);
            
            model.addAttribute("pageNo",pageNo);
            model.addAttribute("boardList",boardList);
            model.addAttribute("totalList",totalList);
            model.addAttribute("paginationInfo",paginationInfo);
            
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        
     return "/account/accountList";
     */
     ModelAndView mav = new ModelAndView();

String cp = request.getContextPath();

String pageNum = request.getParameter("pageNum");

int currentPage = 1;

if(pageNum!=null) {
currentPage = Integer.parseInt(pageNum);
}

int dataCount = accountService.selectDataCount(commandMap);

int numPerPage = 5;
int totalPage = myUtil.getPageCount(numPerPage, dataCount);

if(currentPage>totalPage) {
currentPage = totalPage;
}

int start = (currentPage-1)*numPerPage+1;
int end = currentPage*numPerPage;

List lists = accountService.selectList(start,end);

String listUrl = cp + "/account/accountList.do";

//페이징
String pageIndexList = myUtil.pageIndexList(currentPage, totalPage, listUrl);

mav.setViewName("/account/accountList"); //뷰의 경로 - tiles.xml에 되어있다.

mav.addObject("AccountList", lists);
mav.addObject("pageIndexList", pageIndexList);
mav.addObject("dataCount", dataCount);

return mav;

/*Map<String, Object> inOutMap  = CommUtils.getFormParam(request);

model.put("inOutMap", inOutMap);
return "/account/accountList";*/

}

- accountList.jsp
<div class="paging">
<%-- <ui:pagination paginationInfo = "${paginationInfo}" type="image" jsFunction="linkPage"/> --%>
<div>
<c:if test="${dataCount!=0 }">
${pageIndexList }
</c:if>
<c:if test="${dataCount==0 }">
등록된 게시물이 없습니다.
</c:if>
</div>
</div>

오류 분석 : 데이터의 갯수는 조회가 되었지만 model이나 modelandview, 심지어 request로 데이터를 넘겨도 받지 못했다. 오류도 뜨지 않았다.
타일쪽 문제인가 예상만 되고 자세힌 모르겠다.
결국 페이징처리는 이전 방법으로.. 

2. 엑셀파일 다운로드
https://offbyone.tistory.com/250 - 참고(poi 활용)
https://jhhan009.tistory.com/67 - 참고(엑셀파일다운 간단)
https://www.egovframe.go.kr/wiki/doku.php?id=egovframework:rte3:fdl:excel - 참고(전자정부 프레임워크 정석)
https://stackoverflow.com/questions/62789174/java-lang-nosuchmethoderror-org-apache-xmlbeans-xmloptions-setentityexpansionli - 참고(poi사용 소스)

- pom.xml
<!-- org.apache.poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.1.0</version>
</dependency>

버전을 여러가지 써봤지만 계속 오류가 났다. 하지만 위 버전은 마법같이 잘 됬다.

- Common_SQL.xml
<!-- 엑셀 다운로드할 목록 -->
<select id="selectListExcel" resultType="AccountDTO">
select profit_cost,big_group,middle_group,small_group,detail_group,
transaction_money,TO_CHAR(transaction_date,'YYYY/MM/DD') transaction_date,writer
from ACCOUNT_TB
</select>

- AccountService.java
/**
* 엑셀 다운로드할 목록
* @return List
*/
public List selectListExcel();

- AccountDAO.java
/**
* 엑셀 다운로드할 목록
* @return List
*/
public List selectListExcel() {

return sqlSession.selectList("Common.selectListExcel");

}

- AccountServiceImpl.java
//엑셀 다운로드할 목록
@Override
public List selectListExcel() {

return accountDAO.selectListExcel();

}

- AccountController.java
/**
* 엑셀파일 다운로드
* @param response
* @throws Exception
*/
@RequestMapping(value={"/account/accountListExcel.do"})
public void accountListExcel(HttpServletResponse response) throws Exception{

Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("첫번째 시트");
        Row row = null;
        Cell cell = null;
        int rowNum = 0;

        // Header
        row = sheet.createRow(rowNum++);
        cell = row.createCell(0);
        cell.setCellValue("수익/비용");
        cell = row.createCell(1);
        cell.setCellValue("관");
        cell = row.createCell(2);
        cell.setCellValue("항");
        cell = row.createCell(3);
        cell.setCellValue("목");
        cell = row.createCell(4);
        cell.setCellValue("과");
        cell = row.createCell(5);
        cell.setCellValue("금액");
        cell = row.createCell(6);
        cell.setCellValue("등록일");
        cell = row.createCell(7);
        cell.setCellValue("작성자");
        
        List<AccountDTO> lists = accountService.selectListExcel();
        
        // Body
        for (AccountDTO dto : lists) {
            row = sheet.createRow(rowNum++);
            cell = row.createCell(0);
            cell.setCellValue(dto.getProfit_cost());
            cell = row.createCell(1);
            cell.setCellValue(dto.getBig_group());
            cell = row.createCell(2);
            cell.setCellValue(dto.getMiddle_group());
            cell = row.createCell(3);
            cell.setCellValue(dto.getSmall_group());
            cell = row.createCell(4);
            cell.setCellValue(dto.getDetail_group());
            cell = row.createCell(5);
            cell.setCellValue(dto.getTransaction_money());
            cell = row.createCell(6);
            cell.setCellValue(dto.getTransaction_date());
            cell = row.createCell(7);
            cell.setCellValue(dto.getWriter());
        }
        
        // 컨텐츠 타입과 파일명 지정
        response.setContentType("ms-vnd/excel");
        //response.setHeader("Content-Disposition", "attachment;filename=example.xls");
        response.setHeader("Content-Disposition", "attachment;filename=Account_Inform_List.xlsx");

        // Excel File Output
        wb.write(response.getOutputStream());
        wb.close();
    
}

- accountList.jsp
<form action="./accountListExcel.do" method="post">
<button class="btn btn-primary" type="submit">엑셀 다운</button>
</form>