Super easy to use asynchronous export function of Excel

I've done something about Excel Export case of , This time, it is also transformed and upgraded on its basis ：

https://www.bilibili.com/video/BV1kf4y1i761?p=5

But there are several problems with the previous export ：

• If there is a large amount of data, it is easy to cause the page to get stuck （ I once exported 30w Data , Directly lead to OOM）
• The user experience is terrible , A lot of data will wait a long time , And users can't do anything else .
• Every time you click export, you need to go through the complete export process （ This is actually ok ）
• There is no way to regularize the exported data every time

Use today Export asynchronously To solve the above problems .

One 、UML chart

1-1、 Export diagram

1-2、 Download pictures

As you can see from the picture above , The whole download is asynchronous , Users don't have to wait on the page , Each time we export, we generate a record and a file , Users can download more than once .

Two 、 Function realization

2-1、 Record entities

/** * Excel The list of data  * * @author  Xiaodaoxian 97 * @date 2021/8/8 */
public class ExcelList {
    

    /** * id */
    private String id;

    /** *  file name  */
    private String name;

    /** *  Number of downloaded lines  */
    private Integer rows;

    /** *  Download time  */
    private Long takeUpTime;

    /** *  Download address  */
    private String downloadUrl;
}

2-2、Controller

import javax.servlet.http.HttpServletResponse;
import java.util.*;

/** * Excel export  * * @author  Xiaodaoxian 97 * @date 2021-08-08 */
@RestController
public class ExportExcelController {
    

    @Autowired
    private ExportExcel exportExcel;

    //  Imitate the download list 
    public static List<ExcelList> list = new ArrayList<>(10);

    /** * Excel  export  * @throws Exception */
    @GetMapping("/excel/export")
    public void export() {
    
        Long startTime = System.currentTimeMillis();
        String name = "Excel Asynchronous export test " + UUID.randomUUID().toString() + ".xlsx";
        String id = UUID.randomUUID().toString();
        ExcelList excelList = new ExcelList();
        excelList.setId(id);
        excelList.setName(name);
        list.add(excelList);
        //  Export asynchronously 
        exportExcel.asyncExportExcel(id,name,startTime);

    }

    /** *  Get export list  -  simulation  */
    @GetMapping("/excel/list")
    public List<ExcelList> list() {
    
       return list;
    }

    /** *  File download  */
    @GetMapping("/excel/downLoad")
    public void downLoad(HttpServletResponse response,@RequestParam  String url) {
    
        FileUtils.download(url, response);
    }
}

2-3、 Asynchronous download implementation

ExportExcel

notes ： Use asynchronous annotations @Async You need to start... On the startup class first @EnableAsync

import org.springframework.scheduling.annotation.Async;

public interface ExportExcel {
    

    /** *  Export asynchronously  * @param id  The only record Id * @param name  File name  * @param startTime  Download start time  */
    @Async
    void asyncExportExcel(String id,String name,Long startTime);
}

TestExportExcelImpl

@Service
public class TestExportExcelImpl implements ExportExcel {
    

    @Override
    public void asyncExportExcel(String id,String name,Long startTime) {
    
        //  Simulate the process of querying data 
        String[] header = new String[]{
    " full name "," Age "};
        String[] keys = new String[]{
    "name","age"};
        List<Map<String, Object>> content = new ArrayList<>();
        Map<String, Object> map1 = new HashMap<>();
        map1.put("name"," Xiaodaoxian ");
        map1.put("age","23");
        content.add(map1);
        Map<String, Object> map2 = new HashMap<>();
        map2.put("name"," Xiaodaoxian 97");
        map2.put("age","97");
        content.add(map2);

        try {
    
            //  obtain Excel Export file stream 
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtils.exportExcel(header,keys,content,"first",os);
            byte[] content1 = os.toByteArray();
            InputStream inputStream = new ByteArrayInputStream(content1);
            //  Upload files   Return to download address 
            String url = FileUtils.uploadInputStream(inputStream, name);
            /** *  Find the current data and encapsulate the result set  * *  In fact, it's very simple and not so complicated , In fact, we only need one  update  Statement can be done  */
            for (ExcelList item : ExportExcelController.list) {
    
                if (item.getId().equals(id)) {
    
                    item.setRows(content.size());
                    item.setDownloadUrl(url);
                    item.setTakeUpTime(System.currentTimeMillis() - startTime);
                    break;
                }
            }
        }catch (Exception e){
    
            e.printStackTrace();
        }
    }
}

3、 ... and 、 demonstration

The premise of the following demonstration is to start the project

3-1、 export

Because there is no return value , So it's empty to return , You can also customize any prompt to return .

http://127.0.0.1:8888/excel/export

3-2、 Download list

http://127.0.0.1:8888/excel/list

The results are as follows ：

[
    {
    
        "id": "d3cb3551-cb33-445e-8e60-9534197f6647",
        "name": "Excel Asynchronous export test 80d50264-3930-4667-8bc5-2d4a1cfe0d9d.xlsx",
        "rows": 2,
        "takeUpTime": 5,
        "downloadUrl": "2021/8/8/Excel Asynchronous export test 80d50264-3930-4667-8bc5-2d4a1cfe0d9d.xlsx"
    }
]

3-3、 download

http://127.0.0.1:8888/excel/downLoad?url=2021/8/8/Excel Asynchronous export test 80d50264-3930-4667-8bc5-2d4a1cfe0d9d.xlsx

Look at the video and explain the address ：https://www.bilibili.com/video/BV1kf4y1i761

Pay attention to WeChat official account reply ：xdxFrameSimple Access to the source code .