Operating Excel to import and export is a very common requirement. A very useful tool was introduced earlier EasyPoi . Some readers suggested that in the case of a large amount of data, EasyPoi occupies a large memory and its performance is not good enough. Today, I recommend a better Excel import and export tool EasyExcel. I hope it will be helpful to you!
SpringBoot e-commerce project mall (50k+star) address: github.com/macrozheng/...
Introduction to EasyExcel
EasyExcel is an open source Excel import and export tool from Alibaba. It has the characteristics of fast processing, small memory consumption and convenient use. It has 22k+Star on Github, which shows that it is very popular.
EasyExcel reads 75M(46W rows and 25 columns) Excel. It only needs 64M memory, takes 20s, and the speed mode can be faster!
integrate
Integrating EasyExcel in SpringBoot is very simple and requires only one dependency.
<!--EasyExcel Correlation dependency-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
Copy codeuse
The use of EasyExcel and EasyPoi is very similar. They both control import and export through annotations. Next, we take the import and export of member information and order information as an example to realize the simple single table export and the complex export with one to many relationship respectively.
Simple export
We take the export of member information as an example to experience the export function of EasyExcel.
- First, create a Member object Member to encapsulate the Member information. Here, the annotation of EasyExcel is used;
/**
* Shopping member
* Created by macro on 2021/10/12.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
@ExcelProperty("ID")
@ColumnWidth(10)
private Long id;
@ExcelProperty("user name")
@ColumnWidth(20)
private String username;
@ExcelIgnore
private String password;
@ExcelProperty("nickname")
@ColumnWidth(20)
private String nickname;
@ExcelProperty("date of birth")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
private Date birthday;
@ExcelProperty("cell-phone number")
@ColumnWidth(20)
private String phone;
@ExcelIgnore
private String icon;
@ExcelProperty(value = "Gender", converter = GenderConverter.class)
@ColumnWidth(10)
private Integer gender;
}
Copy code- The above code uses the core annotation of EasyExcel. Let's understand the following respectively:
- @ExcelProperty: core annotation. The value property can be used to set the header name, and the converter property can be used to set the type converter;
- @ColumnWidth: used to set the width of table columns;
- @DateTimeFormat: used to set the date conversion format.
- In EasyExcel, if you want to convert an enumeration type to a string (for example, in the gender attribute, 0 - > male, 1 - > female), you need to customize the converter. The following is the implementation of the customized GenderConverter code;
/**
* excel Gender converter
* Created by macro on 2021/12/29.
*/
public class GenderConverter implements Converter<Integer> {
@Override
public Class<?> supportJavaTypeKey() {
//Object property type
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
//CellData property type
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
//CellData to object properties
String cellStr = context.getReadCellData().getStringValue();
if (StrUtil.isEmpty(cellStr)) return null;
if ("male".equals(cellStr)) {
return 0;
} else if ("female".equals(cellStr)) {
return 1;
} else {
return null;
}
}
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
//Object properties to CellData
Integer cellValue = context.getValue();
if (cellValue == null) {
return new WriteCellData<>("");
}
if (cellValue == 0) {
return new WriteCellData<>("male");
} else if (cellValue == 1) {
return new WriteCellData<>("female");
} else {
return new WriteCellData<>("");
}
}
}
Copy code- Next, we add an interface in the Controller to export the member list to excel. We also need to set the attribute of downloading Excel for the response header. The specific code is as follows;
/**
* EasyExcel Import and export test Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel Import and export tests")
@RequestMapping("/easyExcel")
public class EasyExcelController {
@SneakyThrows(IOException.class)
@ApiOperation(value = "Export member list Excel")
@RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
public void exportMemberList(HttpServletResponse response) {
setExcelRespProp(response, "Member list");
List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
EasyExcel.write(response.getOutputStream())
.head(Member.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("Member list")
.doWrite(memberList);
}
/**
* Set excel download response header attribute
*/
private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
}
}
Copy code- Run the project and test the interface through Swagger. Note that the access interface in Swagger cannot be downloaded directly. You need to click the download button in the return result. Access address: http://localhost:8088/swagger-ui/
- After downloading, check the next file. A standard Excel file has been exported.
Simple import
Next, let's take the import of member information as an example to experience the import function of EasyExcel.
- Add an interface for importing Member information in the Controller. Note that the @ RequestPart annotation is used to modify the file upload parameters, otherwise the upload button cannot be displayed in Swagger;
/**
* EasyExcel Import and export test Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel Import and export tests")
@RequestMapping("/easyExcel")
public class EasyExcelController {
@SneakyThrows
@ApiOperation("from Excel Import member list")
@RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
@ResponseBody
public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
List<Member> memberList = EasyExcel.read(file.getInputStream())
.head(Member.class)
.sheet()
.doReadSync();
return CommonResult.success(memberList);
}
}
Copy code- Then test the interface in Swagger and select the Excel file exported before. After successful import, the parsed data will be returned.
Complex export
Of course, EasyExcel can also realize more complex export, such as exporting an order list nested with commodity information. Let's implement it below!
Implementation using EasyPoi
We have used EasyPoi to realize this function before. Because EasyPoi originally supports the export of nested objects, it can be realized directly by using the built-in @ ExcelCollection annotation, which is very convenient and in line with the object-oriented idea.
Looking for solutions
Since EasyExcel itself does not support this one to many information export, we have to implement it by ourselves. Here is a common method I often use to quickly find solutions.
We can search directly from the issues of open source projects. For example, if we search the next one to many, we will directly find whether there is an issue that can export a more elegant one to many scheme.
From the reply from the issue, we can find that the project maintainer suggests creating a user-defined merge strategy to implement it. The brother who replied has given the implementation code. Next, we will use this scheme to implement it.
Solution ideas
Why can a custom cell merge policy export one to many list information? First, let's look at Excel that tiles nested data without merging and exporting.
After reading it, we can easily understand the solution. As long as we merge the columns that need to be merged in the columns with the same order ID, we can export this one to many nested information.
Implementation process
- First, we have to tile the original nested order commodity information and create a special export object OrderData, which contains order and commodity information. The secondary header can be realized by setting the value of @ ExcelProperty as an array;
/**
* Order export
* Created by macro on 2021/12/30.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class OrderData {
@ExcelProperty(value = "order ID")
@ColumnWidth(10)
@CustomMerge(needMerge = true, isPk = true)
private String id;
@ExcelProperty(value = "Order code")
@ColumnWidth(20)
@CustomMerge(needMerge = true)
private String orderSn;
@ExcelProperty(value = "Creation time")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
@CustomMerge(needMerge = true)
private Date createTime;
@ExcelProperty(value = "Receiving address")
@CustomMerge(needMerge = true)
@ColumnWidth(20)
private String receiverAddress;
@ExcelProperty(value = {"Commodity information", "Commodity code"})
@ColumnWidth(20)
private String productSn;
@ExcelProperty(value = {"Commodity information", "Trade name"})
@ColumnWidth(20)
private String name;
@ExcelProperty(value = {"Commodity information", "Product title"})
@ColumnWidth(30)
private String subTitle;
@ExcelProperty(value = {"Commodity information", "Brand name"})
@ColumnWidth(20)
private String brandName;
@ExcelProperty(value = {"Commodity information", "commodity price"})
@ColumnWidth(20)
private BigDecimal price;
@ExcelProperty(value = {"Commodity information", "Quantity of goods"})
@ColumnWidth(20)
private Integer count;
}
Copy code- Then convert the original nested Order object list into OrderData object list;
/**
* EasyExcel Import and export test Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel Import and export tests")
@RequestMapping("/easyExcel")
public class EasyExcelController {
private List<OrderData> convert(List<Order> orderList) {
List<OrderData> result = new ArrayList<>();
for (Order order : orderList) {
List<Product> productList = order.getProductList();
for (Product product : productList) {
OrderData orderData = new OrderData();
BeanUtil.copyProperties(product,orderData);
BeanUtil.copyProperties(order,orderData);
result.add(orderData);
}
}
return result;
}
}
Copy code- Then create a custom annotation CustomMerge to mark which attributes need to be merged and which is the primary key;
/**
* The user-defined annotation is used to determine whether to merge and the primary key of the merge
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {
/**
* Do you want to merge cells
*/
boolean needMerge() default false;
/**
* Whether it is a primary key, that is, the rows with the same field are merged
*/
boolean isPk() default false;
}
Copy code- Then create a custom cell merge strategy class CustomMergeStrategy. When the primary keys of two columns in Excel are the same, merge the columns marked to be merged;
/**
* Custom cell merge policy
*/
public class CustomMergeStrategy implements RowWriteHandler {
/**
* Primary key subscript
*/
private Integer pkIndex;
/**
* Subscript set of columns to be merged
*/
private List<Integer> needMergeColumnIndex = new ArrayList<>();
/**
* DTO data type
*/
private Class<?> elementType;
public CustomMergeStrategy(Class<?> elementType) {
this.elementType = elementType;
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
// If it is a title, return directly
if (isHead) {
return;
}
// Get current sheet
Sheet sheet = writeSheetHolder.getSheet();
// Get Title row
Row titleRow = sheet.getRow(0);
if (null == pkIndex) {
this.lazyInit(writeSheetHolder);
}
// Judge whether to merge with the previous line
// Cannot merge with header, only between data rows
if (row.getRowNum() <= 1) {
return;
}
// Get previous row data
Row lastRow = sheet.getRow(row.getRowNum() - 1);
// If the row and the previous row are the same type of data (judged by the primary key field), they need to be merged
if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
for (Integer needMerIndex : needMergeColumnIndex) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
needMerIndex, needMerIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
}
}
/**
* Initialize the primary key subscript and the subscript of the field to be merged
*/
private void lazyInit(WriteSheetHolder writeSheetHolder) {
// Get current sheet
Sheet sheet = writeSheetHolder.getSheet();
// Get Title row
Row titleRow = sheet.getRow(0);
// Gets the type of DTO
Class<?> eleType = this.elementType;
// Get all properties of DTO
Field[] fields = eleType.getDeclaredFields();
// Traverse all fields. Because excel is built based on DTO fields, the number of fields > = the number of columns in Excel
for (Field theField : fields) {
// Get the @ ExcelProperty annotation, which is used to get the subscript of the column corresponding to the field in excel
ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
// If it is blank, it means that the field does not need to be imported into excel, and the next field will be processed directly
if (null == easyExcelAnno) {
continue;
}
// Gets a custom annotation for merging cells
CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
// Default do not merge without @ CustomMerge annotation
if (null == customMerge) {
continue;
}
for (int index = 0; index < fields.length; index++) {
Cell theCell = titleRow.getCell(index);
// When the configuration does not require export, the returned is null. Here, make a judgment to prevent NPE
if (null == theCell) {
continue;
}
// Match the field with the header of excel
if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
if (customMerge.isPk()) {
pkIndex = index;
}
if (customMerge.needMerge()) {
needMergeColumnIndex.add(index);
}
}
}
}
// If no primary key is specified, an exception occurs
if (null == this.pkIndex) {
throw new IllegalStateException("use@CustomMerge Annotations must specify a primary key");
}
}
}
Copy code- Next, add an interface to export the order list in the Controller and register our custom merge strategy;
/**
* EasyExcel Import and export test Controller
* Created by macro on 2021/10/12.
*/
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel Import and export tests")
@RequestMapping("/easyExcel")
public class EasyExcelController {
@SneakyThrows
@ApiOperation(value = "Export order list Excel")
@RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
public void exportOrderList(HttpServletResponse response) {
List<Order> orderList = getOrderList();
List<OrderData> orderDataList = convert(orderList);
setExcelRespProp(response, "Order list");
EasyExcel.write(response.getOutputStream())
.head(OrderData.class)
.registerWriteHandler(new CustomMergeStrategy(OrderData.class))
.excelType(ExcelTypeEnum.XLSX)
.sheet("Order list")
.doWrite(orderDataList);
}
}
Copy code- Access interface test in Swagger and export Excel corresponding to order list;
- After downloading, check the file. Since EasyExcel needs to be implemented by itself, it is much more troublesome than using EasyPoi before.
Other use
Because the official document of EasyExcel is relatively simple, if you want to use it more deeply, I suggest you take a look at the official Demo.
summary
I have experienced an EasyExcel, which is very convenient to use and has excellent performance. However, the common one to many export implementation is more complex, and the function is not as good as that EasyPoi Powerful. If the amount of data exported from Excel is small, you can use EasyPoi. If the amount of data is large and you care about performance, you'd better use EasyExcel.
reference material
- Project address: github.com/alibaba/eas...
- Official documents: www.yuque.com/easyexcel/d...