Import and export Excel using Alibab’s EasyExce

First, preparation

1, guide package

<! --> <dependency> <groupId>org.apache.poi</groupId> <artifactId> <version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>3.17</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version> </dependency> <! -- esayexcel2.17.  -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.17.</version>
</dependency>
Copy the code

Second, understand the notes

1. Common annotations

2. @excelProperty annotation

** A necessary annotation, annotation has three parameters value,index represents the column number, respectively

Value corresponds to the header text. Index corresponds to the line number

@excelProperty (value = "id ", index = 0)
private Long id;
Copy the code

3, @columnwith annotation

Set the column width with a single parameter value, which is in character length. The maximum number of characters that can be written to an Excel cell is 255 characters

public class ImeiEncrypt {
    @ColumnWidth(value = 255) // The maximum length of a single excel cell is 255
    private String message;
}
Copy the code

4, @contentfontStyle annotation

Annotations used to format the font for cell content

5, @contentStyle annotation

Set content formatting annotations

6. @headfontStyle annotations

Use to customize the title font format

7. ExcelIgnore annotations

Do not convert this field to Excel

Three, coding

1. Map entity class —- example

package com.pingou.admin.bean.param; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; import java.math.BigDecimal; import java.util.Date; @data@contentrowheight (35) // Text row height @headrowheight (40) // title height @columnWidth (40) Public class OrderExcel {// Set the excel header name @excelProperty (value = "id ", index = 0) private Long ID; @dateTimeFormat (" YYYY ", "MM "," HH ") @excelProperty (value = "createTime ", index = 1) private Date createTime; }Copy the code

The above is a simple example. If you have more attributes, you can write them one by one and then plug them into the entity class

2. Generate Excel

public void excel(a) {
        // Want to export excel data result set
        List<OrderExcel> excel = new ArrayList<>();
        // Omit the insert into the result set

        //UUID generates a unique name
        String name = UUID.randomUUID().toString().replaceAll("-"."") + ".xlsx";
        // Implement excel write operations

        //1 Set the write folder address and excel file name
        String filename = "/" + name;
        JSONObject json = new JSONObject();
        try {
            // 2 Call easyExcel to write
            // The write method takes two arguments: the first argument is the file path name and the second argument is the entity class
            EasyExcel.write(filename, OrderExcel.class).sheet("Name").doWrite(excel);
            // Upload to fastdfs. If you do not upload to Fastdfs, only the local machine can find it
            File file = new File(filename);
            String path = fastDFSClient.upload(new FileInputStream(file), name, null);
            path = (this.fastdfsDomain + path);
            json.put("url", path);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            newFile(filename).delete(); }}Copy the code

And that’s it!

Four, the results

O ~ get