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;

}


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


}



          

            相关阅读:

            
              专题页移动端适配实例
                
iconfont字体图标使用方法
                
HBuilder常用快捷键
                
tab
                
tab-qq
                
微信小程序之购物车功能
                
margin塌陷
                
weui-wxss-master下载地址
                
python_vlc 播放http流
                
go学习笔记-简述
                
            
          
          
            原文地址：https://www.cnblogs.com/zqzhen/p/12762837.html




      
      
        
          
            最新文章
          
          
            
              尤雨溪  新手向：Vue 2.0 的建议学习顺序
                
用webstorm在chrome 调试页面时一直弹出  copy authorization url to clipboard
                
学习 Vue.js 需要掌握的 es6 (2)
                
学习 Vue.js 需要掌握的 es6 (1)
                
在jquery each循环中的continue和break
                
js中push(),pop(),unshift(),shift()的用法小结
                
Gulp 安装Sass时出现错误 Error: Can't find Python executable "python", you can set the PYTHON env variable.解决办法
                
WebAPI 为什么你的[FromBody]参数总是为NULL
                
Redis入门必读，The Little Redis Book中文版
                
1
                
            
          
        

        
          
            热门文章
          
          
            
              书面英语
                
计算机容量
                
2020/21/21，啊vin
                
2020/02/21，痛苦也有出生日期
                
2020/02/16，世界很大，我们贴得很紧
                
2020/02/21，地球不需要生命，生命不需要文明，文明不要智商
                
2020/02/15，公平和责任传承制度
                
2020/02/10，再谈记忆单元之缓存
                
微信小程序之购物车功能
                
微信小程序订单页面