what is easypoi?

Easypoi is a simplified POI tool that allows you to complete various operations in Excel and Word using POI without much knowledge of POI.

Easypoi official documentation, click official documentation

How to useeasypoi?

1. Create onespringbootThe project,pom.xmlIntroducing dependencies in

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>
Copy the code

2, write,easypoiutil

public class EasyPoiUtils {

    /** * Export excel *@param pojoClass
     * @param dataSet
     * @param path
     * @param filename
     * @throws IOException
     */
    public static void exportExcel(Class
        pojoClass, Collection
        dataSet,String path,String filename) throws IOException {

        File savefile = new File(path);
        if(! savefile.exists()) { savefile.mkdirs(); } Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), pojoClass, dataSet);
        FileOutputStream fos = new FileOutputStream(path+filename);
        workbook.write(fos);
        fos.close();
    }

    /** * Create the corresponding Excel based on the Map *@paramList List Multiple Map key titles corresponding to tables Title Key Entity Corresponding to tables Key data * * Collection data *@paramThe path path *@paramfilename  The file name *@throws IOException
     */
    public static void  exportExcel(List<Map<String, Object>> list,String path,String filename) throws IOException{
        File savefile = new File(path);
        if(! savefile.exists()) { savefile.mkdirs(); } Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); FileOutputStream fos =new FileOutputStream(path+filename);
        workbook.write(fos);
        fos.close();
    }


    /** * Import excel *@param file
     * @param pojoClass
     * @param params
     * @param <T>
     * @return* /
    public static <T>List<T> importExcel(File file, Class
        pojoClass, ImportParams params){
        long start = new Date().getTime();
        List<T> list = ExcelImportUtil.importExcel(file,UserEntity.class, params);
        returnlist; }}Copy the code

3. Create entity classes

Create an entity class and annotate @excel on the fields you want to export.

/** * User entity class */
public class UserEntity {

    @Excel(name = "ID")
    private int id;

    @Excel(name = "Name")
    private String name;

    @Excel(name = "E-mail",width = 20)
    private String email;

    @Excel(name = "Age")
    private int age;

    @Excel(name = "Gender",replace={"Male _1"."Female _2"})
    private int sex;

    @Excel(name = "Operation time",format="yyyy-MM-dd HH:mm:ss",width = 20)
    private Date time;

    public int getId(a) {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName(a) {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail(a) {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public int getAge(a) {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getSex(a) {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public Date getTime(a) {
        return time;
    }

    public void setTime(Date time) {
        this.time = time;
    }

    @Override
    public String toString(a) {
        return "UserEntity{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                ", email='" + email + '\' ' +
                ", age=" + age +
                ", sex=" + sex +
                ", time=" + time +
                '} '; }}Copy the code

Notes:

4, test,

1. Test export singlesheet

    /** * Test sheet export *@throws IOException
     */
    public static void testExportExcel(a) throws IOException {
        List<UserEntity> list = new ArrayList<>();
        int i = 0;
        while (i < 10){
            UserEntity user = new UserEntity();
            user.setId(i+1);
            user.setAge(20+i);
            user.setEmail("[email protected]");
            user.setName("Zhang"+i);
            user.setSex(i%2= =0?1:2);
            user.setTime(new Date());
            list.add(user);
            i++;
        }
        EasyPoiUtils.exportExcel(UserEntity.class,list,"src/main/resources/excel/"."user.xls");
    }
Copy the code

Export effect

2, test export multiplesheet

 /** * test multi-sheet export *@throws IOException
     */
    public static void testExportExcels(a) throws IOException {
        List<Map<String, Object>> list = new ArrayList<>();
        for(int n=1; n<4; n++){ ExportParams exportParams =new ExportParams("User Information"+n,"User Information"+n);
            Object entity = UserEntity.class;
            List<UserEntity> data = new ArrayList<>();
            int i = 0;
            while (i < 10){
                UserEntity user = new UserEntity();
                user.setId(i*n+1);
                user.setAge(20+i);
                user.setEmail("[email protected]");
                user.setName("Zhang"+i*n);
                user.setSex(i%2= =0?1:2);
                user.setTime(new Date());
                data.add(user);
                i++;
            }
            / / build the map
            Map<String,Object> map = new HashMap<>();
            map.put("title",exportParams);
            map.put("entity",entity);
            map.put("data",data);
            list.add(map);
        }
        EasyPoiUtils.exportExcel(list,"src/main/resources/excel/"."user1.xls");
    }

Copy the code

Export effect:

3. Test the importexcel

    /** * test import */
    public static void testImportExcel(a){
        List<UserEntity> list = EasyPoiUtils.importExcel(

                new File("src/main/resources/excel/user.xls"),
                UserEntity.class, new ImportParams());
        list.forEach((user)->{
            System.out.println(user);
        });

    }
Copy the code

When importing it, we can convert it directly to an entity class, which is really cool. We print the result to the console with the following output:

The above is easyPOI simple operation of Excel table, of course, easyPOI’s function is far more than so much, easyPOI’s function is still very powerful, interested friends can go to official learning.