1、导入依赖
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.6.13</spring-boot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.2.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
2、导出实体类
/**
* excel导出实体类
*
* @author
* @date 2023/7/20 19:51
*/
@Data
public class UserInfo {
/**
* 用户id
*/
@ExcelProperty("用户编号")
@ColumnWidth(20)
private Long id;
/**
* 用户名
*/
@ExcelProperty("用户名")
@ColumnWidth(20)
private String userName;
/**
* 密码
*/
@ExcelIgnore
private String passWord;
/**
* 昵称
*/
@ExcelProperty("昵称")
@ColumnWidth(20)
private String nickName;
/**
* 生日
*/
@ExcelProperty("生日")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
private Date birtyDay;
/**
* 手机号
*/
@ExcelProperty("手机号")
@ColumnWidth(20)
private String phone;
/**
* 身高
*/
@ExcelProperty("身高(米)")
@NumberFormat("#,##")
@ColumnWidth(20)
private Double height;
/**
* 性别
*/
@ExcelProperty(value = "性别",converter = GenderConverter.class)
@ColumnWidth(10)
private Integer gender;
}
3、用户性别枚举类
/**
* @author
* @date 2023/7/20 20:17
*/
@Getter
@AllArgsConstructor
public enum GenderEnum {
/**
* 未知
*/
UNKNOWN(0, "未知"),
/**
* 男性
*/
MALE(1, "男性"),
/**
* 女性
*/
FEMALE(2, "女性");
private final Integer value;
@JsonFormat
private final String description;
public static GenderEnum convert(Integer value){
return Stream.of(values())
.filter(bean->bean.value.equals(value))
.findAny()
.orElse(UNKNOWN);
}
public static GenderEnum convert(String description){
return Stream.of(values())
.filter(bean->bean.value.equals(description))
.findAny()
.orElse(UNKNOWN);
}
}
4、性别转换类
/**
* 性别转换器
*
* @author
* @date 2023/7/20 20:01
*/
public class GenderConverter implements Converter<Integer> {
@Override
public Class<?> supportJavaTypeKey() {
return Converter.super.supportJavaTypeKey();
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
/**
*
*
* @param context
* @return
* @throws Exception
*/
@Override
public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();
}
/**
*
*
* @param context
* @return
* @throws Exception
*/
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
}
}
5、逻辑层代码
@RestController
@Slf4j
@RequestMapping("/easyExcel")
public class EasyExcelController {
@GetMapping("/getInfo")
public void getInfo(HttpServletResponse response){
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = null;
try {
fileName = URLEncoder.encode("用户列表", "UTF-8").replaceAll("\\+", "%20");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List<UserInfo> userList = getUserList();
try {
EasyExcel.write(response.getOutputStream())
.head(UserInfo.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("用户列表")
.doWrite(userList);
} catch (IOException e) {
e.printStackTrace();
}
}
@PostMapping("/readInfo")
public void readInfo(@RequestPart("file") MultipartFile file){
// @RequestPart这个注解用在multipart/form-data表单提交请求的方法上。
try {
List<UserInfo> userList = EasyExcel.read(file.getInputStream())
.head(UserInfo.class)
.sheet("用户列表")
.doReadSync();
log.info(String.valueOf(userList));
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取用户信息列表
*
* @return
*/
public static List<UserInfo> getUserList(){
List<UserInfo> userInfoList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setId(Long.parseLong(String.valueOf(i)));
userInfo.setUserName("zhangsan"+i);
userInfo.setNickName("zhang"+i);
if(i % 2 == 0){
userInfo.setGender(2);
}else{
userInfo.setGender(1);
}
userInfo.setHeight(100D);
userInfo.setBirtyDay(new Date());
userInfo.setPassWord("10012"+i);
userInfo.setPhone("18623706215");
userInfoList.add(userInfo);
}
return userInfoList;
}
}