This is the fifth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

In the management background system, there is basically a function: data export. General data export is to export system data to Excel files and give them to operation and financial personnel, which is more convenient for them to analyze data and check accounts. The amount of exported data may be very large compared with that of paging viewing data in the system. Therefore, exporting data is sometimes a time-consuming operation. If the data is exported synchronously, users will wait for a response in the browser for a period of time, or even the response times out and no data is returned. This problem can be solved by using asynchronous export.

Asynchronous export means that a file is generated asynchronously during data export. The system provides a download page for users to download the file when the data export is complete.

Basic Design idea

  1. Filter data by criteria
  2. Write the data to Excel format files
  3. Upload Excel files to the file server and generate file records
  4. Provides export records for users to download uploaded files

Table data design

CREATE TABLE 'export_log' (' id' bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 iD ', 'file_sn' bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'filerecord sn', 'user_sn' bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'user sn', 'file_type' tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT 'file type ', 'file_name' varchar(50) NOT NULL DEFAULT COMMENT 'file name ',' status' tinyInt (2) NOT NULL DEFAULT '0' COMMENT 'File status ', Varchar (255) NOT NULL DEFAULT COMMENT '表 示 ', 'file_url' varchar(255) NOT NULL DEFAULT ', 'platform' varchar(20) NOT NULL DEFAULT COMMENT ', 'start_time' datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT 'Start time ', 'end_time' datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT 'End time ', 'create_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time ', 'update_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'UPDATE time ', PRIMARY KEY (' id '), UNIQUE KEY 'uniq_file_sn' (' file_sn ') USING BTREE COMMENT ' KEY 'idx_start_time' (' start_time ') USING BTREE COMMENT 'start time ', KEY 'idx_user_sn' (' user_sn ') USING BTREE COMMENT 'user sn') ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET= UTf8MB4 COMMENT=' update ';Copy the code

Java parses to generate Excel utility classes

Apache POI

Apache POI is a free open source cross-platform Java API written in Java. Apache POI provides API for Java programs to read and write Files in Microsoft Office format. Official document: poi.apache.org/

easyexcel

Easyexcel rewrite POI 07 version of Excel parsing, a 3M Excel with POI SAX parsing still needs about 100M memory, switch to EasyExcel can be reduced to a few M, and no matter how big Excel memory overflow; Version 03 relies on POI’S SAX mode, and makes the encapsulation of model transformation in the upper layer, which makes the user more simple and convenient. (From official document)

Official documentation: github.com/alibaba/eas…

easypoi

Easypoi function as the name easy, the main function is easy, so that a person who has not seen contact with POI can easily write Excel export,Excel template export,Excel import,Word template export, through simple annotations and templates Language (familiar expression syntax), complete with previously complex writing (from official documentation)

Official documentation: easyPoi.mydoc. IO /