1. Introduction to EasyExcel
1.1 Procedure Introduction
The most famous frameworks for Java parsing and generating Excel include Apache poi and jxl. But they all have a serious problem, which is that it consumes very memory. Poi has a set of SAX-mode APIs that can solve some memory overflow problems to a certain extent, but POI still has some defects, such as the decompression and storage after decompression of Excel version 07 are all completed in memory, and the memory consumption is still very large. EasyExcel rewritten poi's analysis of version 07 Excel. It can use POI sax for 3M excel to still require about 100M of memory to reduce it to several M. No matter how large the excel is, it will not overflow memory. Version 03 depends on the POI sax mode. The model conversion package is made on the upper layer, making it easier and more convenient for users.
EasyExcel is an excel processing framework open source by Alibaba.It is known for its simplicity of use and memory saving.
The main reason why easyExcel can greatly reduce memory usage is that when parsing Excel, the file data is not loaded into memory at once, but reads data from lines on the disk and parses one by one.
Memory problem: POI = 100w load into memory OOM first. . Write to the file and easyExcel is a line-by-line completion
EasyExcel's GitHub address:GitHub - alibaba/easyexcel: a fast, concise and java processing Excel tool for solving memory overflow from large files
EasyExcel's official documentation:EasyExcel (the document has been migrated) · Yuque
1.2 Excel format analysis
- xls is the file storage format of excel before Microsoft Excel 2007. The implementation principle is based on Microsoft's ole db, an implementation of Microsoft's com components. It is essentially a micro database. Since many Microsoft's things are not open source, they have also been eliminated. Understanding the details of it is not very meaningful. The underlying programming is developed based on Microsoft's com components.
- xlsx is the file storage format of excel after Microsoft Excel 2007, and its implementation is based on openXml and zip technology. This kind of storage is simple, safe and convenient to transmit, and also makes it simple to process data.
- We can understand csv as a plain text file and can be opened by excel. Its format is very simple, and it is the same as parsing text files.
2. Quick Start
Dependencies required by EasyExcel:
<dependency>
<groupId></groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>fastjson</artifactId>
<version>1.2.71</version>
</dependency>After we introduce EasyExcel dependencies, we need to pay attention to the following issues, because EasyExcel has already integrated many dependencies and contains POI dependencies:
2.1 Write to Excel
First we need to create an entity class to map to the object we will fill in in Excel
@Data
public class DemoData {
@ColumnWidth(20)
@ExcelProperty("String Title")
private String string;
@ExcelProperty("Date Title")
@DateTimeFormat("yyyyy year MM month dd day HH hour mm minute ss seconds")
private Date date;
@ExcelProperty("Number Title")
@NumberFormat("#.##")
private Double doubleData;
/**
* Ignore this field
*/
@ExcelIgnore
private String ignore;
}Error resolution
/questions/7703338
Attribute annotation
@ExcelProperty(""): Used to annotate the title of the field in Excel
@ExcelIgnore: Used to indicate that the field is ignored and does not need to be added to Excel
@DateTimeFormat Date Format Convert
@NumberFormat numerical format conversion
Style annotation
@ColumnWidth sets the width of a certain column of cells, such as 20
@ContentFontStyle, @HeadFontStyle Set the font style of a certain cell
//Font
String fontName() default "";
//Font size (short) 10
short fontHeightInPoints() default -1;
//Italic
boolean italic() default false;
//Delete line
boolean strikeout() default false;
//Font color, look up in the following class
//.COLOR_NORMAL
//.COLOR_RED
//
//
short color() default -1;
short typeOffset() default -1;
//Underline
//Font#U_NONE
//Font#U_SINGLE
//Font#U_DOUBLE
//Font#U_SINGLE_ACCOUNTING
//Font#U_DOUBLE_ACCOUNTING
byte underline() default -1;
//Character Set
//FontCharset
//Font#ANSI_CHARSET
//Font#DEFAULT_CHARSET
//Font#SYMBOL_CHARSET
int charset() default -1;
//Bold
boolean bold() default false;
//Example
@ContentFontStyle(fontName = "Microsoft Yahei",
fontHeightInPoints = 20,
italic = true,
strikeout = true,
color = 10,
bold = true)
private String name;@ContentRowHeight The row height of all data rows
short value() default -1;
@ContentRowHeight(40)
//Example
public class User {
}@ContentStyle, @HeadStyle Set the style of the content row cell
//Poi's dataformat function, see the BuiltinFormats class parameters
short dataFormat() default -1;
// Whether to hide
boolean hidden() default false;
// Whether to lock
boolean locked() default false;
//quotePrefix
boolean quotePrefix() default false;
//Center the level
HorizontalAlignment horizontalAlignment() default;
//Package
boolean wrapped() default false;
//Center vertically
VerticalAlignment verticalAlignment() default;
//Content rotation angle, value 0-180
short rotation() default -1;
//frame
BorderStyle borderLeft() default;
BorderStyle borderRight() default;
BorderStyle borderTop() default;
BorderStyle borderBottom() default;
//Border color
short leftBorderColor() default -1;
short rightBorderColor() default -1;
short topBorderColor() default -1;
short bottomBorderColor() default -1;
//Set the foreground color fill type
//To modify the background color of Excel, the following two values must be set at the same time.
//@ContentStyle(fillForegroundColor = 10,fillPatternType = FillPatternType.SOLID_FOREGROUND)
FillPatternType fillPatternType() default FillPatternType.NO_FILL;
//Foreground color
short fillForegroundColor() default -1;
//Background color (basically not used)
short fillBackgroundColor() default -1;
//Controll whether the cell should automatically adjust the content size to suit the situation where the text is too long
boolean shrinkToFit() default false;@HeadRowHeight The row height of the header row
short value() default -1;
@HeadRowHeight(40)
public class User {
}In this way, our data writing is completed. After running the code, we can see that the easyexcel file has been generated under our project path.
Simple reading
private static String PATH = ("") + + "files";
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
("String" + i);
(new Date());
(0.56);
(data);
}
return list;
}
/**
* The easiest writing
* <p>1. Create an entity object corresponding to excel. Refer to {@link DemoData}
* <p>2. Just write it directly
*/
@Test
public void simpleWrite() {
// Writing method 1
// String fileName = PATH+ + "";
// // Here you need to specify which class to write to, and then write to the first sheet, the name is the template, and the file flow will be automatically closed
// // If you want to use 03 here, then pass in the excelType parameter
// (fileName, )
// .sheet("Template")
// .doWrite(data());
// Writing method 2
String fileName = PATH+ + "";
// Here you need to specify which class to use to write
ExcelWriter excelWriter = null;
try {
excelWriter = (fileName, ).build();
WriteSheet writeSheet = ("Template").build();
(data(), writeSheet);
} finally {
// Don't forget that finish will help close the stream
if (excelWriter != null) {
();
}
}
}More
Write an example
2.2 Read Excel
First we need to create a listener:
// There is a very important point. DemoDataListener cannot be managed by spring. You need to read excel every time. Then, use spring to construct the method to pass it in.
public class DemoDataListener extends AnalysisEventListener<DemoData> {
private static final Logger LOGGER = ();
/**
* Every 5 databases can be stored, and 3,000 can be stored in actual use, and then clean the list to facilitate memory recycling.
*/
private static final int BATCH_COUNT = 5;
List<DemoData> list = new ArrayList<DemoData>();
/**
* Suppose this is a DAO, of course, this can also be a service with business logic. Of course, it would be useless if you don't need to store this object.
*/
private DemoDAO demoDAO;
public DemoDataListener() {
// Here is a demo, so just new one. If you have spring, please use the parameter constructor below.
demoDAO = new DemoDAO();
}
/**
* If spring is used, please use this constructor. Every time you create a Listener, you need to pass the spring managed class in.
*
* @param demoDAO
*/
public DemoDataListener(DemoDAO demoDAO) {
= demoDAO;
}
/**
* Every data analysis will be called
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
((data));
("Submit to a data: {}", (data));
(data);
// It has reached BATCH_COUNT, and it is necessary to store the database once to prevent tens of thousands of pieces of data from being in memory, making it easy to OOM
if (() >= BATCH_COUNT) {
saveData();
// Storage is cleaned up list
();
}
}
/**
* All data parsing will be called
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// You must also save the data here to ensure that the last remaining data is also stored in the database
saveData();
("All data parsing is completed!");
}
/**
* Plus storage database
*/
private void saveData() {
("{} pieces of data, start storing the database!", ());
(list);
("Storage database successfully!");
}
}After that, we need to create a DAO function according to our needs, which is actually similar to our service layer. We can define the methods of related operations related to the database that we may add later.
/**
* Assume this is your DAO storage. Of course, this class is also required for spring to manage. Of course, you don’t need storage, nor do you need this class.
**/
public class DemoDAO {
public void save(List<DemoData> list) {
// If it is mybatis, try not to call insert multiple times. Write a mapper and add a new method batchInsert to insert all data at once.
}
}After the creation is completed, our functions are basically ready, and we can test it later:
/**
* The easiest reading
* <p>1. Create an entity object corresponding to excel. Refer to {@link DemoData}
* <p>2. Since the default line of reading excel is to be used, you need to create a line of excel callback listener, refer to {@link DemoDataListener}
* <p>3. Just read it directly
*/
@Test
public void simpleRead() {
// There is a very important point. DemoDataListener cannot be managed by spring. You need to read excel every time. Then, use spring to construct the method to pass it in.
// Writing method 1:
String fileName = PATH+ "";
// Here you need to specify which class to use to read, and then the first sheet file stream will be automatically closed
(fileName, , new DemoDataListener()).sheet().doRead();
// // Writing 2:
// String fileName = PATH+ "";
// ExcelReader excelReader = null;
// try {
// excelReader = (fileName, , new DemoDataListener()).build();
// ReadSheet readSheet = (0).build();
// (readSheet);
// } finally {
// if (excelReader != null) {
// // Don't forget to close it here. Temporary files will be created when reading, and the disk will crash at that time.
// ();
// }
// }
}specific
Write a case
2.3 Fill Excel template
EasyExcel has provided a more powerful table filling function since 2.1.1. It provides a template file and writes some template parameters to quickly generate it.
Write templates -> Pass the absolute path to the template -> Render data
(1) Template
Fill in the template file with {. attribute name} of the Excel entity class, and the style will also be copied. Note: You need to bring a dot when filling the List, if it is single, it is not needed. as follows:
| Name | age | other |
|---|---|---|
| {.name} | {.age} | To display braces, you can use backslash translation\{ |
(2) API: Fill the list
//The API is the same as writing to Excel, with multiple fill
public ExcelWriterBuilder withTemplate(InputStream templateInputStream) {}
public ExcelWriterBuilder withTemplate(File templateFile) {}
public ExcelWriterBuilder withTemplate(String pathName) {}
public void doFill(Object data) {}
public void doFill(Object data, FillConfig fillConfig) {}
//Example
(()).withTemplate(templateFilePath).sheet().doFill(list)