史上最全的Excel導(dǎo)入導(dǎo)出之easyexcel
喝水不忘挖井人,感謝阿里巴巴項目組提供了easyexcel工具類,github地址:https://github.com/alibaba/easyexcel
環(huán)境搭建
?
???com.alibaba
???easyexcel
???1.1.2-beat1
?
??
?
???org.projectlombok
???lombok
???1.18.2
?
讀取excel文件
小于1000行數(shù)據(jù)
默認讀取
?String?filePath?=?"/home/chenmingjian/Downloads/學(xué)生表.xlsx";
?List指定讀取
String?filePath?=?"/home/chenmingjian/Downloads/學(xué)生表.xlsx";
//第一個1代表sheet1,?第二個1代表從第幾行開始讀取數(shù)據(jù),行號最小值為0
Sheet?sheet?=?new?Sheet(1,?1);
List?String?filePath?=?"/home/chenmingjian/Downloads/學(xué)生表.xlsx";
?Sheet?sheet?=?new?Sheet(2,?0);
?List大于1000行數(shù)據(jù)
默認讀取
String?filePath?=?"/home/chenmingjian/Downloads/學(xué)生表.xlsx";
List指定讀取
String?filePath?=?"/home/chenmingjian/Downloads/學(xué)生表.xlsx";
Sheet?sheet?=?new?Sheet(1,?2);
List導(dǎo)出excle
單個Sheet導(dǎo)出
無模型映射導(dǎo)出
String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
List>?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?head?=?Arrays.asList("表頭1",?"表頭2",?"表頭3");
ExcelUtil.writeBySimple(filePath,data,head);
模型映射導(dǎo)出
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?=?"學(xué)校",index?=?2)
????private?String?school;
}
String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
ArrayList?data?=?new?ArrayList<>();
??for(int?i?=?0;?i?4;?i++){
??????TableHeaderExcelProperty?tableHeaderExcelProperty?=?new?TableHeaderExcelProperty();
??????tableHeaderExcelProperty.setName("cmj"?+?i);
??????tableHeaderExcelProperty.setAge(22?+?i);
??????tableHeaderExcelProperty.setSchool("清華大學(xué)"?+?i);
??????data.add(tableHeaderExcelProperty);
??}
??ExcelUtil.writeWithTemplate(filePath,data);
多個Sheet導(dǎo)出
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?=?"學(xué)校",index?=?2)
????private?String?school;
}
?ArrayList?list1?=?new?ArrayList<>();
?for(int?j?=?1;?j?4;?j++){
??????ArrayList?list?=?new?ArrayList<>();
??????for(int?i?=?0;?i?4;?i++){
??????????TableHeaderExcelProperty?tableHeaderExcelProperty?=?new?TableHeaderExcelProperty();
??????????tableHeaderExcelProperty.setName("cmj"?+?i);
??????????tableHeaderExcelProperty.setAge(22?+?i);
??????????tableHeaderExcelProperty.setSchool("清華大學(xué)"?+?i);
??????????list.add(tableHeaderExcelProperty);
??????}
??????Sheet?sheet?=?new?Sheet(j,?0);
??????sheet.setSheetName("sheet"?+?j);
??????ExcelUtil.MultipleSheelPropety?multipleSheelPropety?=?new?ExcelUtil.MultipleSheelPropety();
??????multipleSheelPropety.setData(list);
??????multipleSheelPropety.setSheet(sheet);
??????list1.add(multipleSheelPropety);
??}
??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");
??????//設(shè)置自適應(yīng)寬度
??????initSheet.setAutoWidth(Boolean.TRUE);
???}
???/**
????*?讀取少于1000行數(shù)據(jù)
????*?@param?filePath?文件絕對路徑
????*?@return
????*/
???public?static?List
測試類
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
?????*/
[email protected]
????public?void?readLessThan1000Row(){
????????String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
????????List?objects?=?ExcelUtil.readLessThan1000Row(filePath);
????????objects.forEach(System.out::println);
????}
????/**
?????*?讀取少于1000行的excle,可以指定sheet和從幾行讀起
?????*/
[email protected]
????public?void?readLessThan1000RowBySheet(){
????????String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
????????Sheet?sheet?=?new?Sheet(1,?1);
????????List?objects?=?ExcelUtil.readLessThan1000RowBySheet(filePath,sheet);
????????objects.forEach(System.out::println);
????}
????/**
?????*?讀取大于1000行的excle
?????*?帶sheet參數(shù)的方法可參照測試方法readLessThan1000RowBySheet()
?????*/
[email protected]
????public?void?readMoreThan1000Row(){
????????String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
????????List?objects?=?ExcelUtil.readMoreThan1000Row(filePath);
????????objects.forEach(System.out::println);
????}
????/**
?????*?生成excle
?????*?帶sheet參數(shù)的方法可參照測試方法readLessThan1000RowBySheet()
?????*/
[email protected]
????public?void?writeBySimple(){
????????String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
????????List>?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?head?=?Arrays.asList("表頭1",?"表頭2",?"表頭3");
????????ExcelUtil.writeBySimple(filePath,data,head);
????}
????/**
?????*?生成excle,?帶用模型
?????*?帶sheet參數(shù)的方法可參照測試方法readLessThan1000RowBySheet()
?????*/
[email protected]
????public?void?writeWithTemplate(){
????????String?filePath?=?"/home/chenmingjian/Downloads/測試.xlsx";
????????ArrayList?data?=?new?ArrayList<>();
????????for(int?i?=?0;?i?4;?i++){
????????????TableHeaderExcelProperty?tableHeaderExcelProperty?=?new?TableHeaderExcelProperty();
????????????tableHeaderExcelProperty.setName("cmj"?+?i);
????????????tableHeaderExcelProperty.setAge(22?+?i);
????????????tableHeaderExcelProperty.setSchool("清華大學(xué)"?+?i);
????????????data.add(tableHeaderExcelProperty);
????????}
????????ExcelUtil.writeWithTemplate(filePath,data);
????}
????/**
?????*?生成excle,?帶用模型,帶多個sheet
?????*/
[email protected]
????public?void?writeWithMultipleSheel(){
????????ArrayList?list1?=?new?ArrayList<>();
????????for(int?j?=?1;?j?4;?j++){
????????????ArrayList?list?=?new?ArrayList<>();
????????????for(int?i?=?0;?i?4;?i++){
????????????????TableHeaderExcelProperty?tableHeaderExcelProperty?=?new?TableHeaderExcelProperty();
????????????????tableHeaderExcelProperty.setName("cmj"?+?i);
????????????????tableHeaderExcelProperty.setAge(22?+?i);
????????????????tableHeaderExcelProperty.setSchool("清華大學(xué)"?+?i);
????????????????list.add(tableHeaderExcelProperty);
????????????}
????????????Sheet?sheet?=?new?Sheet(j,?0);
????????????sheet.setSheetName("sheet"?+?j);
????????????ExcelUtil.MultipleSheelPropety?multipleSheelPropety?=?new?ExcelUtil.MultipleSheelPropety();
????????????multipleSheelPropety.setData(list);
????????????multipleSheelPropety.setSheet(sheet);
????????????list1.add(multipleSheelPropety);
????????}
????????ExcelUtil.writeWithMultipleSheel("/home/chenmingjian/Downloads/aaa.xlsx",list1);
????}
????/*******************匿名內(nèi)部類,實際開發(fā)中該對象要提取出去**********************/
????
????
????/**
?????*?@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?=?"學(xué)校",index?=?2)
????????private?String?school;
????}
????/*******************匿名內(nèi)部類,實際開發(fā)中該對象要提取出去**********************/
}
? 作者?|??沖奶粉的奶爸
來源 |??csdn.net/qq_32258777/article/details/89031479

評論
圖片
表情



