Excel表格导出

1.后端

    /**
     * 导出一对一线下课签到表
     * @param response
     * @param selectedArriveTime
     * @param selectedClassRoom
     */
    @RequestMapping(value = "/exportExcel", method = {RequestMethod.GET})
    public void exportExcel(HttpServletResponse response, String selectedArriveTime, Integer selectedClassRoom) {
        //查询当天有一对一线下课同学信息
        List<TDArrangingReservation> studentInfo = reservationService.getStudentInfo(selectedArriveTime, selectedClassRoom);
        //创建excel表格
        if (studentInfo != null && studentInfo.size()  > 0) {
            //创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            //建立新的sheet对象(excel的表单)
            HSSFSheet sheet0 = wb.createSheet("sheet0");
            //创建单元格样式
            CellStyle centerStyle = wb.createCellStyle();
            //设置单元格内容的水平对齐方式
            centerStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置单元格内容的垂直对齐方式
            centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //创建标题行
            HSSFRow titleRow = sheet0.createRow(0);
            //设置标题行高
            titleRow.setHeight((short) 500);
            //设置标题行
            String[] titles = {"序号", "学生姓名", "上课时间", "上课教室", "督导/指导教师", "任课老师", "上课科目", "本人签字", "到校时间", "离校时间"};
            //设置列宽
            int[] columnWidths = {1500, 3000, 4000, 3000, 4000, 3000, 4000, 3000, 3000, 3000};
            //设置标题行值和样式
            for (int i = 0; i < titles.length; i++) {
                sheet0.setColumnWidth(i, columnWidths[i]);
                HSSFCell cell = titleRow.createCell(i);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(centerStyle);
            }

            int rowIndex = 1;
            String lastStudentName = "";
            //设置开始合并的行号
            int mergeStartRow = 1;
            //循环赋值
            for (int i = 0; i < studentInfo.size(); i++) {
                TDArrangingReservation reservation = studentInfo.get(i);
                //创建数据行
                HSSFRow row = sheet0.createRow(rowIndex);
                //设置数据行高
                row.setHeight((short) 400);
                //学生姓名
                String currentStudentName = reservation.getStuNickName() != null ? reservation.getStuNickName() : "";
                //如果当前学生姓名与上一个不同，且不是第一个学生，则需要合并单元格(合并的是之前一样的数据)。
                if (!currentStudentName.equals(lastStudentName) && i > 0) {
                    //合并指定列的单元格
                    //学生姓名
                    mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 1, centerStyle);
                    //上课教室
                    mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 3, centerStyle);
                    //督导/指导教师
                    mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 4, centerStyle);
                    //本人签字
                    mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 7, centerStyle);
                    //到校时间
                    mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 8, centerStyle);
                    //离校时间
                    mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 9, centerStyle);
                    mergeStartRow = rowIndex;
                }
                //创建单元格，设置值和样式。
                //序号
                setCellValueAndStyle(row.createCell(0), String.valueOf(rowIndex), centerStyle);
                //学生姓名
                setCellValueAndStyle(row.createCell(1), currentStudentName, centerStyle);
                //上课时间
                setCellValueAndStyle(row.createCell(2), reservation.getPreTimeFragment(), centerStyle);
                //上课教室
                setCellValueAndStyle(row.createCell(3), reservation.getClassRoom(), centerStyle);
                //督导/指导教师
                setCellValueAndStyle(row.createCell(4), reservation.getCounselorName(), centerStyle);
                //任课老师
                setCellValueAndStyle(row.createCell(5), reservation.getTeacherName(), centerStyle);
                //上课科目
                setCellValueAndStyle(row.createCell(6), reservation.getSubName(), centerStyle);
                //本人签字
                setCellValueAndStyle(row.createCell(7), "", centerStyle);
                //到校时间
                setCellValueAndStyle(row.createCell(8), "", centerStyle);
                //离校时间
                setCellValueAndStyle(row.createCell(9), "", centerStyle);
                lastStudentName = currentStudentName;
                rowIndex++;
            }
            // 处理最后一组相同学生姓名的合并
            mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 1, centerStyle);
            mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 3, centerStyle);
            mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 4, centerStyle);
            mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 7, centerStyle);
            mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 8, centerStyle);
            mergeCellsIfNeeded(sheet0, mergeStartRow, rowIndex - 1, 9, centerStyle);
            //输出文件
            try {
                OutputStream output = response.getOutputStream();
                String filename = "一对一线下课签到表";
                String encodedFilename = URLEncoder.encode(filename, "UTF-8");
                response.reset();
                response.setHeader("Content-disposition", "attachment; filename=" + encodedFilename + ".xls");
                response.setContentType("application/msexcel");
                wb.write(output);
                output.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 合并单元格
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param col
     * @param style
     */
    private void mergeCellsIfNeeded(HSSFSheet sheet, int firstRow, int lastRow, int col, CellStyle style) {
        if (firstRow < lastRow) {
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, col, col);
            sheet.addMergedRegion(region);
            for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
                HSSFRow row = sheet.getRow(i);
                if (row == null) {
                    row = sheet.createRow(i);
                }
                HSSFCell cell = row.getCell(col);
                if (cell == null) {
                    cell = row.createCell(col);
                }
                cell.setCellStyle(style);
            }
        }
    }

    /**
     * 设置单元格值和样式
     * @param cell
     * @param value
     * @param style
     */
    private void setCellValueAndStyle(HSSFCell cell, String value, CellStyle style) {
        cell.setCellValue(value != null ? value : "");
        cell.setCellStyle(style);
    }

2.前端

//导出签到表数据
	function exportData() {
		//签到时间
		var selectedArriveTime = $("#time1").val();
		//上课地点
		var classRoom = document.getElementsByName('information');
		var selectedClassRoom = '';
		for (var i = 0; i < classRoom.length; i++) {
			if (classRoom[i].checked) {
				selectedClassRoom = classRoom[i].value;
				break;
			}
		}
		window.location.href = '${currentBaseUrl}/exportExcel?' + 'selectedArriveTime='+selectedArriveTime+'&selectedClassRoom='+selectedClassRoom;
		setTimeout(function() {
			$("#exportTipsInfoModule").hide();
		}, 3000);
	}