Excel读写工具使用手册
大约 7 分钟
Excel读写工具使用手册
一、excel读取
1.依赖包引用
<dependency>
<groupId>com.pcitc.si</groupId>
<artifactId>common-utils-excel</artifactId>
<version>1.0.0-SNAPSHOT</version>
</dependency>
2.使用
excel示例
对象类定义
简单方式：
@Getter
@Setter
@EqualsAndHashCode
public class ExcelDemoData {
private String name;
private int age;
private String sex;
}
指定列名或下标方式：（注，下标和列名，在同一个对象定义中最好只用一种方式，下标从 0 开始，列名为对应的表头名称）
@Getter
@Setter
@EqualsAndHashCode
public class ExcelDemoData {
@ExcelProperty(index = 0)
private String name;
@ExcelProperty(index = 1)
private int age;
@ExcelProperty("性别")
private String sex;
}
读取
1. 简单读取
// 写法1：JDK8+ ,不用额外写一个DemoDataListener
String fileName = "/Users/zenglei/work/tmp/test-upload.xlsx";
EasyExcel.read(fileName, ExcelDemoData.class,new PageReadListener<ExcelDemoData>(datalist -> {
for (ExcelDemoData excelDemoData : datalist) {
log.info("读取到一条数据:{}", JSONUtil.toJsonStr(excelDemoData));
}
})).sheet().doRead();
2. 匿名内部类方式读取
// 写法2：
String fileName = "/Users/zenglei/work/tmp/test-upload.xlsx";
EasyExcel.read(fileName, ExcelDemoData.class, new ReadListener<ExcelDemoData>() {
/**
* 单次缓存的数据量
*/
public static final int BATCH_COUNT = 100;
/**
*临时存储
*/
private List<ExcelDemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(ExcelDemoData data, AnalysisContext context) {
log.info("读取到数据：{}", JSONUtil.toJsonStr(data));
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
}
/**
* 加上存储数据库
*/
private void saveData() {
System.out.println(cachedDataList.size()+"条数据，开始存储数据库！");
System.out.println("存储数据库成功！");
}
}).sheet().doRead();
3. 自定义Listener方式
定义一个Listener：
@Slf4j
public class ExcelDemoListener implements ReadListener<ExcelDemoData> {
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<ExcelDemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DemoDAO demoDAO;
public ExcelDemoListener() {
// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoDAO
*/
public ExcelDemoListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(ExcelDemoData data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSONUtil.toJsonStr(data));
cachedDataList.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", cachedDataList.size());
demoDAO.save(cachedDataList);
log.info("存储数据库成功！");
}
}
持久层：
/**
* 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
**/
public class DemoDAO {
public void save(List<DemoData> list) {
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
}
}
读取：
String fileName = "/Users/zenglei/work/tmp/test-upload.xlsx";
EasyExcel.read(fileName, ExcelDemoData.class, new ExcelDemoListener()).sheet().doRead();
或
String fileName = "/Users/zenglei/work/tmp/test-upload.xlsx";
// 一个文件一个reader
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(fileName, ExcelDemoData.class, new ExcelDemoListener()).build();
// 构建一个sheet 这里可以指定名字或者no
ReadSheet readSheet = EasyExcel.readSheet(0).build();
// 读取一个sheet
excelReader.read(readSheet);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}
更多用法
读取多个sheet
/**
* 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
* <p>
* 3. 直接读即可
*/
@Test
public void repeatedRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 读取全部sheet
// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();
// 读取部分sheet
fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(fileName).build();
// 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
// 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
excelReader.read(readSheet1, readSheet2);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}
}
读取表头
在自定义的listener中重写invokeHead方法即可
**
* 这里会一行行的返回头
*
* @param headMap
* @param context
*/
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
log.info("解析到一条头数据:{}", JSON.toJSONString(headMap));
// 如果想转成成 Map<Integer,String>
// 方案1： 不要implements ReadListener 而是 extends AnalysisEventListener
// 方案2： 调用 ConverterUtils.convertToStringMap(headMap, context) 自动会转换
}
不创建对象读
1. 先自定义listener
@Slf4j
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
private List<Map<Integer, String>> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", cachedDataList.size());
log.info("存储数据库成功！");
}
}
2.读取
/**
* 不创建对象的读
*/
@Test
public void noModelRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// 这里 只要，然后读取第一个sheet 同步读取会自动finish
EasyExcel.read(fileName, new NoModelDataListener()).sheet().doRead();
}
二、写excel
对象定义
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
写入
/**
* 最简单的写
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 直接写即可
*/
@Test
public void simpleWrite() {
// 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
return data();
});
// 写法2
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
// 写法3
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(data(), writeSheet);
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
复杂头写入
在对象定义中，通过ExcelProperty注解指定复杂头。示例如下：
@Getter
@Setter
@EqualsAndHashCode
public class ComplexHeadData {
@ExcelProperty({"主标题", "字符串标题"})
private String string;
@ExcelProperty({"主标题", "日期标题"})
private Date date;
@ExcelProperty({"主标题", "数字标题"})
private Double doubleData;
}
展示效果为：
不创建对象的写
/**
* 不创建对象的写
*/
@Test
public void noModelWrite() {
// 写法1
String fileName = TestFileUtil.getPath() + "noModelWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
}
private List<List<String>> head() {
List<List<String>> list = ListUtils.newArrayList();
List<String> head0 = ListUtils.newArrayList();
head0.add("字符串" + System.currentTimeMillis());
List<String> head1 = ListUtils.newArrayList();
head1.add("数字" + System.currentTimeMillis());
List<String> head2 = ListUtils.newArrayList();
head2.add("日期" + System.currentTimeMillis());
list.add(head0);
list.add(head1);
list.add(head2);
return list;
}
private List<List<Object>> dataList() {
List<List<Object>> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
List<Object> data = ListUtils.newArrayList();
data.add("字符串" + i);
data.add(new Date());
data.add(0.56);
list.add(data);
}
return list;
}
web中的写
/**
* 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
*
* @since 2.1.1
*/
@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// 这里注意 使用swagger 会导致各种问题，请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), DownloadData.class).autoCloseStream(Boolean.FALSE).sheet("模板")
.doWrite(data());
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}