This is the 13th day of my participation in Gwen Challenge
JAVA Excel parsing tool EasyExcel
Java parsing, Excel generation frameworks are well known Apache POI, JXL. Poi has a SET of SAX mode API, which can solve some problems of memory overflow to a certain extent. However, POI still has some defects. For example, the decompression and storage after decompression of 07 Version Excel are completed in memory, which still consumes a lot of memory. Easyexcel rewrites poI on 07 version of Excel parsing, can originally a 3M Excel with POI SAX still need about 100M memory reduced to a few M, and no matter how large Excel memory overflow, 03 version depends on POI SAX mode. The model transformation is encapsulated in the upper layer to make the user more simple and convenient
EasyExcel version
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.9</version>
</dependency>
Copy the codeBefore using EasyExcel, we will talk about EasyExcel Listener
A Listener has a method for each piece of data
ReadListener source code:
public interface ReadListener<T> extends Listener {
// When parsing throws an exception, it is caught by this method
void onException(Exception exception, AnalysisContext context) throws Exception;
// Parse the table header. HeadMap defaults to the first row of the table
void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context);
// A parsed object, data, can be processed again in this method
void invoke(T data, AnalysisContext context);
// Call to return extra information, not used
void extra(CellExtra extra, AnalysisContext context);
// After parsing is complete, this method is run
void doAfterAllAnalysed(AnalysisContext context);
// Verify whether parsing continues. Default is true
boolean hasNext(AnalysisContext context);
}
Copy the codeThis method will have a generic passed in, which is the object parsed into each row of the table
For example, if we just want to convert to a List, override AnalysisEventListener (an implementation class of ReadListener) to pass in the List
We can convert, and of course there’s an official implementation of the default List NoModelDataListener
But you’d better rewrite the AnalysisEventListener yourself if you want to get the data from the parse table, so you can process the data
EasyExcel use
Read the data
EasyExcel has many different ways of reading data, so let’s use NoModelDataListener to see how we can parse table data
public void test(a) {
// File can be a file, a stream, or a file path but it must be an.xsl or.xslx file
File file = new File("");
// read Reads data passed into a table in an Excel file
// The first type: sheet() The default (no arguments) is the first type of table
// sheetNo can also specify the number of tables that start with 0
// sheetName can also specify table names
// The file stream closes automatically after a successful read
EasyExcel.read(file,new NoModelDataListener()).sheet(0."").doRead();
// The first type: sheet() defaults to the first table, or you can specify the number of tables, starting with 0
ExcelReader reader = EasyExcel.read(file, new NoModelDataListener()).build();
ReadSheet () defaults (no arguments) to the first table
// sheetNo can also specify the number of tables that start with 0
// sheetName can also specify table names
//sheet cannot be read multiple times. Multiple reads require a re-read of the file
ReadSheet sheet = EasyExcel.readSheet().build();
reader.read(sheet);
Remember to close ExcelReader
reader.finish();
// EasyExcel can also be replaced with EasyExcelFactory
// EasyExcel inherits EasyExcelFactory. EasyExcel does not rewrite EasyExcelFactory
EasyExcel and EasyExcelFactory are equivalent to the same class
// read read the incoming Excel file, all the table data
/ / the first
// But if parsing fails, an exception is thrown or the Listener's hasNext method returns false
EasyExcel.read(file,new NoModelDataListener()).doReadAll();
/ / the second
// However, if parsing fails, an exception is thrown. Parsing stops but some excelReader data is closed. A new excelReader must be created to continue the loop
If the hasNext method returns false, the Listener does not proceed
ExcelReader excelReader = EasyExcel.read(file, new NoModelDataListener()).build();
List<ReadSheet> readSheets = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheets) {
excelReader.read(readSheet);
}
Remember to close ExcelReader
excelReader.finish();
}
Copy the codeCustom Listener
The following describes the custom Listener
We can actually encapsulate a generic Listener
This is the Listener I wrapped myself
@Slf4j
public class DefaultExcelListener<T> extends AnalysisEventListener<T> {
// Parsed data
private final List<T> rows = new ArrayList();
// Parse the table header. HeadMap defaults to the first row of the table
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =");
log.info("Parse the first row :{}" + JSON.toJSONString(headMap));
log.info("= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =");
}
// Save the parsed data to rows
@Override
public void invoke(T object, AnalysisContext context) {
rows.add(object);
}
// After reading excel data
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("Read successfully [" + rows.size() + "】 article data");
}
// This interface is called when an Excel exception is read to obtain other exceptions. Throwing an exception stops reading. Continue reading the next line if no exception is thrown here.
@Override
public void onException(Exception exception, AnalysisContext context) {
System.out.println("Parsing failed, but proceed to the next line :{}" + exception.getMessage());
}
public List<T> getRows(a) {
returnrows; }}Copy the codeWe pass in a generic object (object property annotated with EasyExcel) to parse, and then fetch rows to get the parsed data
Of course, you can modify it according to your own understanding
Problems in use
Talk about the problems encountered in the process of use
1. The problem you want to stop parsing
EasyExcel want to throw out ExcelAnalysisStopException parsing needs to stop parsing
For example, if you want to check the table header, it will not parse if the table header does not match
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
/ / don't match
throw new ExcelAnalysisStopException();
}
Copy the codeAn exception thrown will be caught by the onException method
We’ll throw in onException ExcelAnalysisStopException can throw
@Override
public void onException(Exception exception, AnalysisContext context) {
if (exception instanceof ExcelAnalysisStopException) {
throw new ExcelAnalysisStopException("");
}
System.out.println("Parsing failed, but proceed to the next line :{}" + exception.getMessage());
}
Copy the codeHowever, some versions do not throw this exception, and onException will be caught by the upper layer to throw another exception
So if you want to stop parsing, it’s safe to use hasNext
2. The string “” will not be checked when converting table cell data into objects.
Converts cell data into objects using a dedicated Converter
The Converter interface
public interface Converter<T> {
// The type of the converted object
Class supportJavaTypeKey(a);
// Cell data type
CellDataTypeEnum supportExcelTypeKey(a);
// cellData cellData
// T the converted object
// Convert cell data to objects: this is mainly used for reading
T convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception;
// Convert an object to object cell data: this is mainly used for writing
CellData convertToExcelData(T value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration)
throws Exception;
}
Copy the codeEasyExcel has some basic types of default converters that also inherit the Converter interface
We can also inherit this interface to override methods if we want to customize converters
EasyExcel uses the String to base object method by default when using converters
But there is no validation for the string “”, so an error will be reported when converting objects such as Data
I have customized the solution, I do not know whether I use the wrong way, or there is a solution but I did not use it, if you find that I did not find, please tell me
EasyExcel default converters are converted using the custom base type utils