Project introduction

IExcel is used to elegantly read and write Excel.

Avoid big Excel in OOM, simple but not simple.

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.

Change log

Change log

V0.0.4 major changes

  • Introduce the ExcelBs bootstrap class to optimize the user experience.

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

Environmental requirements

Jdk1.7 +

maven 3.x

The introduction of the jar

Manage using Maven.

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

Excel to write

The sample

/** * writes the contents of the list directly to an Excel file */
public void writeTest(a) {
    // Path to the Excel file to be generated
    final String filePath = PathUtil.getAppTestResourcesPath()+"/excelWriter03.xls";

    // List of objects
    List<User> models = User.buildUserList();

    // Write directly to the file
    ExcelBs.newInstance(filePath).write(models);
}
Copy the code

Among them:

  • User.java
public class User {

    private String name;

    private int age;

    //fluent getter/setter/toString()
}
Copy the code
  • buildUserList()

Build the object list as follows:

/** * Build the user class table *@returnUser List@since0.0.4 * /
public static List<User> buildUserList(a) {
    List<User> users = new ArrayList<>();
    users.add(new User().name("hello").age(20));
    users.add(new User().name("excel").age(19));
    return users;
}
Copy the code

Write to effect

Excel content generation into:

name	age
hello	20
excel	19
Copy the code

Excel to read

The sample

/** * Read all information in excel file */
public void readTest(a) {
    // Path to the Excel file to be generated
    final String filePath = PathUtil.getAppTestResourcesPath()+"/excelWriter03.xls";
    List<User> userList = ExcelBs.newInstance(filePath).read(User.class);
    System.out.println(userList);
}
Copy the code

information

[User{name='hello', age=20}, User{name='excel', age=19}]
Copy the code

# ExcelBs profile

Compared with static method, fluent object tool is more convenient for later expansion.

Common default properties and flexible API interfaces are provided for user convenience.

Use profile

ExcelBs.newInstance("Excel file path")
Copy the code

You can use the above method to create. The system automatically selects 03 Excel or 07 Excel according to the file suffix for reading and writing.

The configuration properties

Attributes that

Attribute values type The default value instructions
path string NA This is specified by default when ExcelBs is created, and can be specified again through the path() method.
bigExcelMode Boolean false Large Excel mode. If large data is written or read, you are advised to set this parameter to true

Set up the

Fluent Mode setting

  • Set an example
ExcelBs.newInstance("Excel file path").bigExcelMode(true)
Copy the code

Method statement

Methods the overview

methods parameter The return value instructions
append(Collection<? >) The object list ExcelBs Writes the list to a buffer, but not to a file
write() There is no void Writes an object in a buffer to a file
write(Collection<? >) There is no void Writes the buffer object to a file, and writes the list to a file
read(Class) The type of the read object The object list
read(Class, startIndex, endIndex) Object type, start subscript, end subscript The object list

write

One-time write

The most common way is to write directly.

ExcelBs.newInstance("Excel file path").write(Collection<? >)Copy the code

Many times to write

Sometimes we build a list of objects multiple times, such as paging from a database.

You can use the following methods:

ExcelBs.newInstance("Excel file path").append(Collection<? >) .append(Collection<? >).write()Copy the code

Read the file

Read all

ExcelBs.newInstance("Excel file path").read(Class<T>);
Copy the code

Reads the specified subscript

The subscripts here start at 0 and represent the first line of data, excluding the header line.

ExcelBs.newInstance("Excel file path").read(Class<T>, 1.1);
Copy the code

@ExcelFieldIntroduction to the

Sometimes we need to specify field attributes flexibly, such as the corresponding Excel header field name.

Like whether to read or write the line.

The @excelfield annotation is designed for this purpose.

Note that

public @interface ExcelField {

    /** * Excel header field name * If not passed: the current field name is used by default@returnField name */
    String headName(a) default "";

    /** * Excel file whether to write this field **@returnWhether to write this field */
    boolean writeRequire(a) default true;

    /** * Excel file to read this field *@returnWhether to read this field */
    boolean readRequire(a) default true;

}
Copy the code

Using the example

public class UserField {

    @ExcelField(headName = "Name")
    private String name;

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

}
Copy the code

The resulting Excel header is the Chinese we specify.