前端页面直接下载excel

前端：

var url = '//?datatime='+$("#riqi").val();
location.href = url; 

 

后端：

 

public void toExport(String datatime, HttpServletRequest request,
                           HttpServletResponse response) throws Exception {
    try {
        String year_date = datatime.substring(0, 4);
        String month_date =Integer.parseInt(datatime.substring(5, 7))+"";//去0化

        HSSFWorkbook wb = new HSSFWorkbook();//创建HSSFWorkbook对象
        HSSFSheet sheet = wb.createSheet(datatime.replaceAll("-", "") + "员工考勤");//建立sheet对象
        HSSFRow row1 = sheet.createRow(0); //在sheet里创建第一行，参数为行索引
        HSSFCell cell = row1.createCell(0); //创建单元格
        cell.setCellValue(datatime + "员工考勤"); //设置单元格内容
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));
        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("年份");
        row2.createCell(1).setCellValue("月份");
        row2.createCell(2).setCellValue("员工工号");
        row2.createCell(3).setCellValue("员工姓名");
        //写入日期
        List<String> datalist = getMonthFullDay(datatime);
        for (int d = 0; d < datalist.size(); d++) {
            row2.createCell(d + 4).setCellValue(datalist.get(d).substring(8, 10));
        }
        //查询数据
        List<YgKaoqinEntity> ygKaoqinEntityList = ygKaoqinService.getKaoQinListBymonth(year_date, month_date);

        for (int i = 0; i < ygKaoqinEntityList.size(); i++) {
            //在sheet里创建行
            HSSFRow row = sheet.createRow(i + 2);
            row.createCell(0).setCellValue(ygKaoqinEntityList.get(i).getYear() == null ? "" : ygKaoqinEntityList.get(i).getYear());
            row.createCell(1).setCellValue(ygKaoqinEntityList.get(i).getMonth() == null ? "" : ygKaoqinEntityList.get(i).getMonth());
            row.createCell(2).setCellValue(ygKaoqinEntityList.get(i).getYgcode() == null ? "" : ygKaoqinEntityList.get(i).getYgcode());
            row.createCell(3).setCellValue(ygKaoqinEntityList.get(i).getYgname() == null ? "" : ygKaoqinEntityList.get(i).getYgname());
            row.createCell(4).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay1()));
            row.createCell(5).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay2()));
            row.createCell(6).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay3()));
            row.createCell(7).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay4()));
            row.createCell(8).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay5()));
            .......
            row.createCell(31).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay28()));
            if (datalist.size() > 28) {
                row.createCell(32).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay29()));
            }
            if (datalist.size() > 29) {
                row.createCell(33).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay30()));
            }
            if (datalist.size() > 30) {
                row.createCell(34).setCellValue(EnumsTjApp.KaoQinStatus.getDescByCode(ygKaoqinEntityList.get(i).getDay31()));
            }
        }

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        //设置响应头，
        response.setHeader("Content-disposition", "attachment; filename=kaoqinExcel.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

//获取当月日期
public List<String> getMonthFullDay(String date) {
    List<String> fullDayList = new ArrayList<String>();
    int year = Integer.parseInt(date.substring(0, 4));
    int month = Integer.parseInt(date.substring(5, 7));
    int day = 1;// 所有月份从1号开始
    Calendar cal = Calendar.getInstance();// 获得当前日期对象
    cal.clear();// 清除信息
    cal.set(Calendar.YEAR, year);
    cal.set(Calendar.MONTH, month - 1);// 1月从0开始
    cal.set(Calendar.DAY_OF_MONTH, day);
    int count = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
    for (int j = 0; j <= (count - 1); ) {
        if (DateUtil.formatFromDate("yyyy-MM-dd", cal.getTime()).equals(getLastDay(year, month)))
            break;
        cal.add(Calendar.DAY_OF_MONTH, j == 0 ? +0 : +1);
        j++;
        fullDayList.add(DateUtil.formatFromDate("yyyy-MM-dd", cal.getTime()));
    }
    return fullDayList;
}

public String getLastDay(int year, int month) {
    Calendar cal = Calendar.getInstance();
    cal.set(Calendar.YEAR, year);
    cal.set(Calendar.MONTH, month);
    cal.set(Calendar.DAY_OF_MONTH, 0);
    return DateUtil.formatFromDate("yyyy-MM-dd", cal.getTime());
}