从数据库导出数据到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文件,文件内地数据是正常的。