Ali easyexcel is a toolkit for reading and writing Excel based on the package of poi, which mainly solves the problem of large memory occupied by reading large file poi, and also provides some simple excel operation API. But I found it on the Internet. There are few official explanations. The examples are all version 1. * and there are still some changes in version 2.0. So write a simple read-write example.

Introduce maven dependency

		<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.0.5</version>
		</dependency>

Write test code

Test idea: first define a List, write it to the local Excel file, and then read it out for printing, as follows:

    @Test
    public void testExcel() throws FileNotFoundException {
    	//Definition list
		List<Book> books = Arrays.asList(new Book("isbn-1", "SpringMVC"), new Book("isbn-2", "Mybatis"));

		//Write to excel
		ExcelUtil.writeListTo(new FileOutputStream("E:/testExcels/books_write.xlsx"),books,Book.class,Arrays.asList("isbn Number","Title"));

		//Read excel
		List<Book> readBooks = ExcelUtil.readListFrom(new FileInputStream("E:/testExcels/books_write.xlsx"), Book.class);

		System.out.println(readBooks);
	}

Book object

Excel property is a comment provided by easyexcel, which is used to correspond to excel columns.

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable {

    @ExcelProperty
    private String isbn;

    @ExcelProperty
    private String title;

}

Write to Excel

Simply write a Sheet, using the header of a single line

public static <T> void writeListTo(OutputStream os, List<T> data, Class<T> clz, List<String> simpleHead) {
		WriteSheet writeSheet = new WriteSheet();
		writeSheet.setClazz(clz);
		writeSheet.setNeedHead(true);

		List<List<String>> head = simpleHead.stream().map(a -> Arrays.asList(a)).collect(Collectors.toList());
		writeSheet.setHead(head);
		ExcelWriter write = EasyExcel.write(os).build();
		write.write(data, writeSheet);
		write.finish();
	}

Read Excel

easyexcel provides a SyncReadListener, which can read each row of data and put it into a List, and finally get it together.

	public static <T> List<T> readListFrom(InputStream is, Class<T> clz) {
		SyncReadListener tmpListener = new SyncReadListener();
		ReadSheet readSheet = new ReadSheet();
		readSheet.setClazz(clz);
		EasyExcel.read(is).registerReadListener(tmpListener).build().read(readSheet);
		return (List<T>) tmpListener.getList();
	}

extend

The above is done by simply reading and writing Excel according to Pojo object. If you need complex functions, you can define your own ReadListener when reading and more complex headers when writing.