首先添加pom文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
controller层
@RestController
@RequestMapping("/adminApi/excel")
public class ExcelController {
@Resource
private ExcelService excelService;
/*
*导出用户信息
* 2018/10/30+
* dayue
*/
@RequestMapping(value = "/export")
public void downloadAllClassmate(@RequestParam(value = "status") String status,
@RequestParam(value = "sex") String sex, @RequestParam(value = "type") String type,
@RequestParam(value = "firstTime") String firstTime, @RequestParam(value = "lastTime") String lastTime,
HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("用户信息表");
//查询出的数据
List<ExcelUser> classmateList = excelService.selectUser(status,sex,type,firstTime,lastTime);
//文件名称 必须用英文
String fileName = "User" + ".xls";//设置要导出的文件的名字
//新增数据行,并且设置单元格数据
int rowNum = 1;
//根据实际情况编写列表名(顺序必须一一对应)
String[] headers = {"姓氏", "名字"};
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//查询出来的数据可能为null这种看着很不舒服 将null变为空
String lastName = null;
String firstName = null;
//在表中存放查询到的数据放入对应的列
for (ExcelUser excelUser : classmateList) {
HSSFRow row1 = sheet.createRow(rowNum);
//判断lastName是否为null 若为null 给空 若不为null 传相应的值
if (excelUser.getLastName() == null){
lastName = "";
}else {
lastName = excelUser.getLastName();
}
row1.createCell(0).setCellValue(lastName);
//同上
if (excelUser.getFirstName() == null){
firstName = "";
}else {
firstName = excelUser.getFirstName();
}
row1.createCell(1).setCellValue(firstName);
rowNum++;
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
}
Service层
List<ExcelUser> selectUser(String status,String sex, String type, String firstTime, String lastTime);
ServiceImp层
@Override
public List<ExcelUser> selectUser(String status,String sex, String type, String firstTime, String lastTime){
return bpUserMapper.findUser(status,sex,type,firstTime,lastTime);
}
sql就是根据你查询条件写一个sql语句 就不写了
最后重点 不能使post请求 用get或者链接请求 否则会返回乱码