EasyExcel导出Excel文件
介绍
-
EasyExcel是阿里巴巴出品的Excel文件处理工具,功能十分强大,能省去我们很多的编码,下面来通过一个例子,看看如何使用EasyExcel导出Excel文件的
准备
-
我们从数据库读取User实体对象数据,然后使用EasyExcel写入到HttpServletResponse输出流中,在浏览器就可以下载导出的Excel文件了
POM
-
<?xml version="1.0" encoding="UTF-8"?> <project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.1</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>excel</artifactId> <version>0.0.1-SNAPSHOT</version> <name>excel</name> <description>excel</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.2</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--excel--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.3</version> </dependency> <!--json--> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.69</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
User实体
-
package com.example.excel.entity; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import lombok.Data; import java.math.BigDecimal; import java.util.Date; @Data public class User { @ExcelProperty("id") private String id; @ExcelProperty("username") private String username; @ExcelProperty("password") private String password; @ExcelProperty("address") private String address; @ExcelProperty("email") private String email; @ExcelProperty("profile") private String profile; @ExcelProperty("birthday") @DateTimeFormat("yyyy-MM-dd") private Date birthday; @ExcelProperty("registerDay") @DateTimeFormat("yyyy-MM-dd") private Date registerDay; @ExcelProperty("loginDay") @DateTimeFormat("yyyy-MM-dd") private Date loginDay; @ExcelProperty("status") private Integer status; @ExcelProperty("account") private BigDecimal account; @ExcelProperty("balance") private BigDecimal balance; @ExcelProperty("age") private Integer age; @ExcelProperty("sex") private Integer sex; @ExcelProperty("avatar") private String avatar; @ExcelProperty("level") private Integer level; }
ExcelUtil
-
package com.example.excel.util; import com.alibaba.excel.EasyExcel; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.List; /** * @description: ExcelUtil * @author: zj * @date: 2022-07-14 17:07 */ public class ExcelUtil { public static void exportExcel(List<?> objects, Class clazz, String fileName, String sheetName, HttpServletResponse response) throws IOException { // 设置响应头 response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // 设置防止中文名乱码 String exportName = URLEncoder.encode(fileName, "utf-8"); String sheetSubName = URLEncoder.encode(sheetName, "utf-8"); // 文件下载方式(附件下载还是在当前浏览器打开) response.setHeader("Content-disposition", "attachment;filename=" + exportName + ".xlsx"); // 向Excel中写入数据 EasyExcel.write(response.getOutputStream(), clazz) .sheet(sheetSubName) .doWrite(objects); } }
Controller
-
package com.example.excel.controller; import com.example.excel.entity.User; import com.example.excel.mapper.UserMapper; import com.example.excel.util.ExcelUtil; import lombok.extern.slf4j.Slf4j; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; /** * @description: Excel * @author: zj * @date: 2022-07-14 17:02 */ @RestController @Slf4j public class ExcelController { @Resource private UserMapper userMapper; @GetMapping("/excel") public void excel(HttpServletResponse response) throws IOException { long st = System.currentTimeMillis(); List<User> users = userMapper.selectSub(0, 10000); ExcelUtil.exportExcel(users, User.class, "user_info", "sheet_one", response); log.info("export excel success, cost {} s.", (System.currentTimeMillis() - st) / 1000); } }
效果
-
浏览器输入接口地址下载Excel文件,浏览器输入localhost/excel