导出统计签到次数excel表，没有则默认为0

1.jsp页面

<td><a class="btn btn-light btn-xs" onclick="toExcel();" title="导出签到汇总表"></a></td>

function toExcel(){
	window.location.href="<c:url value='/kaoqin/excel.do'/>";
}

2.跳转方法

@Controller
@RequestMapping(value="/kaoqin")
public class KaoqinController extends BaseController {
	
	String menuUrl = "kaoqin/list.do"; //菜单地址(权限用)
	@Resource(name="kaoqinService")
	private KaoqinManager kaoqinService;

	
	@RequestMapping(value="/excel")
	public ModelAndView exportExcel() throws Exception{
		ModelAndView mv = new ModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		Map<String,Object> dataMap = new HashMap<String,Object>();
		List<String> titles = new ArrayList<String>();
		titles.add("序号");	//1
		titles.add("姓名");	//1
		titles.add("打卡次数");	//1
		dataMap.put("titles", titles);
		List<PageData> varOList = kaoqinService.ToExcelList(pd);
		List<PageData> varList = new ArrayList<PageData>();
		for(int i=0;i<varOList.size();i++){
			PageData vpd = new PageData();
			String xuhao = String.valueOf(i + 1);
                        vpd.put("var1", xuhao); 
			vpd.put("var2", varOList.get(i).getString("NAME"));	    //9
			vpd.put("var3", varOList.get(i).get("total").toString());	    //1
			varList.add(vpd);
		}
		dataMap.put("varList", varList);
		ObjectExcelView erv = new ObjectExcelView();
		mv = new ModelAndView(erv,dataMap);
		return mv;
	}

}

3.SQL语句

<select id="ToExcelList" parameterType="pd" resultType="pd">
    SELECT suser.NAME,IFNULL(t1.count1,0) AS total FROM SYS_USER suser
	LEFT JOIN (SELECT CREATEUSER_ID,COUNT(*) AS count1 FROM work_kaoqin GROUP BY CREATEUSER_ID) t1 
	on suser.USER_ID = t1.CREATEUSER_ID 
	ORDER BY total
</select>