1. Overview of Java operation Excel
1.1 overview of excel requirements
As a commonly used report file, excel operation function is often used in project development. At present, there are three common methods for Excel operation: POI of Apache, ExcelUtil of Hutool and EasyExcel of Ali.
1.2 comparison of three methods of Excel operation
Apache POI is relatively native, powerful, complex and cumbersome to use, and occupies a large amount of memory.
The ExcelUtil of Hutool is encapsulated on the basis of POI, which is simple to use and has incomplete functions. It is not available for some special operations, such as labeling.
Ali's EasyExcel is also encapsulated on the basis of POI, which is simple and easy to use, has relatively comprehensive functions, and has very strong performance. It is most recommended.
2. ApachePOIExcel
2.1 introduction to Apache poi
Apache POI [1] is a free, open source, cross platform Java API written in Java. Apache POI provides Java programs with the ability to read and write files in Microsoft Office format. POI is the acronym of "Poor Obfuscation Implementation", which means "concise fuzzy implementation".
2.2 ApachePOI functional structure
HSSF [1] － Provide reading and writing Microsoft Excel XLS Function of format file. XSSF [1] － Provide reading and writing Microsoft Excel OOXML XLSX Function of format file. HWPF [1] － Provide reading and writing Microsoft Word DOC Function of format file. HSLF [1] － Provide reading and writing Microsoft PowerPoint Function of format file. HDGF [1] － Provide read Microsoft Visio Function of format file. HPBF [1] － Provide read Microsoft Publisher Function of format file. HSMF [1] － Provide read Microsoft Outlook Function of format file.
2.3 description of apachepoi official website
POI official website address: https://poi.apache.org/index.html
POI download address: https://poi.apache.org/download.html
POI document description: https://poi.apache.org/apidocs/index.html
2.4 Apache POI implementation verification
Maven dependency configuration
<!-- poi relevant -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
Code test
package com.zrj.easyexcel.excel;
import com.google.common.collect.Lists;
import org.junit.Test;
import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Native Excel test class
*
* @author zrj
* @since 2021/12/29
**/
public class EasyExcelPoi {
@Test
public void createExcelTest() throws Exception {
//The base path of the file
String fileBasePath = System.getProperty("user.dir") + File.separator + "excel" + File.separator;
System.out.println("Base path of file:" + fileBasePath);
String filepath = fileBasePath + "writeexcel.xlsx";
String sheetName = "Template name";
List<String> titles = Lists.newArrayList("name", "sex", "age");
List<Map<String, Object>> values = Lists.newArrayList();
Map map2 = new HashMap(6);
map2.put("name", "Wang Wu");
map2.put("sex", "female");
map2.put("age", "20");
values.add(map2);
Map map3 = new HashMap(6);
map3.put("name", "ASA");
map3.put("sex", "female");
map3.put("age", "66");
values.add(map3);
ExcelUtils.writeExcel(filepath, sheetName, titles, values);
System.out.println("establish Excel complete");
}
@Test
public void copyExcelTest() throws Exception {
//The base path of the file
String fileBasePath = System.getProperty("user.dir") + File.separator + "excel" + File.separator;
System.out.println("Base path of file:" + fileBasePath);
String filepath = fileBasePath + "writeexcel.xlsx";
String fileNewPath = fileBasePath + "writeexcel2.xlsx";
ExcelUtils.writeExcel(filepath, fileNewPath);
System.out.println("copy Excel complete");
}
@Test
public void readExcelTest() throws Exception {
//The base path of the file
String fileBasePath = System.getProperty("user.dir") + File.separator + "excel" + File.separator;
String filepath = fileBasePath + "writeexcel.xlsx";
System.out.println("File path:" + fileBasePath);
String readExcel = ExcelUtils.readExcel(filepath);
System.out.println(readExcel);
System.out.println("read Excel complete");
}
}
3. HutoolExcel
3.1 introduction to hutool
Hutool is a small and comprehensive Java tool class library. It can reduce the learning cost of relevant API s and improve work efficiency through static method encapsulation, so that Java has the elegance of functional language and Java language can be "sweet".
The tools and methods in Hutool are carefully crafted by each user. It covers all aspects of the underlying code of Java development. It is not only a sharp tool to solve small problems in large-scale project development, but also an efficiency role in small-scale projects;
Hutool is a friendly alternative to the "util" package in the project. It saves developers' packaging time for public classes and public tool methods in the project, makes the development focus on business, and can avoid bug s caused by imperfect packaging to the greatest extent.
A small tool widely used in the project. It has no dependency and is very clean and easy to use.
Official website address: https://www.hutool.cn/
Reference documents: https://www.hutool.cn/docs/#/
API documentation: https://apidoc.gitee.com/dromara/hutool/
GITEE: https://gitee.com/dromara/hutool/
GITHUB: https://github.com/dromara/hutool/
Video introduction: https://player.bilibili.com/player.html?aid=710062843&bvid=BV1bQ4y1M7d9&cid=170377135&page=2
3.2 Hutool components
3.1 HutoolExcel implementation verification
Mave dependency configuration
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.18</version>
</dependency>
Code implementation verification
package com.zrj.easyexcel.excel;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.zrj.easyexcel.utils.FileUtils;
import org.junit.Test;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* HutoolExcel Test class
* Note: don't get the wrong package. The class names of easyexcel and hutool are the same!!!
*
* @author zrj
* @since 2021/12/29
**/
public class EasyExcelHutool {
/*******************************Excel Read - ExcelReader*******************************************/
/**
* 1. Read all rows and columns in Excel, which are represented by a list
*/
@Test
public void readExcelObjectTest() {
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeBeanKeyTest.xlsx";
//Creating an ExcelReader from a tool class
ExcelReader reader = ExcelUtil.getReader(filepath);
List<List<Object>> readAll = reader.read();
System.out.println("Excel Read results:" + readAll);
}
/**
* 2. Read as the Map list. By default, the first row is the title row. The key in the Map is the title and the value is the cell value corresponding to the title.
*/
@Test
public void readExcelMapTest() {
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeBeanKeyTest.xlsx";
//Creating an ExcelReader from a tool class
ExcelReader reader = ExcelUtil.getReader(filepath);
List<Map<String, Object>> readAll = reader.readAll();
System.out.println("Excel Read results:" + readAll);
}
/**
* 3. Read as the Bean list, the field name in the Bean is the title, and the field value is the cell value corresponding to the title.
*/
@Test
public void readExcelBeanTest() {
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeBeanKeyTest.xlsx";
//Creating an ExcelReader from a tool class
ExcelReader reader = ExcelUtil.getReader(filepath);
List<UserBean> readAll = reader.readAll(UserBean.class);
System.out.println("Excel Read results:" + readAll);
}
/*******************************Excel Generate - ExcelWriter*******************************************/
/**
* 1. Write out row and column objects to Excel
*/
@Test
public void writeExcelObjectTest() {
//Define a nested list. The elements of the list are also a list. An inner list represents a row of data, each row has four cells, and the final list object represents multiple rows of data.
List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeTest.xlsx";
//Create writer through tool class
ExcelWriter writer = ExcelUtil.getWriter(filepath);
//Create writer by construction method
//ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");
//Skip the current line, i.e. the first line, which is not required. It is used in this demonstration
writer.passCurrentRow();
//The title row after merging cells uses the default title style
writer.merge(row1.size() - 1, "Test title");
//Write out the content at one time and force the output of the title
writer.write(rows, true);
//Close the writer to free up memory
writer.close();
System.out.println("Creation completed, file path:" + filepath);
}
/**
* 2. Write out Map data
*/
@Test
public void writeExcelMapTest() {
Map<String, Object> row1 = new LinkedHashMap<>();
row1.put("full name", "Zhang San");
row1.put("Age", 23);
row1.put("achievement", 88.32);
row1.put("Is it qualified", true);
row1.put("Test date", DateUtil.date());
Map<String, Object> row2 = new LinkedHashMap<>();
row2.put("full name", "Li Si");
row2.put("Age", 33);
row2.put("achievement", 59.50);
row2.put("Is it qualified", false);
row2.put("Test date", DateUtil.date());
ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeMapTest.xlsx";
// Create writer through tool class
ExcelWriter writer = ExcelUtil.getWriter(filepath);
// The title row after merging cells uses the default title style
writer.merge(row1.size() - 1, "Class one report card");
// Write out the content at one time, use the default style, and force the output of the title
writer.write(rows, true);
// Close the writer to free up memory
writer.close();
System.out.println("Creation completed, file path:" + filepath);
}
/**
* 3. Write Bean data
*/
@Test
public void writeExcelBeanTest() {
UserBean bean1 = UserBean.builder().name("Zhang San").age(22).isPass(true).score(66.30).examDate(DateUtil.date()).build();
UserBean bean2 = UserBean.builder().name("Li Si").age(30).isPass(false).score(38.50).examDate(DateUtil.date()).build();
List<UserBean> rows = CollUtil.newArrayList(bean1, bean2);
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeBeanTest.xlsx";
// Create writer through tool class
ExcelWriter writer = ExcelUtil.getWriter(filepath);
// The title row after merging cells uses the default title style
writer.merge(4, "Class one report card");
// Write out the content at one time, use the default style, and force the output of the title
writer.write(rows, true);
// Close the writer to free up memory
writer.close();
System.out.println("Creation completed, file path:" + filepath);
}
/**
* 4. key alias of custom Bean (sort title)
* Note: the difference from 3 is that the title in 3 is in English and the title in 4 is a user-defined alias
*/
@Test
public void writeExcelBeanKeyTest() {
UserBean bean1 = UserBean.builder().name("Zhang San").age(22).isPass(true).score(66.30).examDate(DateUtil.date()).build();
UserBean bean2 = UserBean.builder().name("Li Si").age(30).isPass(false).score(38.50).examDate(DateUtil.date()).build();
List<UserBean> rows = CollUtil.newArrayList(bean1, bean2);
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeBeanKeyTest.xlsx";
// Create writer through tool class
ExcelWriter writer = ExcelUtil.getWriter(filepath);
//Custom title alias
writer.addHeaderAlias("name", "full name");
writer.addHeaderAlias("age", "Age");
writer.addHeaderAlias("score", "fraction");
writer.addHeaderAlias("isPass", "Pass or not");
writer.addHeaderAlias("examDate", "Examination time");
// By default, the attribute without alias will also be written out. If you want to write out only the field with alias, you can call this method to exclude it
writer.setOnlyAlias(true);
// The title row after merging cells uses the default title style
writer.merge(4, "Class one report card");
// Write out the content at one time, use the default style, and force the output of the title
writer.write(rows, true);
// Close the writer to free up memory
writer.close();
System.out.println("Creation completed, file path:" + filepath);
}
/**
* 5. Write to stream
*/
@Test
public void writeExcelStreamTest() {
//Define content
UserBean bean1 = UserBean.builder().name("Zhang San").age(22).isPass(true).score(66.30).examDate(DateUtil.date()).build();
UserBean bean2 = UserBean.builder().name("Li Si").age(30).isPass(false).score(38.50).examDate(DateUtil.date()).build();
List<UserBean> rows = CollUtil.newArrayList(bean1, bean2);
//Define output stream
ByteArrayOutputStream out = FileUtils.cloneInputStream(IoUtil.toUtf8Stream(rows.toString()));
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "writeStreamTest.xlsx";
if (!FileUtil.isFile(filepath)) {
FileUtil.touch(filepath);
}
//Create writer through tool class
ExcelWriter writer = ExcelUtil.getWriter();
//Create xlsx format
//ExcelWriter writer = ExcelUtil.getWriter(true);
//Write out the content at one time, use the default style, and force the output of the title
writer.write(rows, true);
//Out is OutputStream, which needs to be written out to the target stream
writer.flush(out);
//Close the writer to free up memory
writer.close();
System.out.println("Creation completed, file path:" + filepath);
}
/**
* 6. Write out to client download (write out to Servlet)
*/
@Test
public void writeExcelDownloadTest() {
//Define content
UserBean bean1 = UserBean.builder().name("Zhang San").age(22).isPass(true).score(66.30).examDate(DateUtil.date()).build();
UserBean bean2 = UserBean.builder().name("Li Si").age(30).isPass(false).score(38.50).examDate(DateUtil.date()).build();
List<UserBean> rows = CollUtil.newArrayList(bean1, bean2);
// The writer is created through the tool class, and the xls format is created by default
ExcelWriter writer = ExcelUtil.getWriter();
// Write out the content at one time, use the default style, and force the output of the title
writer.write(rows, true);
//Out is OutputStream, which needs to be written out to the target stream
//response is an HttpServletResponse object
//response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls is the file name of the pop-up download dialog box, which cannot be in Chinese. Please code it yourself in Chinese
//response.setHeader("Content-Disposition", "attachment;filename=test.xls");
//ServletOutputStream out = response.getOutputStream();
//writer.flush(out, true);
//Close the writer to free up memory
writer.close();
//Remember to close the output Servlet stream here
//IoUtil.close(out);
}
}
4. EasyExcel
4.1 introduction to easyexcel
EasyExcel is an open source Excel operation tool from Alibaba. It is simple to use, powerful, memory saving and powerful.
YuQue address: https://www.yuque.com/easyexcel/doc/easyexcel
Github address: https://github.com/alibaba/easyexcel
mvn dependent address: https://mvnrepository.com/artifact/com.alibaba/easyexcel
4.2 HutoolExcel implementation verification
Mave dependency configuration
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
Basic implementation case of EasyExcel
Github case address: https://github.com/alibaba/easyexcel
Project path: Test - > read / write - > readtest and WriteTest classes to realize various reading and writing functions of basic Excel.
EasyExcel batch add comments
ByteToInputStreamUtil
package com.zrj.easyexcel.excel;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
/**
* Input stream and byte conversion
*
* @author zrj
* @since 2021/12/27
**/
public class ByteToInputStreamUtil {
/**
* Byte to input stream
*
* @param buf
* @return java.io.InputStream
*/
public static InputStream byte2Input(byte[] buf) {
return new ByteArrayInputStream(buf);
}
/**
* Input stream byte
*
* @param inStream
* @return byte[]
*/
public static byte[] input2byte(InputStream inStream) throws Exception {
ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
byte[] buff = new byte[100];
int rc = 0;
while ((rc = inStream.read(buff, 0, 100)) > 0) {
swapStream.write(buff, 0, rc);
}
byte[] in2b = swapStream.toByteArray();
return in2b;
}
}
CommentWriteHandler
package com.zrj.easyexcel.excel;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* Annotation processor
*
* @author zrj
* @since 2021/12/29
**/
public class CommentWriteHandler extends AbstractRowWriteHandler {
/**
* sheet Name KEY
*/
public static final String SHEETNAME_NAME = "sheetName";
/**
* Document suffix
*/
private String extension;
/**
* Column index key
*/
public static final String COLINDEX_NAME = "colIndex";
/**
* Row index key
*/
public static final String ROWINDEX_NAME = "rowIndex";
/**
* Annotation content key
*/
public static final String COMMENTCONTENT_NAME = "commentContent";
/**
* sheet Page name list
*/
private List<String> sheetNameList;
/**
* Annotation collection
*/
List<Map<String, String>> commentList = new ArrayList<>(10);
/**
* CommentWriteHandler
*
* @param commentList
* @param extension
*/
public CommentWriteHandler(List<Map<String, String>> commentList, String extension) {
this.commentList = commentList != null && commentList.size() > 0
? commentList.stream().filter(x ->
x.keySet().contains(SHEETNAME_NAME) == true && x.get(SHEETNAME_NAME) != null && StrUtil.isNotBlank(x.get(SHEETNAME_NAME).toString())
&& x.keySet().contains(COLINDEX_NAME) == true && x.get(COLINDEX_NAME) != null && StrUtil.isNotBlank(x.get(COLINDEX_NAME).toString())
&& x.keySet().contains(ROWINDEX_NAME) == true && x.get(ROWINDEX_NAME) != null && StrUtil.isNotBlank(x.get(ROWINDEX_NAME).toString())
&& x.keySet().contains(COMMENTCONTENT_NAME) == true && x.get(COMMENTCONTENT_NAME) != null && StrUtil.isNotBlank(x.get(COMMENTCONTENT_NAME).toString())
).collect(Collectors.toList()) : new ArrayList<>();
sheetNameList = this.commentList.stream().map(x -> x.get(SHEETNAME_NAME).toString()).collect(Collectors.toList());
this.extension = extension;
}
/**
* Generate annotation information
*
* @param sheetName sheet Page name
* @param rowIndex Line number
* @param columnIndex Column number
* @param commentContent Annotation content
* @return
*/
public static Map<String, String> createCommentMap(String sheetName, int rowIndex, int columnIndex, String commentContent) {
Map<String, String> map = new HashMap<>();
//sheet page name
map.put(SHEETNAME_NAME, sheetName);
//Line number
map.put(ROWINDEX_NAME, rowIndex + "");
//Column number
map.put(COLINDEX_NAME, columnIndex + "");
//Annotation content
map.put(COMMENTCONTENT_NAME, commentContent);
return map;
}
/**
* Function description
* @param writeSheetHolder
* @param writeTableHolder
* @param row
* @param relativeRowIndex
* @param isHead
* @return void
*/
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
//You do not need to add comments, or the current sheet page does not need to add comments
if (commentList == null || commentList.size() <= 0 || sheetNameList.contains(sheet.getSheetName()) == false) {
return;
}
//Gets the annotation information for the current row
List<Map<String, String>> rowCommentList = commentList.stream().filter(x ->
StrUtil.equals(x.get(SHEETNAME_NAME).toString(), sheet.getSheetName())
&& relativeRowIndex == Integer.parseInt(x.get(ROWINDEX_NAME))).collect(Collectors.toList());
//The current line has no annotation information
if (rowCommentList == null || rowCommentList.size() <= 0) {
return;
}
List<String> colIndexList = rowCommentList.stream().map(x -> x.get(COLINDEX_NAME)).distinct().collect(Collectors.toList());
for (String colIndex : colIndexList) {
//Annotation information for the same cell
List<Map<String, String>> cellCommentList = rowCommentList.stream().filter(x ->
StrUtil.equals(colIndex, x.get(COLINDEX_NAME))).collect(Collectors.toList());
if (CollectionUtil.isEmpty(cellCommentList)) {
continue;
}
//Put together a comment
String commentContent = cellCommentList.stream().map(x -> x.get(COMMENTCONTENT_NAME)).collect(Collectors.joining());
Cell cell = row.getCell(Integer.parseInt(colIndex));
addComment(cell, commentContent, extension);
}
//Delete annotation information
commentList.remove(rowCommentList);
//Retrieve the sheet page name to be added
sheetNameList = commentList.stream().map(x -> x.get(SHEETNAME_NAME).toString()).collect(Collectors.toList());
}
/**
* Annotate Cell
*
* @param cell Cell
* @param value Annotation content
* @param extension Extension
*/
public static void addComment(Cell cell, String value, String extension) {
Sheet sheet = cell.getSheet();
cell.removeCellComment();
if ("xls".equals(extension)) {
ClientAnchor anchor = new HSSFClientAnchor();
// Key modifications
anchor.setDx1(0);
anchor.setDx2(0);
anchor.setDy1(0);
anchor.setDy2(0);
anchor.setCol1(cell.getColumnIndex());
anchor.setRow1(cell.getRowIndex());
anchor.setCol2(cell.getColumnIndex() + 5);
anchor.setRow2(cell.getRowIndex() + 6);
// end
Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(anchor);
// Enter annotation information
comment.setString(new HSSFRichTextString(value));
// Add comments to cell objects
cell.setCellComment(comment);
} else if ("xlsx".equals(extension)) {
ClientAnchor anchor = new XSSFClientAnchor();
// Key modifications
anchor.setDx1(0);
anchor.setDx2(0);
anchor.setDy1(0);
anchor.setDy2(0);
anchor.setCol1(cell.getColumnIndex());
anchor.setRow1(cell.getRowIndex());
anchor.setCol2(cell.getColumnIndex() + 5);
anchor.setRow2(cell.getRowIndex() + 6);
// end
Drawing drawing = sheet.createDrawingPatriarch();
Comment comment = drawing.createCellComment(anchor);
// Enter annotation information
comment.setString(new XSSFRichTextString(value));
// Add comments to cell objects
cell.setCellComment(comment);
}
}
}
EasyExcelUtils
package com.zrj.easyexcel.excel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.zrj.easyexcel.entity.DemoData;
import com.zrj.easyexcel.excel.read.ReadTest;
import com.zrj.easyexcel.excel.write.WriteTest;
import org.junit.Test;
import java.io.File;
import java.util.*;
/**
* EasyExcel Test class
*
* @author zrj
* @since 2021/12/29
**/
public class EasyExcelUtils {
/**
* EasyExcel Read write test class
*/
@Test
public void easyExcelReadWriteTest() {
//EasyExcel read test class
ReadTest readTest = new ReadTest();
//EasyExcel write test class
WriteTest writeTest = new WriteTest();
}
/**
* EasyExcel Batch add comments
*/
@Test
public void batchAddCommentTest() {
try {
//Build data
List<DemoData> demoDataList = new ArrayList<>(10);
demoDataList.add(DemoData.builder().string("Zhang San").date(new Date()).doubleData(3.14).build());
demoDataList.add(DemoData.builder().string("Wang Wu").date(new Date()).doubleData(6.68).build());
demoDataList.add(DemoData.builder().string("Zhao Liu").date(new Date()).doubleData(8.32).build());
demoDataList.add(DemoData.builder().string("Li Si").date(new Date()).doubleData(8.66).build());
//Define file path
String filepath = System.getProperty("user.dir") + File.separator + "excel" + File.separator + "EasyExcelCommentWriteTest3.xlsx";
String sheetName = "Annotation template";
List<Map<String, String>> commentList = new ArrayList<>();
commentList.add(CommentWriteHandler.createCommentMap(sheetName, 0, 1, "First comment."));
commentList.add(CommentWriteHandler.createCommentMap(sheetName, 1, 1, "Second comment."));
// Here, you need to specify which class to write, and then write to the first sheet with the name of template, and then the file stream will be closed automatically
// Note that inMemory must be set to true to support annotation. At present, there is no good solution not to process annotations in memory. This needs to be chosen by yourself.
EasyExcel.write(filepath, DemoData.class).inMemory(Boolean.TRUE)
.registerWriteHandler(new CommentWriteHandler(commentList, "xlsx"))
.sheet(sheetName).doWrite(demoDataList);
System.out.println("Annotation template completed, template address:" + filepath);
} catch (Exception e) {
e.printStackTrace();
}
}
}
5. Source code address
Test source address: https://gitee.com/rjzhu/opencode/tree/master/easyexcel