This is the 27th day of my participation in the Novembermore Challenge.The final text challenge in 2021

Hello, hello, I am gray little ape, a super bug writing program ape!

In the last article, I briefly introduced the use of EasyExcel technology to achieve Excel file writing operations with and without objects.

So today this article, I will continue to talk to you, using easyExcel technology how to read data in Excel?

The advantage of easyexcel

Apache POI, JXL, etc., are well-known frameworks for parsing and generating Excel in the Java field. However, when using them, they all have a serious problem, that is, they consume a lot of memory. If your system has a small amount of concurrency, it may be ok. However, once the concurrency comes up, it will be OOM or JVM frequent garbage collection.

EasyExcel is an Excel processing framework of Alibaba open source. It has the characteristics of simple use and memory saving. EasyExcel can greatly reduce the memory occupied by the main reason is that when parsing Excel, it does not load all the file data into the memory at one time, but reads the data from the disk row by row and analyzes it one by one. This feature will also show up when reading Excel data later.

Add easyExcel dependencies

When using EasyExcel, you need to import the corresponding dependency files into poM.

<! Poi </groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <! --> <dependency> <groupId>org.apache.poi</groupId> <artifactId> Poi-ooxml </artifactId> <version>3.17</version>  </dependency> <! Alibaba </groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version>  </dependency>Copy the code

Since EasyExcel is built on poI, importing dependencies also requires importing dependencies on excel versions 03 and 07.

Use EasyExcel to read data

When using easyExcel to read file data, need to set up a listener, through the implementation of the listener, you can achieve a single line of data read operation, let’s take the following data object as an example:

/** * demodata */ @data public class Demodata {@excelProperty (value = "String title ") private String stringTitle; @excelProperty (value = "dateTitle ") private Date dateTitle; @columnWidth (50) @excelProperty (value = "value ") private int doubleTitle; }Copy the code

The data read is as follows:

Implementation of a listener

When reading Excel data, we need to implement AnalysisEventListener, which needs to pass in the corresponding data type. In this listening interface, the main method used is: \

  • Invoke: Read line by line, and this method is called each time a row of data is read.
  • InvokeHeadMap: Reads header data,
  • doAfterAllAnalysed: called after all data is read. It is generally used to process the last read data.
  • OnException: This interface is called in the case of converting an exception and getting another exception, stops reading if an exception is thrown, and continues reading if no exception is thrown

The implementation of the interface is as follows:

@slf4j public class DemoDataListener extends AnalysisEventListener<DemoData> {/** * define a storage boundary. */ private static final int BATCH_COUNT = 5; private static final int BATCH_COUNT = 5; / / private List<DemoData> cacheDataList = new ArrayList<>(); / / Private List<DemoData> cacheDataList = new ArrayList<>(); Private DemoDao DemoDao; Public DemoDataListener() {this.demodao = new demoDao (); } /** * has parameter constructs, if Autowired is not available directly, @param demoDao */ public DemoDataListener(demoDao demoDao) {this.demoDao = demoDao; } @override public void invoke(demoData, demoData); {log.info(" Read data: "+ demoData); cacheDataList.add(demoData); /** * if (cacheDataList. Size () == BATCH_COUNT) {// saveData to database saveData(); CacheDataList. Clear (); }} @override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {log.info(" " + headMap); } @override public void doAfterAllAnalysed(analysisContext) { Log.info (" read finished ~~~"); // Save the last data to the database saveData(); cacheDataList.clear(); } /** * this interface is called when an exception is thrown and the reading stops. If no exception is thrown, the reading continues. * @param exception * @param context * @throws Exception */ @Override public void onException(Exception exception, AnalysisContext context) throws Exception {log.info(" Parsing failed, but parsing continues on the next line: {}",exception.getMessage()); if (exception instanceof ExcelDataConvertException){ ExcelDataConvertException e = (ExcelDataConvertException) exception; Log.info (" abnormal data resolution, row :{}, column :{}, data :{}", LLDB etRowIndex(), LLDB etColumnIndex(), LLDB etCellData()); Private void saveData() {log.info(" will save [" + cacheDataList. Size () + "] data "); demoDao.saveDataforList(cacheDataList); Log.info (" [" + cacheDataList. Size () + "]) ); }}Copy the code

After the implementation of the interface, the method of data reading is actually very simple, just need a line of code can be,

Read as follows:

Public void readAllSheetDataForExcel() {/** * EasyExcel. Read (FILEPATH +) "testExcel_1.xlsx", DemoData.class, new DemoDataListener()).sheet().doRead(); }Copy the code

The read result is as follows:

The above is the operation of reading data using easyExcel technology. I will continue to share with you the operation of reading and writing complex data.

Feel good, praise attention to small ape ah! I’m Grey Ape, and I’ll see you next time!