EasyExcel

喝水不忘挖井人，感谢阿里巴巴项目组提供了easyexcel工具类，github地址：https://github.com/alibaba/easyexcel

原文链接：https://blog.csdn.net/qq_32258777/article/details/89031479

文章目录

• 环境搭建
• 读取excel文件
• 小于1000行数据
• 默认读取
• 指定读取
• 大于1000行数据
• 默认读取
• 指定读取
• 导出excle
• 单个Sheet导出
• 无模型映射导出
• 模型映射导出
• 多个Sheet导出
• 工具类
• 测试类

环境搭建

• easyexcel 依赖（必须）
• springboot (不是必须)
• lombok （不是必须）

 <dependency>
	  <groupId>com.alibaba</groupId>
	  <artifactId>easyexcel</artifactId>
	  <version>1.1.2-beat1</version>
 </dependency>
<dependency>

<groupId>org.projectlombok</groupId>

<artifactId>lombok</artifactId>

<version>1.18.2</version>

</dependency>

读取excel文件

小于1000行数据

默认读取

读取Sheet1的全部数据

 String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
 List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);

指定读取

下面是学生表.xlsx中Sheet1，Sheet2的数据


获取Sheet1表头以下的信息

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
//第一个1代表sheet1, 第二个1代表从第几行开始读取数据，行号最小值为0
Sheet sheet = new Sheet(1, 1);
List<Object> objects = ExcelUtil.readLessThan1000Row(filePath,sheet);

获取Sheet2的所有信息

 String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
 Sheet sheet = new Sheet(2, 0);
 List<Object> objects = ExcelUtil.readLessThan1000Row(filePath,sheet);

大于1000行数据

默认读取

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);

指定读取

String filePath = "/home/chenmingjian/Downloads/学生表.xlsx";
Sheet sheet = new Sheet(1, 2);
List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath，sheet);

导出excle

单个Sheet导出

无模型映射导出

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
List<List<Object>> data = new ArrayList<>();
data.add(Arrays.asList("111","222","333"));
data.add(Arrays.asList("111","222","333"));
data.add(Arrays.asList("111","222","333"));
List<String> head = Arrays.asList("表头1", "表头2", "表头3");
ExcelUtil.writeBySimple(filePath,data,head);

结果

模型映射导出

1、定义好模型对象

package com.springboot.utils.excel.test;
import com.alibaba.excel.annotation.ExcelProperty;

import com.alibaba.excel.metadata.BaseRowModel;

import lombok.Data;

import lombok.EqualsAndHashCode;
/**


@description:


@author: chenmingjian


@date: 19-4-3 14:44

*/

@EqualsAndHashCode(callSuper = true)

@Data

public class TableHeaderExcelProperty extends BaseRowModel {
/**

value: 表头名称
index: 列的号, 0表示第一列

*/

@ExcelProperty(value = "姓名", index = 0)

private String name;

@ExcelProperty(value = "年龄",index = 1)

private int age;
@ExcelProperty(value = "学校",index = 2)

private String school;

}

2、调用方法

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";
ArrayList<TableHeaderExcelProperty> data = new ArrayList<>();
  for(int i = 0; i < 4; i++){
      TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();
      tableHeaderExcelProperty.setName("cmj" + i);
      tableHeaderExcelProperty.setAge(22 + i);
      tableHeaderExcelProperty.setSchool("清华大学" + i);
      data.add(tableHeaderExcelProperty);
  }
ExcelUtil.writeWithTemplate(filePath,data);

多个Sheet导出

1、定义好模型对象

package com.springboot.utils.excel.test;
import com.alibaba.excel.annotation.ExcelProperty;

import com.alibaba.excel.metadata.BaseRowModel;

import lombok.Data;

import lombok.EqualsAndHashCode;
/**


@description:


@author: chenmingjian


@date: 19-4-3 14:44

*/

@EqualsAndHashCode(callSuper = true)

@Data

public class TableHeaderExcelProperty extends BaseRowModel {
/**

value: 表头名称
index: 列的号, 0表示第一列

*/

@ExcelProperty(value = "姓名", index = 0)

private String name;

@ExcelProperty(value = "年龄",index = 1)

private int age;
@ExcelProperty(value = "学校",index = 2)

private String school;

}

2、调用方法

 ArrayList<ExcelUtil.MultipleSheelPropety> list1 = new ArrayList<>();
 for(int j = 1; j < 4; j++){
      ArrayList<TableHeaderExcelProperty> list = new ArrayList<>();
      for(int i = 0; i < 4; i++){
          TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();
          tableHeaderExcelProperty.setName("cmj" + i);
          tableHeaderExcelProperty.setAge(22 + i);
          tableHeaderExcelProperty.setSchool("清华大学" + i);
          list.add(tableHeaderExcelProperty);
      }
  Sheet sheet <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Sheet</span><span class="token punctuation">(</span>j<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  sheet<span class="token punctuation">.</span><span class="token function">setSheetName</span><span class="token punctuation">(</span><span class="token string">"sheet"</span> <span class="token operator">+</span> j<span class="token punctuation">)</span><span class="token punctuation">;</span>

  ExcelUtil<span class="token punctuation">.</span>MultipleSheelPropety multipleSheelPropety <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">ExcelUtil<span class="token punctuation">.</span>MultipleSheelPropety</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  multipleSheelPropety<span class="token punctuation">.</span><span class="token function">setData</span><span class="token punctuation">(</span>list<span class="token punctuation">)</span><span class="token punctuation">;</span>
  multipleSheelPropety<span class="token punctuation">.</span><span class="token function">setSheet</span><span class="token punctuation">(</span>sheet<span class="token punctuation">)</span><span class="token punctuation">;</span>

  list1<span class="token punctuation">.</span><span class="token function">add</span><span class="token punctuation">(</span>multipleSheelPropety<span class="token punctuation">)</span><span class="token punctuation">;</span>

}
ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx",list1);

工具类

package com.springboot.utils.excel;
import com.alibaba.excel.EasyExcelFactory;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.event.AnalysisEventListener;

import com.alibaba.excel.metadata.BaseRowModel;

import com.alibaba.excel.metadata.Sheet;

import lombok.Data;

import lombok.Getter;

import lombok.Setter;

import lombok.extern.slf4j.Slf4j;

import org.springframework.util.CollectionUtils;

import org.springframework.util.StringUtils;
import java.io.*;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;
/**


@description:


@author: chenmingjian


@date: 19-3-18 16:16

*/

@Slf4j

public class ExcelUtil {
private static Sheet initSheet;
static {

initSheet = new Sheet(1, 0);

initSheet.setSheetName("sheet");

//设置自适应宽度

initSheet.setAutoWidth(Boolean.TRUE);

}
/**

读取少于1000行数据
@param filePath 文件绝对路径
@return

*/

public static List<Object> readLessThan1000Row(String filePath){

return readLessThan1000RowBySheet(filePath,null);

}

/**

读小于1000行数据, 带样式
filePath 文件绝对路径
initSheet ：

 sheetNo: sheet页码，默认为1



 headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取



 clazz: 返回数据List&lt;Object&gt; 中Object的类名



*/

public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet){

if(!StringUtils.hasText(filePath)){

return null;

}
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;

try {

fileStream = new FileInputStream(filePath);

return EasyExcelFactory.read(fileStream, sheet);

} catch (FileNotFoundException e) {

log.info("找不到文件或文件路径错误, 文件：{}", filePath);

}finally {

try {

if(fileStream != null){

fileStream.close();

}

} catch (IOException e) {

log.info("excel文件读取失败, 失败原因：{}", e);

}

}

return null;

}
/**

读大于1000行数据
@param filePath 文件觉得路径
@return

*/

public static List<Object> readMoreThan1000Row(String filePath){

return readMoreThan1000RowBySheet(filePath,null);

}

/**


读大于1000行数据, 带样式


@param filePath 文件觉得路径


@return

*/

public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet){

if(!StringUtils.hasText(filePath)){

return null;

}
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;

try {

fileStream = new FileInputStream(filePath);

ExcelListener excelListener = new ExcelListener();

EasyExcelFactory.readBySax(fileStream, sheet, excelListener);

return excelListener.getDatas();

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

}finally {

try {

if(fileStream != null){

fileStream.close();

}

} catch (IOException e) {

log.error("excel文件读取失败, 失败原因：{}", e);

}

}

return null;

}


/**

生成excle
@param filePath  绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
@param data 数据源
@param head 表头

*/

public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head){

writeSimpleBySheet(filePath,data,head,null);

}

/**


生成excle


@param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx


@param data 数据源


@param sheet excle页面样式


@param head 表头

*/

public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet){

sheet = (sheet != null) ? sheet : initSheet;
if(head != null){

List<List<String>> list = new ArrayList<>();

head.forEach(h -> list.add(Collections.singletonList(h)));

sheet.setHead(list);

}
OutputStream outputStream = null;

ExcelWriter writer = null;

try {

outputStream = new FileOutputStream(filePath);

writer = EasyExcelFactory.getWriter(outputStream);

writer.write1(data,sheet);

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

}finally {

try {

if(writer != null){

writer.finish();

}
  <span class="token keyword">if</span><span class="token punctuation">(</span>outputStream <span class="token operator">!=</span> null<span class="token punctuation">)</span><span class="token punctuation">{</span>
     outputStream<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>

} catch (IOException e) {

log.error("excel文件导出失败, 失败原因：{}", e);

}

}


}
/**

生成excle
@param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx
@param data 数据源

*/

public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){

writeWithTemplateAndSheet(filePath,data,null);

}

/**


生成excle


@param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx


@param data 数据源


@param sheet excle页面样式

*/

public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet){

if(CollectionUtils.isEmpty(data)){

return;

}
sheet = (sheet != null) ? sheet : initSheet;

sheet.setClazz(data.get(0).getClass());
OutputStream outputStream = null;

ExcelWriter writer = null;

try {

outputStream = new FileOutputStream(filePath);

writer = EasyExcelFactory.getWriter(outputStream);

writer.write(data,sheet);

} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

}finally {

try {

if(writer != null){

writer.finish();

}
  <span class="token keyword">if</span><span class="token punctuation">(</span>outputStream <span class="token operator">!=</span> null<span class="token punctuation">)</span><span class="token punctuation">{</span>
     outputStream<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>

} catch (IOException e) {

log.error("excel文件导出失败, 失败原因：{}", e);

}

}


}
/**


生成多Sheet的excle


@param filePath 绝对路径, 如：/home/chenmingjian/Downloads/aaa.xlsx


@param multipleSheelPropetys

*/

public static void writeWithMultipleSheel(String filePath,List<MultipleSheelPropety> multipleSheelPropetys){

if(CollectionUtils.isEmpty(multipleSheelPropetys)){

return;

}
OutputStream outputStream = null;

ExcelWriter writer = null;

try {

outputStream = new FileOutputStream(filePath);

writer = EasyExcelFactory.getWriter(outputStream);

for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {

Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;

if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){

sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());

}

writer.write(multipleSheelPropety.getData(), sheet);

}
} catch (FileNotFoundException e) {

log.error("找不到文件或文件路径错误, 文件：{}", filePath);

}finally {

try {

if(writer != null){

writer.finish();

}
  <span class="token keyword">if</span><span class="token punctuation">(</span>outputStream <span class="token operator">!=</span> null<span class="token punctuation">)</span><span class="token punctuation">{</span>
     outputStream<span class="token punctuation">.</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>

} catch (IOException e) {

log.error("excel文件导出失败, 失败原因：{}", e);

}

}


}
/匿名内部类开始，可以提取出去*********/
@Data

public static class MultipleSheelPropety{
private List<? extends BaseRowModel> data;
private Sheet sheet;

}
/**


解析监听器，


每解析一行会回调invoke()方法。


整个excel解析结束会执行doAfterAllAnalysed()方法



@author: chenmingjian


@date: 19-4-3 14:11

*/

@Getter

@Setter

public static class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**

逐行解析
object : 当前行的数据

*/

@Override

public void invoke(Object object, AnalysisContext context) {

//当前行

// context.getCurrentRowNum()

if (object != null) {

datas.add(object);

}

}

/**

解析完所有数据后会调用该方法

*/

@Override

public void doAfterAllAnalysed(AnalysisContext context) {

//解析结束销毁不用的资源

}



}
/匿名内部类结束，可以提取出去***/


}

测试类

package com.springboot.utils.excel;
import com.alibaba.excel.annotation.ExcelProperty;

import com.alibaba.excel.metadata.BaseRowModel;

import com.alibaba.excel.metadata.Sheet;

import lombok.Data;

import lombok.EqualsAndHashCode;

import org.junit.runner.RunWith;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;
/**


@description: 测试类


@author: chenmingjian


@date: 19-4-4 15:24

*/

@SpringBootTest

@RunWith(SpringRunner.class)

public class Test {
/**

读取少于1000行的excle

*/

@org.junit.Test

public void readLessThan1000Row(){

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";

List<Object> objects = ExcelUtil.readLessThan1000Row(filePath);

objects.forEach(System.out::println);

}

/**

读取少于1000行的excle，可以指定sheet和从几行读起

*/

@org.junit.Test

public void readLessThan1000RowBySheet(){

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";

Sheet sheet = new Sheet(1, 1);

List<Object> objects = ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);

objects.forEach(System.out::println);

}

/**

读取大于1000行的excle
带sheet参数的方法可参照测试方法readLessThan1000RowBySheet()

*/

@org.junit.Test

public void readMoreThan1000Row(){

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";

List<Object> objects = ExcelUtil.readMoreThan1000Row(filePath);

objects.forEach(System.out::println);

}

/**

生成excle
带sheet参数的方法可参照测试方法readLessThan1000RowBySheet()

*/

@org.junit.Test

public void writeBySimple(){

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";

List<List<Object>> data = new ArrayList<>();

data.add(Arrays.asList("111","222","333"));

data.add(Arrays.asList("111","222","333"));

data.add(Arrays.asList("111","222","333"));

List<String> head = Arrays.asList("表头1", "表头2", "表头3");

ExcelUtil.writeBySimple(filePath,data,head);

}

/**

生成excle, 带用模型
带sheet参数的方法可参照测试方法readLessThan1000RowBySheet()

*/

@org.junit.Test

public void writeWithTemplate(){

String filePath = "/home/chenmingjian/Downloads/测试.xlsx";

ArrayList<TableHeaderExcelProperty> data = new ArrayList<>();

for(int i = 0; i < 4; i++){

TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();

tableHeaderExcelProperty.setName("cmj" + i);

tableHeaderExcelProperty.setAge(22 + i);

tableHeaderExcelProperty.setSchool("清华大学" + i);

data.add(tableHeaderExcelProperty);

}

ExcelUtil.writeWithTemplate(filePath,data);

}

/**


生成excle, 带用模型,带多个sheet

*/

@org.junit.Test

public void writeWithMultipleSheel(){

ArrayList<ExcelUtil.MultipleSheelPropety> list1 = new ArrayList<>();

for(int j = 1; j < 4; j++){

ArrayList<TableHeaderExcelProperty> list = new ArrayList<>();

for(int i = 0; i < 4; i++){

TableHeaderExcelProperty tableHeaderExcelProperty = new TableHeaderExcelProperty();

tableHeaderExcelProperty.setName("cmj" + i);

tableHeaderExcelProperty.setAge(22 + i);

tableHeaderExcelProperty.setSchool("清华大学" + i);

list.add(tableHeaderExcelProperty);

}
 Sheet sheet <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Sheet</span><span class="token punctuation">(</span>j<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
 sheet<span class="token punctuation">.</span><span class="token function">setSheetName</span><span class="token punctuation">(</span><span class="token string">"sheet"</span> <span class="token operator">+</span> j<span class="token punctuation">)</span><span class="token punctuation">;</span>

 ExcelUtil<span class="token punctuation">.</span>MultipleSheelPropety multipleSheelPropety <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">ExcelUtil<span class="token punctuation">.</span>MultipleSheelPropety</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
 multipleSheelPropety<span class="token punctuation">.</span><span class="token function">setData</span><span class="token punctuation">(</span>list<span class="token punctuation">)</span><span class="token punctuation">;</span>
 multipleSheelPropety<span class="token punctuation">.</span><span class="token function">setSheet</span><span class="token punctuation">(</span>sheet<span class="token punctuation">)</span><span class="token punctuation">;</span>

 list1<span class="token punctuation">.</span><span class="token function">add</span><span class="token punctuation">(</span>multipleSheelPropety<span class="token punctuation">)</span><span class="token punctuation">;</span>

}
ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx",list1);


}
/匿名内部类，实际开发中该对象要提取出去***/
/**


@description:


@author: chenmingjian


@date: 19-4-3 14:44

*/

@EqualsAndHashCode(callSuper = true)

@Data

public static class TableHeaderExcelProperty extends BaseRowModel {
/**

value: 表头名称
index: 列的号, 0表示第一列

*/

@ExcelProperty(value = "姓名", index = 0)

private String name;

@ExcelProperty(value = "年龄",index = 1)

private int age;
@ExcelProperty(value = "学校",index = 2)

private String school;

}


/匿名内部类，实际开发中该对象要提取出去***/


}