前言
导出大量的数据,格式基本上都是Excel,EasyExcel是阿里巴巴开源的一个Excel处理框架,使用简单、节省内存。节省内存的原理也很简单,在解析Excel时没有将文件数据全部加载到内存当中,而是从磁盘文件中一行行读取。
一、使用步骤
1.引入库
代码如下(示例):
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.8</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
2.代码示例
代码如下(示例):
实体映射类
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.util.Date;
@Data
public class UserData {
@ColumnWidth(20)
@ExcelProperty(index = 0, value = "姓名")
private String username;
@ColumnWidth(20)
@ExcelProperty(index = 1, value = "年龄")
private int age;
@ColumnWidth(30)
@DateTimeFormat("yyyy-MM-dd")
@ExcelProperty(index = 2, value = "生日")
private Date birthday;
@ColumnWidth(20)
@ExcelProperty(index = 3, value = "性别", converter = SexConverter.class)
private Integer sex;
//需要保留两位小数
@ColumnWidth(20)
@ExcelProperty(index = 4, value = "体重KG")
@NumberFormat("0.##") // 会以字符串形式生成单元格,要计算的列不推荐
private Double weight;
//需要保留一位小数
@ColumnWidth(20)
@ExcelProperty(index = 5, value = "身高cm")
@NumberFormat("0.#")
private Double high;
@ExcelIgnore // 该字段不生成excel
private String desc;
}
导出示例:
import com.alibaba.excel.EasyExcel;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelExample {
public static void main(String[] args) {
// 实现excel写操作
//1.设置写入文件夹地址和excel文件名称
String fileName = "F:\\Excel\\write.xlsx";
//调用easyExcel里面的方法实现写操作
//2个参数,第一个参数是文件名称,第二个参数是实体类
EasyExcel.write(fileName, UserData.class).sheet("学生信息表").doWrite(getData());
}
//创建方法返回list集合
public static List<UserData> getData() {
List<UserData> list = new ArrayList<>();
UserData userData1 = new UserData();
userData1.setUsername("张三");
userData1.setAge(22);
userData1.setBirthday(formatDate("2000-10-11"));
userData1.setSex(1);
userData1.setWeight(67.123);
userData1.setHigh(170.1123);
list.add(userData1);
UserData userData2 = new UserData();
userData2.setUsername("李四");
userData2.setAge(23);
userData2.setBirthday(formatDate("1999-5-3"));
userData2.setSex(0);
userData2.setWeight(66.123);
userData2.setHigh(169.9889);
list.add(userData2);
return list;
}
public static Date formatDate(String birthday) {
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
try {
return sdf.parse(birthday);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
}
自定义转换器
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
public class SexConverter implements Converter<Integer> {
@Override
public Class<Integer> supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
//读取文件
return "男".equals(cellData.getStringValue()) ? 1 : 0;
}
@Override
public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
//写入文件
return new CellData<>(integer.equals(1) ? "男" : "女");
}
}
解析Excel示例:
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.Map;
public class ExcelListener extends AnalysisEventListener<UserData> {
/**
* 一行一行的读取excel内容
*/
@Override
public void invoke(UserData data, AnalysisContext analysisContext) {
System.out.println("****" + data);
}
/**
* 读取表头内容
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头" + headMap);
}
/**
* 读取完成操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("读取Excel完毕");
}
}
读取Excel测试类
import com.alibaba.excel.EasyExcel;
public class EasyExcelReadText {
public static void main(String[] args) {
// 实现excel写操作
//1.设置写入文件夹地址和excel文件名称
String fileName = "F:\\Excel\\write.xlsx";
//调用easyExcel里面的方法实现写操作
//2个参数,第一个参数是文件名称,第二个参数是实体类
EasyExcel.read(fileName, UserData.class, new ExcelListener()).sheet().doRead();
}
}
总结
简单易用,做个记录,以备后用