Communication Group 1 in QQ(Full): 662022184
Communication Group 2 in QQ(Full): 1097936804
Communication Group 3 in QQ(Full): 453928496
Communication Group 4 in QQ: 496594404
Communication Group 1 in DingTalk(Full): 21960511
Communication Group 2 in DingTalk(Full): 32796397
Communication Group 3 in DingTalk(Full): 33797247
Communication Group 4 in DingTalk(Full): 33491624
Communication Group 5 in DingTalk: 32134498
Official Website: https://yuque.com/easyexcel
There are several java frameworks or toolkit which can parse and generate Excel, such as Apache POI or jxl. But they all have some difficulties to handle problems like excessive memory usage. Apache POI framework has a set of SAX mode API can fix some memory overflow problems at some extent, but it still has some flaws. For example, the unzipping and the storage of the unzipping of Excel file in version 07 are done in memory, so the memory consumption is still very high. The EasyExcel toolkit rewrites the logic of POI for parsing Excel version 07. One 3 megabytes Excel file parsed with POI still requires about 100M memory, which can be reduced to a few megabyte by using EasyExcel instead. And yes, there is no memory overflow for even larger excel with EasyExcel. EasyExcel version 03 depends on POI SAX model and does model transformation/encapsulation in the upper layer to make it simpler and more convenient for users.
Using EasyExcel version 3.0.2+, a machine with 64M RAM can read a 75 megabyte Excel file containing 460,000 rows and 25 columns in 20 seconds
Of course, there is also a very fast mode can be faster, but the memory consumption will be a little more than 100M
- EasyExcel version 2+ works on Java7 or Java6
- EasyExcel version 3+ works on Java8 or java8+
- It is not recommended upgrading across major versions, especially across 2 major versions.
- There are some incompatibilities in upgrading from version 2+ to version 3+.
- Using a custom interceptor to modify the style can cause problems, even if it does not compile with errors.
- When reading the Excel file, the
invoke
function will throw an exception, there will not be an additional layer ofExcelAnalysisException
wrapped here, and it will not compile with errors. - Style and other annotations involving
boolean
or some enumeration values have been changed, adding the default value. The compiler will report an error, just change the annotation.
- It is recommended to re-test the relevant functions after upgrading across major versions.
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>
Alibaba New Retail Business Department sincerely recruit JAVA senior development, technical experts. If you are interested, you can contact us by WeChat, or send your Resume to my email jipengfei.jpf@alibaba-inc.com.
Anyone who wants to participate in this project can apply, mainly responsible for answering questions in the communication group and dealing with the issues. Of course, you can also do some PR.
Since there is no material reward for participating in the open source project, and the current maintainers are also maintaining the project in their spare time. So people who want to join this project need to be persistent, not just on a whim.
The requirements are as follows:
- There are certain java coding skills & good coding habits
- Understand the read&write principles of EasyExcel
- Has passion for open source projects
- Be able to do things consistently for a long time
- Your job is not so busy
姬朋飞(玉霄)、庄家钜、怀宇
/**
* The easiest way to read Excel file using EasyExcel toolkit
*
* <p>
* 1. Create an entity object, such as {@link DemoData}, each property of the entity object corresponds to a specific field in any row of Excel.
* 2. When reading each row of an Excel file, create a callback listener for the corresponding row. Refer to{@link DemoDataListener}
* 3. Invoke the read function
* </p>
*/
@Test
public void simpleRead() {
String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
// Specify which entity object class to use to read the Excel content. The file stream will close automatically after reading the first sheet of Excel.
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
/**
* The easiest way to write Excel file using EasyExcel toolkit
*
* <p>
* 1. Create an entity object, refer to{@link com.alibaba.easyexcel.test.demo.write.DemoData}.
* Each property of the entity object corresponds to a specific field of Excel
* 2. Invoke write function
* </p>
*/
@Test
public void simpleWrite() {
String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
// Specify which entity object class to use to write Excel, it will write to the first sheet of Excel with the name template. Then the file stream will be closed automatically.
// With version 03, just pass in the excelType parameter
EasyExcel.write(fileName, DemoData.class).sheet("template").doWrite(data());
}
/**
* File downloading
*
* Note: returns an Excel with partial data if it fails
*
* <p>
* 1. Create an entity object, refer to{@link DownloadData}.
* Each property of the entity object corresponds to a specific field of Excel
* 2. Specify the returned properties
* 3. Invoke write function, then the OutputStream is automatically closed when it ends.
* </p>
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// Using swagger may cause some problems, please use your browser directly or use postman to invoke this
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// URLEncoder.encode function can prevent Chinese garbled code
String fileName = URLEncoder.encode("test", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("template").doWrite(data());
}
/**
* File uploading
*
* <p>
* 1. Create an entity object, refer to{@link UploadData}
* Each property of the entity object corresponds to a specific field of Excel
* 2. When reading each row of an Excel file, create a callback listener for the corresponding row. Refer to{@link UploadDataListener}
* 3. Invoke read function
* </p>
*/
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
return "success";
}
If you have any questions, Alibaba colleagues can find me in DingTalk, and others can leave messages here. All related questions are well welcomed.