Surprised! 7 lines of code for elegant Excel file generation & download function

Welcome to pay attention to personal wechat public number: Xiaoxue Java

Personal website: www.exception.site/essay/how-t…

directory

• One, foreword

• Second, Apache POI, JXL defects

• Third, Ali produced EasyExcel, amway wave

• What does EasyExcel solve

• 5. Get started quickly

• Support for special scenarios

• Web download sample code

• 8. Points needing attention

• Nine,

One, foreword

Exporting Excel files is a feature that most services need to integrate. So how do you implement this gracefully and quickly (and lazily)?

Your first thought might be: Isn’t that easy? Apache open source framework POI, or JXL can be implemented. Facing Baidu programming, the code template copy down, according to their own business to change, can be difficult?

HMM.. It’s not hard, but your code might look like this:

Does this code look long and smelly? Today, Ha will show you how to use 7 lines of code to create Excel files!

Second, Apache POI, JXL defects

Before we talk about how to do that, let’s discuss the limitations of traditional Excel frameworks! In addition to the fact that both Apache POI and JXL are not simple, elegant and fast enough to generate Excel files, they also have a serious problem: they consume a lot of memory, and sometimes run out of memory.

POI is by far the most widely used Excel parsing framework, but it’s not perfect.

Why do you say that?

Most developers use poIS using their userModel schema. The advantage of userModel is that it is easy to get started and simple to use, just copy the code to run, and the rest is to write the business transformation. Although the transformation also needs to write hundreds of lines of code, but it is still controllable.

However, the biggest problem with the userModel model is that it is very expensive in memory, a few megabytes of file parsing can even use hundreds of megabytes of memory. The reality is that many applications are now using this mode, the reason why it is still normal to run because there is not a lot of concurrency, after the concurrency, must be OOM or frequent full GC.

Third, Ali produced EasyExcel, amway wave

What is a EasyExcel? It is the acid cool that makes you operate Excel abnormally. EasyExcel GitHub official screenshot:

So far, 5519 Star has been established. The official profile of 5519 Star is:

Quick and easy to avoid OOM Java processing Excel tools!

Here is the official introduction:

What does EasyExcel solve

Mainly, there are the following points:

• Traditional Excel frameworks, such as Apache POI and JXL, have the problem of memory overflow.
• The use of traditional Excel open source framework is complicated and tedious;
• EasyExcel is still using POI at the bottom, but it has made a lot of optimizations, such as fixing some bugs in the case of concurrency, for details, you can read the official document github.com/alibaba/eas… ;

5. Get started quickly

5.1 Adding a Dependency

<! --alibaba easyexcel-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>1.1.2 - beta5</version>
</dependency>
Copy the code

5.2 Seven lines of code for Excel generation

@Test
public void writeExcel1(a) throws Exception {
  // File output location
  OutputStream out = new FileOutputStream("/Users/a123123/Work/tmp_files/test.xlsx");

  ExcelWriter writer = EasyExcelFactory.getWriter(out);

  // Write an Excel file with only a Sheet. This scenario is more common
  Sheet sheet1 = new Sheet(1.0, WriteModel.class);

  // The first sheet name
  sheet1.setSheetName("The first sheet");

  // Write data to Writer context
  // Input parameter 1: create the model data to write
  // Input parameter 2: the target sheet to write
  writer.write(createModelList(), sheet1);

  // Writes the final outputStream from the context to the specified file
  writer.finish();

  / / close the flow
  out.close();
}
Copy the code

In the above sample code, there are two important points that ha has highlighted:

• The WriteModel object is a data model object to write to Excel. Table head, and each cell data order is not specified, can achieve the desired effect? Don’t worry, we’ll talk about that later!
• ② : Create the data set that needs to be written. Of course, in normal business, this data set is queried from the database.

PS: If the amount of data to be written is large, you need to perform fragment query before writing the data. Otherwise, it may be OOM (Out of Memory).

Back up, let’s take a look at what’s going on inside the WriteModel object!

/ * * *@authorWechat official account: Xiaoxue Java *@Site: www.exception.site *@date 2019/5/9
 * @timeOn the afternoon of 2:07 *@discriptionWrite the Excel model object **/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class WriteModel extends BaseRowModel {

    @ExcelProperty(value = "Name", index = 0)
    private String name;

    @ExcelProperty(value = "Password", index = 1)
    private String password;

    @ExcelProperty(value = "Age", index = 2)
    private Integer age;
}
Copy the code

ExayExcel provides annotations to easily define the data model that Excel requires:

• 1.: First, the defined write model must inherit fromBaseRowModel.java;
• 2.Through:@ExcelPropertyAnnotations to specify each fieldColumn name, as well asThe subscript position;

Meanwhile, the createModelList() method defined above is also very simple, creating a List of writes to the model through a loop:

Without further ado, this quick access case is also introduced, run a unit test to see the actual effect:

How, the effect is quite good!

Support for special scenarios

In the real business, we will also have some specific requirements, such as the following.

6.1 Dynamically Generating Excel Content

In other words, I have defined a data model, and the resulting Excel file must follow that model. However, the actual business needs may change dynamically. How do I do that?

@Test
public void writeExcel2(a) throws Exception {
  // File output location
  OutputStream out = new FileOutputStream("/Users/a123123/Work/tmp_files/test2.xlsx");

  ExcelWriter writer = EasyExcelFactory.getWriter(out);

  // Dynamically add table headers, suitable for some scenarios where table headers change dynamically
  Sheet sheet1 = new Sheet(1.0);

  sheet1.setSheetName("The first sheet");

  // Create a table for use in Sheet
  Table table1 = new Table(1);

  // Unannotated mode, dynamically add headers
  table1.setHead(DataUtil.createTestListStringHead());
  / / write data
  writer.write1(createDynamicModelList(), sheet1, table1);

  // Writes the final outputStream from the context to the specified file
  writer.finish();

  / / close the flow
  out.close();
}
Copy the code

• ① : no annotation mode, dynamic add header, also can freely combine complex header, the code is as follows:

public static List<List<String>> createTestListStringHead(){
    // There are no annotations on the model, header data is passed in dynamically
    List<List<String>> head = new ArrayList<List<String>>();
    List<String> headCoulumn1 = new ArrayList<String>();
    List<String> headCoulumn2 = new ArrayList<String>();
    List<String> headCoulumn3 = new ArrayList<String>();
    List<String> headCoulumn4 = new ArrayList<String>();
    List<String> headCoulumn5 = new ArrayList<String>();

    headCoulumn1.add(First column); headCoulumn1.add(First column); headCoulumn1.add(First column);
    headCoulumn2.add(First column); headCoulumn2.add(First column); headCoulumn2.add(First column);

    headCoulumn3.add("Second column"); headCoulumn3.add("Second column"); headCoulumn3.add("Second column");
    headCoulumn4.add("Third column"); headCoulumn4.add("Column 3 2"); headCoulumn4.add("Column 3 2");
    headCoulumn5.add(First column); headCoulumn5.add("Column 3"); headCoulumn5.add("Column 4");

    head.add(headCoulumn1);
    head.add(headCoulumn2);
    head.add(headCoulumn3);
    head.add(headCoulumn4);
    head.add(headCoulumn5);
    return head;
}
Copy the code

• 2.Create dynamic data, note that the data type here isObject:

Run the unit test to see how it works:

6.2 Customize table headers and content styles

I want to customize the header, content style, how to do?

We reused the above example code and added additional code to set the custom table style, createTableStytle() as follows:

public static TableStyle createTableStyle(a) {
    TableStyle tableStyle = new TableStyle();
    // Set the header style
    Font headFont = new Font();
    // Whether the font is bold
    headFont.setBold(true);
    // Font size
    headFont.setFontHeightInPoints((short)12);
    / / font
    headFont.setFontName("Regular script");
    tableStyle.setTableHeadFont(headFont);
    / / the background color
    tableStyle.setTableHeadBackGroundColor(IndexedColors.BLUE);


    // Set the table body style
    Font contentFont = new Font();
    contentFont.setBold(true);
    contentFont.setFontHeightInPoints((short)12);
    contentFont.setFontName("Black");
    tableStyle.setTableContentFont(contentFont);
    tableStyle.setTableContentBackGroundColor(IndexedColors.GREEN);
    return tableStyle;
}
Copy the code

We can use the TableStyle class to style the table header and table theme.

6.3 Merging cells

We can merge cells using the merge() method:

Note that the subscript starts at 0, that is, lines 6 through 7 are merged, with columns 1 through 5. Run the code to see what happens:

6.4 Custom Processing

For more complex processing, EasyExcel provides a WriterHandler interface that allows you to customize your processing code:

Three methods are defined in the interface:

• sheet(): Custom business logic processing after each sheet is created;
• row(): Custom business logic handling after each row is created
• cell(): Custom business logic processing after each cell is created.

After we implemented the interface, write custom logic to handle code, then call getWriterWithTempAndHandler () static method to obtain ExcelWriter object, incoming WriterHandler implementation class.

For example:

ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLSX, true.new MyWriterHandler());
Copy the code

Web download sample code

public class Down {
    @GetMapping("/a.htm")
    public void cooperation(HttpServletRequest request, HttpServletResponse response) {
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        String fileName = new String(("UserInfo " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()))
                .getBytes(), "UTF-8");
        Sheet sheet1 = new Sheet(1.0);
        sheet1.setSheetName("The first sheet");
        writer.write0(getListString(), sheet1);
        writer.finish();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition"."attachment; filename="+fileName+".xlsx"); out.flush(); }}Copy the code

8. Points needing attention

8.1 Fragmentation is required when writing big data

For example, when we need to query a large amount of data from the database, we need to do fragment processing in the business layer, that is, we need to query several times before writing, to prevent memory overflow OOM.

8.2 Maximum number of Excel lines

Excel 03 and 07 have limits on the number of rows and columns:

version	The biggest row	The largest column
Excel 2003	65536	256
Excel 2007	1048576	16384

Because CSV is a text file, there is no limit on the maximum number of lines, but it is still not displayed when opened with Excel client.

That is, if you want to write more lines than you can write, forcing it will raise an exception like the following

Invalid row number (1048576) outside allowable range (0.. 1048575).Copy the code

How to solve it?

1. Write into multiple Excel files;
2. Write the same Excel file into multiple sheets;

Nine,

Today, I mainly introduced EasyExcel to my friends, why to use it, and demonstrated the relevant example code. Of course, EasyExcel in addition to write Excel files, it also has the function of fast reading Excel, because this article is mainly introduced: how to gracefully achieve Excel file generation, so there is no introduction, interested partners, you can also go to GitHub official website to view the relevant documents.

In the end, I wish you a good harvest after reading this article. See you next time!

GitHub source code address

Github.com/weiwosuoai/…

Eleven, Ref

Github.com/alibaba/eas…

Free to share | interview & learning welfare resources

Recently found a good PDF resource on the Internet “Java core knowledge & interview. PDF” to share with you, not only interview, learning, you are worth it!!

Access: pay attention to the public number: xiahaxuejava, background reply resources, both free access to resources without routine links, the following is the directory and some screenshots:

Important things to say twice, pay attention to the public number: small ha learning Java, background reply resources, both free no routine access to resources links!!

Welcome to follow the wechat public number: Xiaoxue Java