【开发心得】EasyExcel导入导出
概述:
使用 easyExcel 3.x版本导出参考
环境:
jdk1.8
Springboot 2.5.7
easyExcel 3.0.5
easyPoi 5.0.0
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
<exclusions>
<exclusion>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
<exclusions>
<exclusion>
<groupId>org.apache.xmlgraphics</groupId>
<artifactId>batik-all</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.santuario</groupId>
<artifactId>xmlsec</artifactId>
</exclusion>
<exclusion>
<groupId>de.rototor.pdfbox</groupId>
<artifactId>graphics2d</artifactId>
</exclusion>
</exclusions>
</dependency>【模板类参考.脱敏】
@Data
@NoArgsConstructor
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 表头样式
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER) // 内容样式
@HeadFontStyle(bold = BooleanEnum.TRUE,color = -1)
public class DataOutput {
@ColumnWidth(value = 20)
@ExcelProperty(value = {"id"} ,index = 0)
private String uuid;
@ColumnWidth(value = 20)
@ExcelProperty(value = {"名称"} ,index = 1)
private String name;
@ContentStyle(dataFormat = 49)
@ColumnWidth(value = 20)
@ExcelProperty(value = {"时间点"} ,index = 2)
private Long materialId;
@ExplicitConstraint(source = {"类型1", "类型2", "类型3"}) // 固定下拉内容
@ColumnWidth(value = 20)
@ExcelProperty(value = {"类型"} ,index = 4)
private String type;
@ExplicitConstraint(sourceClass = ScopeExplicitConstraint.class) // 动态下拉内容
@ColumnWidth(value = 20)
@ExcelProperty(value = {"使用范围"} ,index = 8)
private String scope;
}【合并列】
package com.xxx.output;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.xxx.UploadDataListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.io.*;
import java.lang.reflect.Field;
import java.util.List;
/**
* @author
* @date 2020-12-01 13:34
**/
@Slf4j
public class ExcelAnalysisHelper<T> {
public List<T> getList(File file, Class<T> clazz) {
try (InputStream inputStream = new FileInputStream(file)) {
return getList(inputStream, clazz, 0, 1);
} catch (FileNotFoundException e) {
log.error("file not found:{}", e.getMessage());
} catch (IOException e) {
log.error("file io exception:{}", e.getMessage());
}
return null;
}
public List<T> getList(InputStream inputStream, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
UploadDataListener<T> listener = new UploadDataListener<>(headRowNumber);
EasyExcel.read(inputStream, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
if (CollectionUtils.isEmpty(extraMergeInfoList)) {
return listener.getData();
}
List<T> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
return data;
}
/**
* 处理合并单元格
*
* @param data 解析数据
* @param extraMergeInfoList 合并单元格信息
* @param headRowNumber 起始行
* @return 填充好的解析数据
*/
private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
// 循环所有合并单元格信息
extraMergeInfoList.forEach(cellExtra -> {
int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
int firstColumnIndex = cellExtra.getFirstColumnIndex();
int lastColumnIndex = cellExtra.getLastColumnIndex();
// 获取初始值
Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
// 设置值
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
setInitValueToList(initValue, i, j, data);
}
}
});
return data;
}
/**
* 设置合并单元格的值
*
* @param filedValue 值
* @param rowIndex 行
* @param columnIndex 列
* @param data 解析数据
*/
public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
T object = data.get(rowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
// 提升反射性能，关闭安全检查
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == columnIndex) {
try {
field.set(object, filedValue);
break;
} catch (IllegalAccessException e) {
log.error("analysis data error: {}", e.getMessage());
}
}
}
}
}
/**
* 获取合并单元格的初始值
* rowIndex对应list的索引
* columnIndex对应实体内的字段
*
* @param firstRowIndex 起始行
* @param firstColumnIndex 起始列
* @param data 列数据
* @return 初始值
*/
private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
Object filedValue = null;
T object = data.get(firstRowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
// 提升反射性能，关闭安全检查
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == firstColumnIndex) {
try {
filedValue = field.get(object);
break;
} catch (IllegalAccessException e) {
log.error("analysis error:{}", e.getMessage());
}
}
}
}
return filedValue;
}
}
package com.xxx.output;
/**
* @Author: martin
* @Date: 2021/12/15 6:36 下午
* @Description:
*/
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
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
* @date
*/
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
Cell firstCell = cell.getRow().getCell(0); // 当前行第一个cell
Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0); // 上一行第一个cell
if (firstCell != null && preFirstCell != null) {
if (!firstCell.getStringCellValue().equals(preFirstCell.getStringCellValue())) {
return; // 首行不一致，跳过合并
}
}
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
if (cell.getCellType() == CellType.BLANK) {
return;
}
Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (curData.equals(preData)) {
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 (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
【下拉框-动态】
package com.xxx.output;
import java.lang.annotation.*;
/**
* @Author: martin
* @Date: 2021-12-22 10:46
* @Description:
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExplicitConstraint {
//定义固定下拉内容
String[]source()default {};
//定义动态下拉内容，
Class[]sourceClass()default {};
}
package com.xxx.output;
/**
* @Author: martin
* @Date: 2021-12-22 10:47
* @Description:
*/
public interface ExplicitInterface {
/**
* 下拉列表的内容数组
* @return
*/
String[] source();
}
package com.xxx.output;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
/**
* @Author: martin
* @Date: 2021-12-22 11:08
* @Description:
*/
public class ExcelExplicitStrategy implements SheetWriteHandler {
// 处理下拉列表
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 下拉列表集合
Map<Integer, String[]> explicitListConstraintMap = new HashMap<>();
// 循环获取对应列得下拉列表信息
Field[] declaredFields = XXX.class.getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field field = declaredFields[i];
// 解析注解信息
ExplicitConstraint explicitConstraint = field.getAnnotation(ExplicitConstraint.class);
String[] explicitArray = resolveExplicitConstraint(explicitConstraint);
if (explicitArray != null && explicitArray.length > 0) {
explicitListConstraintMap.put(i, explicitArray);
}
// 通过sheet处理下拉信息
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
explicitListConstraintMap.forEach((k, v) -> {
CellRangeAddressList rangeList = new CellRangeAddressList();
CellRangeAddress addr = new CellRangeAddress(1, 1000, k, k);
rangeList.addCellRangeAddress(addr);
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
DataValidation validation = helper.createValidation(constraint, rangeList);
sheet.addValidationData(validation);
});
}
}
/**
* 解析注解内容 获取下列表信息
*
* @param explicitConstraint
* @return
*/
public String[] resolveExplicitConstraint(ExplicitConstraint explicitConstraint) {
if (explicitConstraint == null) {
return null;
}
//固定下拉信息
String[] source = explicitConstraint.source();
if (source.length > 0) {
return source;
}
//动态下拉信息
Class<? extends ExplicitInterface>[] classes = explicitConstraint.sourceClass();
if (classes.length > 0) {
ExplicitInterface explicitInterface = null;
try {
explicitInterface = classes[0].newInstance();
String[] source1 = explicitInterface.source();
if (source1.length > 0) {
return source1;
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return null;
}
}
public class ScopeExplicitConstraint implements ExplicitInterface {
@Override
public String[] source() {
List<String> list = new ArrayList<>();
for (ScopeEnum item : ScopeEnum.values()) {
list.add(item.name);
}
return list.toArray(new String[list.size()]);
}
}
【导出参考】
public void exportData(HttpServletResponse response, List<Long> dataIds) {
PageRequest pageRequest = PageRequest.of(0, dataIds.size());
List<xxxOutput> resultList = xxxService.getEditDocument(dataIds, pageRequest);
try {
// 设置第几列合并
int[] mergeColumnIndex = {0, 1, 2, 3}; // 合并第一列，所以赋值0，如果需要合并多列，直接逗号分隔：int[] mergeColumnIndex = {0,1,2}
// 需要从第几行开始合并
int mergeRowIndex = 1;
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置文件名称
// String fileName = URLEncoder.encode("xxx", "UTF-8");
String fileName = this.getXlsFileName();
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
// sheet名称
EasyExcel.write(response.getOutputStream(), xxx.class)
// excel版本
.excelType(ExcelTypeEnum.XLSX)
// 是否自动关流
.autoCloseStream(Boolean.TRUE)
.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex))
.registerWriteHandler(new ExcelExplicitStrategy())
.sheet("xxx导出").doWrite(resultList);
} catch (Exception e) {
log.error("export xxx error: {}", e.getMessage());
}
}
