Why EasyExcel can make you say goodbye to EasyPoi? Before we get to that, let’s take a look at easyPOI
easypoi
Easypoi function as the name easy, the main function is easy, so that a person who has not seen contact with POI can easily write Excel export,Excel template export,Excel import,Word template export, through simple annotations and template language (familiar expression grammar), complete the previous complex writing method
This is the official definition given by EasyPOi. It is really convenient to import and export Excel after using this tool. Especially something simple like Excel
EasyExcel processing simple Excel and EasyPOI as simple, processing complex Excel can be fully annotated by the way in one step. Developers only need to write very little style code to achieve the desired effect directly, let’s take a look at the power of easyExcel
Introducing Maven dependencies
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> </dependency> <dependency> <groupId>org.apache. Poi </groupId> <artifactId>poi</artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId> Poi-ooxml </artifactId> <version>3.15</version> </dependency>Copy the codeThe new entity
@Data @Accessors(chain = true) @FieldNameConstants @HeadRowHeight(value = 25) @ContentRowHeight(value = 18) @ColumnWidth(value = 20) @HeadStyle(fillBackgroundColor = 64) @HeadFontStyle(bold = false) @ContentStyle(borderTop= BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,borderBottom = BorderStyle.THIN) public Class ComplexSubjectEasyExcel {@ExcelProperty(value = {" balance table "," },index = 0) private String subjectId; @excelproperty (value = {" subject balance "," unit: 321412"," subjectName "," subjectName "},index = 1) private String subjectName; @headfontstyle (bold = true) @excelProperty (value = {" account balance "," Test unit 321412"," Start balance "," debit ", index = 2) private BigDecimal firstBorrowMoney; @headfontstyle (bold = true) @excelProperty (value = {" account balance "," Index = 3) private BigDecimal firstCreditMoney; @headfontStyle (bold = true) @excelProperty (value = {" Account balance table "," September 2021 to September 2021 "," current amount "," Debit "},index = 4) private BigDecimal nowBorrowMoney; @headfontStyle (bold = true) @excelProperty (value = {" Account balance table "," September 2021 to September 2021 "," current amount "," credit "},index = 5) private BigDecimal nowCreditMoney; @headfontStyle (bold = true) @excelProperty (value = {" account balance table "," September 2021 to September 2021 "," cumulative amount "," debit "},index = 6) private BigDecimal yearBorrowMoney; @headfontStyle (bold = true) @excelProperty (value = {" account balance table "," September 2021 to September 2021 "," cumulative amount "," credit "},index = 7) private BigDecimal yearCreditMoney; @headfontstyle (bold = true) @excelProperty (value = {" account balance "," unit: 元"," ending balance "," debit "},index = 8) private BigDecimal endBorrowMoney; @headfontstyle (bold = true) @excelProperty (value = {" account balance "," unit: $"," ending balance "," credit "},index = 9) private BigDecimal endCreditMoney; }Copy the codeThe value of the @excelProperty annotation is an array, indexed from top to bottom, and the same value headers are merged. This method of merging heads is more intuitive and convenient than the entity nesting of EasyPOI. After we query the data of the page list, we do not need to conduct array object nesting assembly, which saves a lot of work. If we want the header to be dynamic, we can also set it to the way of #{title} (of course, this is my own encapsulation).
Export tool
The following I package easyExcel export tool, use of words can be directly copied, and their own to do some appropriate adjustment
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.builder.ExcelWriterTableBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import org.springframework.util.Assert;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.*;
/**
* easyExcel工具
*/
public class EasyExcelUtilsV1 {
public static final String FILE_PATH = "/home/easy/excel/";
public static final Map<String,List<ExcelAnnotationValue>> annotationValues = new HashMap<>();
private static String outputStream(String fileName){
try {
String path = FILE_PATH+new Date().getTime() +"/";
String filePath = path+fileName+".xls";
File dir = new File(path);
if(!dir.exists()){
dir.mkdirs();
}
File file = new File(filePath);
if(file.exists()){
file.deleteOnExit();
}
file.createNewFile();
return filePath;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 默认导出方式 单个sheet
*/
public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, String fileName, String sheetName, Map<String,String> vars){
resetCLassAnnotationValue(pojoClass);
setExportClassAnnotation(pojoClass,vars);
String filePath = outputStream(fileName);
EasyExcel.write(filePath,pojoClass)
.sheet(sheetName)
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(list);
return getExcelOssUrl(filePath,fileName);
}
/**
* 默认导出方式 单个sheet
*/
public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, CellWriteHandler handler, String fileName, String sheetName, Map<String,String> vars){
resetCLassAnnotationValue(pojoClass);
setExportClassAnnotation(pojoClass,vars);
String filePath = outputStream(fileName);
EasyExcel.write(filePath,pojoClass)
.sheet(sheetName)
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(handler)
.doWrite(list);
return getExcelOssUrl(filePath,fileName);
}
/**
* 默认导出excel 单个sheet
*/
public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, List<WriteHandler> handlers, String fileName, String sheetName, Map<String,String> vars) {
resetCLassAnnotationValue(pojoClass);
setExportClassAnnotation(pojoClass,vars);
String filePath = outputStream(fileName);
ExcelWriterSheetBuilder builder = EasyExcel.write(filePath,pojoClass)
.sheet(sheetName);
if(!ObjectUtils.isEmpty(handlers)){
for(WriteHandler handler : handlers){
builder.registerWriteHandler(handler);
}
}
builder.doWrite(list);
return getExcelOssUrl(filePath,fileName);
}
/**
* 默认导出excel 单个sheet 多个table
*/
public static String defaultExportOssUrl(EasyExcelMoreSheetMoreTableEntity entity, String fileName, Map<String,String> vars) {
String filePath = outputStream(fileName);
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
String sheetName = entity.getSheetName();
List<WriteHandler> handlers = entity.getHandlers();
List<EasyExcelMoreSheetEntity> list = entity.getList();
try {
WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).build();
for (int i = 0; i < list.size(); i++) {
EasyExcelMoreSheetEntity sheetEntity = list.get(i);
List date = sheetEntity.getList();
Class clazz = sheetEntity.getClazz();
resetCLassAnnotationValue(clazz);
setExportClassAnnotation(clazz,vars);
ExcelWriterTableBuilder tableBuilder = EasyExcel.writerTable(i);
if (!ObjectUtils.isEmpty(handlers)) {
for (WriteHandler handler : handlers) {
tableBuilder.registerWriteHandler(handler);
}
}
WriteTable table = tableBuilder.head(clazz).needHead(true).build();
excelWriter.write(date, writeSheet, table);
}
}catch (Exception e){
e.printStackTrace();
}finally {
excelWriter.finish();
}
return getExcelOssUrl(filePath,fileName);
}
/**
* 多个sheet页导出
*/
public static String moreSheetExportOssUrl(List<EasyExcelMoreSheetEntity> entities,String fileName){
String filePath = outputStream(fileName);
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
try {
for (int i = 0; i < entities.size(); i++) {
EasyExcelMoreSheetEntity entity = entities.get(i);
Class clazz = entity.getClazz();
List list = entity.getList();
Map<String,String> vars = entity.getVars();
resetCLassAnnotationValue(clazz);
setExportClassAnnotation(clazz,vars);
String sheetName = entity.getSheetName();
List<WriteHandler> handlers = entity.getHandlers();
ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(i, sheetName);
if(!ObjectUtils.isEmpty(handlers)){
for(WriteHandler handler :handlers){
builder.registerWriteHandler(handler);
}
}
WriteSheet writeSheet = builder.head(clazz).build();
excelWriter.write(list, writeSheet);
}
}catch (Exception e){
e.printStackTrace();
}finally {
excelWriter.finish();
}
return getExcelOssUrl(filePath,fileName);
}
@SuppressWarnings("unchecked")
public static String moreSheetMoreTableExportOssUrl(List<EasyExcelMoreSheetMoreTableEntity> entities,String fileName){
String filePath = outputStream(fileName);
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
try {
for (int i = 0; i < entities.size(); i++) {
EasyExcelMoreSheetMoreTableEntity entity = entities.get(i);
List<EasyExcelMoreSheetEntity> list = entity.getList();
String sheetName = entity.getSheetName();
List<WriteHandler> handlers = entity.getHandlers();
ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet(i, sheetName);
if(!ObjectUtils.isEmpty(handlers)){
for(WriteHandler handler :handlers){
sheetBuilder.registerWriteHandler(handler);
}
}
//创建sheet
WriteSheet writeSheet = sheetBuilder.build();
//创建table
Assert.isTrue(!ObjectUtils.isEmpty(list),"缺少table数据");
for(int j = 0 ; j < list.size() ; j++){
EasyExcelMoreSheetEntity tableEntity = list.get(j);
Map<String,String> vars = tableEntity.getVars();
List<?> date = tableEntity.getList();
Class<?> clazz = tableEntity.getClazz();
resetCLassAnnotationValue(clazz);
setExportClassAnnotation(clazz, vars);
ExcelWriterTableBuilder tableBuilder = EasyExcel.writerTable(j);
if(j > 0){
tableBuilder.relativeHeadRowIndex(2);
}
WriteTable table = tableBuilder.head(clazz).needHead(true).build();
excelWriter.write(date,writeSheet,table);
}
}
}catch (Exception e){
e.printStackTrace();
}finally {
excelWriter.finish();
}
return getExcelOssUrl(filePath,fileName);
}
public static void defaultExport(List<?> list, Class<?> pojoClass, String filePath, String sheetName) {
EasyExcel.write(filePath,pojoClass)
.sheet(sheetName)
.registerWriteHandler(new CustomCellWriteHandler())
.doWrite(list);
}
private static String getExcelOssUrl(String filePath,String fileName) {
InputStream in = null;
try{
//临时缓冲区
in = new FileInputStream(filePath);
} catch (Exception e){
e.printStackTrace();
}
// 此处可以调用腾讯云的cos 或者阿里云的oss todo
String url = "";
return url;
}
public static void setExportClassAnnotation(Class<?> clazz,Map<String,String> map){
Field[] fields = clazz.getDeclaredFields();
for(Field field : fields){
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if(property != null){
List<String> newValues = new ArrayList<>();
String[] values = property.value();
for(String value : values){
value = replace(value,map);
newValues.add(value);
}
InvocationHandler h = Proxy.getInvocationHandler(property);
try {
Field annotationField = h.getClass().getDeclaredField("memberValues");
annotationField.setAccessible(true);
Map memberValues = (Map) annotationField.get(h);
memberValues.put("value",newValues.toArray(new String[]{}));
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
private static void resetCLassAnnotationValue(Class<?> clazz){
String className = clazz.getSimpleName();
List<ExcelAnnotationValue> values = annotationValues.get(className);
if(ObjectUtils.isEmpty(values)){
//如果静态资源是空的，保存
Field[] fields = clazz.getDeclaredFields();
values = new ArrayList<>();
for(Field field : fields){
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if(!ObjectUtils.isEmpty(excelProperty)) {
String[] vs = excelProperty.value();
ExcelAnnotationValue value = new ExcelAnnotationValue()
.setFieldName(field.getName())
.setValues(vs);
values.add(value);
}
}
annotationValues.put(className,values);
return;
}
Field[] fields = clazz.getDeclaredFields();
for(Field field : fields){
String fieldName = field.getName();
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if(!ObjectUtils.isEmpty(excelProperty)){
ExcelAnnotationValue value = values.stream().filter(v->v.getFieldName().equals(fieldName)).findFirst().orElse(null);
if(!ObjectUtils.isEmpty(value)){
String[] oldValues = value.getValues();
InvocationHandler handler = Proxy.getInvocationHandler(excelProperty);
try {
Field annotationField = handler.getClass().getDeclaredField("memberValues");
annotationField.setAccessible(true);
Map memberValues = (Map) annotationField.get(handler);
memberValues.put("value",oldValues);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
public static String replace(String el,Map<String,String> map){
if(map == null){
return el;
}
String evl = el;
for(Map.Entry<String,String> m : map.entrySet()){
String key = m.getKey();
String value = m.getValue();
el = el.replaceAll("#\{"+key+"\}",value);
if(!evl.equals(el)) {
return el;
}
}
return el;
}
}
Copy the codeTable Merge configuration
import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.List; /** * @author zl */ public class CustomCellMergeStrategy implements CellWriteHandler {private int[] mergeColumnIndex; private int mergeRowIndex; public CustomCellMergeStrategy() { } public CustomCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { int curRowIndex = cell.getRowIndex(); int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; }}}} /** * Current cell merges up ** @param writeSheetHolder * @param cell Current cell * @param curRowIndex Current line * @param curColIndex */ private void mergeWithPrevRow(WriteSheetHolder WriteSheetHolder, Cell Cell, int curRowIndex, int curColIndex) { Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); // Compare current cell data with previous cell data Boolean dataBool = predata.equals (curData); // Note here: Since I decide whether to merge according to the ordinal number, I get the first column data of each row and compare it with the first column data of the previous row. GetRow ().getCell(0).getStringCellValue().equals(cell.getsheet ().getrow (curRowIndex -) 1).getCell(0).getStringCellValue()); if (dataBool && bool) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && ! isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // If the previous cell has been merged, remove the original merged cell first, If (cellRangeAddr.isinrange (Currowindex-1, curColIndex)) {sheet.removemergedregion (I); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; }} // If the last cell is not merged, add merged cell if (! isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); }}}}Copy the codeDefault setting for the height of the table
public class CustomCellWriteHandler extends AbstractRowHeightStyleStrategy { @Override protected void setHeadColumnHeight(Row row, int i) { if(i == 0){ row.setHeight((short) (1000)); }else if(i == 1){ row.setHeight((short) 300); }else{ row.setHeight((short) 500); } } @Override protected void setContentColumnHeight(Row row, int i) { row.setHeight((short) 500); }}Copy the code@data @accessors (chain = true) public class ExcelAnnotationValue {/** * private String fieldName; /** * Private String[] values; }Copy the codeMultiple sheets Export object parameters
@data @accessors (chain = true) public class EasyExcelMoreSheetEntity {/** * entity class */ private class <? > clazz; /** * private List<? > list; /** * sheet name */ private String sheetName; /** * style */ private List<WriteHandler> Handlers; /** * head parameter */ private Map<String,String> vars; }Copy the codeMultiple tables and multiple sheets Export object parameters
@ Data @ Accessors (chain = true) public class EasyExcelMoreSheetMoreTableEntity {/ * * * * / private Data List<EasyExcelMoreSheetEntity> list; /** * sheet name */ private String sheetName; /** * style */ private List<WriteHandler> Handlers; /** * head parameter */ private Map<String,String> vars; }Copy the codeThe following is the simplest export
ComplexSubjectEasyExcel Excel = New ComplexSubjectEasyExcel().setSubjectid ("1001").setSubjectName(" Stock cash ") .setFirstBorrowMoney(BigDecimal.valueOf(100)) .setNowBorrowMoney(BigDecimal.valueOf(105)) .setNowCreditMoney(BigDecimal.valueOf(100)) .setYearBorrowMoney(BigDecimal.valueOf(200)) .setYearCreditMoney(BigDecimal.valueOf(205)) .setEndBorrowMoney(BigDecimal.valueOf(240)); List<ComplexSubjectEasyExcel> excels = new ArrayList<>(); excels.add(excel); String url = EasyExcelUtils. DefaultExportOssUrl (excels, ComplexSubjectEasyExcel. Class, "subject balance sheet", "subject balance sheet," new HashMap < > ()); System.out.println(url);Copy the codeMore sheet export
public void moreSheetTest(){ ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel() .setSubjectId("1001") .setSubjectName(" Stock cash ").setFirstBorrowMoney(BigDecimal.valueof (100)).setNowBorrowMoney(BigDecimal.valueof (105)) .setNowCreditMoney(BigDecimal.valueOf(100)) .setYearBorrowMoney(BigDecimal.valueOf(200)) .setYearCreditMoney(BigDecimal.valueOf(205)) .setEndBorrowMoney(BigDecimal.valueOf(240)); List<ComplexSubjectEasyExcel> excels = new ArrayList<>(); excels.add(excel); List<EasyExcelMoreSheetEntity> entities = new ArrayList<>(); for(int i=0 ; i< 2; i++){ EasyExcelMoreSheetEntity entity = new EasyExcelMoreSheetEntity() .setClazz(ComplexSubjectEasyExcel.class) .setList(excels).setSheetName(" Account Balance "+ I); entities.add(entity); } String url = EasyExcelUtils. MoreSheetExportOssUrl (entities, "subject balance sheet"); System.out.println(url); }Copy the codeMultiple sheets, multiple table export
public void moreSheetMoreTableTest(){ ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel() .setSubjectid ("1001").setSubjectName(" Stock cash ").setFirstBorrowMoney(BigDecimal. ValueOf (100)) .setNowBorrowMoney(BigDecimal.valueOf(105)) .setNowCreditMoney(BigDecimal.valueOf(100)) .setYearBorrowMoney(BigDecimal.valueOf(200)) .setYearCreditMoney(BigDecimal.valueOf(205)) .setEndBorrowMoney(BigDecimal.valueOf(240)); List<ComplexSubjectEasyExcel> excels = new ArrayList<>(); excels.add(excel); List<EasyExcelMoreSheetMoreTableEntity> entities = new ArrayList<>(); for(int i=0 ; i< 2; I++) {EasyExcelMoreSheetMoreTableEntity tableEntity = new EasyExcelMoreSheetMoreTableEntity () setSheetName (" subject balance sheet "+ I) .setHandlers(Arrays.asList(new CustomCellWriteHandler())); List<EasyExcelMoreSheetEntity> tables = new ArrayList<>(); EasyExcelMoreSheetEntity table = new EasyExcelMoreSheetEntity() .setClazz(ComplexSubjectEasyExcel.class) .setList(excels); if(i== 1){ tables.add(table); } tables.add(table); tableEntity.setList(tables); entities.add(tableEntity); } String url = EasyExcelUtils. MoreSheetMoreTableExportOssUrl (entities, "subject balance sheet"); System.out.println(url); }Copy the codeThat’s it! After use you will find easyExcel convenient and powerful