“This is the 14th day of my participation in the First Challenge 2022.
Hello, I’m looking at the mountains.
Export is a common function in the background. Excel file is a common export format.
In the Java stack, the common ones are JXL (currently renamed JExcel) and Apache POI. JXL was last updated in 2012, except for the shadow of the old system, there is almost no trace. At present, POI rules the world.
EasyExcel alibaba open source Excel export class library today, is the ENCAPSULATION of POI, to achieve a lot of advanced functions, and set aside an extension to support the extension of the customization function. To use a metaphor, POI is like Lego building blocks. Small building blocks can be assembled freely, and only those with strong hands-on skills can be assembled as they wish. EasyExcel is more like assembling these little blocks in advance. If you want to build a house, you can just find the assembled roof and walls. If only so, then EasyExcel is just a simple toolkit, its more attractive place is the control of memory, it by compressing files, batch reading, discarding unimportant data, file caching and other ways to reduce memory consumption.
Content is more, the article will only list the key code, want to complete the source code, you can pay attention to the public number “see the mountain hut” reply “easyExcel” to obtain.
The easiest way is to write a table
Before we begin, let’s define the base class that will be used throughout this article, where all functionality is a simple variant.
@Data
public class Item {
@excelProperty (" String title ")
private String string;
@excelProperty (" Date title ")
private Date date;
@excelProperty (" Number title ")
private Double doubleData;
@ExcelIgnore
private String ignore;
}
Copy the codeWrite a table to a worksheet based on a header object and a list
This is the simplest implementation, simply defining an object class and reading a list of data.
/** * with {@linkCom. Alibaba. Excel. Write. Builder. ExcelWriterSheetBuilder} automatically create {@linkCom. Alibaba. Excel. ExcelWriter} writing data. * <p> * Provides lists and functions as data sources */
public static void writeAutoWriter(a) {
final String fileName = defaultFileName("writeAutoWriter");
EasyExcelFactory.write(fileName)
.head(Item.class)
.sheet("Template")
.doWrite(WriteSample::sampleItems);
}
Copy the codeEasyExcel provides EasyExcelFactory class, API method is fluent way, can be silky smooth to achieve the generation of Excel files. If EasyExcelFactory feels too long, you can also write EasyExcel directly, which is a subclass of EasyExcelFactory, similar to an alias.
However, this way of defining an alias is Smell Code in some specifications, so choose according to your own or your company’s specifications.
Com. Alibaba. Excel. EasyExcelFactory# write (Java. Lang. String) method of parameters is the export file filename, if do not need to generate files, only need to create a file stream, can also be introduced to an output stream OutputStream, This allows for more flexibility in implementing the generation logic.
Com. Alibaba. Excel. Metadata. AbstractParameterBuilder# head () method is to define a header, as long as you pass in a class, will read this kind of all of the fields as a header. If the field on the com. Alibaba. Excel. The annotation. ExcelProperty annotations, defines the value, will take the value value as the header. For example, if value is an array, you can define more than one. If adjacent fields define the same table header, the cells will be merged, and the contents of the table body will select the contents of the first cell. This annotation can also define index, Order, Converter, and so on, as we’ll see below.
Com. Alibaba. Excel. Write. Builder. ExcelWriterBuilder# sheet () method to define the worksheet, there are multiple overloads, you can define sheetNo indicate what is a work table, can be introduced to indicate the working table name sheetName.
Com. Alibaba. Excel. Write. Builder. ExcelWriterSheetBuilder# doWrite () method is to write the excel file, the list of all incoming data, or use Java8 + : Supplier function. Paging writes can also be implemented, as shown below. This method will automatically close the file stream, which is really sweet.
The result is:
Write data to multiple worksheets based on header objects and lists
Above we write data to one worksheet, next we write data to multiple worksheets.
This example will cover more internal objects, such as ExcelWriter and WriteSheet.
/** * create {@linkCom. Alibaba. Excel. ExcelWriter}, and specify the write data sheet. * <p> * Provides lists and functions as data sources */
public static void writeManualWither(a) {
String fileName = defaultFileName("writeManualWriter");
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelFactory.write(fileName)
.head(Item.class)
.build();
final WriteSheet writeSheet1 = EasyExcelFactory.writerSheet("Template 1").build();
excelWriter.write(WriteSample::sampleItems, writeSheet1);
final WriteSheet writeSheet2 = EasyExcelFactory.writerSheet("Template 2").build();
excelWriter.write(sampleItems(), writeSheet2);
} finally {
// Don't forget that Finish helps close the stream
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeSince we want to write data across multiple worksheets, we can’t use the doWrite method directly.
Com. Alibaba. Excel. ExcelWriter class is excel writing object, used to create the excel workbook. Com. Alibaba. Excel. Write. Metadata. WriteSheet class is Sheet writing object, used to create the work Sheet table. Through com. Alibaba. Excel. ExcelWriter# write () method, specify the goal of writing data and written work table, can be achieved to the function of multiple work to write data in the table.
Note here that when we create the ExcelWriter object, we call the head() method to define the table header, which is the entire Excel definition, and the Sheet inherits this definition. This way, all worksheets in an Excel file have the same header. Don’t stop. Examples of different worksheets defining different table headers are shown below.
The result is:
Specify export columns by definition
In the background system, there is control of row and column permissions. Row permissions are realized through data rows, and only the row data with permissions can be exported. Column permissions, which can exclude columns that do not have permissions (usually hierarchical sensitive data) by exporting only those columns that do.
Sometimes you need to customize the export. If the table for exporting all columns is large, you can specify the columns to be exported as required.
Exclude specified columns
private static void writeExcludeColumn(a) {
String fileName = defaultFileName("writeExcludeColumn");
Set<String> excludeColumnFiledNames = new HashSet<>();
excludeColumnFiledNames.add("date");
EasyExcelFactory.write(fileName)
.head(Item.class)
.excludeColumnFiledNames(excludeColumnFiledNames)
.sheet("Template")
.doWrite(WriteSample::sampleItems);
}
Copy the codeThis requirement, need to use the com. Alibaba. Excel. Write. Builder. AbstractExcelWriterParameterBuilder# excludeColumnFiledNames method. This method is defined in the parent class of ExcelWriterBuilder, ExcelWriterSheetBuilder, and ExcelWriterTableBuilder. That is, the entire Excel workbook can exclude specified fields. It can also be a sheet that excludes specified fields, or a table that excludes specified fields.
ExcludeColumnFiledNames excludeColumnIndexes In addition to excludeColumnFiledNames excluding fields by field name, you can also use excludeColumnIndexes to exclude columns. If you want to control indexes, you need to define ExcelProperty on the field to specify the index attribute. This also makes it easier to fix field subscripts.
The result is:
Only the specified columns are exported
private static void writeIncludeColumn(a) {
String fileName = defaultFileName("writeIncludeColumn");
Set<String> includeColumnFiledNames = new HashSet<>();
includeColumnFiledNames.add("date");
EasyExcelFactory.write(fileName)
.head(Item.class)
.includeColumnFiledNames(includeColumnFiledNames)
.sheet("Template")
.doWrite(WriteSample::sampleItems);
}
Copy the codeThis requirement, need to use the com. Alibaba. Excel. Write. Builder. AbstractExcelWriterParameterBuilder# includeColumnFiledNames method. ExcludeColumnFiledNames are similar to excludeColumnFiledNames in that they can be hierarchical and used in the same way but in reverse.
Similarly, the includeColumnIndexes method can be used to specify columns by specifying field subscripts.
The result is:
header
The definition of header is critical and directly affects the quality of Excel files. Therefore, EasyExcel provides a richer method for defining table headers.
The original header
A new base class is defined: EmptyItem, distinguished from Item by removing the ExcelProperty definition.
@Data
public class EmptyItem {
private String string;
private Date date;
private Double doubleData;
}
Copy the codeIn this case, the field of the EmptyItem object is used directly as the header name.
private static void writeNoAnnotation(a) {
final String fileName = defaultFileName("writeNoAnnotation");
EasyExcelFactory.write(fileName)
.head(EmptyItem.class)
.sheet("Template")
.doWrite(WriteSample::sampleItems);
}
Copy the codeHere you can see that EasyExcel for table header object, table body list object, there is no mandatory requirements must be the same object, as long as the field is consistent, can normally assemble data.
The result is:
Custom table headers
Using fields as table headers is obviously not what we want, and EasyExcel provides the ExcelProperty annotation to define the name of the table header. The annotation also provides the index and order attributes, which define the position and order of the columns.
@Data
public class IndexItem {
@excelProperty (value = "string title ", index = 1)
private String string;
@excelProperty (value = "date title ", index = 3)
private Date date;
@excelProperty (value = "number ", index = 5)
private Double doubleData;
}
Copy the codeIt’s also easy to use:
private static void writeWithIndex(a) {
final String fileName = defaultFileName("writeWithIndex");
EasyExcelFactory.write(fileName)
.head(IndexItem.class)
.sheet("Template")
.doWrite(WriteSample::sampleItems);
}
Copy the codeThe result is:
It is important to note that when using the ExcelProperty annotation, index indicates the column in which the field is placed and order indicates the order.
Depending on the semantics of index and order, the control for both is different. If the index is the same, an exception is thrown because the program cannot determine which column is in which. If the index value has a blank number in the middle, a blank column will appear. If order and index are used together, index takes precedence and order does the sorting. If index=-1, use the Java default sort. The lower the order value, the higher the column.
The value attribute of ExcelProperty is an array of strings, which is equivalent to a field that can define multiple headers. At the same time, if the definitions of adjacent columns are identical, they are also collated and juxtaposed. Such as:
@Data
public class ComplexHeadItem {
@excelProperty ({" header ", "string header "})
private String string;
@excelProperty (" Date title ")
private Date date;
@excelProperty ({" header ", "number header 0"})
private Double doubleData;
@excelProperty ({" number title "})
private Double doubleData1;
@excelProperty ({" number title "})
private Double doubleData2 = 0.0;
}
Copy the codeThe result is:
Dynamic table header, table body
In the previous examples, the header definition is passed in to an object and extracted by parsing the object’s property field. EasyExcel provides dynamic table header functions when there is no way to define the table header object in advance, or the table header changes dynamically according to conditions, permissions, etc.
/** * dynamic header, passed in {@codeList<List<String>>} format data. * <p> * Multiple headers can be implemented. * /
private static void writeDynamicHead(a) {
String fileName = defaultFileName("writeDynamicHead");
EasyExcelFactory.write(fileName)
.head(dynamicHead())
.sheet()
.doWrite(sampleItems());
}
private static List<List<String>> dynamicHead() {
List<List<String>> heads = new ArrayList<>();
final List<String> head0 = new ArrayList<>(Arrays.asList("0"."String title [dynamic]"));
heads.add(head0);
final List<String> head1 = new ArrayList<>(Arrays.asList("0"."Date title [Dynamic]"));
heads.add(head1);
final List<String> head2 = new ArrayList<>(Collections.singletonList("Digital title [dynamic]"));
heads.add(head2);
return heads;
}
Copy the codeUsing com. Alibaba. Excel. Metadata. AbstractParameterBuilder# head (Java. Util. List < Java. Util. List < Java. Lang. String > >) method, Pass in List
>.
The result is:
Of course, this is not a complete dynamic. We can use com. Alibaba. Excel. Write. Builder. ExcelWriterSheetBuilder# doWrite (Java. Util. Collection
implement dynamic table body. The code is as follows:
private static void writeDynamicData(a) {
String fileName = defaultFileName("writeDynamicData");
EasyExcelFactory.write(fileName)
.head(dynamicHead())
.sheet()
.doWrite(dynamicData());
}
private static List<List<Object>> dynamicData() {
List<List<Object>> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<Object> data = new ArrayList<>();
data.add("String" + i);
data.add(new Date());
data.add((i + 1) * 0.1);
list.add(data);
}
return list;
}
Copy the codeThe result is:
Header internationalization
The Internet knows no borders, and in many cases, we need to be international. At this point, we can use the dynamic table header function to pass in different table header definitions and generate different Excel files. Sometimes, we also need to define the format of the table body in advance, which can be done with dynamic table bodies. Is there an easier way? There must be.
Define the formatting header object first:
@Data
public class FormatContentItem {
@ ExcelProperty (value = "title" string, the converter = TitleFormatConverter. Class)
private String string;
@datetimeformat (" YYYY ")
@excelProperty (value = "date title ")
private Date date;
@ NumberFormat (" 0.000% ")
@excelProperty (" Number title ")
private Double doubleData;
}
Copy the codeThen realize internationalization:
/** * the head argument can be set at the same time: * {@link AbstractParameterBuilder#head(java.util.List)}
* {@linkAbstractParameterBuilder#head(java.lang.class)} * <p> *@linkAbstractParameterBuilder#head(java.util.list)} in this case, we can implement an internationalized configuration. * /
private static void writeDynamicMultiHead(a) {
String fileName = defaultFileName("writeDynamicMultiHead");
EasyExcelFactory.write(fileName)
.head(dynamicHead())
.head(FormatContentItem.class)
.sheet()
.doWrite(sampleItems());
}
Copy the codeThe result is:
Many times to write
Generally speaking, the amount of data in the middle and background is not small, sometimes it is necessary to export hundreds of thousands of lines of data at a time, or even more, and this operation is not normal, if the memory configuration is relatively large, the memory is idle most of the time. At this point, data can be written in batches with EasyExcel’s file caching capabilities.
Write multiple times to the same Excel and sheet
The first is the more common scenario of writing data in batches to a sheet.
private static void writeOneSheet(a) {
String fileName = defaultFileName("writeOneSheet");
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelFactory.write(fileName)
.head(Item.class)
.build();
final WriteSheet writeSheet = EasyExcelFactory.writerSheet("Template").build();
for (int i = 0; i < 5; i++) { excelWriter.write(sampleItems(), writeSheet); }}finally {
// Don't forget that Finish helps close the stream
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeFirst define ExcelWriter to manipulate the entire Excel workbook, then define the ExcelWritersheet, Is the next cycle call com. Alibaba. Excel. ExcelWriter. Write (Java. Util. Collection
, com. Alibaba. Excel. Write. Metadata. WriteSheet) method, the data written to the specified work sheet in the table. Here it is important to note that the last must call com. Alibaba. Excel. ExcelWriter. Finish method, said stop writing and close the flow.
The result is:
Write multiple times to different sheets in the same Excel (same header)
Another scenario is to export annual order data on a monthly basis, with a sheet for each month. In this way, the table header is the same. Based on our experience above, we need to define multiple WriteSheet worksheet objects, and then write data to that object.
There may also be cases where we need to switch between multiple WriteSheet objects for writing, and specify sheetNo to avoid looking for the wrong object.
The code is as follows:
private static void writeDiffSheetWithSameHead(a) {
String fileName = defaultFileName("writeDiffSheetWithSameHead");
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelFactory.write(fileName)
.head(Item.class)
.build();
for (int i = 0; i < 5; i++) {
final WriteSheet writeSheet = EasyExcelFactory.writerSheet(i, "Template"+ i) .build(); excelWriter.write(sampleItems(), writeSheet); }}finally {
// Don't forget that Finish helps close the stream
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeThe result is:
Write multiple times to different sheets in the same Excel (different headers)
Another scenario is to export order information for a specified time range, with one sheet containing specific product information and the other sheet containing order delivery address information. This scenario requires a different header, and as we learned earlier, we just need to specify a different header object when we define the WriteSheet object.
The code is as follows:
private static void writeDiffSheetWithDiffHead(a) {
String fileName = defaultFileName("writeDiffSheetWithDiffHead");
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcelFactory.write(fileName)
.build();
final WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0."Template 1")
.head(Item.class)
.build();
excelWriter.write(sampleItems(), writeSheet0);
final WriteSheet writeSheet1 = EasyExcelFactory.writerSheet(1."Template 2")
.head(ComplexHeadItem.class)
.build();
excelWriter.write(sampleItems(), writeSheet1);
} finally {
// Don't forget that Finish helps close the stream
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeThe result is:
Realize the table
EasyExcel divides Excel file definitions into three layers,
- Workbooks, otherwise known as Excel files
- A worksheet, which corresponds to a Sheet in an Excel file
- A table is a combination of a header and a body in a Sheet
The function in question is to create multiple tables in a Sheet.
Create a table in the same form
This is an appetizer to show you how to specify the table individually.
private static void writeTable(a) {
String fileName = defaultFileName("writeTable");
final ExcelWriter excelWriter = EasyExcelFactory.write(fileName)
.head(Item.class)
.build();
try {
// Set the sheet so that it does not need a header otherwise it will print the header of the sheet so that it looks like the first table has 2 heads
WriteSheet writeSheet = EasyExcelFactory.writerSheet()
.needHead(Boolean.FALSE)
.build();
Table inherits the configuration of sheet. If sheet is configured, it does not need the header. By default, table does not need the header
WriteTable writeTable0 = EasyExcelFactory.writerTable(0)
.needHead(Boolean.TRUE)
.build();
excelWriter.write(sampleItems(), writeSheet, writeTable0);
} finally {
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeAnd you can see, in addition to ExcelWriter and WriteSheet, we’re also using WriteTable, which is the write object for the table. With this object, we can create multiple tables in a single Sheet by simply creating a few more.
The result is:
As in the previous example, all roads lead to Rome.
Create different tables in the same form (same table header)
With that in mind, let’s go straight to code:
private static void writeTables(a) {
String fileName = defaultFileName("writeTables");
final ExcelWriter excelWriter = EasyExcelFactory.write(fileName)
.build();
try {
// Set the sheet so that it does not need a header otherwise it will print the header of the sheet so that it looks like the first table has 2 heads
WriteSheet writeSheet = EasyExcelFactory.writerSheet()
.head(Item.class)
.needHead(Boolean.FALSE)
.build();
Table inherits the configuration of sheet. If sheet is configured, it does not need the header. By default, table does not need the header
WriteTable writeTable0 = EasyExcelFactory.writerTable(0)
.needHead(Boolean.TRUE)
.build();
WriteTable writeTable1 = EasyExcelFactory.writerTable(1)
.needHead(Boolean.TRUE)
.build();
// The first write creates a header
excelWriter.write(sampleItems(), writeSheet, writeTable0);
// The second write also creates the header and writes data after the first
excelWriter.write(sampleItems(), writeSheet, writeTable1);
} finally {
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeBecause we use the same header, we can directly define the header object in WriteSheet, and by inheritance, the two tables have the same header. It is important to note that when WriteSheet is built, the needHead(Boolea.false) is set. If it is not set or is set to true, the first table will have two headers.
The result is:
Create different tables in the same form (different table headers)
We only need to set different headers for our WriteTable object:
private static void writeTablesWithDiffHead(a) {
String fileName = defaultFileName("writeTablesWithDiffHead");
final ExcelWriter excelWriter = EasyExcelFactory.write(fileName)
.build();
try {
WriteSheet writeSheet = EasyExcelFactory.writerSheet()
.build();
WriteTable writeTable0 = EasyExcelFactory.writerTable(0)
.head(Item.class)
.build();
excelWriter.write(sampleItems(), writeSheet, writeTable0);
WriteTable writeTable1 = EasyExcelFactory.writerTable(1)
.head(ComplexHeadItem.class)
.build();
excelWriter.write(sampleItems(), writeSheet, writeTable1);
} finally {
if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the codeThe result is:
Write to template file (not fill)
Sometimes we need to export data from a template that has a fixed style and no dynamic data. For example, the first few lines are headings, permission claims, responsibility claims, and so on, followed by list data. If spelling the data by hand is cumbersome, EasyExcel provides a way to write to a template file. First look at the code:
private static void writeByTemplate(a) {
String fileName = defaultFileName("writeByTemplate");
String templateFile = getPath() + File.separator + "template_write_after_fill.xlsx";
EasyExcelFactory.write(fileName)
.withTemplate(templateFile)
.head(Item.class)
.sheet()
.doWrite(sampleItems());
}
Copy the codeYou need to use the com. Alibaba. Excel. Write. Builder. ExcelWriterBuilder# withTemplate (Java. Lang. String) specified template file path. The withTemplate method has several overloaded implementations:
- Specifies the path to the template file
ExcelWriterBuilder#withTemplate(java.lang.String) - Specifies the template file object
ExcelWriterBuilder#withTemplate(java.io.File) - Specifies the template file input stream
ExcelWriterBuilder#withTemplate(java.io.InputStream)
Specify that both template files and template file objects operate on files and require file information.
Specifying the template file input stream is as long as the file stream, the maneuverability space is relatively large. For example, if a template file is mutable, we can initialize the template file using a fill write, based on a template file with variables, and then write to the list using a template write. (This will be explained in more detail in tips)
The contents of the template file in the code:
Contents of the exported file:
As you can see, the list is appended in this way. The original template content will not be modified, and the list information will be written from the first blank line.
At the end of the article to summarize
This article from the actual combat point of view said EasyExcel if the realization of writing tables, the next will explain how to better look to write.
Green hills never change, green waters always flow. See you next time.
Recommended reading
- This article describes 24 operations for Java8 Stream Collectors
- Java8 Optional 6 kinds of operations
- Use Lambda expressions to achieve super sorting functions
- Java8 Time Library (1) : Describes the time class and common apis in Java8
- Java8 time library (2) : Convert Date to LocalDate or LocalDateTime
- Java8 Time Library (3) : Start using Java8 time classes
- Java8 time library (4) : check if the date string is valid
- New features in Java8
- New features in Java9
- New features in Java10
- Optimization of access control based on nested relationships in Java11
- New features for Java11
- New features in Java12
Hello, I’m looking at the mountains. Swim in the code, play to enjoy life. If this article is helpful to you, please like, bookmark, follow.