rely on

<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>

Notes usage

• @Excel is used on fields
• @ExcelCollection represents the list in the root object
• @ExcelEntity represents an entity in the root object
• @ExcelIgnore ignore export
• @ExcelTarget binding Id

@ExcelTarget binding Id

The same entity is mapped to Excel, but the header names are different. Different names are mapped according to different classes

The @ ExcelTarget here indicates that different processing can be done for different fields by using the X object
Similarly, both ExcelEntity and ExcelCollection support this method

@Excel is used on fields

attribute	type	Default value	function
name	String	null	Column name, support ExcelTarget
needMerge	boolean	fasle	Vertically merge cells (used to merge multiple rows created by a list with a single cell in the list)
orderNum	String	"0"	Column sorting, support ExcelTarget
replace	String[]	{}	Character replacement export is {"replacement value source", "replacement value source"}
savePath	String	"upload"	The path to save the imported file can be filled in if it is a picture. The default is upload/className/ IconEntity. This class corresponds to upload/Icon/
type	int	1	Export type 1 is text, 2 is picture, 3 is function, 10 is number, and the default is text
width	double	10	Column width
height	double	10	Column height. We plan to use @ ExcelTarget height uniformly in the future. This will be discarded. Please pay attention
isStatistics	boolean	fasle	Automatic statistics: add a row of statistics and output all data [this processing will swallow exceptions, please pay attention to this]
isHyperlink	boolean	FALSE	Hyperlink, if it is necessary to implement the interface, return the object
isImportField	boolean	TRUE	Check the field to see if it is in the imported Excel. If it is not indicated that it is an error, the reading fails and ExcelTarget is supported
exportFormat	String	""	The exported time format determines whether the date needs to be formatted by whether it is empty
importFormat	String	""	The imported time format determines whether the date needs to be formatted by whether it is empty
format	String	""	The time format is equivalent to setting both exportFormat and importFormat
databaseFormat	String	"yyyyMMddHHmmss"	Export time settings. If the field is of Date type, you do not need to set the database. If it is of string type, you need to set the database format to convert the time format output
numFormat	String	""	Number format, the parameter is Pattern, and the object used is DecimalFormat
imageType	int	1	Export type 1 reads from file 2 reads from the database. The default is file. The same is true for import
suffix	String	""	Text suffix, e.g. 90 suffix = "%" becomes 90%
isWrap	boolean	TRUE	Whether line feed is supported \ n
mergeRely	int[]	{}	Merge cell dependencies. For example, if the second column is merged based on the first column, {0} is OK
mergeVertical	boolean	fasle	Vertically merge cells with the same content
fixedIndex	int	-1	Column corresponding to excel, ignoring name
isColumnHidden	boolean	FALSE	Export hidden columns

See how to use it. There is complete code below to copy

@Data
@ExcelTarget("user1")
@Accessors(fluent = true)
public class TestBean implements Serializable {
    @Excel(name = "A Class number_user1,B Class number_user2" ,//Header name
            width = 15.0, //width
            orderNum = "0", //Sorting can also be used consecutively in @ ExcelEntity. Excel in ExcelCollection cannot be used consecutively
            needMerge = true)
    private Integer id;
    @Excel(name = "full name",width = 15.0,orderNum = "1",needMerge = true)
    private String name;
    @Excel(name = "Gender",
            replace = {"male_1","female_2"}, //Character replacement source real 1 for male and 2 for female
            suffix = "living",//Add Suffix 
            orderNum = "2",needMerge = true)
    private int sex;
    @Excel(name = "birthday", databaseFormat = "yyyyMMddHHmmss",//If it is a string > > synchronize the database format
            format = "yyyy-MM-dd HH:mm:ss",//Set import / export format
            orderNum = "3",
            needMerge = true)
    private Date bir;
    @ExcelIgnore //Do not generate data
    @Excel(name = "Birthday 2", databaseFormat = "yyyy-MM-dd HH:mm:ss",format = "yyyy-MM-dd HH:mm:ss",orderNum = "4")
    private Date bir2;
    @Excel(name = "Photo",type = 2,//Set data type 2 as picture
            imageType = 1,//Picture usage
            orderNum = "8",width = 30,height = 30)
    private String img;
    @Excel(name = "Photo 2",type = 2,imageType = 2,orderNum = "8",width = 30,height = 30)
    private byte[] img1;

    @ExcelEntity  //It means that this is an entity, which is actually equivalent to adding other fields to the root entity
    private Card card;

    @ExcelCollection(name = "goods",orderNum = "7")//This collection will loop through the object.. the previous field data is only displayed in the first row
    private List<Order> orders;

@ExcelCollection

attribute	type	Default value	function
id	String	null	Definition ID
name	String	null	Define collection column names and support ExcelTarget
orderNum	int	0	Sorting, support ExcelTarget
type	Class<?>	ArrayList.class	Create objects on import using

Entity code

3 entities

@Data
@ExcelTarget("user1")
@Accessors(fluent = true)
public class TestBean implements Serializable {
    public static String imgPath="imgs/company/baidu.png";
    @Excel(name = "A Class number_user1,B Class number_user2" ,//Header name
            width = 15.0, //width
            orderNum = "0", //Sorting can also be used consecutively in @ ExcelEntity. Excel in ExcelCollection cannot be used consecutively
            needMerge = true)
    private Integer id;
    @Excel(name = "full name",width = 15.0,orderNum = "1",needMerge = true)
    private String name;
    @Excel(name = "Gender",
            replace = {"male_1","female_2"}, //Character replacement source real 1 for male and 2 for female
            suffix = "living",//Add Suffix 
            orderNum = "2",needMerge = true)
    private int sex;
    @Excel(name = "birthday", databaseFormat = "yyyyMMddHHmmss",//If it is a string > > synchronize the database format
            format = "yyyy-MM-dd HH:mm:ss",//Set import / export format
            orderNum = "3",
            needMerge = true)
    private Date bir;
    @ExcelIgnore //Do not generate data
    @Excel(name = "Birthday 2", databaseFormat = "yyyy-MM-dd HH:mm:ss",format = "yyyy-MM-dd HH:mm:ss",orderNum = "4")
    private Date bir2;
    @Excel(name = "Photo",type = 2,//Set data type 2 as picture
            imageType = 1,//Picture usage
            orderNum = "8",width = 30,height = 30)
    private String img;
    @Excel(name = "Photo 2",type = 2,imageType = 2,orderNum = "8",width = 30,height = 30)
    private byte[] img1;

    @ExcelEntity  //It means that this is an entity, which is actually equivalent to adding other fields to the root entity
    private Card card;

    @ExcelCollection(name = "goods",orderNum = "7")//This collection will loop through the object.. the previous field data is only displayed in the first row
    private List<Order> orders;

    //Get 10 complete data
    public static List<TestBean> getListUser(){
        List<TestBean> list=new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            list.add(new TestBean().id(i).name("full name"+i).bir(new Date()).sex(i%2==0?1:2).card(new Card())
            .orders(getOrder())
                    .img(imgPath));
        }
        return list;
    }
    //Only get the root object data, no pictures, list s, entities
    public static List<TestBean> getBigListFor10000(int page){
        List<TestBean> list=new ArrayList<>();
        for (int i = page; i <= page*10000; i++) {
            list.add(new TestBean().id(i).name("full name"+i).bir(new Date()).sex(i%2==0?1:2));
        }
        return list;
    }
    public static List<Order> getOrder(){
        List<Order> list=new ArrayList<>();
        for (int i = 1; i <=3 ; i++) {
            list.add(new Order().id(i).name("goods"+i));
        }
        return list;
    }



    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getSex() {
        return sex;
    }

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

    public Date getBir() {
        return bir;
    }

    public void setBir(Date bir) {
        this.bir = bir;
    }

    public Date getBir2() {
        return bir2;
    }

    public void setBir2(Date bir2) {
        this.bir2 = bir2;
    }

    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }

    public byte[] getImg1() {
        return img1;
    }

    public void setImg1(byte[] img1) {
        this.img1 = img1;
    }

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
    //Verification file directory
    public static String existsDir(){
        String dir="D:/eazyPoi/";
        File savefile = new File(dir);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        return dir;
    }


}

@Accessors(fluent = true)
@ExcelTarget("card1")
@Data
public class Card{
    @Excel(name = "ID",orderNum = "5")
    private String id ;
    @Excel(name = "address",orderNum = "6")
    private String addr;
    public Card() {
        this.id = "123212321";this.addr = "Chaoyang District of Beijing City";
    }

    public String getId() {
        return id;
    }

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

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }
}

@Accessors(fluent = true)
@ExcelTarget("order1")
@Data
public class Order{
    @Excel(name = "Serial number",orderNum = "1")
    public int id ;
    @Excel(name = "goods",orderNum = "2")
    private String name;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

Excel export

Normal quantity export (1-2W pieces)

package com.ruoyi.web.eazyPoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import cn.afterturn.easypoi.handler.inter.IWriter;
import org.apache.poi.hssf.record.DVALRecord;
import org.apache.poi.ss.usermodel.Workbook;


import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Test {


    public static void main(String[] args) throws Exception {
        Test.exportExcel();
    }

    //Methods below 10000
    public static void exportExcel()throws Exception{
        ExportParams exportParams=new ExportParams("User information table", "User information");
        List<TestBean> listUser = TestBean.getListUser();
        Workbook sheets = ExcelExportUtil.exportExcel(exportParams, TestBean.class, listUser);
        sheets.write(new FileOutputStream(TestBean.existsDir()+System.currentTimeMillis() +".xls"));
        sheets.close();
    }

}

Large data export

The @ Override function here is a circular function. Obj is the queryparams (parameter 4) passed from the outer layer, but the page will cycle to > obj. For example, our 10 page data page will cycle more than 10 times

• After the sql condition is paged to the last page, the query is

public class Test {


    public static void main(String[] args) throws Exception {
        Test.exportExcel();
    }

    public static void exportBigExcel()throws Exception{
        Workbook     workbook = null;
        Date         start    = new Date();
        ExportParams exportParams=new ExportParams("User information table", "User information");
        workbook = ExcelExportUtil.exportBigExcel(exportParams, TestBean.class, new IExcelExportServer() {
            /**
             * @param obj   PageCount 
             * @param page  Current number of pages
             * @return
             */
            @Override
            public List<Object> selectListForExcelExport(Object obj, int page) {
                //page is incremented from 1 * * (it should be made to end when the number is transmitted outside, but it is not added back directly), so it is manually limited
                if((int)obj==page){return null;};
                //Last returned table object
                List<Object> listUser = new ArrayList<>();
                //Simulated service paging query
                listUser.addAll(TestBean.getBigListFor10000(page));
                return listUser;
            }
        },10);
        workbook.write(new FileOutputStream(TestBean.existsDir()+System.currentTimeMillis() +".xlsx"));
        workbook.close();
    }
    
}

Multi sheet export

    public void moveSheet(){
        //sheet collection information
        List<Map<String,Object>> sheetList=new ArrayList<Map<String,Object>>();
        //Create 2 sheet s in a circular manner
        for (int i = 0; i <2 ; i++) {
            //get data
            List<TestBean> listUser = TestBean.getListUser();
            //Encapsulate sheet information
            Map<String,Object> mapData=new HashMap<String, Object>();
            ExportParams params = new ExportParams();
            //sheet name
            params.setSheetName("Student information sheet"+i);
            mapData.put("title",params);//Table information
            mapData.put("entity",TestBean.class);//Entity class
            mapData.put("data",listUser);//List set
            sheetList.add(mapData);//sheet set
        }
        //export
        Workbook workbook = ExcelExportUtil.exportExcel(sheetList, ExcelType.XSSF);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream("D:/home/excel/Test more Sheet.xls");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Excel import

ImportParams parameter

value	type	Default value	function
titleRows	int	0	Number of table title rows, default 0
headRows	int	1	Number of header rows, 1 by default
startRows	int	0	Set the position to start reading, that is, read the data after the first row from 0 under the title to all 1
keyIndex	int	0	How to set the primary key? If the cell has no value, skip or think it is the value below the list. This column must have a value, otherwise it is considered as invalid data
startSheetIndex	int	0	Used in combination with sheetNum. There are 10 sheets when there are multiple sheets. We want to read 1-4 sheets
sheetNum	int	1	The number of sheet s to be read when uploading a table. The default value is 1
needSave	boolean	FALSE	Do you want to save the uploaded Excel
needVerfiy	boolean	FALSE	Need to verify the uploaded Excel
saveUrl	String	"upload/excelUpload"	Save the uploaded excel directory. The default is TestEntity. The save path is upload / Excel upload / test / yyyymmddhhmss_ Save name upload time_ Five digit random number
verifyHanlder	IExcelVerifyHandler	null	Verification processing interface, custom verification
lastOfInvalidRow	int	0	Last invalid rows, unread rows
readRows	int	0	Manually control the number of rows read. 2 = the first row of data after head is set
importFields	String[]	null	There must be some fields in excel file, and there must be these data
keyMark	String	":"	Key Value reads the tag. Take this as the key, the next Cell as the Value, and multiple cells as ArrayList
readSingleCell	boolean	FALSE	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
dataHanlder	IExcelDataHandler	null	The data processing interface is mainly based on this. Replace and format are behind it

Import importExcel normally

You need to comment out the Excel fields of the picture in the entity

    //Normal table
    public void importExcel1() {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        params.setStartRows(0);//Set the position to start reading, that is, read the data after the first row from 0 under the title to all 1
//        params.setKeyIndex(1);//
//        params.setStartSheetIndex(0); params.setSheetNum(1);// When two sheets are used together, there are 10 sheets. We want to read 1-4 sheets
//        params.setReadRows(3);// 2 = the first row of data after the measured setting of head
        params.setImportFields(new String[]{"full name","Gender"});//There must be some fields in excel file, and there must be these data
//        params.setReadRows(4);
        File file = new File(getWebRootPath("import/B.xlsx"));
        List<TestBean> list = ExcelImportUtil.importExcel(file,TestBean.class, params);
        list.stream().forEach(i-> System.out.println(i.toString()));
    }

Large data importExcelBySax

    //Import large amounts of data
    public void importExcelBySax() throws FileNotFoundException {
        /**
         You need to add a jar package that relies on xml parsing
         <dependency>
         <groupId>xerces</groupId>
         <artifactId>xercesImpl</artifactId>
         <version>2.9.1</version>
         </dependency>
         */
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        List<TestBean> list=new ArrayList<>();
        ExcelImportUtil.importExcelBySax(
                new FileInputStream(
                        new File(getWebRootPath("import/ExcelExportMsgClient.xlsx"))),
                TestBean.class, params, new IReadHandler<TestBean>() {
                    @Override//Each row read
                    public void handler(TestBean o) {
                        list.add(o);
                        System.out.println(ReflectionToStringBuilder.toString(o));
                    }
                    @Override
                    public void doAfterAll() {
                        System.out.println("Processing after data execution");
                    }
                });
    }

asynchronous

I can see from the official demo that it's not going anywhere soon

    public void asyncTask(){
        ImportParams params =new ImportParams();
        params.setHeadRows(1);
        params.setTitleRows(1);
        params.setConcurrentTask(true);//Turn on asynchronous
        params.setCritical(500);
        List<TestBean> objects = ExcelImportUtil.importExcel(
                new File(getWebRootPath("import/B.xlsx")),
                TestBean.class,
                params
        );
        objects.stream().forEach(i-> System.out.println(i.toString()));
    }

Read merged header

    //Normal table
    public void importExcel1() {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//Title 1 line
        params.setHeadRows(2);//There are 2 rows in the header
        File file = new File(getWebRootPath("import/B.xlsx"));
        List<TestBean> list = ExcelImportUtil.importExcel(file,TestBean.class, params);
        list.stream().forEach(i-> System.out.println(i.toString()));
    }

Verify the import (screen out the conforming and nonconforming data)

The official gives a lot of demo s, which is a little cumbersome. I think this is more practical
Add in entity class hibernate field validation annotation
This does not require the introduction of additional dependencies
Mainly verify these three locations

    public void validFieldImport() throws IOException {
        ImportParams params=new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(2);
        params.setNeedVerify(true);//Settings need validation
        params.setVerifyGroup(new Class[]{ValidGroup.class});//Set authentication group
        ExcelImportResult<TestBean> importResult = ExcelImportUtil.importExcelMore(new File(getWebRootPath("import/B.xlsx")), TestBean.class, params);
        //Compliance verification
        List<TestBean> list = importResult.getList();
        //Non conformance verification
        List<TestBean> failList = importResult.getFailList();
        //Get map
        Map<String, Object> map = importResult.getMap();
        //Export validated data
        importResult.getWorkbook().write(new FileOutputStream("D:/eazyPoi/validExcel.xlsx"));
        //Non conforming data
        importResult.getFailWorkbook().write(new FileOutputStream("D:/eazyPoi/failValidExcel.xlsx"));
        System.out.println("Verification failed"+importResult.isVerifyFail());
    }

The bir field does not have a validation group, so it does not participate in validation

excel that fails to export will be prompted

groupName of header

import picture

    @Excel(name = "company LOGO", type = 2 ,width = 40 , height = 30,imageType = 1,
            savePath="D:\\javaProject\\")
    private String companyLogo;

CSV import

New C.csv file

id,name,sex,bir
1,Xiao Ming,1,1991-08-08
2,Xiao Zhang,1,1991-09-08
3,Xiao Hong,2,1991-09-08

public  void importCSV()throws Exception{
       Date start = new Date();
       CsvImportParams params = new CsvImportParams(CsvImportParams.UTF8);
       CsvImportUtil.importCsv(new FileInputStream(
                       new File(getWebRootPath("import/C.csv"))),
               Map.class, params, new IReadHandler<Map>() {
                   @Override
                   public void handler(Map o) {
                       System.out.println(JSON.toJSONString(o));
                   }

                   @Override
                   public void doAfterAll() {

                   }
               });
}

excel to csv

Excel

    public void excel2Csv()throws Exception{
        //Exported CSV
        FileOutputStream fos    = new FileOutputStream("D:\\eazyPoi\\aa.csv");
        ImportParams     params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        CsvExportParams csvExportParams = new CsvExportParams();
        csvExportParams.setEncoding(CsvExportParams.GBK);//UTF8 can be used
        IWriter ce = CsvExportUtil.exportCsv(csvExportParams, TestBean.class, fos);
        ExcelImportUtil.importExcelBySax(
                new FileInputStream(
                        new File(getWebRootPath("import/B.xlsx"))),
                TestBean.class, params, new IReadHandler<TestBean>() {
                    private List<TestBean> list = new ArrayList<>();
                    @Override
                    public void handler(TestBean o) {
                        //Add to list after reading
                        list.add(o);
                    }
                    @Override
                    public void doAfterAll() {
                        //Convert output after reading
                        ce.write(list);
                        list.clear();
                        System.out.println("succcess--------------------------------");
                    }
                });
    }

The CSV red box is the entity class annotation field

Excel to html

    public void testToAllHtmlWorkbookAndImage()throws Exception{
        Workbook wb = new XSSFWorkbook(new FileInputStream(new File(getWebRootPath("import/B.xlsx"))));
        String     html = ExcelXorHtmlUtil.excelToHtml(new ExcelToHtmlParams(wb, true, "D:\\eazyPoi\\"));
        FileWriter fw   = new FileWriter("D:/eazyPoi/B.html");
        fw.write(html);
        fw.close();
    }