1、pom.xml,除了mybatis数据库依赖之外,要加上以下的依赖。
<!-- 表格导出--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.xmlunit</groupId><artifactId>xmlunit-core</artifactId></dependency>
2.前端页面
<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><title>Title</title><script src="/axios/dist/axios.min.js"></script></head><body><table border="0" style="margin-top:4px; margin-left: 18px"><tr><td><a href="#" class="easyui-linkbutton" onclick="downloadfile();">数据导出</a></td></tr><script>function downloadfile(){window.location.href="/UserExcelDownloads";}</script></table></body></html>
3.配置文件
4.建立数据库
5.实体类
package com.wei.domain;public class Teacher {private Integer tno;private String tName;private String type;private String tPassword;private String phone;private String e_mail;public Teacher() {}public Teacher(Integer tno, String tName, String type, String tPassword, String phone, String e_mail) {this.tno = tno;this.tName = tName;this.type = type;this.tPassword = tPassword;this.phone = phone;this.e_mail = e_mail;}@Overridepublic String toString() {return "Teacher{" +"tno=" + tno +", tName='" + tName + '\'' +", type='" + type + '\'' +", tPassword='" + tPassword + '\'' +", phone='" + phone + '\'' +", e_mail='" + e_mail + '\'' +'}';}public Integer getTno() {return tno;}public void setTno(Integer tno) {this.tno = tno;}public String gettName() {return tName;}public void settName(String tName) {this.tName = tName;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String gettPassword() {return tPassword;}public void settPassword(String tPassword) {this.tPassword = tPassword;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getE_mail() {return e_mail;}public void setE_mail(String e_mail) {this.e_mail = e_mail;}}
6.mapper接口
package com.wei.mapper;import com.wei.domain.Teacher;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Select;import java.util.List;@Mapperpublic interface teacherMapper {@Select("select * from teacher")public List<Teacher> teacherinfor();}
7.service层
package com.wei.service;import com.wei.domain.Teacher;import com.wei.mapper.teacherMapper;import org.springframework.stereotype.Service;import javax.annotation.Resource;import java.util.List;@Servicepublic class teacherService {@Resourcepublic teacherMapper teachermapper;public List<Teacher> teacherinfor(){return teachermapper.teacherinfor();}}
8.controller层
package com.wei.controller;import com.wei.domain.Teacher;import com.wei.service.teacherService;import org.apache.poi.hssf.usermodel.*;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import javax.annotation.Resource;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.util.List;@Controller@RequestMapping("/")public class teacherController {@Resourcepublic teacherService teacherservice;@RequestMapping(value = "UserExcelDownloads", method = RequestMethod.GET)public void downloadAllClassmate(HttpServletResponse response) throws IOException {HSSFWorkbook workbook = new HSSFWorkbook();//创建HSSFWorkbook对象, excel的文档对象HSSFSheet sheet = workbook.createSheet("信息表"); //excel的表单List<Teacher> classmateList = teacherservice.teacherinfor();String fileName = "userinf" + ".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);}//在表中存放查询到的数据放入对应的列for (Teacher teacher : classmateList) {HSSFRow row1 = sheet.createRow(rowNum);row1.createCell(0).setCellValue(teacher.getTno());row1.createCell(1).setCellValue(teacher.gettName());row1.createCell(2).setCellValue(teacher.getType());row1.createCell(3).setCellValue(teacher.gettPassword());rowNum++;}response.setContentType("application/octet-stream");response.setHeader("Content-disposition", "attachment;filename=" + fileName);response.flushBuffer();workbook.write(response.getOutputStream());}}
9、运行效果
希望对你有所帮助!!!!!