Springboot + easyexcel to realize export operation

Time:2022-1-5
objective

Export data to excel according to business requirements. The excel includes four templates (sheets), and each template represents an entity object. As shown in the figure:

Springboot + easyexcel to realize export operation

sheet

start

This project is implemented by springboot + Vue + elementui. Because it needs to use the export excel function, it is shallow into the easyexcel module.

1. Import easyexcel coordinate dependency
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

Easyexcel official document:https://www.yuque.com/easyexcel/doc/quickstart

2. Entity class writing

The Lombok plug-in is used here. To use easyexcel, you only need to set the column name of the exported excel table and use @ excelproperty (value = “”) annotation. For specific configuration of column name, data conversion, date format and custom excel table style, please go to the official website.

Springboot + easyexcel to realize export operation

Entity class partial graph
3. Written by controller
/**
     *Export file
     */
    @RequestMapping("/export")
    public void export(@RequestParam Map<String, Object> params, HttpServletResponse response) throws IOException {
        OutputStream outputStream = response.getOutputStream();
        //Get data
        PageUtils page = archService.getStatistics(params);
        //Obtain the file information of rural cooperative medical system
        List<ArchEntity> archEntityList = (List<ArchEntity>) page.getList();
        //Traversal settings list
        if (archEntityList.size() > 0) {
            List<FamilyEntity> familyEntityList = new ArrayList<>();
            List<CardEntity> cardEntityList = new ArrayList<>();
            List<PayEntity> payEntityList = new ArrayList<>();
            for (int i = 0; i < archEntityList.size(); i++) {
                //Get family files
                familyEntityList.add(i, archEntityList.get(i).getFamilyEntity());
                //Obtain chronic disease card file
                cardEntityList.add(i, archEntityList.get(i).getCardEntity());
                //Get chronic disease reimbursement information
                payEntityList.add(i, archEntityList.get(i).getPayEntity());
            }
            try {
                //Set response
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                //Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
                String fileName = URLEncoder. Encode ("statistical information", "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
                //New excelwriter
                ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
                //Get archsheet object
                WriteSheet archSheet = EasyExcel. Writersheet (0, "rural cooperative medical information file") head(ArchEntity.class). build();
                //Obtain the information of participants and write data to archsheet
                excelWriter.write(archEntityList, archSheet);
                //Get archsheet object
                WriteSheet familySheet = EasyExcel. Writersheet (1, "participating family files") head(FamilyEntity.class). build();
                //Obtain family participation information and write data to the familysheet
                excelWriter.write(familyEntityList, familySheet);
                //Get cardsheet object
                WriteSheet cardSheet = EasyExcel. Writersheet (2, "chronic disease card information") head(CardEntity.class). build();
                //Obtain family participation information and write data to cardsheet
                excelWriter.write(cardEntityList, cardSheet);
                //Get paysheet object
                WriteSheet paySheet = EasyExcel. Writersheet (3, "reimbursement record") head(PayEntity.class). build();
                //Obtain family participation information and write data to paysheet
                excelWriter.write(payEntityList, paySheet);
                //Close flow
                excelWriter.finish();
                outputStream.flush();
            } catch (IOException e) {
                log. Error ("export exception {}", e.getmessage());
            }
        }
    }
  • 1. Create OutputStream object
OutputStream outputStream = response.getOutputStream();
  • 2. Set header, type and encoding format
//Set response
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
String fileName = URLEncoder. Encode ("statistical information", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
  • 3. Create an excelwriter object
//New excelwriter
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
  • 4. Create a writesheet object and call easyexcel The writersheet() method writes parameters
//Get archsheet object
WriteSheet archSheet = EasyExcel. Writersheet (0, "rural cooperative medical information file") head(ArchEntity.class). build();

0 is the first template (sheet), and the name is the agricultural cooperative information file. The parameter passed in head () is an entity class

  • 5. Call excel writer Write() writes parameters to the sheet template
//Obtain the information of participants and write data to archsheet
excelWriter.write(archEntityList, archSheet);

To excel writer Pass in the list data and writesheet object in the write () method

  • 6. Close excel writer finish()
//Close flow
excelWriter.finish();
  • 7. Close OutputStream flush();
 outputStream.flush();
3. Front end interface preparation
//Export file
    exportHandle () {
      this.$nextTick(() => {
        this.$http({
          url: this.$http.adornUrl('/mxbbx/arch/export'),
          method: 'get',
          responseType: 'blob',
          params: this.$http.adornParams({
            'page': this.pageIndex,
            'limit': this.pageSize,
            'groupId': this.dataForm.groupId,
            'apName': this.dataForm.apName,
            'areaTime': this.dataForm.areaTime,
            'drName': this.dataForm.drName
          })
        }).then(({data}) => {
          //Create blob object
          let blob = new Blob([data], { type: 'application/vnd.ms-excel;charset=utf-8' })
          //Get path
          let url = window.URL.createObjectURL(blob)
          //Create a label
          const link = document.createElement('a')
          //Set a label link parameters
          link.href = url
          //Rename file
          link. Download = 'reimbursement information xlsx'
          link.click()
          //Release URL object after download
          URL.revokeObjectURL(url)
          //Remove a tag
          document.body.removeChild(link)
        })
      })
    },

matters needing attention
1. Here I use Axios asynchronous call handling (no big difference from Ajax)
2. Set responsetype: ‘blob’ in the request parameters, and set the return response type to blob
3. In the return success then (), create the a tag, set the hyperlink for the a tag, and set the click to download file stream. If you do not use the method of creating a tag, the download pop-up window will not be displayed!!! (Dakeng, the back-end succeeded. There has been no pop-up window for downloading files on the front-end, which may be due to Vue. It does not need to be set in HTML)

Recommended Today

[JS] a regular to get the page IP

See one, there are always people checking IP and writing it at will The code is as follows: Javascr ī pt:var arr=document. body. innerText. match(/(\d{1,3}\.) {3}/g); var str=””; var ip; for(var i=0;i<arr.length;i++){ip=arr[i]+Math.floor(Math.random()*255);str+=”<li>”+ip+”  <a href=’ http://www.baidu.com/baidu?word= “+ IP +” > obtain the IP source < / a > < / Li > “;} if (arr.length > 0) {document. Write (” < br […]