springboot+vue实现excel的导出

首先是springboot对数据的处理，前端使用vue进行数据的导出
依赖的导入

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
1
2
3
4
5

@RequestMapping("/exportExcel")
public R exportResult(HttpServletResponse response) {
    XSSFWorkbook xssfSheets = kucunService.formGeneration();
    String fileName = "Goods报表.xlsx";
    OutputStream outputStream = null;
    try {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        //设置ContentType请求信息格式
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        outputStream = response.getOutputStream();
        xssfSheets.write(outputStream);
        System.out.println(xssfSheets);
        outputStream.flush();
        outputStream.close();
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return R.ok();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

service层

 public XSSFWorkbook formGeneration() {
        //查询数据库中的数据
        List<kuchun> listBlogVOS = kucunDao.getAllKuchunList();

        XSSFWorkbook xssfSheets = new XSSFWorkbook();

        XSSFSheet userList = xssfSheets.createSheet("库存药品表");

        Row titleRow = userList.createRow(0);//创建第一行，起始为0
        titleRow.createCell(0).setCellValue("id");//第一列
        titleRow.createCell(1).setCellValue("药品编码");
        titleRow.createCell(2).setCellValue("药品名称");
        titleRow.createCell(3).setCellValue("仓库");
        titleRow.createCell(4).setCellValue("库存数量");
        titleRow.createCell(5).setCellValue("盘点数量");
        titleRow.createCell(6).setCellValue("是否盘点");

        for (int i = 0; i < listBlogVOS.size(); i++) {
            Row row = userList.createRow(i + 1);//设置对哪一行操作
            row.createCell(0).setCellValue(listBlogVOS.get(i).getId());
            row.createCell(1).setCellValue(listBlogVOS.get(i).getDrugcode());
            row.createCell(2).setCellValue(listBlogVOS.get(i).getDrugname());
            row.createCell(3).setCellValue(listBlogVOS.get(i).getStorename());
            row.createCell(4).setCellValue(listBlogVOS.get(i).getNumber());
            row.createCell(5).setCellValue(listBlogVOS.get(i).getPandianstock());
            if(listBlogVOS.get(i).getNormal()=="0"){
                row.createCell(6).setCellValue("库存正常");
            }else{
                row.createCell(6).setCellValue("库存不正常");
            }
        }

        return xssfSheets;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

dao层

    List<kuchun> getAllKuchunList();

1
2

xml文件

<resultMap id="BaseResultMap" type="com.example.quanxian.entity.kuchun">
		<id column="id" jdbcType="INTEGER" property="id" />
		<result column="drugname" jdbcType="VARCHAR" property="drugname" />
		<result column="drugcode" jdbcType="VARCHAR" property="drugcode" />
		<result column="storeid" jdbcType="INTEGER" property="storeid" />
		<result column="number" jdbcType="INTEGER" property="number" />
		<result column="pandianstock" jdbcType="INTEGER" property="pandianstock" />
		<result column="normal" jdbcType="VARCHAR" property="normal" />
		<result column="updatetime" jdbcType="VARCHAR" property="updatetime" />
		<result column="createtime" jdbcType="VARCHAR" property="createtime" />
		<result column="storename" jdbcType="VARCHAR" property="storename" />
		<association property="store" javaType="com.example.quanxian.entity.store">
			<id column="aid" jdbcType="INTEGER" property="id" />
			<result column="storename" jdbcType="VARCHAR" property="storename"/>
			<result column="address" jdbcType="VARCHAR" property="address"/>
		</association>
	</resultMap>


	<select id="getAllKuchunList" resultMap="BaseResultMap" parameterType="com.example.quanxian.entity.kuchun">
		select kuchun.*,store.storename as storename,store.address,store.id as aid
		from kuchun
				 left join store on kuchun.storeid = store.id
	</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

前端excel对文件流进行对应的处理

let exportExcelParam=()=>{
      exportExcel().then(res=>{
        console.log(res)
        const link = document.createElement("a");
        const blob = new Blob([res], {
          type: "application/vnd.ms-excel;charset=utf-8",
        });
        link.style.display = "none";
        link.href = URL.createObjectURL(blob);
        link.setAttribute("download", `库存药品.xls`);
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
      })
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

excel文件的导出

Vue前端处理后端返回的文件流乱码,导出Excel

二：接口（注意：一定要加上responseType: ‘blob’）
// 导出
export function exportAllList (data) {
return request({
url: ‘/word/group/exportWords’,
method: ‘post’,
data: data,
responseType: ‘blob’
})
}

三：页面掉接口

exportAllList(params).then((res) => {
//这里是重点
const link = document.createElement(“a”);
const blob = new Blob([res], {
type: “application/vnd.ms-excel;charset=utf-8”,
});
link.style.display = “none”;
link.href = URL.createObjectURL(blob);
link.setAttribute(“download”, 词库数据.xls);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
this.msgSuccess(“导出成功”);
});