Vue+Springboot,通过EasyExcel工具实现Excel导出

1.前端Vue

(注意：调用后端接口时需指定{responseType:'blob'}，否则导出的excel表格打不开)

//Vue导出方法 
exportExcel(){
            //searchParam:为前端需传到后端的所需参数
            var searchParam =  {
                test:this.data.test,


                };
           //调用后端接口，传入参数searchParam
           //注意：调用后端接口时需指定{responseType:'blob'}，否则导出的excel表格打不开
            request.post("/exceltest/exportExcel",searchParam,{responseType:'blob'}).then((res) => {
                const aLink = document.createElement('a')
                //为Blob指定导出的文件类型，此处为xlsx[{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}]
                var blob = new Blob([res.data], {type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"})
                // //从response的headers中获取filename, 后端response.setHeader("Content-disposition", "attachment; filename=xxxx.docx") 设置的文件名;
                var patt = new RegExp('filename=([^;]+\\.[^\\.;]+);*')
                var contentDisposition = decodeURI(res.headers['content-disposition'])
                var result = patt.exec(contentDisposition)
                var fileName = result[1]
                fileName=fileName = fileName.replace(/\"/g, '')
                aLink.href = URL.createObjectURL(blob)
                aLink.setAttribute('download', fileName) // 设置下载文件名称
                document.body.appendChild(aLink)
                aLink.click()
                document.body.appendChild(aLink)
            })
        },

2.后端Spring boot

2.1Rest层

@RestController
@RequestMapping("/exceltest")
public class ExcelExportRest {

    @Autowired
    private ExcelExportDao excelExportDao;


    
    @PostMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response,@RequestBody HashMap queryParam) {
        try {
            //定义文件名字
            String filename = URLEncoder.encode(System.currentTimeMillis() + ".xlsx", "UTF-8");
            response.setCharacterEncoding("UTF-8");
            //设定输出文件头
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
            // 定义输出类型
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            //从数据库查询需写入excel的结果集[queryParam前端所传参数]
            //注意返回类型需指定为定义的实体：SupportingDetailsDto
            List<SupportingDetailsDto> hashMaps = excelExportDao.querySendMaterial(queryParam);
             //将实体以流的方式写入
            EasyExcel.write(response.getOutputStream(), SupportingDetailsDto.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    //指定sheet名字
                    .autoCloseStream(Boolean.FALSE).sheet("配套明细")
                     //写入数据库返回的参数到excel
                    .doWrite(hashMaps);
        

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("失败！");
        }
    }

}

2.2Dto层

@Data
@EqualsAndHashCode
public class SupportingDetailsDto {

    //数据类型需和数据库返回类型一致
    @ExcelProperty("test字段名称")
    private String test;

   

3.pom.xml

       <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/poi/poi -->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

3.excel导出效果图