Individuals encounter a need at work to read a third-party reconciliation file, but the cell format of the date in the reconciliation file is not text, so it will be a number that represents the number of days from today on January 1, 1990. Moreover, this excel is divided into two tables. The first table is what I need, the second table does not need to be read, and there are constraints such as describing rows, and the header of the table is not the first row.
The initial simple idea was to read out the numbers and then use Date to accept them, so that you know that the string of numbers corresponding to Excel is actually date, and then define a converter in a custom listener for date conversion. The general code is as follows:
public class CustomModelBuildListener<T> extends AnalysisEventListener<Object> {
/**
* The line where the head is located is used for multiple head rows, and you only want to obtain the data of a certain line as the head
*/
private final int headRow;
/**
* data start line, you can skip some data you don't want to read, such as sample data
*/
private final int dataRow;
/**
* Final data storage List
*/
@Getter
private final List<T> result = new ArrayList<>();
/**
* Used to convert the original structure to the specified type
*/
private final Class<T> clazz;
/**
* Used to correspond to field relationships
*/
private Map<Integer, ReadCellData<?>> headMap;
public CustomModelBuildListener(int headRow, int dataRow, Class<T> clazz) {
= headRow;
= dataRow;
= clazz;
}
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
if (getCurrentRow(context) != headRow) {
return;
}
= headMap;
//("Invoke head of listener: {}", (headMap));
}
@Override
public void invoke(Object data, AnalysisContext context) {
int currentRow = getCurrentRow(context);
if (currentRow < dataRow) {
return;
}
(converter(data));
//("Invoke data of listener, data:{}, row: {}", (data), currentRow);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
("All analysed done of listener, row: {}", getCurrentRow(context));
}
private int getCurrentRow(AnalysisContext context) {
return ().getRowIndex() + 1;
}
@SuppressWarnings({"unchecked", "rawtypes"})
private T converter(Object data) {
try {
Map<Integer, ReadCellData> dataMap = (Map<Integer, ReadCellData>) data;
Map<String, Object> name2Value = new HashMap<>();
((key, value) -> {
if (!(key)) {
return;
}
ReadCellData<?> cellData = (key);
if (() == ) {
((), ());
} else if (() == ) {
((), ());
}
});
T instance = ();
for (Field field : ()) {
if (()) {
ExcelProperty excelProperty = ();
String desc = ()[0];
if ((desc)) {
(true);
if (() == ) {
Object value = (desc);
if (value instanceof BigDecimal) {
(instance, ((BigDecimal) value).intValue());
} else if (value instanceof String) {
(instance, ((String) value));
} else {
("Unknown type for integer convert, type:{}", ());
throw new RuntimeException("Unknown type for converter, type:" + ());
}
} else if (() == ) {
Object value = (desc);
if (value instanceof BigDecimal) {
(instance, ((BigDecimal)value).longValue());
} else if (value instanceof String) {
(instance, ((String)value));
} else {
("Unknown type for long convert, type:{}", ());
throw new RuntimeException("Unknown type for converter, type:" + ());
}
} else if (() == ) {
Object value = (desc);
if (value instanceof BigDecimal) {
(instance, ((BigDecimal)value).doubleValue());
} else if (value instanceof String) {
(instance, ((String)value));
} else {
("Unknown type for double convert, type:{}", ());
throw new RuntimeException("Unknown type for converter, type:" + ());
}
} else if (() == ) {
(instance, (desc));
} else if (() == ) {
(instance, ((Integer) (desc)));
} else {
//If you reach this logic, it means that some types of conversion have not been adapted, just make up for it. ("Unknown type for converter, type:{}", ());
throw new RuntimeException("Unknown type for converter, type:" + ());
}
}
}
}
return instance;
} catch (Exception e) {
("Convert data error, data:{}", (data), e);
throw new RuntimeException(e);
}
}
}
The key is that the last () == indicates that the number needs to be converted to date, but if the text format in excel is originally in text format and you accept it with Date, you will also go into this logic, and then an error will be reported, because the conversion method is as follows:
public static Date formatExcelDate(int day) {
LocalDate localDate = (1990, 1);
localDate = (day);
return (().atZone(()).toInstant());
}
So this writing only applies to dates where Excel is in non-text format, and accepts class attributes that are Date.
So the second method was discovered.
Click on the @ExcelProperty annotation and found the following annotation
@Target()
@Retention()
@Inherited
public @interface ExcelProperty {
/**
* The name of the sheet header.
*
* <p>
* write: It automatically merges when you have more than one head
* <p>
* read: When you have multiple heads, take the last one
*
* @return The name of the sheet header
*/
String[] value() default {""};
/**
* Index of column
*
* Read or write it on the index of column, If it's equal to -1, it's sorted by Java class.
*
* priority: index > order > default sort
*
* @return Index of column
*/
int index() default -1;
/**
* Defines the sort order for an column.
*
* priority: index > order > default sort
*
* @return Order of column
*/
int order() default Integer.MAX_VALUE;
/**
* Force the current field to use this converter.
*
* @return Converter
*/
Class<? extends Converter<?>> converter() default ;
/**
*
* default @see if default is not meet you can set format
*
* @return Format string
* @deprecated please use {@link }
*/
@Deprecated
String format() default "";
}
The last format is discarded, but points to a new annotation, which is used to parse the time to convert it into Date type.
@Target()
@Retention()
@Inherited
public @interface DateTimeFormat {
/**
*
* Specific format reference {@link }
*
* @return Format pattern
*/
String value() default "";
/**
* True if date uses 1904 windowing, or false if using 1900 date windowing.
*
* @return True if date uses 1904 windowing, or false if using 1900 date windowing.
*/
BooleanEnum use1904windowing() default ;
}
So use this annotation to specify format, such as -MM-dd.
However, because the header row needs to be specified and the reading process is manually interrupted, a listener is still customized
public class DefaultModelBuildListener<T> extends AnalysisEventListener<T> {
/**
* Final data storage List
*/
@Getter
private List<T> result = new ArrayList<>();
private boolean continueRead = true;
@Override
public void invoke(T data, AnalysisContext context) {
if ((data) || isFieldNull(data)) {
// Stop reading continueRead = false;
return;
}
// Add the read data to the list (data);
}
public static boolean isFieldNull(Object object) {
if (object == null) {
return true;
}
Field[] fields = ().getDeclaredFields();
for (Field field : fields) {
try {
(true);
if ((object) == null) {
return true;
} else {
return false;
}
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
}
return false;
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
("All analysed done of listener");
}
@Override
public boolean hasNext(AnalysisContext context) {
return continueRead;
}
}
The most important thing is to judge the termination condition. Because the first table is different from the second table, the column of the first table is not in the second table. The corresponding entity class attribute of the reading of the second table is null, so the condition is added to the invoke method. I first found that null was used. However, when an exception is found, click in and find that it is another discarded method. It is prompted to use hasNext to terminate. To further deepen, it is found that the reading process is:
for (ReadListener readListener : ().readListenerList()) {
try {
if (isData) {
((), analysisContext);
} else {
(cellDataMap, analysisContext);
}
} catch (Exception e) {
onException(analysisContext, e);
break;
}
if (!(analysisContext)) {
throw new ExcelAnalysisStopException();
}
}
Each time a line is read, hasNext will be used to determine whether the succession will continue, so rewrite the hasNext method to terminate without exception.
There is no problem after testing.
The final two reading methods are as follows
/**
* Excel parsing method, there are many custom attributes, so it is recommended to use parse(InputStream inputStream, Class<T> clazz)
* @param inputStream input stream
* @param clazz Type of data model class
* @param <T> Generics of data models
* @param headRowNumber There are several rows in the header row of the table, and the rows here will not be read.
* @param headRow header row number
* @param dataRow Number of start rows
* @return Data list
*/
public static <T> List<T> parse(InputStream inputStream, Class<T> clazz, int headRowNumber, int headRow, int dataRow) {
CustomModelBuildListener<T> dataListener = new CustomModelBuildListener<>(headRow,dataRow, clazz);
try (InputStream in = inputStream) {
ExcelReaderBuilder excelReaderBuilder = EasyExcel
.read(in, clazz, dataListener)
.useDefaultListener(false); //It is important that the object will not be converted using ModelBuildEventListener ExcelReaderSheetBuilder sheetBuilder = ().headRowNumber(headRowNumber);
();
return ();
} catch (Exception e) {
("Failed to parse excel file", e);
throw new ServiceException(SystemCode.PARAM_VALID_ERROR, "Failed to parse Excel file "+ ());
}
}
/**
* Excel parsing method, there are many custom attributes, so it is recommended to use parse(InputStream inputStream, Class<T> clazz)
* @param inputStream input stream
* @param clazz Type of data model class
* @param <T> Generics of data models
* @param headRowNumber Which row is the header of the table? The next row is data by default. If there are rows that do not want to be read between the header and the data row, you need to customize the process in the listener.
* @return Data list
*/
public static <T> List<T> parseDefault(InputStream inputStream, Class<T> clazz, int headRowNumber) {
DefaultModelBuildListener excelDataListener = new DefaultModelBuildListener();
try (InputStream in = inputStream) {
(in, clazz, excelDataListener)
.sheet()
.headRowNumber(headRowNumber)
.doRead();
return ();
} catch (Exception e) {
("Failed to parse excel file", e);
throw new ServiceException(SystemCode.PARAM_VALID_ERROR, "Failed to parse Excel file "+ ());
}
}The first method corresponds to the parse method, and the second solution corresponds to the parseDefault method.
This is the article about EasyExcel reading date cells in Excel and automatically determines that the reading is terminated. For more information about EasyExcel reading date cells, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!