1.controller
package com.tuanzi.controller;
import com.tuanzi.entity.Student;
import com.tuanzi.service.StudentService;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@RestController
public class StudentController {
@Autowired
private StudentService studentService;
@RequestMapping(value = "/UserExcelDownloads",method = RequestMethod.GET)
public void UserExcelDownloads(HttpServletResponse response)throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("信息表");
List<Student> UserExcelDownloads = studentService.UserExcelDownloads();
String fileName = "students" + ".xls";
int rowNum = 1;
String [] headers = {"学号","姓名","身份类型","登录密码"};
HSSFRow row = sheet.createRow(0);
for (int i = 0;i<headers.length;i++){
HSSFCell cell = row.createCell((short) i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (Student student : UserExcelDownloads){
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell((short) 0).setCellValue(new HSSFRichTextString(student.getNumber()));
row1.createCell((short) 1).setCellValue(new HSSFRichTextString(student.getName()));
row1.createCell((short) 2).setCellValue(new HSSFRichTextString(student.getType()));
row1.createCell((short) 3).setCellValue(new HSSFRichTextString(student.getPassword()));
rowNum++;
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
}
}
2.service
package com.tuanzi.service;
import com.tuanzi.dao.StudentMapper;
import com.tuanzi.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentService {
@Autowired
private StudentMapper studentMapper;
public List<Student> UserExcelDownloads() {
return studentMapper.UserExcelDownloads();
}
}
3.mapper接口
List<Student> UserExcelDownloads();
4.mapper.xml
<select id="UserExcelDownloads" resultMap="BaseResultMap">
select
*
from student
</select>
5.实体类
package com.tuanzi.entity;
public class Student {
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column student.id
*
* @mbg.generated
*/
private Integer id;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column student.number
*
* @mbg.generated
*/
private String number;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column student.name
*
* @mbg.generated
*/
private String name;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column student.type
*
* @mbg.generated
*/
private String type;
/**
*
* This field was generated by MyBatis Generator.
* This field corresponds to the database column student.password
*
* @mbg.generated
*/
private String password;
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column student.id
*
* @return the value of student.id
*
* @mbg.generated
*/
public Integer getId() {
return id;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column student.id
*
* @param id the value for student.id
*
* @mbg.generated
*/
public void setId(Integer id) {
this.id = id;
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column student.number
*
* @return the value of student.number
*
* @mbg.generated
*/
public String getNumber() {
return number;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column student.number
*
* @param number the value for student.number
*
* @mbg.generated
*/
public void setNumber(String number) {
this.number = number == null ? null : number.trim();
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column student.name
*
* @return the value of student.name
*
* @mbg.generated
*/
public String getName() {
return name;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column student.name
*
* @param name the value for student.name
*
* @mbg.generated
*/
public void setName(String name) {
this.name = name == null ? null : name.trim();
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column student.type
*
* @return the value of student.type
*
* @mbg.generated
*/
public String getType() {
return type;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column student.type
*
* @param type the value for student.type
*
* @mbg.generated
*/
public void setType(String type) {
this.type = type == null ? null : type.trim();
}
/**
* This method was generated by MyBatis Generator.
* This method returns the value of the database column student.password
*
* @return the value of student.password
*
* @mbg.generated
*/
public String getPassword() {
return password;
}
/**
* This method was generated by MyBatis Generator.
* This method sets the value of the database column student.password
*
* @param password the value for student.password
*
* @mbg.generated
*/
public void setPassword(String password) {
this.password = password == null ? null : password.trim();
}
}
6.运行结果
对应数据库
源码地址
Q.E.D.