Easy-Excel简单使用
一、Easy-Excel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出
二、读取excel
- 创建实体类
- 创建转换器 (可选)
- 创建监听器
- 开始读取
实体类
@ExcelProperty (value = “指定列名”, index = 指定列下标)
@ExcelIgnore 忽略这个字段
@DateTimeFormat 日期格式转换
@NumberFormat 数据格式转换 如:@NumberFormat(value = “#.##%”) 0.3947 -> 39.47%
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserInfoModel {
@ExcelProperty(value = "昵称")
private String name;
@ExcelProperty(value = "性别", converter = UserInfoGenderConverter.class)
private Integer sex;
@ExcelProperty(value = "生日")
@DateTimeFormat(value = "yyyy年MM月dd日") // 日期格式转换
private String birthday;
@ExcelProperty(value = "邮箱")
private String email;
@ExcelProperty(value = "积分")
private Integer score;
@ExcelProperty(value = "排名")
@NumberFormat(value = "#.##%") //数据格式装换
private String rank;
}
转换器
// 用户信息性别转换器
public class UserInfoGenderConverter implements Converter<Integer> {
// 从eacel表格读取 -> 数据库存储类型
@Override
public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
String value = cellData.getStringValue();
switch (value) {
case "男":
return 1;
case "女":
return 2;
default:
return 0;
}
}
// 从数据库读取数据 -> excel表格展示类型
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
switch (value) {
case 1:
return new WriteCellData<>("男");
case 2:
return new WriteCellData<>("女");
default:
return new WriteCellData<>("未知");
}
}
}
监听器
@Slf4j(topic = "e")
public class UserInfoReadListener implements ReadListener<UserInfoModel> {
private final int batchSize; // 分批入库大小
private Consumer<List<UserInfoModel>> consume; //消费者函数
private Predicate<UserInfoModel> predicate; // 条件表达式
@Getter
private ReadErrorModel readErrorModel;
private List<UserInfoModel> cacheData;
public UserInfoReadListener(Predicate<UserInfoModel> predicate, Consumer<List<UserInfoModel>> consume) {
this(5,predicate, consume);
}
public UserInfoReadListener(int batchSize, Predicate<UserInfoModel> predicate, Consumer<List<UserInfoModel>> consume) {
this.batchSize = batchSize;
this.cacheData = new ArrayList<>(this.batchSize);
this.predicate = predicate;
this.consume = consume;
}
/* 读取数据 */
@Override
public void invoke(UserInfoModel userInfoModel, AnalysisContext analysisContext) {
// log.debug("invoke:{}",userInfoModel);
if (!predicate.test(userInfoModel)){ // 检查该数据是否符合条件表达式
return;
}
this.cacheData.add(userInfoModel);
if (this.cacheData.size() >= this.batchSize){
this.consume.accept(this.cacheData);
this.cacheData.clear();
}
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
int rowIndex = excelDataConvertException.getRowIndex(); // 行索引。
int columnIndex = excelDataConvertException.getColumnIndex(); // 列索引。
String cellData = excelDataConvertException.getCellData().getStringValue(); // 单元格数据。
String reason = exception.getMessage(); // 异常原因。
// log.error("第{}行,第{}列,单元格[{}],解析异常:{}", rowIndex + 1, columnIndex + 1, cellData, reason);
this.readErrorModel = new ReadErrorModel(rowIndex, columnIndex, cellData, reason);
throw new ExcelAnalysisStopException();
}
throw exception;
}
/* 所有数据读取完 */
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// log.debug("all size:{}",cacheData.size());
if (this.cacheData.size() > 0){
this.consume.accept(this.cacheData);
}
}
}
读取
package com.it;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.ReadListener;
import com.it.converter.UserInfoGenderConverter;
import com.it.listener.UserInfoReadListener;
import com.it.model.UserInfoModel;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import java.io.File;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.function.Predicate;
@Slf4j(topic = "e")
public class EasyExcelTest {
@Test
public void testReadListenerHeader(){
// 加载文件流
InputStream inputStream = EasyExcelTest.class.getClassLoader().getResourceAsStream("user-list-02.xlsx");
// 设置条件
Predicate<UserInfoModel> predicate = (item)->{
return item.getSex() != -1;
};
UserInfoReadListener userInfoReadListener = new UserInfoReadListener(predicate, this::saveBatch);
// List<UserInfoModel> list = EasyExcel.read(inputStream).sheet(0).head(UserInfoModel.class).registerReadListener(userInfoReadListener).doReadSync();
//headRowNumber:表头所在行
EasyExcel.read(inputStream, UserInfoModel.class, userInfoReadListener).headRowNumber(6).sheet(0).doRead();
if (userInfoReadListener.getReadErrorModel() != null){
log.info("读取错误:{}", userInfoReadListener.getReadErrorModel());
}else{
log.debug("读取成功");
}
}
private void saveBatch(List<UserInfoModel> list) {
log.info("批量插入:{}", list.size());
for (UserInfoModel item : list) {
log.info("item: {}", item);
}
}
}
三、导出excel
- 创建实体类
- 创建转换器(可选)
- 开始导出
实体类
@Data
public class UserModel {
@ExcelProperty(value = {"基本信息", "用户姓名"})
private String userName;
@ExcelProperty(value = {"基本信息", "用户性别"}, converter = UserInfoGenderConverter.class)
private Integer userGender;
@ExcelProperty(value = {"基本信息", "用户生日"})
@DateTimeFormat(value = "yyyy年MM月dd日")
@ColumnWidth(20) // 设置列宽
private Date userBirth;
@ExcelProperty(value = {"账户信息", "用户积分"})
private Integer userScore;
@ExcelProperty(value = {"账户信息", "用户佣金"})
@NumberFormat(value = "¥#.##元")
private BigDecimal userReward;
}
转换器
public class UserInfoGenderConverter implements Converter<Integer> {
// 从eacel表格读取 -> 数据库存储类型
@Override
public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
String value = cellData.getStringValue();
switch (value) {
case "男":
return 1;
case "女":
return 2;
default:
return 0;
}
}
// 从数据库读取数据 -> excel表格展示类型
@Override
public WriteCellData<?> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
switch (value) {
case 1:
return new WriteCellData<>("男");
case 2:
return new WriteCellData<>("女");
default:
return new WriteCellData<>("未知");
}
}
}
导出
@Slf4j(topic = "e")
public class EasyExcelTest01 {
/**
* 导出路径。
*/
private final String EXPORT_PATH = "C:\\Users\\ch\\Desktop\\export.xlsx";
/**
* 测试:模型映射导出。
*/
@Test
@SneakyThrows
public void testExport() {
// 模拟数据获取:用户信息列表。
List<UserModel> list = this.getList();
// 处理:导出数据。
EasyExcel.write(EXPORT_PATH)
.sheet("导出数据")
.head(UserModel.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) //宽度根据表头信息自适应
// .includeColumnFieldNames(Arrays.asList("userName", "userGender","userBirth", "userScore", "userReward"))
.doWrite(list);
}
/**
* 获取用户信息列表。
*
* @return 返回结果。
*/
@SneakyThrows
private List<UserModel> getList() {
UserModel user1 = new UserModel();
user1.setUserName("郭德纲"); // 用户姓名。
user1.setUserGender(1); // 用户性别。
user1.setUserBirth(DateUtils.parseDate("1973-01-18")); // 用户生日。
user1.setUserScore(100); // 用户积分。
user1.setUserReward(BigDecimal.valueOf(123.45)); // 用户佣金。
UserModel user2 = new UserModel();
user2.setUserName("于谦"); // 用户姓名。
user2.setUserGender(2); // 用户性别。
user2.setUserBirth(DateUtils.parseDate("1967-12-06")); // 用户生日。
user2.setUserScore(200); // 用户积分。
user2.setUserReward(BigDecimal.valueOf(234.56)); // 用户佣金。
UserModel user3 = new UserModel();
user3.setUserName("岳云鹏"); // 用户姓名。
user3.setUserGender(0); // 用户性别。
user3.setUserBirth(DateUtils.parseDate("1985-09-17")); // 用户生日。
user3.setUserScore(300); // 用户积分。
user3.setUserReward(BigDecimal.valueOf(345.67)); // 用户佣金。
return Arrays.asList(user1, user2, user3);
}
}
Controller层
@RestController("/user")
@Slf4j(topic = "e")
public class UserController {
@Autowired
private IUserInfoService userInfoService;
@RequestMapping("/list")
public List<UserInfoEntity> list(){
return userInfoService.list();
}
@GetMapping(value = "/export")
@SneakyThrows
public void export(HttpServletResponse response) {
// 从数据库获取数据
List<UserInfoEntity> list = this.userInfoService.list();
// 设置响应头,告诉浏览器是文件
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
//编写文件名
String fileName = URLEncoder.encode("用户信息", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//流写入
ServletOutputStream outputStream = response.getOutputStream();
//getResourceAsStream("export-template02.xlsx")读取模板文件
InputStream exportTemplate = Application.class.getClassLoader().getResourceAsStream("export-template02.xlsx");
EasyExcel.write(outputStream)
.withTemplate(exportTemplate)
.sheet()
.doFill(list);
}
}