javaee导出excel文件并下载到本地

javaee导出excel文件并下载到本地

后台代码，controller

 @RequestMapping(value = {"excel"})
    public void excel(HttpServletResponse response,HttpServletRequest request) {
        //创建新的Excel工作簿
        String student_id = (String) request.getSession().getAttribute("student_id");
        SXSSFWorkbook workbook = null;
        UtilPoi export = new UtilPoi();
        try {
            String excelName = "absence";
            OutputStream out = response.getOutputStream();
            excelName = new String(excelName.getBytes("GBK"), "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + excelName + ".xlsx");

            workbook = export.expor_Absences(absenceService.selectBysidstate1(student_id));

            workbook.write(out);
            out.flush();
            out.close();


        } catch (IOException e) {
            e.printStackTrace();
        }


    }

其中absenceService.selectBysidstate1(student_id)为查找出的数据

UtilPoi 代码

public class UtilPoi {
    public SXSSFWorkbook expor_Absences(List<Absence> absences){
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        Sheet sheet = workbook.createSheet();
        int applicantsSize = absences.size();
        Row row = sheet.createRow(0);
        Cell title1 = row.createCell(0);
        Cell title2 = row.createCell(1);
        Cell title3 = row.createCell(2);
        Cell title4 = row.createCell(3);
        Cell title5 = row.createCell(4);
        Cell title6 = row.createCell(5);
        Cell title7 = row.createCell(6);
        Cell title8 = row.createCell(7);
        Cell title9 = row.createCell(8);
        Cell title10 = row.createCell(9);

        title1.setCellValue("Id");
        title2.setCellValue("姓名");
        title3.setCellValue("日期");
        title4.setCellValue("开始时间");
        title5.setCellValue("结束时间");
        title6.setCellValue("类型");
        title7.setCellValue("原因");
        title8.setCellValue("状态");
        title9.setCellValue("学期");
        title10.setCellValue("课程");

        for (int i = 0; i < applicantsSize; i++){
            Absence absence = absences.get(i);
            Row row1 = sheet.createRow(i+1);
            Cell cell1 = row1.createCell(0);
            Cell cell2 = row1.createCell(1);
            Cell cell3 = row1.createCell(2);
            Cell cell4 = row1.createCell(3);
            Cell cell5 = row1.createCell(4);
            Cell cell6 = row1.createCell(5);
            Cell cell7 = row1.createCell(6);
            Cell cell8 = row1.createCell(7);
            Cell cell9 = row1.createCell(8);
            Cell cell10 = row1.createCell(9);

            cell1.setCellValue(absence.getId());
            cell2.setCellValue(absence.getStudentName());
            cell3.setCellValue(absence.getAddTime());
            cell4.setCellValue(absence.getStartTime());
            cell5.setCellValue(absence.getEndTime());
            cell6.setCellValue(absence.getType());
            cell7.setCellValue(absence.getReason());
            cell8.setCellValue(absence.getStateId());
            cell9.setCellValue(absence.getTerm());
            cell10.setCellValue(absence.getCourse());
        }

        return workbook;
    }

title为excel第一行内容

cell为导出数据的内容