link

  • GitHub
  • Complete User manual
  • blog

AutoExcel V2.0.0 brings the following new features

  1. Supports import and export of millions of seconds of data
  2. Override import method

Maven

<dependency>
  <groupId>net.fenghaitao</groupId>
  <artifactId>auto-excel</artifactId>
  <version>2.0.0</version>
</dependency>
Copy the code

Supports import and export of millions of seconds of data

Compared with EasyExcel of babar (version 2.2.6), the following results were measured in milliseconds under the same environment

10W rows and 10 columns of data 100W rows and 10 columns of data
AutoExcel (template export) 6258 23540
EasyExcel (Template Export) 7358 28881
AutoExcel (direct export) 5711 24952
EasyExcel (Direct export) 5775 27118
AutoExcel (import) 4466 21595
AutoExcel (Import + Transform) 4823 26279
EasyExcel (Import) 5966 30844

It can be seen that AutoExcel and EasyExcel have little difference in import and export efficiency, because the export of both are based on SXSSF, import is based on SAX, but AutoExcel’s packaging of POI is lighter, so the efficiency will be slightly higher, the greater the amount of data is more obvious.

In reality, of course, AutoExcel is not focused on import and export efficiency, but rather on the development experience, providing automation while allowing developers to write as little code as possible. For example, automatic application of cell styles, automatic filling of line numbers, automatic filling formulas, automatic summarizing, automatic column width, etc., you can go to the AutoExcel user manual (V2.0.0) for details.

Override import method

The template-based import approach in V1.0.0 has been deprecated and FieldSetting is used to specify the mapping between column and field names

public void importExcel(a) {
    List<ImportPara> importParas = new ArrayList<ImportPara>() {{
        add(new ImportPara(0, DataGenerator.genProductFieldSettings()));
        add(new ImportPara(1, DataGenerator.genProjectFieldSettings(), 1.5));
    }};
    String fileName = this.getClass().getResource("/template/Import.xlsx").getPath();
    DataSet dataSet = AutoExcel.read(fileName, importParas);
    // Get the data directly, no type conversion, you can check whether the data meets the requirements in this way
    List<Map<String, Object>> products = dataSet.get("Product");
    List<Map<String, Object>> projects = dataSet.get("Project");
    Method 2: Obtain the data of the specified class through the sheet index. The type is automatically converted. If the conversion fails, an exception will be thrown
    // List<Product> products = dataSet.get(0, Product.class);
    // List<Project> projects= dataSet.get(1, Project.class);
    // Method 3. Obtain the data of the specified class from the sheet name. The type is automatically converted
    // List<Product> products = dataSet.get("Product", Product.class);
    // List<Project> projects = dataSet.get("Project", Project.class);
}
Copy the code
public static List<FieldSetting> genProjectFieldSettings(a) {
    List<FieldSetting> fieldSettings = new ArrayList<>();
    fieldSettings.add(new FieldSetting("projName"."Project Name"));
    fieldSettings.add(new FieldSetting("projInfo"."Project Info."));
    fieldSettings.add(new FieldSetting("basalArea"."Basal Area"));
    fieldSettings.add(new FieldSetting("availableArea"."Available Area"));
    fieldSettings.add(new FieldSetting("buildingArea"."Building Area"));
    fieldSettings.add(new FieldSetting("buildingsNumber"."Buildings Number"));
    fieldSettings.add(new FieldSetting("saleStartDate"."Sales Start Date"));
    fieldSettings.add(new FieldSetting("landAcquisitionTime"."Land Acquisition Time"));
    fieldSettings.add(new FieldSetting("availablePrice"."Available Price"));
    fieldSettings.add(new FieldSetting("availableAmount"."Available Amount"));
    fieldSettings.add(new FieldSetting("insideArea"."Inside Area"));
    return fieldSettings;
}

public static List<FieldSetting> genProductFieldSettings(a) {
    List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
        add(new FieldSetting("projName"."Project Name"));
        add(new FieldSetting("basalArea"."Basal Area"));
        add(new FieldSetting("availableArea"."Available Area"));
        add(new FieldSetting("buildingArea"."Building Area"));
        add(new FieldSetting("buildingsNumber"."Buildings Number"));
    }};
    return fieldSettings;
}
Copy the code

ImportPara constructor parameter:

  1. SheetIndex: required, sheet index
  2. FieldSettings: Mandatory, mapping between column and field names
  3. TitleIndex: can be omitted, title line start index, default 0
  4. DataStartIndex: can be omitted. The default value is 1

Why not annotate column names with FieldSetting?

  1. Non-invasive, does not affect the original code
  2. In the system design, in order to reuse the same configuration, such as page display, export, import, print display the same column name, we will store these configurations in storage media such as database, to be used to load out, this way can also avoid hard coding, but also convenient dynamic configuration. FieldSetting is designed to complement this approach. AutoExcel tries to integrate imports and exports into your automation system.