EasyExcel的简单介绍
官方文档:https://easyexcel.opensource.alibaba.com/docs/current/api/
依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>
常用的注解
注解 | 涵义 |
---|---|
@ExcelProperty | 注解中有三个参数value,index,converter分别代表列明,列序号,数据转换方式 |
@ColumnWith | 设置列宽度的注解,注解中只有一个参数value,value的单位是字符长度,最大可以设置255个字符 |
@ContentFontStyle | 用于设置单元格内容字体格式的注解 |
@ContentLoopMerge | 设置合并单元格的注解 |
@ContentRowHeight | 设置行高 -1为自动 |
@ExcelIgnore | 不将该字段转换成Excel |
@ExcelIgnoreUnannotated | 没有注解的字段都不转换 |
写Excel
最终成果:
首先最重要的就是Excel实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import java.util.Date;
import lombok.Data;
import lombok.EqualsAndHashCode;
/**
* @Author: yangypeng
* @date: 2022/11/11 22:25
* @Version: 1.0
* @Description:
*/
@Data
@EqualsAndHashCode
public class VoteExcel {
@ExcelProperty({"投票主题","描述","投票时间","序号"})
private Integer id;
@ExcelProperty({"${title}","${describe}","${voteTime}","投票时间"})
@DateTimeFormat("yyyy-MM-dd HH:mm::ss")
private Date voteTime;
@ExcelProperty({"${title}","${describe}","${voteTime}","投票人"})
private String voteName;
@ExcelProperty({"${title}","${describe}","${voteTime}","${title}"})
private String voteOption;
}
在这个实体类内可以给每一个字段加上标题,可以给对应的值加一些转换等。在这里我采用了${title}的占位符,之后会用实际值代替这个占位符。
替换标题中的占位符
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;
/**
* @Author: yangypeng
* @date: 2022/11/14 21:17
* @Version: 1.0
* @Description:
*/
public class VoteTitleHandler implements CellWriteHandler {
private String title;
private String describe;
private Date voteTime;
PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
public VoteTitleHandler(String title,String describe, Date voteTime) {
this.title = title;
this.describe = describe;
this.voteTime = voteTime;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
if (head != null) {
List<String> headNameList = head.getHeadNameList();
if (CollectionUtils.isNotEmpty(headNameList)) {
Properties properties = new Properties();
properties.setProperty("title", title);
properties.setProperty("describe", describe);
properties.setProperty("voteTime",
new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(voteTime));
for (int i = 0; i < headNameList.size(); i++) {
headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
}
}
}
}
}
重写CellWriteHandler中的beforeCellCreate,将占位符替换掉。
通用的方法
public static String exportVoteExcel(String fileName, String sheetName, Class clazz,
List dataList, String title, String describe, Date voteDate) throws IOException {
File result = File.createTempFile(fileName, ".xlsx",new File("/Users/yangyapeng/IdeaProjects/myProject/"));
EasyExcel.write(result)
.head(clazz)
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(new VoteTitleHandler(title, describe, voteDate))
.sheet(sheetName)
.doWrite(dataList);
return null;
}
Test类
public static void main(String[] args) throws IOException {
List<VoteExcel> data = new ArrayList<>();
for (int i = 0; i < 100; i++) {
VoteExcel topicExcel = new VoteExcel();
topicExcel.setId(i);
topicExcel.setVoteTime(new Date());
topicExcel.setVoteName("name" + i);
topicExcel.setVoteOption("b");
data.add(topicExcel);
}
ExcelUtils.exportVoteExcel("测试导出excel表","sheet1", VoteExcel.class, data,
"测试","测试这次是否成功", new Date());
}