Springboot项目导出excel表功能

修改list内容即可
package com.ias.teacherPC.common.controller;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.pool.ObjectPool;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ias.admin.common.controller.BaseController;
import com.ias.admin.common.service.StudentService;
import com.ias.admin.eduAdmin.pojo.ArrCoursePO;
import com.ias.admin.eduAdmin.pojo.StuCoursePO;
import com.ias.admin.eduAdmin.pojo.TeaClaDetaPO;
import com.ias.admin.eduAdmin.service.ArrCourseService;
import com.ias.admin.eduAdmin.service.CourseService;
import com.ias.admin.eduAdmin.service.StuCourseService;
import com.ias.admin.eduAdmin.service.TeaClaDetaService;
import com.ias.admin.eduAdmin.service.TeachClassService;
import com.ias.admin.eduAdmin.service.TermService;

@Controller
@RequestMapping("/tp/Excel")
public class ExcelUtil extends BaseController {
   @Autowired
   TermService termService;
   @Autowired
   CourseService courseService;
   @Autowired
   TeachClassService teachClassService;
   @Autowired
   ArrCourseService arrCourseService;
   @Autowired
   StudentService studentService;
   @Autowired
   StuCourseService stuCourseService;
   @Autowired
   TeaClaDetaService teaClaDetaService;

   @ResponseBody
   @GetMapping("excel")
    public void excel( @RequestParam Map<String, Object> map,HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        @SuppressWarnings("resource")
        HSSFWorkbook wb = new HSSFWorkbook();
        Object[]numberlist = null;
        List<Object> temp= null;
        List<List<Object>> all = null;
        HSSFSheet sheet = wb.createSheet("table");  //创建table工作薄
        HSSFRow row;
        HSSFCell cell;
      //找出所有課程id
      List<ArrCoursePO> arrCoursePOs1 =  arrCourseService.list(map);
      map.put("teacherId", this.getTeacherId());
      if( !arrCoursePOs1.isEmpty() ){
         //找出term_id course_id teach_class_id 符合條件的學生
         Map<String,Object> teaClaDetKey = new HashMap<String,Object>();
         teaClaDetKey.put("teachClassId", map.get("teachClassId"));
         List<TeaClaDetaPO> teaClaDetaPOs =  teaClaDetaService.list(teaClaDetKey);

         List<Integer> arrCourseIds = new ArrayList<>();
         for(ArrCoursePO arrCoursePO : arrCoursePOs1){
            arrCourseIds.add(arrCoursePO.getId());
         }
         System.out.println(arrCourseIds);

         //考勤情況
         List<List<StuCoursePO>> List = new ArrayList<List<StuCoursePO>>();
         List<StuCoursePO> list;
         Map<String,Object> stu_numKey = new HashMap<String,Object>();
         for (TeaClaDetaPO teaClaDetaPO : teaClaDetaPOs) {
            list = new ArrayList<StuCoursePO>();
            stu_numKey.put("studentId", teaClaDetaPO.getStudentId());
            stu_numKey.put("arrCourseIds", arrCourseIds);
            List<StuCoursePO> stCous = stuCourseService.TPAttendForm(stu_numKey);
            /*for (ArrCoursePO ArrCoursePO : arrCoursePOs1) {
               stu_numKey.put("arrCourseId", ArrCoursePO.getId());
               stCous = stuCourseService.list(stu_numKey);
               if(!stCous.isEmpty()){
                  list.add(stCous.get(0));
               }
            }*/
            List.add(stCous);
            stu_numKey.clear();
         }

         //表格數字
         List<String> number = new ArrayList<String>();
         for(int num = 1;num<=List.get(0).size();num++){
            number.add(String.valueOf(num));
         }
         number.add(0,"学生学号");
         number.add(1,"学生姓名");
         number.add(2,"学生班级");
         numberlist = number.toArray();
         all = new ArrayList<List<Object>>();
         List<Object> temp2 ;
         for (int i = 0; i < teaClaDetaPOs.size(); i++) {
            temp2 = new ArrayList<Object>();
            TeaClaDetaPO teaClaDetaPO = teaClaDetaPOs.get(i);
            temp2.add(teaClaDetaPO.getStudentNumber());
            temp2.add(teaClaDetaPO.getStudentName());
            temp2.add(teaClaDetaPO.getClassName());
            for (int j = 0; j < List.get(i).size(); j++) {
               /*<span th:case="1">√</span>
               <span th:case="0">×</span>
               <span th:case="-1">o</span>
                <span th:case="-2">△</span>
               <span th:case="-3">●</span>
                * */
               if(List.get(i).get(j).getAttend() == 1){
                  temp2.add("√");
               }else if(List.get(i).get(j).getAttend() == 0){
                  temp2.add("×");
               }else if(List.get(i).get(j).getAttend() == -1){
                  temp2.add("o");
               }else if(List.get(i).get(j).getAttend() == -2){
                  temp2.add("△");
               }else{
                  temp2.add("●");
               }
               
            }
            all.add(temp2);
         }
      }
      Object[][] datas = {{" ", " ", " ","节次（两节算一次课）√表示正常，×表示旷课，o表示迟到，△表示迟到，●表示请假"},numberlist};
      for (int i = 0; i < datas.length; i++) {
           temp = new ArrayList<Object>();
           Object [] a = datas[i];
           if(a != null){
              for (int j = 0; j < a.length; j++) {
                 temp.add(a[j]);
              }
              all.add(i,temp);
           }
        }

        for(int i = 0; i < all.size(); i++) {
            row = sheet.createRow(i);//创建表格行
            for(int j = 0; j < all.get(i).size(); j++) {
                cell = row.createCell(j);//根据表格行创建单元格
                cell.setCellValue(String.valueOf(all.get(i).get(j)));
            }
        }
           ByteArrayOutputStream os = new ByteArrayOutputStream();
           wb.write(os);
           byte[] content = os.toByteArray();
           InputStream is = new ByteArrayInputStream(content);
           response.reset();
           response.setContentType("application/vnd.ms-excel;charset=utf-8");
           response.setHeader("Content-Disposition", "attachment;filename=table.xls");
           
                 ServletOutputStream out = response.getOutputStream();
              BufferedInputStream bis = null;
              BufferedOutputStream bos = null;
         
              try {
                bis = new BufferedInputStream(is);
                bos = new BufferedOutputStream(out);
                byte[] buff = new byte[2048];
                int bytesRead;
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                  bos.write(buff, 0, bytesRead);
                }
              } catch (Exception e) {
                e.printStackTrace();
              } finally {
                if (bis != null)
                  bis.close();
                if (bos != null)
                  bos.close();
              }
//        wb.write(new FileOutputStream("D:/table22.xls"));//这行是直接写到本地的
    }

}

 

效果图：