1217 엑셀 파일 다운로드 ajax

@Mapper
public interface ActorMapper {
	List<Map<String, Object>> selectActorList(Map<String, Object> paramMap);
}

<mapper namespace="com.example.restSakila.mapper.ActorMapper">
	<select id="selectActorList" parameterType="map" resultType="map">
		SELECT 
			actor_id actorId
			, first_name firstName
			, last_name lastName
			, last_update lastUpdate
		FROM actor
		LIMIT #{beginRow}, #{rowPerPage}
	</select>
</mapper>

@Service
@Transactional
public class ActorService {
	@Autowired ActorMapper actorMapper;
	
	private final int ROW_PER_PAGE = 10;
	
	public List<Map<String, Object>> getActorList(Integer currentPage) {
		int beginRow = (currentPage-1) * ROW_PER_PAGE;
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("beginRow", beginRow);
		paramMap.put("rowPerPage", ROW_PER_PAGE);
		return actorMapper.selectActorList(paramMap);
	}
}

10개만 조회

@Controller
public class ActorController {
	@Autowired ActorService actorService;
	
	@GetMapping("/actorList")
	public String actorList(Model model,@RequestParam(defaultValue="1") Integer currentPage) {
		model.addAttribute("list", actorService.getActorList(currentPage));
		model.addAttribute("currentPage", currentPage);
		return "actorList";
	}
}

@RestController
public class ActorRest {
	@Autowired ActorService actorService;
	
	@GetMapping("/rest/actorList/{currentPage}")
	public List<Map<String,Object>> actorList(@PathVariable Integer currentPage) {
		return actorService.getActorList(currentPage);
	}
}

 

<h1>ACTOR LIST</h1>

<input type="hidden" id="currentPage" value="${currentPage}">

<div>
    <button type="button" id="btnExcel">엑셀파일로 다운로드</button>
</div>

<table class="table">
    <thead>
        <tr>
            <th>Actor ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Last Update</th>
        <tr>
    </thead>
    <c:forEach var="a" items="${list}">
        <tr>
            <td>${a.actorId}</td>
            <td>${a.firstName}</td>
            <td>${a.lastName}</td>
            <td>${a.lastUpdate}</td>
        </tr>
    </c:forEach>
</table>

<!-- excel api : sheetjs-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.5/xlsx.full.min.js"></script>
<!-- file download api : FileServer saveAs-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/1.3.8/FileSaver.min.js"></script>

<script>
    // ajax 반환값 -> 엑셀객체(sheetjs) -> 엑셀파일(FileServer saveAs)
    let table = []; // 엑셀객체에 사용할 데이터

    $('#btnExcel').click(function(){
        $.ajax({
            async : false
            , url:'/rest/actorList/'+$('#currentPage').val()
            , type: 'get'
        }).done(function(result){
            // console.log(result);
            $(result).each(function(index,item){
                let row = []; // 엑셀객체의 행에 사용할 데이터
                row.push(item.actorId);
                row.push(item.firstName);
                row.push(item.lastName);
                row.push(item.lastUpdate);
                // 엑셀객체 사용할 데이터
                table.push(row);
            })
        }).fail(function(){
            alert('실패!')
        });

        // 엑셀객체에 사용할 데이터 생성(ajax result -> 데이터)

        // 1) 엑셀 객체 생성자 호출
        let book = XLSX.utils.book_new(); 
        // 2) 엑셀 객체에 빈 sheet 생성
        book.SheetNames.push('one');
        // 3) 2)의 빈 sheet에 엑셀객체 데이터에 sheet객체 생성 후 book에 대입
        let sheet = XLSX.utils.aoa_to_sheet(table);
        book.Sheets['one'] = sheet;

        // book객체를 엑셀파일로 변경
        let bin = XLSX.write(book,{bookType:'xlsx', type:'binary'});

        // bin 크기의 burffer(스트림) 생성
        let buf = new ArrayBuffer(bin.length);
        let buf2 = new Uint8Array(buf);

        for(let i=0; i<bin.length; i++){
            buf2[i] = bin.charCodeAt(i) & 0xFF;  // 디코딩
        }

        // buf2를 이용하여 엑셀 파일 생성
        let f = new Blob([buf2], {type:"application/octet-stream"});
        saveAs(f, 'actor.xlsx');
    });
</script>

 

 

localhost/actorList