Product demand

The product manager needs to export all the information for a page to an EXCEL file.

Demand analysis

Excel export is a very common requirement.

The most common solution is to use poI to export an Excel file directly.

Customer Experience & Service Performance

• Customer experience

If the exported file is large, such as hundreds of thousands of data pieces, the page will be blocked and users cannot perform other operations.

• Service performance

When exporting, the main thread is blocked because the task is time-consuming.

If the exported service is exposed externally (front-end and back-end separation), this massive data transfer can be a performance drain.

The solution

The request is exported using exception handling, and the backend MQ notifies itself to process it.

After MQ consumption, multithreading processes excel file export, generates files and uploads them to FTP and other file servers.

The front end directly queries and displays the task execution list. You can download files from a file server such as FTP.

EXCEL export issues to consider

OOM

Normal POI will run out of memory when processing large Excel files.

There are also more online solutions.

For example, the official SXSSF (Since POI 3.8 beta3) solution.

Or use a packaged package

1. easypoi ExcelBatchExportServer

2. hutool BigExcelWriter

The principle is to force the XSSF version of Excel.

You can also use EasyExcel, although the annotations are somewhat lacking and complex in design, which is not recommended.

I’m using Hutool BigExcelWriter here, I’m too lazy to write it again myself.

FULL GC

It is not desirable to query 100W databases at a time and then load all of this information into memory.

There are two suggestions:

1. Limit the number of pages per session. For example, you can query a maximum of 1W entries at a time. Divided into 100 queries. (must)

2. Limit the total number of queries. For example, the maximum limit is 10W. (Select based on actual conditions)

Although the user offered to export all the information for a period of three months, it was too much to be meaningful. (The proposer himself may not realize it)

Try to avoid full-GC because all the current methods take up memory for Excel output streams, and 100W can easily lead to full-GC.

Database stress

Go to the database to read the time must remember paging, lest give the database too much pressure.

Reading too much at once can also cause memory to skyrocket.

For example, 100W pieces of data are divided into 100 times to read from the database.

Network transmission

In traditional Excel exports, a request is front-end and HTTP synchronization is returned directly. Export 100W and just sit there and wait.

This customer experience is not friendly, and network transmission, the system occupies a variety of issues.

You are advised to upload files to the file server in asynchronous mode. The front end goes directly to the file server to read.

The ease of programming

For the tools mentioned above, such as Hutool, there is no easy uniformity in how headers are handled.

You can define annotations like easyPOi/EasyExcel and reflect them yourself.

Then unified processing table header can be.

IExcel is a convenient and elegant Excel framework

features

• OO way to operate Excel, programming more convenient and elegant.

• Sax mode reads, SXSS mode writes. Avoid the Excel large file OOM.

• Based on annotations, programming is more flexible.

• Writing can be based on either a list of objects or a Map, making it easier to use.

• Simple design, complete annotation. It is convenient for everyone to learn and transform.

The late features

• Read skips blank lines

• Excel style related annotation development

results

In actual work and study, Apache POI is too complicated to operate Excel.

I’ve also looked at some other tool frameworks recently:

• easypoi

• easyexcel

• hutool-poi

Are more or less difficult to meet their actual needs, so I wrote a tool to operate Excel export.

Quick start

The introduction of the Jar

Manage using Maven.

<dependency>
     <groupId>com.github.houbb</groupId>
     <artifactId>iexcel</artifactId>
     <version>hundreds</version>
</dependency>
Copy the code

Define the object

You can refer directly to Excelutiltest.java

Define an Excel object that needs to be written/read.

• ExcelFieldModel.java

Only properties that are declared @excelfield will be processed. The instructions are @excelfield

public class ExcelFieldModel {

    @ExcelField
    private String name;

    @ExcelField(headName = "Age")
    private String age;

    @ExcelField(mapKey = "EMAIL", writeRequire = false, readRequire = false)
    private String email;

    @ExcelField(mapKey = "ADDRESS", headName = "Address", writeRequire = true)
    private String address;
    
    //getter and setter
}
Copy the code

Write examples

The realization of the IExcelWriter

IExcelWriter has several implementation classes that you can create either directly with new or with the ExcelUtil class.

IExcelWriter implementation class	How is ExcelUtil created	instructions
HSSFExcelWriter	ExcelUtil.get03ExcelWriter()	Excel 2003
XSSFExcelWriter	ExcelUtil.get07ExcelWriter()	2007 version of Excel
SXSSFExcelWriter	ExcelUtil.getBigExcelWriter()	Large file excel, avoid OOM

IExcelWriter Interface description

Write to 2003

• excelWriter03Test()

An example of writing a list of objects to a 2003 Excel file.

/** * write to 03 excel file */
@Test
public void excelWriter03Test(a) {
    // Path to the Excel file to be generated
    final String filePath = "excelWriter03.xls";

    // List of objects
    List<ExcelFieldModel> models = buildModelList();

    try(IExcelWriter excelWriter = ExcelUtil.get03ExcelWriter();
        OutputStream outputStream = new FileOutputStream(filePath)) {
        // The list can be written multiple times as required
        excelWriter.write(models);

        // Output the list to an Excel file
        excelWriter.flush(outputStream);
    } catch (IOException e) {
        throw newExcelRuntimeException(e); }}Copy the code

• buildModelList()

/** * Build test object list *@returnObject list */
private List<ExcelFieldModel> buildModelList(a) {
    List<ExcelFieldModel> models = new ArrayList<>();
    ExcelFieldModel model = new ExcelFieldModel();
    model.setName("Test Number one");
    model.setAge("25");
    model.setEmail("[email protected]");
    model.setAddress("23 Baker Street.");

    ExcelFieldModel modelTwo = new ExcelFieldModel();
    modelTwo.setName("Test Number two.");
    modelTwo.setAge("30");
    modelTwo.setEmail("[email protected]");
    modelTwo.setAddress("26 Baker Street.");

    models.add(model);
    models.add(modelTwo);
    return models;
}
Copy the code

Write once to 2007 Excel

Sometimes it is common to write lists only once, so they are simply encapsulated:

/** * write the list only once * is a simple encapsulation of the original method */
@Test
public void onceWriterAndFlush07Test(a) {
    // Path to the Excel file to be generated
    final String filePath = "onceWriterAndFlush07.xlsx";

    // List of objects
    List<ExcelFieldModel> models = buildModelList();

    // The corresponding Excel write object
    IExcelWriter excelWriter = ExcelUtil.get07ExcelWriter();

    // Write the list only once
    ExcelUtil.onceWriteAndFlush(excelWriter, models, filePath);
}
Copy the code

Read the example

Excel reads and determines the version of Excel based on the file name.

The realization of the IExcelReader

IExcelReader has several implementation classes that you can create either directly with new or with the ExcelUtil class.

IExcelReader implementation class	How is ExcelUtil created	instructions
ExcelReader	ExcelUtil.getExcelReader()	Small file excel reading implementation
Sax03ExcelReader	ExcelUtil.getBigExcelReader()	Large file 2003 Excel reading implementation
Sax07ExcelReader	ExcelUtil.getBigExcelReader()	2007 Excel reading implementation of large files

Description of the IExcelReader interface

Excel reading examples

/** * Read test */
@Test
public void readWriterTest(a) {
    File file = new File("excelWriter03.xls");
    IExcelReader<ExcelFieldModel> excelReader = ExcelUtil.getExcelReader(file);
    List<ExcelFieldModel> models = excelReader.readAll(ExcelFieldModel.class);
    System.out.println(models);
}
Copy the code

ExcelField notes

The attributes of @excelfield are described as follows:

attribute	type	The default value	instructions
mapKey	String	""	The value of map.key is mapped to the bean only when the generated input parameter is map. If not, the current field name is used by default
headName	String	""	Excel header field name. If not: The current field name is used by default
writeRequire	boolean	true	Whether excel files need to write this field
readRequire	boolean	true	Whether the Excel file reads this field

IExcelWriter Interface description

<br> * <p> * Data supports the following types of elements: * <pre> * 1. Bean, that is, the element is a Bean, the first Bean's list of field names will be the first row, the rest of the Bean's list of field values, data represents multiple rows <br> * </pre> *@paramThe data data *@return this
 */
IExcelWriter write(Collection
        data);

<br> convert map to list of objects according to targetClass * Direct mybatis mapper query out of the map results, or other construction results. *@paramMapList Map collection *@paramTargetClass Target type *@return this
 */
IExcelWriter write(Collection<Map<String, Object>> mapList, finalClass<? > targetClass);

/** * Flush Excel Workbook to output stream **@paramOutputStream outputStream *@return this
 */
IExcelWriter flush(OutputStream outputStream);
Copy the code

Specify the sheet

When creating an IExcelWriter, you can specify the subscript or name of the sheet. To specify the sheet to write.

Whether the table header is included

After creating an IExcelWriter, you can call excelWriter.containshead (bool) to specify whether to generate the Excel header.

Description of the IExcelReader interface

/** * Read all information about the current sheet *@paramTClass specifies the Javabean type *@returnObject list */
List<T> readAll(Class<T> tClass);

/** * reads * in the specified range@paramTClass generic *@paramStartIndex Specifies the starting line (starting from 0) *@paramEndIndex Indicates the end line *@returnList of objects read */
List<T> read(Class<T> tClass, final int startIndex, final int endIndex);
Copy the code

Specify the sheet

When creating the excel Reader, you can specify the subscript or name of the sheet. To specify the sheet to read.

Note: In large file SAX reading mode, only subscripts of sheet are supported.

Whether the table header is included

Excelreader.containshead (bool) specifies whether to read the Excel header.

Develop reading

Excel exports best practices

Iexcel framework