Use of Easypoi package

preface

Recently, my boss asked me to implement an import and export function. Because this project is a very old maintenance project, the JExcel API package is still used for the import and export of excel. Although I have encapsulated the tool class, I still feel very troublesome ~, especially the title, which keeps add ing (vegetable chicken me)

Recently, after reading some technical blogs, I happened to see the Easy-to-use packages for file import and export, EasyPoi and EasyExcel. Well, look at the name. Let's see how Easy it is to use these two~~

First, let's talk about the difference between the two packages:

• EasyPoi takes up a lot of memory and its performance is not good enough (see who compares with it). When learning this, I saw many people on the official website commented on the corresponding problems, but I still think it is easier to use than JExcel API~~
• EasyExcel is an open source product from Alibaba and guaranteed by large manufacturers. It should have no performance to say (dog head protects life). It has the characteristics of fast processing, small memory occupation and convenient use. It has 22k+Star on Github, which is very popular.

This article mainly explains the knowledge I learned about the Easypoi package. In the next blog arrangement of EasyExcel, I'll play first~~

Well, back to the point, let's see what Easypoi is and how to play it~

Mandarin: the easypoi function is like the name easy. The main function is easy, so that a person who has not seen and contacted the poi can easily write Excel export, Excel template export, Excel import and Word template export, and complete the previous complex writing through simple annotation and template language (familiar expression syntax).

Human words: if you can use this package with @ annotation, it will be imported and exported. Is it especially easy.

characteristic:

• Exquisite design, annotation and calling some tool classes can be used, which is simple to use
• It provides many interfaces, and the extension is very simple
• There are a lot of default values. In this case, I'm not afraid to tell the truth~
• Support spring MVC and spring boot

function

Excel adapts to xls and xlsx formats, and word only supports docx mode

• Excel import

  • Annotation import

  • Map import

  • Large amount of data import sax mode

  • Import file save

  • File verification

  • Field verification

• Excel export

  • Annotation export
  • Template export
  • html export

• Excel to html

• Word export

• PDF export

The official lists so many functions. Is there such a powerful one? Let's play a little~

use

For such third-party packages, there are three steps to use: package guide, official website example reference use, and verification. I am interested in exploring the specific principles. I will talk about them when I have time to learn~

1. Guide Package

In project POM Import the package from the XML folder.

There are two import methods: SpringBoot project and SpringMvc project. If you simply use the excel import and export function, this package is enough.

The source code designed below is also for version 4.3.0. Other versions can see the corresponding source code~

// This package contains the following packages
<dependency>
     	<groupId>cn.afterturn</groupId>
     	<artifactId>easypoi-spring-boot-starter</artifactId>
     	<version>4.3.0</version>
</dependency>

// Import in SpringMvc project
<dependency>
		<groupId>cn.afterturn</groupId>
		<artifactId>easypoi-base</artifactId> //Import and export toolkit can complete Excel export, import, Word export and Excel export functions
		<version>4.3.0</version>
</dependency>
<dependency>
		<groupId>cn.afterturn</groupId>
		<artifactId>easypoi-web</artifactId> //Spring MVC is coupled, which greatly simplifies the export function under spring MVC based on AbstractView
		<version>4.3.0</version>
</dependency>
<dependency>
		<groupId>cn.afterturn</groupId>
		<artifactId>easypoi-annotation</artifactId> //The basic annotation package is used on entity objects to facilitate maven's multi project dependency management after splitting
		<version>4.3.0</version>
</dependency>
		

If you need verification, PDF export, Word export and sax, add them according to your needs~

			<!-- sax Used when reading -->
			<dependency>
				<groupId>xerces</groupId>
				<artifactId>xercesImpl</artifactId>
				<version>${xerces.version}</version>
				<optional>true</optional>
			</dependency>
			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi-scratchpad</artifactId>
				<version>${poi.version}</version>
				<optional>true</optional>
			</dependency>
			
			<!-- Word Need to use -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.3</version>
                <optional>true</optional>
            </dependency>
			
			<!-- check,The following two implementations -->
			<dependency>
				<groupId>org.hibernate</groupId>
				<artifactId>hibernate-validator</artifactId>
				<version>5.1.3.Final</version>
				<optional>true</optional>
			</dependency>
			
			<dependency>
				<groupId>org.apache.bval</groupId>
				<artifactId>org.apache.bval.bundle</artifactId>
				<version>1.1.0</version>
			</dependency>
			
			<!-- PDF -->
			<dependency>
				<groupId>com.itextpdf</groupId>
				<artifactId>itextpdf</artifactId>
				<version>5.5.6</version>
				<optional>true</optional>
			</dependency>

			<dependency>
				<groupId>com.itextpdf</groupId>
				<artifactId>itext-asian</artifactId>
				<version>5.2.0</version>
				<optional>true</optional>
			</dependency>

2. Annotation description

In fact, there are very few comments on this package. There are six, @ CelleStyle has nothing. I mainly introduce the other five:

annotation	Use location	describe
Excel	Applicable only on attribute FIELD	A column in Excel
ExcelCollection	Applicable only on attribute FIELD	Represents a collection, mainly for one to many export
ExcelEntity	Applicable only on attribute FIELD	Represents an entity that continues to drill down to export Note that this Feild is an entity class, and each attribute in it should be exported
ExcelIgnore	Applicable only on attribute FIELD	Skip this Fileld when exporting
ExcelTarget	Applicable to class interface enumeration	Put it on the outermost layer Describe the id of this object so that an object can be processed differently for different exports

2.1 introduction to basic knowledge of annotation

// Indicates where annotations can be used
// This indicates that the annotation can be used to construct the method parameter type in the method attribute annotation 
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.ANNOTATION_TYPE, ElementType.CONSTRUCTOR, ElementType.PARAMETER, ElementType.TYPE_USE})
//Describes various strategies for retaining annotations, which, together with meta annotations (@ Retention), specify how long annotations are to be retained
@Retention(RetentionPolicy.RUNTIME)
//Indicates that this annotation is recorded by javadoc
@Documented
public @interface MyOwnAnnotation {
    
}

2.2 @Excel

@The excel annotation is generally marked on the file to indicate that the file is a column in Excel, which is generally realized by it. The corresponding fields and their explanations are as follows (after a look, there are still many functions that can be realized)

Annotation source code:

package cn.afterturn.easypoi.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Excel {
    // Column name, support name_id, must be given
    String name();
    
    // Export time settings. If the field is of Date type, it does not need to be set 
    // If the database is of string type, you need to set the database format to convert the time format output
    // Default yyyyMMddHHmmss
    String databaseFormat() default "yyyyMMddHHmmss";

    // Exported time format
    // If this value is empty, there is no need to format the date
    // If it is not empty, you need to format the date
    String exportFormat() default "";
    
    // Imported time format
    // If this value is empty, there is no need to format the date
    // If it is not empty, you need to format the date
    String importFormat() default "";

    //The time format is equivalent to setting both exportFormat and importFormat
    String format() default "";
    
    // Sorting of columns
    String orderNum() default "0";

    // The replacement export of values is {a_1,b_2} import, and vice versa
    // When the value is 1, it is replaced with 1, and when the value is 2, it is replaced with b
    String[] replace() default {};
    
    // Number format, the parameter is Pattern, and the object used is DecimalFormat
    String numFormat() default "";
    
    
    // Text suffix, such as% 90 becomes 90%, add the value specified by suffix after the exported value
    String suffix() default "";
    
    // Cell width
    double width() default 10.0D;
    
    
    // Time zone, Zone ID of GMT zone. See Java util. Optional values in zoneid
    String timezone() default "";

    
    /** @deprecated */
    @Deprecated
    double height() default 10.0D;

    // Export type 
    // 1 read from file 2 read from database 
    // The default is 1. Reading from a file is the same as importing
    int imageType() default 1;

    
    // Whether line feed is supported \ n
    // Default support
    boolean isWrap() default true;

    // Whether to merge cells vertically (used to merge single cells in the list and multiple rows created by the list)
    // An array of row s whose values are merged cells
    int[] mergeRely() default {};
    
    // Whether to merge cells vertically (used to merge single cells in the list and multiple rows created by the list)
    // If you need to merge, you need to specify mergeRely
    boolean needMerge() default false;

    // Merge cells with the same content vertically
    // Default false do not merge
    boolean mergeVertical() default false;
    
    // The group name, which corresponds to the name of @ ExcelEntity, is used for different export processing
    String groupName() default "";

    // Dictionary name
    String dict() default "";
    
 	// Insert drop-down
    boolean addressList() default false;
	
    // Address to save the picture
    String savePath() default "/excel/upload/img";
    
	// The cell type, corresponding to the type, defaults to 1
    // STRING_TYPE = 1; string type
    // IMAGE_TYPE = 2;  Picture type
    // FUNCTION_TYPE = 3;  function
    // DATE_TYPE = 4;  date
    // DOUBLE_TYPE = 10; double precision
    int type() default 1;

    
    // Is it statistics
    boolean isStatistics() default false;

    // Is this a hyperlink? If it needs to implement the interface, return the object
    boolean isHyperlink() default false;
    
	// Import the verification field. The default value is false. No verification is required
    String isImportField() default "false";

    // Fixed column
    int fixedIndex() default -1;

    // Hide column
    boolean isColumnHidden() default false;

    // Enumerating export attribute fields
    String enumExportField() default "";
    
	// Enumeration import static methods
    String enumImportMethod() default "";
    
	// Desensitization rule
    String desensitizationRule() default "";
}

2.2 @ExcelCollection

@The ExcelCollection annotation is generally marked on the file to represent an entity that continues to be exported in depth, indicating that the feed is an entity class, and each attribute in it should be exported according to the @ Excel tag

Annotation source code:

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelCollection {
    // Column name, required
    String name();
    // If name is not specified, name is supported_ id (if you label ExcelTarget, this id can be its value)
    String id() default "";
    // Sort number
    String orderNum() default "0";
	// By default, this field is ArrayList, and the Collection type of this field can be specified
    Class<?> type() default ArrayList.class;
}

2.3 @ExcelEntity

@The ExcelEntity annotation is usually marked on the file

Annotation source code:

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelEntity {
    //The name of the column. Name is supported_ id (if you label ExcelTarget, this id can be its value)
    String id() default "";
	// The column name must be specified if show() is true
    String name() default "";
	// Whether to display this file. If it is true, name must be specified
    boolean show() default false;
}

2.4 @ExcelIgnore

@The ExcelIgnore annotation is usually marked on the file to indicate whether to ignore it. The ignored attribute can be marked @ ExcelIgnore

Annotation source code:

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelIgnore {
}

2.5 @ExcelTarget

@The ExcelTarget annotation is generally marked on the class interface enumeration and placed on the outermost layer to describe the id of the object, so that an object can be processed differently for different exports

Annotation source code:

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
public @interface ExcelTarget {
    // The name of @ Excel can be the value of which export is marked
    String value();
}

3. Tool class description

3.1 ExportParams

Export parameter is mainly used to define some information of Excel. This parameter is used as the export parameter for file export in ExcelExportUtil.

The fields and meanings involved in this class are as follows. Only a few constructors are provided:

• Set the title of the first row of the table, the sub title of the second row of the table, and the sheet name
• Set the title and sheet name of the first row of the table
• Set the title of the first row of the table, the name of the sheet, and the format of the exported file (default xls)

/**
 * Excel Export parameters
 */
@Data
public class ExportParams extends ExcelBaseParams {
    /**
     * title name in the first row of the table
     */
    private String  title;

    /**
     * title height of the first row of the table
     */
    private short   titleHeight = 10;

    /**
     * Sub title name in the first row of the table
     */
    private String   secondTitle;

    /**
     * Height of sub title in the first row of the table
     */
    private short    secondTitleHeight = 8;
    /**
     * sheetName
     */
    private String    sheetName;
    /**
     * Filtered properties
     */
    private String[]  exclusions;
    /**
     * Add index
     */
    private boolean   addIndex;
    /**
     * index name
     */
    private String    indexName         = "Serial number";
    /**
     * Frozen column
     */
    private int       freezeCol;
    /**
     * Header color & Title Color
     */
    private short     color             = HSSFColor.HSSFColorPredefined.WHITE.getIndex();
    /**
     * Second line Title Color
     * The color of the property description line, for example: hssfcolor SKY_ BLUE. Index default
     */
    private short     headerColor       = HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex();
    /**
     * Excel Export version
     */
    private ExcelType type              = ExcelType.HSSF;
    /**
     * Excel Export style
     */
    private Class<?>  style             = ExcelExportStylerDefaultImpl.class;

    /**
     * Meter height
     */
    private double  headerHeight     = 9D;
    /**
     * Create header
     */
    private boolean isCreateHeadRows = true;
    /**
     * Get data dynamically
     */
    private boolean isDynamicData    = false;
    /**
     * Append drawing
     */
    private boolean isAppendGraph    = true;
    /**
     * Fixed header
     */
    private boolean isFixedTitle     = true;
    /**
     * Maximum value of single sheet
     * 03 The version defaults to 6W line, and 07 defaults to 100W
     */
    private int     maxNum           = 0;

    /**
     * When exporting, the height unit of each column in excel is characters, and one Chinese character = 2 characters
     * Global settings, preferred
     */
    private short height = 0;

    /**
     * read-only
     */
    private boolean readonly = false;
    /**
     * Column width is adaptive. If width is not set, it is also adaptive
     */
    private boolean autoSize = false;
	// Constructor
    public ExportParams() {

    }
    public ExportParams(String title, String sheetName) {
        this.title = title;
        this.sheetName = sheetName;
    }
    public ExportParams(String title, String sheetName, ExcelType type) {
        this.title = title;
        this.sheetName = sheetName;
        this.type = type;
    }
    public ExportParams(String title, String secondTitle, String sheetName) {
        this.title = title;
        this.secondTitle = secondTitle;
        this.sheetName = sheetName;
    }
    public short getSecondTitleHeight() {
        return (short) (secondTitleHeight * 50);
    }
    public short getTitleHeight() {
        return (short) (titleHeight * 50);
    }
    public short getHeight() {
        return height == -1 ? -1 : (short) (height * 50);
    }
    public short getHeaderHeight() {
        return (short) (titleHeight * 50);
    }

}

3.2 ExcelExportUtil

Export tool classes. Construct the parameter ExportParams required for export, and export the specified excel file with the static method in this class

The source code is as follows, and the corresponding methods and parameters have the following meanings: (for details, see the following usage examples)

/**
 * excel Export tool class
 */
public final class ExcelExportUtil {

    public static       int    USE_SXSSF_LIMIT = 1000000;
    public static final String SHEET_NAME      = "sheetName";

    private ExcelExportUtil() {
    }
    /**
     * Large data export
     *
     * @param entity    Table Title Properties
     * @param pojoClass Excel Object Class
     */
    public static IWriter<Workbook> exportBigExcel(ExportParams entity, Class<?> pojoClass) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, pojoClass);
        return batchServer;
    }

    /**
     * Large data export
     *
     * @param entity Table Title Properties
     * @param excelParams cell Mapping class parameter list
     * @return
     */
    public static IWriter<Workbook> exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, excelParams);
        return batchServer;
    }

    /**
     * Large data export
     *
     * @param entity      Table Title Properties
     * @param pojoClass   Excel Object Class
     * @param server      Interface for querying data
     * @param queryParams Parameters of query data
     */
    public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                          IExcelExportServer server, Object queryParams) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, pojoClass);
        return batchServer.exportBigExcel(server, queryParams);
    }

    /**
     * Large data export
     * @param entity Table Title Properties
     * @param excelParams Table Title Properties
     * @param server      Interface for querying data
     * @param queryParams Parameters of query data
     * @return
     */
    public static Workbook exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,
                                          IExcelExportServer server, Object queryParams) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, excelParams);
        return batchServer.exportBigExcel(server, queryParams);
    }


    /**
     * @param entity    Table Title Properties
     * @param pojoClass Excel Object Class
     * @param dataSet   Excel Object data List
     */
    public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,
                                       Collection<?> dataSet) {
        Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
        new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
        return workbook;
    }

    private static Workbook getWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else {
            return new XSSFWorkbook();
        }
    }

    /**
     * Create corresponding Excel based on Map
     *
     * @param entity     Table Title Properties
     * @param entityList Map Object list
     * @param dataSet    Excel Object data List
     */
    public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList,
                                       Collection<?> dataSet) {
        Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
        ;
        new ExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet);
        return workbook;
    }

    /**
     * Create corresponding excel according to Map (one excel creates multiple sheets)
     *
     * @param list Multiple map key titles correspond to table Title key entity corresponds to table entity key data
     *             Collection data
     */
    public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
        Workbook workbook = getWorkbook(type, 0);
        for (Map<String, Object> map : list) {
            ExcelExportService service = new ExcelExportService();
            ExportParams params = (ExportParams) map.get("title");
            params.setType(type);
            service.createSheet(workbook,params,
                    (Class<?>) map.get("entity"), (Collection<?>) map.get("data"));
        }
        return workbook;
    }

    /**
     * The exported file is parsed through the template. This is not recommended. It is recommended that all processing be performed through the template
     * @param params    Export parameter class
     * @param pojoClass Corresponding entity
     * @param dataSet   Entity collection
     * @param map       Template collection
     * @return
     */
    @Deprecated
    public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass,
                                       Collection<?> dataSet, Map<String, Object> map) {
        return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, pojoClass, dataSet,
                map);
    }

    /**
     * The exported file is parsed through a template. There are only templates but no collections
     *
     * @param params Export parameter class
     * @param map    Template collection
     * @return
     */
    public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
        return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, null, null, map);
    }

    /**
     * The exported file is parsed through a template. There are only templates but no collections
     * Each sheet corresponds to a map, and the key is the NUM of the sheet
     *
     * @param params Export parameter class
     * @param map    Template collection
     * @return
     */
    public static Workbook exportExcel(Map<Integer, Map<String, Object>> map,
                                       TemplateExportParams params) {
        return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, map);
    }

    /**
     * The exported file is parsed through a template. There are only templates but no collections
     * Each sheet corresponds to a list, which is exported and sorted according to the quantity. The key is the NUM of the sheet
     *
     * @param params Export parameter class
     * @param map    Template collection
     * @return
     */
    public static Workbook exportExcelClone(Map<Integer, List<Map<String, Object>>> map,
                                            TemplateExportParams params) {
        return new ExcelExportOfTemplateUtil().createExcelCloneByTemplate(params, map);
    }

}

3.4 ImportParams

The imported parameter settings are mainly set according to the format of the contents in your file

/**
 * Import parameter settings
 */
@Data
public class ImportParams extends ExcelBaseParams {

    public static final String SAVE_URL = "/excel/upload/excelUpload";

    /**
     * Number of table title rows, default 0
     */
    private int                 titleRows        = 0;
    /**
     * Number of header rows, 1 by default
     */
    private int                 headRows         = 1;
    /**
     * The distance between the true value of the field and the column header is 0 by default
     */
    private int                 startRows        = 0;

    /**
     * How to set the primary key? If the cell has no value, skip or think it is the value below the list
     * We don't understand. Remove this
     */
    private Integer             keyIndex         = null;
    /**
     * The sheet position to start reading. The default value is 0
     */
    private int                 startSheetIndex  = 0;
    /**
     * The number of sheet s to be read when uploading a table. The default value is 1
     */
    private int                 sheetNum         = 1;
    /**
     * Whether to save the uploaded Excel. The default value is false
     */
    private boolean             needSave         = false;
    /**
     * Verification group
     */
    private Class[]             verifyGroup = null;
    /**
     * Whether to verify the uploaded Excel. The default value is false
     */
    private boolean             needVerify = false;
    /**
     * Returns whether to split the file. The default is split
     */
    private boolean             verifyFileSplit = true;
    /**
     * Verification processing interface
     */
    private IExcelVerifyHandler verifyHandler;
    /**
     * Save the uploaded Excel directory. By default, such as TestEntity, the save path is
     * upload/excelUpload/Test/yyyyMMddHHmss_***** Save name upload time_ Five digit random number
     */
    private String              saveUrl          = SAVE_URL;
    /**
     * Last invalid rows
     */
    private int                 lastOfInvalidRow = 0;
    /**
     * Manually control the number of rows read
     */
    private int                 readRows = 0;
    /**
     * Verify whether the data template is correct Excel when importing
     */
    private String[]            importFields;
    /**
     * Verify the title column order of excel when importing. Configuration order dependent on importFields
    */
    private boolean             needCheckOrder = false;
    /**
     * Key-Value Read the tag, take this as the Key, the next Cell as the Value, and multiple cells as ArrayList
     */
    private String              keyMark = ":";
    /**
     * Read the global scan Excel according to the key value rule, but skip the List reading range to improve performance
     * Only titleRows + headRows + startRows and lastOfInvalidRow are supported
     */
    private boolean             readSingleCell = false;
    /**
     * Parallel computing
     */
    private boolean             concurrentTask = false;
    /**
     * Minimum intercept size
     */
    private Integer             critical = 1000;
}

3.3 ExcelImportUtil

Import tool. Import the file of the incoming place, the exported entity class, and the corresponding import parameter.

/**
 * Excel Import tool
 */
@SuppressWarnings({ "unchecked" })
public class ExcelImportUtil {

    private ExcelImportUtil() {
    }

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);

    /**
     * Excel Import local data source files without returning verification results. Import field types Integer,Long,Double,Date,String,Boolean
     * 
     * @param file Imported files
     * @param pojoClass You need to import the data into the entity class
     * @param params Imported parameters
     * @return
     */
    public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            return new ExcelImportService().importExcelByIs(in, pojoClass, params, false).getList();
        } catch (ExcelImportException e) {
            throw new ExcelImportException(e.getType(), e);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelImportException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * Excel Import the data source IO stream without returning verification results. Import the field types Integer,Long,Double,Date,String,Boolean
     * 
     * @param inputstream File stream
     * @param pojoClass You need to import the data into the entity class
     * @param params Imported parameters
     */
    public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass,
                                          ImportParams params) throws Exception {
        return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, false).getList();
    }

    /**
     * Excel Import data source IO stream field type Integer,Long,Double,Date,String,Boolean
     * Support verification and key value
     * 
     * @param inputstream File stream
     * @param pojoClass You need to import the data into the entity class
     * @param params Imported parameters
     */
    public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream,
                                                             Class<?> pojoClass,
                                                             ImportParams params) throws Exception {
        return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, true);
    }

    /**
     * Excel Import data source local file field type Integer,Long,Double,Date,String,Boolean
     * Support verification and key value
      * @param inputstream File stream
     * @param pojoClass You need to import the data into the entity class
     * @param params Imported parameters
     */
    public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass,
                                                             ImportParams params) {
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            return new ExcelImportService().importExcelByIs(in, pojoClass, params, true);
        } catch (ExcelImportException e) {
            throw new ExcelImportException(e.getType(), e);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelImportException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * Excel Through SAX parsing method, it is suitable for big data import and does not support pictures
     * Import local data source files without returning verification results. Import field types Integer,Long,Double,Date,String,Boolean
     * 
      * @param inputstream File stream
     * @param pojoClass You need to import the data into the entity class
     * @param params Imported parameters
     * @param handler Interface custom processing class, which is used to resolve objects
     */
    public static void importExcelBySax(InputStream inputstream, Class<?> pojoClass,
                                        ImportParams params, IReadHandler handler) {
        new SaxReadExcel().readExcel(inputstream, pojoClass, params, handler);
    }

}

4 simple use

We mainly try the attributes of the above annotations to give a very intuitive feeling of how to use them

Here, I only test some attributes that I think may be frequently used, and some complex attributes. You need to record them here after you encounter them (subsequent updates)~

4.1 @Entity usage 1: export excel using simple attributes

Mainly use the following attributes:

• name , databaseFormat,format(exportFormat,importFormat),orderNum,replace,numFormat,suffix,width

Let's review what these attributes do:

    // Column name, support name_id, must be given
    String name();
    
    // Export time settings. If the field is of Date type, it does not need to be set 
    // If the database is of string type, you need to set the database format to convert the time format output
    // Default yyyyMMddHHmmss
    String databaseFormat() default "yyyyMMddHHmmss";

    // Exported time format
    // If this value is empty, there is no need to format the date
    // If it is not empty, you need to format the date
    String exportFormat() default "";
    
    // Imported time format
    // If this value is empty, there is no need to format the date
    // If it is not empty, you need to format the date
    String importFormat() default "";

    //The time format is equivalent to setting both exportFormat and importFormat
    String format() default "";
    
    // Sorting of columns
    String orderNum() default "0";

    // The replacement export of values is {a_1,b_2} import, and vice versa
    // When the value is 1, it is replaced with 1, and when the value is 2, it is replaced with b
    String[] replace() default {};
    
    // Number format, the parameter is Pattern, and the object used is DecimalFormat
    String numFormat() default "";
    
    // Text suffix, such as% 90 becomes 90%, add the value specified by suffix after the exported value
    String suffix() default "";

 	// Cell width, default is 10
    double width() default 10.0D;
    

(1) Define entity class

It mainly defines some fields for some attributes. When outputting the file, you can see whether the attribute is effective, so as to determine the use of the attribute

package com.study.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
@Builder
public class EasyPoiStudy {
    /**
     * id: excel is not added and cannot be exported
     */
    private Integer id;
    /**
     * Student name
     * Test name and width
     */
    @Excel(name = "Student name", width = 30)
    private String name;
    /**
     * Student gender, the value of this field is 1 / 2
     * So when we output the file, it corresponds to men and women
     * suffix If a student is added after, the value must be male / female
     * Test replace and suffix
     */
    @Excel(name = "Student gender", replace = {"male_1", "female_2"}, suffix = "living")
    private int sex;

    /**
     * date of birth
     * Test format and orderNum (other defaults are 0, and this is 2, which must be after all columns)
     */
    @Excel(name = "date of birth", orderNum = "2", format = "yyyy-MM-dd", width = 20)
    private Date birthday;

    /**
     * Student's height
     * Test numFormat
     */
    @Excel(name = "Student's height", numFormat = "#.0")
    private Double height;

}

(2) Execution class

We define two entities. The data is divided into

• EasyPoiStudy(id=1, name=zhangsan, sex=1, birthday=Tue Jan 11 17:42:17 CST 2022, height=175.0)
• EasyPoiStudy(id=2, name=lisi, sex=2, birthday=Tue Jan 11 17:42:17 CST 2022, height=173.45)

public class EasyPoiStudyMain {

    public static void main(String[] args) {
        File file = new File("D:\\test.xls");
        if (file.exists()) {
            file.delete();
        }
        try {
            FileOutputStream outputStream = new FileOutputStream(file);
            List<EasyPoiStudy> list = new ArrayList<>();
            EasyPoiStudy study_1 = EasyPoiStudy.builder().id(1).name("zhangsan").sex(1).birthday(new Date()).height(175.0).build();
            EasyPoiStudy study_2 = EasyPoiStudy.builder().id(2).name("lisi").sex(2).birthday(new Date()).height(173.45).build();
            list.add(study_1);
            list.add(study_2);
            ExportParams exportParams = new ExportParams("My headline", "My subtitle", "my sheet");
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,
                    EasyPoiStudy.class, list);
            workbook.write(outputStream);
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

(3) Verify

According to the attributes defined by entity, we need to verify the following points:

• id is not exported * * (if Excel is not added, it will not be exported)**

• Column name * * (whether the name attribute is effective)**

• The width of the column (the default width is 10, the name field and birthday specify the widths 30 and 20, and whether the width attribute is effective)

• Whether the gender is male / Female * * (whether the replace and suffix attributes are effective)**

• Format of birth date * * (whether the format attribute is effective)**

• Format of the student's height * * (whether the numFormat attribute takes effect)**

excel screenshot:

4.2 usage 2: import excel with simple attributes

Let's change the contents of the file exported in 4.1. The screenshot of the modified file is as follows:

(1) Entity: the above one is still used, but (it should be noted that we must have a parameterless constructor, which is changed in front of the class here)

@Getter
@Setter
//@Builder
@ToString
@AllArgsConstructor
@NoArgsConstructor
// Or mark @ data directly
//@Data
public class EasyPoiStudy {
}

(2) Execution class

package com.study.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.study.pojo.EasyPoiStudy;
import org.apache.poi.ss.usermodel.Workbook;

import javax.xml.bind.SchemaOutputResolver;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class EasyPoiStudyMain {

    public static void main(String[] args) {
		
        File file = new File("D:\\test.xls");
        ImportParams importParams = new ImportParams();
        // Which line of the file does the title start from? It's the third line. Here it starts from 0, so it's 2
        importParams.setTitleRows(2);
        List<EasyPoiStudy> list = ExcelImportUtil.importExcel(file, EasyPoiStudy.class, importParams);
        System.out.println("list size :  " +  list.size());
        list.forEach(System.out::println);
    }

}

(3) Look at the results

• EasyPoiStudy(name=zhangsan, sex=1, birthday=Tue Jan 11 12:00:00 CST 2022, height=175.02)
• EasyPoiStudy(name=lisi, sex=2, birthday=Tue Jan 11 00:00:00 CST 2022, height=173.4)

Two problems were encountered during verification:

• Why do the replace and suffix defined by us take effect, but the format and numformat do not take effect?
• Why is an error reported when a class has a parameterless constructor?

Note: when I was playing this, I encountered the above two problems. I was particularly curious, so I downloaded the source code and debugged it before I knew why

1) Source code for the first problem

Reason: when obtaining cells, we need to determine the format of cells in the file, so the format of cells in the file must be confirmed~

• For the use of numformat, the format of the cell must be celltype NUMERIC

• For the use of format, the format of the cell must not be celltype NUMERIC

if(cell instanceof Cell){
    // Gets the value of the cell
    result = getCellValue(classFullName, (Cell) cell, entity);
}else{
     result = cell;
}
if (entity != null) {
     result = handlerSuffix(entity.getSuffix(), result);
     result = replaceValue(entity.getReplace(), result);
     result = replaceValue(entity.getReplace(), result);
     if (dictHandler != null && StringUtils.isNoneBlank(entity.getDict())) {
         result = dictHandler.toValue(entity.getDict(), object, entity.getName(), result);
     }
}

/**
     * Gets the value in the cell
     *
     * @param cell
     * @param entity
     * @return
     */
    private Object getCellValue(String classFullName, Cell cell, ExcelImportEntity entity) {
        if (cell == null) {
            return "";
        }
        Object result = null;
        // Determine the type of field
        if ("class java.util.Date".equals(classFullName)
                || "class java.sql.Date".equals(classFullName)
                || ("class java.sql.Time").equals(classFullName)
                || ("class java.time.Instant").equals(classFullName)
                || ("class java.time.LocalDate").equals(classFullName)
                || ("class java.time.LocalDateTime").equals(classFullName)
                || ("class java.sql.Timestamp").equals(classFullName)) {
            //FIX: cell is used when cell yymmdd is numeric The date parsed by getdatecellvalue() is incorrect
            if (CellType.NUMERIC == cell.getCellType() && DateUtil.isCellDateFormatted(cell)) {
                result = DateUtil.getJavaDate(cell.getNumericCellValue());
            } else {
                String val = "";
                try {
                    val = cell.getStringCellValue();
                } catch (Exception e) {
                    return null;
                }
                // The numberformat is processed here
                result = getDateData(entity, val);
                if (result == null) {
                    return null;
                }
            }
            if (("class java.time.Instant").equals(classFullName)) {
                result = ((Date) result).toInstant();
            } else if (("class java.time.LocalDate").equals(classFullName)) {
                result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            } else if (("class java.time.LocalDateTime").equals(classFullName)) {
                result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
            } else if (("class java.time.OffsetDateTime").equals(classFullName)) {
                result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toOffsetDateTime();
            }  else if (("class java.time.ZonedDateTime").equals(classFullName)) {
                result = ((Date) result).toInstant().atZone(ZoneId.systemDefault());
            }  else if (("class java.sql.Date").equals(classFullName)) {
                result = new java.sql.Date(((Date) result).getTime());
            } else if (("class java.sql.Time").equals(classFullName)) {
                result = new Time(((Date) result).getTime());
            } else if (("class java.sql.Timestamp").equals(classFullName)) {
                result = new Timestamp(((Date) result).getTime());
            }
        } else {
            switch (cell.getCellType()) {
                    // When it's text
                case STRING:
                    result = cell.getRichStringCellValue() == null ? ""
                            : cell.getRichStringCellValue().getString();
                    break;
                   // When it was NUMERIC
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        if ("class java.lang.String".equals(classFullName)) {
                            result = formateDate(entity, cell.getDateCellValue());
                        }
                    } else {
                        // If it is a numerical value, it is parsed according to entity
                        result = readNumericCell(cell);
                    }
                    break;
                case BOOLEAN:
                    result = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case BLANK:
                    break;
                case ERROR:
                    break;
                case FORMULA:
                    try {
                        result = readNumericCell(cell);
                    } catch (Exception e1) {
                        try {
                            result = cell.getRichStringCellValue() == null ? ""
                                    : cell.getRichStringCellValue().getString();
                        } catch (Exception e2) {
                            throw new RuntimeException("Failed to get cells of formula type", e2);
                        }
                    }
                    break;
                default:
                    break;
            }
        }
        return result;
    }

2) The source code for the second question is as follows:

When assigning a value to an object, you need to create an object. You must need a parameterless constructor. If you don't, you will report an error. Someone may ask, even if I don't define a constructor, if I don't define a constructor, I will bring a parameterless constructor. How can you report an error?

Reason: for the entity we defined in 4.1, I used lombok's @ Builder annotation. This annotation will generate a full parameter constructor, so a parameterless constructor will not be generated automatically. You need to add a parameterless constructor manually. If you don't know the use of lombok, you can see another blog: Use of lombok

/**
 * Create an object completely
 *
 * @param clazz
 * @return
 */
public static Object createObject(Class<?> clazz, String targetId) {
    Object obj = null;
    if (clazz.equals(Map.class)) {
            return new LinkedHashMap<String, Object>();
     }
     // clazz.newInstance();  To create an instance, this must have a parameterless constructor
        obj = clazz.newInstance();
        Field[] fields = getClassFields(clazz);
        for (Field field : fields) {
            if (isNotUserExcelUserThis(null, field, targetId)) {
                continue;
            }
            if (isCollection(field.getType())) {
                ExcelCollection collection = field.getAnnotation(ExcelCollection.class);
                PoiReflectorUtil.fromCache(clazz).setValue(obj, field.getName(),
                        collection.type().newInstance());
            } else if (!isJavaClass(field) && !field.getType().isEnum()) {
                PoiReflectorUtil.fromCache(clazz).setValue(obj, field.getName(),
                        createObject(field.getType(), targetId));
            }
        }
    return obj;
}

summary

Generally speaking, I don't think it's very difficult to see the source code of Easypoi. It may be that it wants to do too many things. It provides a lot of extended fields, and the description of many fields is not particularly clear. In the above learning process, I still read the source code to know what it means and how to use it~

Here is a brief introduction to the simple usage of import and export

Portal

API official website (old)

Introduction to official website (New)

Source address (Git)

EasyPoi Development Guide