1、前言
1.本文提到的解析方式,是一种轻量的解析方式,不适用于大excel,如果excel文件比较大请用easyexcel。
2.主要用到的技术就是注解,解析时扫描dto属性对应的注解,然后验证,验证成功赋值给相应的属性。
3.所列举的场景覆盖了本人目前接触的到范围,应该能适用于大部分的场景。
4.若有缺陷请提出,谢谢。
2.1 改动记录
2021.05.14 增加数值记录。
2023.02.28 优化性能,尝试解耦,暂没找到多列去重的好方式。
2.2 、项目结构
2.2、相关依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
3、相关类
package com.workSupport.operationEXCEL;
public abstract class IHandler {
private StringBuilder errMsg;
public IHandler(StringBuilder errMsg){
assert errMsg != null;
this.errMsg = errMsg;
}
public StringBuilder getErrMsg() {
return errMsg;
}
/**
* 元素重复
* @param rowNum 从0开始
* @param colNum 从0开始
* @return
*/
abstract public boolean duplicate(int rowNum, int colNum);
/**
* 元素为空
* @param rowNum 从0开始
* @param colNum 从0开始
* @return
*/
abstract public boolean isNull(int rowNum, int colNum);
/**
* 元素超出长度
* @param rowNum 从0开始
* @param colNum 从0开始
* @return
*/
abstract public boolean beyondLength(int rowNum, int colNum);
/**
* 正则不匹配
* @param rowNum 从0开始
* @param colNum 从0开始
* @return
*/
abstract public boolean unmatchRegex(int rowNum, int colNum);
/**
* 自定义处理
* @param cellName 列名称
* @param cellValue 具体行列的值
* @param sheetNum 从0开始
* @param rowNum 从0开始
* @param colNum 从0开始
* @return
*/
abstract public void callback(String cellName, String cellValue, int sheetNum, int rowNum, int colNum);
}
package com.workSupport.operationEXCEL;
import java.text.MessageFormat;
public class DefaultHandler extends IHandler {
public DefaultHandler(StringBuilder errMsg) {
super(errMsg);
}
@Override
public boolean duplicate(int rowNum, int colNum) {
getErrMsg().append(MessageFormat.format("{0}行{1}列重复,", rowNum + 1, colNum + 1));
return false;
}
@Override
public boolean isNull(int rowNum, int colNum) {
getErrMsg().append(MessageFormat.format("{0}行{1}列不能为空,", rowNum + 1, colNum + 1));
return false;
}
@Override
public boolean beyondLength(int rowNum, int colNum) {
getErrMsg().append(MessageFormat.format("{0}行{1}列超长,", rowNum + 1, colNum + 1));
return false;
}
@Override
public boolean unmatchRegex(int rowNum, int colNum) {
getErrMsg().append(MessageFormat.format("{0}行{1}列不符合正则规则,", rowNum + 1, colNum + 1));
return false;
}
@Override
public void callback(String cellName, String cellValue, int sheetNum, int rowNum, int colNum) {
// do nothing
}
}
package com.workSupport.operationEXCEL;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* excel 解析注解
* @author yu
*
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelResolveAnno {
/**
* excel中的标题
* @return
*/
String headerName() ;
/**
* 字段的最大长度
* @return
*/
int maxLenght() default -1;
/**
* 检查非空
*/
boolean checkNull() default false;
/**
* 正则校验
*/
String regex() default "";
/**
* 检查是否重复
* @return
*/
boolean checkDuplicate() default false;
}
package com.workSupport.operationEXCEL;
import java.util.Date;
/**
* 测试用的dto
* @author yu
*
*/
public class TestDTO {
@ExcelResolveAnno(headerName = "姓名" , maxLength = 50 , checkNull = true , checkDuplicate = true)
private String name;
private Date birthDate;
@ExcelResolveAnno(headerName = "出生日期" , maxLength = 10 , checkNull = true , regex = "\\d{4}-\\d{2}-\\d{2}")
private String birthDateStr;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthDate() {
return birthDate;
}
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
public String getBirthDateStr() {
return birthDateStr;
}
public void setBirthDateStr(String birthDateStr) {
this.birthDateStr = birthDateStr;
}
@Override
public String toString() {
return "ExcelResolveDTO [name=" + name + ", birthDate=" + birthDate + ", birthDateStr=" + birthDateStr + "]";
}
}
package com.workSupport.operationEXCEL;
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* 解析excel工具
* @author yu
*
* @param <T>
*/
public class ExcelResolveUtil<T> {
/**
* 解析excel
* @param errMsg
* @param excelFile
* @param clazz
* @return
* @throws Exception
*/
public List<T> resolveExcel(StringBuilder errMsg, File excelFile, Class<T> clazz) throws Exception
{
return resolveExcel(new DefaultHandler(errMsg), excelFile, clazz);
}
public List<T> resolveExcel(IHandler handler, File excelFile, Class<T> clazz) throws Exception
{
List<T> resolveList = new LinkedList<T>();
if(excelFile != null)
{
Map<String , Integer> cellIndexMap = new HashMap<String , Integer>();
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelFile));
for (int sheetNum = 0, totalSheetNum = workbook.getNumberOfSheets(); sheetNum < totalSheetNum; ++sheetNum) {
XSSFSheet xssfSheet = workbook.getSheetAt(sheetNum);
if (xssfSheet == null) {
handler.getErrMsg().append("sheet为空!");
break;
}
boolean addFlag = true;
Map<String , Set<String>> checkDuplicateMap = new HashMap<>();
for (int rowNum = 0, totalRowNum = xssfSheet.getLastRowNum(); rowNum <= totalRowNum; ++ rowNum)
{
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if(xssfRow != null)
{
T t = clazz.newInstance();
fieldEach : for(Field field : clazz.getDeclaredFields())
{
ExcelResolveAnno excelResolveAnno = field.getAnnotation(ExcelResolveAnno.class);
if(excelResolveAnno != null)
{
String cellName = field.getName();
if(rowNum == 0) { //将excle中的表头和dto的属性建立联系
cellEach:
for (int cellNum = 0, totalCellNum = xssfRow.getLastCellNum(); cellNum < totalCellNum; ++cellNum) {
String cellValue = getCellValue(xssfRow, cellNum);
if (excelResolveAnno.headerName().equals(cellValue)) {
cellIndexMap.put(cellName, cellNum);
}
// 如果校验非空,生成重复校验的set
if (excelResolveAnno.checkDuplicate()) {
checkDuplicateMap.put(cellName, new HashSet<String>());
}
}
}
else
{ //content
addFlag = true;
int colNum = cellIndexMap.get(cellName);
String cellValue = getCellValue(xssfRow , colNum);
if(excelResolveAnno.checkNull() && StringUtils.isBlank(cellValue))
{
addFlag = handler.isNull(rowNum, colNum);
break fieldEach;
}
if(cellValue == null)
{
continue;
}
else if(cellValue.length() > excelResolveAnno.maxLength())
{
addFlag = handler.beyondLength(rowNum, colNum);
break fieldEach;
}
if(excelResolveAnno.regex() != null && excelResolveAnno.regex().length() > 0)
{
if(!cellValue.matches(excelResolveAnno.regex()))
{
addFlag = handler.unmatchRegex(rowNum, colNum);
break fieldEach;
}
}
if(excelResolveAnno.checkDuplicate())
{
Set<String> duplicateSet = checkDuplicateMap.get(cellName);
if(duplicateSet.contains(cellValue))
{
addFlag = handler.duplicate(rowNum, colNum);
break fieldEach;
}
}
Field declaredField = t.getClass().getDeclaredField(cellName);
declaredField.setAccessible(true);
declaredField.set(t, cellValue);
}
}
else
{
addFlag = false;
}
}
if(addFlag && t != null)
{
resolveList.add(t);
for(Field field : t.getClass().getDeclaredFields())
{
ExcelResolveAnno excelResolveAnno = field.getAnnotation(ExcelResolveAnno.class);
if(excelResolveAnno != null)
{
if(excelResolveAnno.checkDuplicate())
{
String fieldName = field.getName();
field.setAccessible(true);
Set<String> duplicateSet = checkDuplicateMap.get(fieldName);
duplicateSet.add((String)field.get(t));
}
}
}
}
}
}
}
}
else
{
handler.getErrMsg().append("文件为空!");
}
return resolveList;
}
/**
* 浠tring鐨勬柟寮忚繑鍥瀋ell涓殑鍐呭
* @param xssfRow
* @param cellNum
* @return
*/
private String getCellValue(XSSFRow xssfRow, int cellNum)
{
// TODO Auto-generated method stub
if(xssfRow != null)
{
XSSFCell xssfCell = xssfRow.getCell(cellNum);
CellType cellType = xssfCell.getCellType();
if(CellType._NONE.equals(cellType))
{
return null;
}
else if(CellType.BLANK.equals(cellType))
{
return null;
}
else if(CellType.ERROR.equals(cellType))
{
return null;
}
else if(CellType.BOOLEAN.equals(cellType))
{
boolean cellValue = xssfCell.getBooleanCellValue();
return Boolean.toString(cellValue);
}
else if(CellType.FORMULA.equals(cellType))
{
return xssfCell.getCellFormula();
}
else if(CellType.STRING.equals(cellType))
{
return xssfCell.getStringCellValue();
}
else if(CellType.NUMERIC.equals(cellType))
{
double cellValue = xssfCell.getNumericCellValue();
return String.valueOf(cellValue);
}
}
return null;
}
public void mannual_free(List<T> result)
{
if(result != null)
{
result.clear();
}
}
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
ExcelResolveUtil<TestDTO> excelResolveMain = new ExcelResolveUtil<>();
StringBuilder stringBuilder = new StringBuilder();
List<TestDTO> result = excelResolveMain.resolveExcel(stringBuilder,
new File("E:\\javaFile\\thinkTest_space\\ThinkTestInMaven\\src\\main\\resources\\com\\workSupport\\operExcel\\ExcelResolve.xlsx"),
TestDTO.class);
System.out.println(result);
excelResolveMain.mannual_free(result);
System.out.println(stringBuilder);
}
}