springcloud--数据库中数据导出Excel功能

从数据库导出数据到excel

代码

service和mapper层代码如下：

//Mapper层
@Repository
@Mapper
public interface PatStudentMapper extends BaseMapper<Patuser> {
    List<Patstudent> selectAllStudent();

//service
@Service
public class PatStudentService {

    @Autowired
    private PatStudentMapper patStudentMapper;

    public List<Patstudent> selectAllStudent() {
        return patStudentMapper.selectAllStudent();
    }
}

PatStudent类是提前创建好的。

后台Controller层代码如下：

@RestController
@RequestMapping("/student")
public class PatStudentController {
@Autowired
    private PatStudentService patStudentService;

@RequestMapping(value = "/UserExcelDownloads", method = RequestMethod.GET)
    public Result downloadAllClassmate(HttpServletResponse response){

		//返回前端结果
        Result result = new Result();

        logger.info("创建Excel表");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("学生信息表");

        List<Patstudent> patstudentList = patStudentService.selectAllStudent();

        String fileName = "Pat-Student-Info.xls";//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据

        int rowNum = 1;

        String[] headers = { "用户名", "身份证号","考试类型","毕业年月","姓名","性别","手机号","邮箱","学校","学院","专业名称","班级","学号","准考证号","座位号","考试教室","密码"};
        //headers表示excel表中第一行的表头

        logger.info("在excel表中添加表头");
        HSSFRow row = sheet.createRow(0);

        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        logger.info("在表中存放查询到的数据放入对应的列");
        for (Patstudent patstudent : patstudentList) {
            HSSFRow row1 = sheet.createRow(rowNum);

            row1.createCell(0).setCellValue(patstudent.getPatuser());
            row1.createCell(1).setCellValue(patstudent.getId());
            row1.createCell(2).setCellValue(patstudent.getType());
            row1.createCell(3).setCellValue(patstudent.getGraduate());
            row1.createCell(4).setCellValue(patstudent.getName());
            row1.createCell(5).setCellValue(patstudent.getSex());
            row1.createCell(6).setCellValue(patstudent.getPhone());
            row1.createCell(7).setCellValue(patstudent.getEmail());
            row1.createCell(8).setCellValue(patstudent.getSchool());
            row1.createCell(9).setCellValue(patstudent.getCollege());
            row1.createCell(10).setCellValue(patstudent.getMajority());
            row1.createCell(11).setCellValue(patstudent.getClassroom());
            row1.createCell(12).setCellValue(patstudent.getStuNumber());
            row1.createCell(13).setCellValue(patstudent.getTicketNumber());
            row1.createCell(14).setCellValue(patstudent.getSeatNumber());
            row1.createCell(15).setCellValue(patstudent.getExamroom());
            row1.createCell(16).setCellValue(patstudent.getPassword());

            rowNum++;
        }

        logger.info("导出数据");
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            result.setStatus(200,"数据导出成功");
        }catch (IOException e){
            logger.info("导出失败");
            result.setStatus(400,"数据导出失败");
            e.printStackTrace();
        }
        return result;
    }
}

参考链接：springboot实现数据库中数据导出Excel功能

在写完我是用postman进行测试的

主要是参考：
postman 测试Excel文件导入导出功能
注：在postman中测试是显示的是一堆乱码，不过这是正常情况，用上述链接的方式导出excel文件，文件内地数据是正常的。