1.pom依赖包
<!--easypoi包是excel导入导出需要--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
2.要导出的实体字段entity
package com.jk.cs.entity; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import java.io.Serializable; import java.time.LocalDate; @Data public class StudentExport implements Serializable { //isImportField找不到对应的字段用true_st @Excel(name = "学生姓名",height = 20,width = 30,isImportField = "true_st",orderNum = "0") private String name; @Excel(name = "学生性别",replace = {"男_1","女_2"},suffix = "生",orderNum = "1") private Integer sex; @Excel(name = "出生日期",databaseFormat = "yyyyMMdd",format = "yyyy-MM-dd",orderNum = "2") private LocalDate birthday; @Excel(name = "进校日期") private LocalDate registrationDate; }
3.对应导出实体的list集合
mapper中 List<StudentExport> listExport();
4.controller中用HttpServletRespose流程excel
package com.jk.cs.controller; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import com.jk.cs.entity.Student; import com.jk.cs.entity.StudentExport; import com.jk.cs.service.IStudentService; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.List; /** * <p> * 前端控制器 * </p> * * @author jie * @since 2021-09-20 */ @RestController @RequestMapping("/student") public class StudentController { @Autowired private IStudentService studentService; /** * 保存和修改 * @param student * @return */ @RequestMapping("/edit") public boolean saveStudent(@RequestBody Student student){ return studentService.saveOrUpdate(student); } /** * studentExport导出 */ @RequestMapping("/export") public void studentExport(HttpServletResponse response){ //1.导出的字段list集合 List<StudentExport> studentExports = studentService.studentExportList(); //2.下载的excel名称 String fileName="学生信息.xlsx"; try { //3.设置浏览器下载格式 response.setCharacterEncoding("UTF-8"); //文件下载的内容类型 response.setContentType("application/octet-stream"); //Content-Disposition提供一个默认的文件名,文件直接在浏览器上显示或者在访问时弹出文件下载对话框 response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode(fileName,"UTF-8")); //4.title为文件内开头名,sheetName是对应底下文件名称,第二个参数是需要导出的实体类,第三个参数是list集合 Workbook exportExcel = ExcelExportUtil.exportExcel(new ExportParams("学生信息", "学生"), StudentExport.class, studentExports); //5.流方式输出excel exportExcel.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } } }
可以参考excel文档为:http://easypoi.mydoc.io/#text_186905