This is the 23rd day of my participation in the Gwen Challenge in November. Check out the details: The last Gwen Challenge in 2021″

easyExcel

Import dependencies (EasyExcel dependencies already have POI dependencies)

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>
Copy the code

Field entity class

Public class excel {@excel (" String title ") private String String; @excelProperty (" Date title ") private Date Date; @excelProperty (" private int aDouble "); public excel() { } public excel(String string, Date date, int aDouble) { this.string = string; this.date = date; this.aDouble = aDouble; } public String getString() { return string; } public void setString(String string) { this.string = string; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } public int getaDouble() { return aDouble; } public void setaDouble(int aDouble) { this.aDouble = aDouble; }}Copy the code

Specifies the sequential order of columns

@excelProperty (value = "name ",index = 1) private String String; @excelProperty (value = "Date ",index = 2) private Date Date; @excelProperty (value = "student ",index = 0) private int aDouble;Copy the code

If the specified column is written, the ignored column is left empty

Complex head

@excelProperty ({" main title ", "String title "}) private String String; @excelProperty ({" private title ", "Date title "}) private Date Date; @excelProperty ({" private ", "private "}) private int aDouble;Copy the code

write

Write data to list

// Write list private list <excel> data(){list <excel> excels = new ArrayList<>(); for(int i = 0; i < 10; i++){ excel e = new excel(); e.setaDouble(i); e.setString("yzy"); e.setDate(new Date()); excels.add(e); } return excels; }Copy the code

Simple write method

The list in excel

Excel @test public void write(){// The first argument is path, the second argument is title entity class // then write the board, EasyExcel. Write(PATH+"easy. XLSX ",excel. Class).sheet(" template ").dowrite (data()); }Copy the code

Specify column write

Set is used to store the column names to be ignored, and add specifies the column names to be ignored

Public void daochuzhidinglie(){/* ignore the column */ Set<String> Set = new HashSet<>(); set.add("aDouble"); ExcludeColumnFiledNames (set).sheet("1").dowrite (data()); // Filter write easyExcel.write (PATH+" export specified column 1.xlsx",excel.class).excludecolumnFiledNames (set). Set<String> set1 = new HashSet<>(); // Set<String> set1 = new HashSet<>(); set1.add("date"); set1.add("string"); // write EasyExcel (PATH+" export specified column 1.xlsx",excel. Class). IncludeColumnFiledNames (set1).sheet(" specify the name of the school id ").dowrite (data()); * /}Copy the code

Ignore student id

Note that if you specify the order of columns, specifying columns to write will leave the other columns empty

Write image

public void pic() throws Exception { //InputStream inputStream = null; ArrayList< excel > excels = new ArrayList<>(); excel excel = new excel(); // Picture PATH String picPath = PATH+"1.jpg"; / * five types of picture * / excel. The setBytes (FileUtils. ReadFileToByteArray (new File (picPath))); //excel.setFile(new File(picPath)); //excel.setPic(picPath); //inputStream = FileUtils.openInputStream(new File(picPath)); //excel.setUrl(new URL("")); excels.add(excel); EasyExcel.write(PATH+"pic.xlsx",excel.class).sheet().doWrite(excels); //inputStream.close(); }Copy the code

read

The listener

Reading exccel data requires a listener to save every 100 reads. Since each read is of a new UserInfoDataListener, this list is not thread-safe. The read data will be printed out as JSON string and put into the list. It will judge whether the data stored in the list exceeds the limit and store it in the database and empty the list

public class UserInfoDataListener extends AnalysisEventListener<excel> { private static final int BATCH_COUNT = 100; List<excel> list = new ArrayList<>(); Public void invoke(Excel data) @override // Excel type // AnalysisContext AnalysisContext) {system.out.println (" Parse to a data :{}"+ json.tojsonString (data)); list.add(data); if (list.size() >= BATCH_COUNT) { saveData(); List list.clear(); }} @override public void doAfterAllAnalysed(AnalysisContext AnalysisContext) {// The last remaining data is stored in the database saveData();  System.out.println(" All data parsed complete! ") ); } private void saveData() {system.out.println ("{} data, start storing database!" +list.size()); //userService.save(list); System.out.println(" Database saved successfully!" ); }}Copy the code

Read method: The third argument to read is used by the listener to determine whether the data in the list is stored in the database

@Test
    public void read(){
        
        EasyExcel.read(PATH+"easy.xlsx",excel.class,new UserInfoDataListener()).sheet().doRead();
    }
Copy the code