Author: roast chicken Prince

brief introduction

Friends who have done analysis of excel requirements on the server side know that Apache poi and jxl are well-known frameworks for Java parsing and generating excel, but these frameworks actually have a serious problem that they consume a lot of memory. POI has API s that provide Sax mode, which can solve some memory overflow problems to a certain extent, but POI still has some defects, For example, the decompression and post decompression storage of version 07 excel are completed in memory, and the memory consumption is still very large. Easyexcel rewrites POI's parsing of 07 version excel. A 3M excel still needs about 100M memory to be parsed with POI sax. Using easyexcel can be reduced to a few meters, and no matter how large the excel is, there will be no memory overflow; Version 03 relies on the Sax mode of POI and encapsulates the model transformation on the upper layer, making it easier and convenient for users.

The framework to be introduced today is EasyExcel, an open source excel processing framework of Alibaba, which is famous for its simplicity and memory saving.

performance

The main reason why EasyExcel can greatly reduce the memory consumption is that it does not load all the file data into the memory at one time when parsing Excel, but reads the data line by line from the disk and parses it one by one.

EasyExcel adopts the line by line parsing mode, and notifies the processing of the parsing results of one line in the mode of observer (analysis event listener)

According to the official website, his version above 3.0.2 reads 75M(46W rows and 25 columns) Excel in 64M memory for 20 seconds. The speed mode can be faster, but the memory occupation will be a little more than 100M

Package introduction

Version Description

The latest version before the deadline is 3.0.5. The 2 + version supports Java 7 and Java 6, and the + version at least supports Java 8

• It is not recommended to upgrade across major versions, especially across two major versions

• 2 + upgrade to 3 + some incompatibilities

  • Using a custom interceptor to modify the style will cause problems (no compilation error)
  • When reading, an exception is thrown in invoke, and an extra layer of ExcelAnalysisException will not be encapsulated (no compilation error will be reported)
  • Style and other annotations involve changes in boolean or some enumeration values, and add default values (errors will be reported in compilation, and the annotation will be changed)
• After the large version is upgraded, it is recommended to retest the relevant contents

maven introduction

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.0.5</version>
    </dependency>

Common class parsing

• EasyExcel entry class is used to build and start various operations
• ExcelReaderBuilder ExcelWriterBuilder builds a ReadWorkbook WriteWorkbook, which can be understood as an excel object. An excel only needs to build one
• ExcelReaderSheetBuilder ExcelWriterSheetBuilder constructs a ReadSheet WriteSheet object, which can be understood as a page in excel, and one for each page
• ReadListener will call ReadListener to process data after reading each row
• WriteHandler will call WriteHandler to process data in every operation, including creating cells and tables
• All configurations are inherited, and the configuration of Workbook will be inherited by sheet, so when setting parameters with EasyExcel, in EasyExcel Before the sheet () method, the scope is the whole sheet, and then for a single sheet

excel operation example

I have a company information on hand xlsx, which contains ID, company name, establishment time, industry involved and five columns of information on the official website, let's read this excel form (if some information is involved, please allow me to code)

First, analyze the results

Paste the code below

• Implement basic information class

    @Data
    public class CompanyInfoData {
        //Set header name
        @ExcelProperty("ID")
        private String id;
  
        //Set header name
        @ExcelProperty("corporate name")
        private String company_name;
  
        //Set header name
        @ExcelProperty("Establishment time")
        private String create_time;
  
        //Set header name
        @ExcelProperty("Get involved in industry")
        private String in_industry;
  
        //Set header name
        @ExcelProperty("Official website")
        private String official_website;
    }

• Create listening class

    public class ExcelListener extends AnalysisEventListener<CompanyInfoData{
        //Create a list collection to encapsulate the final data
        List<CompanyInfoDatalist = new ArrayList<CompanyInfoData>();
  
        //Read the excle content line by line
        @Override
        public void invoke(CompanyInfoData info, AnalysisContext analysisContext) {
            System.out.println("Table content"+info);
            list.add(info);
        }
  
        //Reading excel header information
        @Override
        public void invokeHeadMap(Map<Integer, StringheadMap, AnalysisContext context) {
            System.out.println("Header information:"+headMap);
        }
  
        //Execute after reading
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        }
    }   

• Read the file to implement public class TestEasyExcel{

    public static void main(String[] args) {
  
            String fileName = "E:\\workspace\\flywaydemo\\src\\main\\resources\\Company information.xlsx";
            // Here, you need to specify which class to use to read, and then read the first sheet file. The stream will close automatically
            EasyExcel.read(fileName, CompanyInfoData.class, new ExcelListener()).sheet().doRead();
        }
    }

summary

Easyexcel's memory optimization has certain advantages over other excel operation frameworks. In addition to ordinary reading and writing of Excel, easyexcel's support for Excel's web upload and download is relatively friendly. Students in need can go to the official website for details. Official website address: https://www.yuque.com/easyexc...