SpringBoot如何实现导出数据并生成Excel文件后返回下载链接？

本文共计853个文字，预计阅读时间需要4分钟。

一、基于框架IDE：IntelliJ IDEA

二、软件环境：Spring Boot、MySQL、MyBatis、org.apache.poi

三、环境集成：

1. 创建Spring Boot项目 2. Maven引入poi依赖 3. 实现数据导出为Excel

一、基于框架

1.IDE

IntelliJ IDEA

2.软件环境

Spring boot

mysql

mybatis

org.apache.poi

二、环境集成

1.创建spring boot项目工程

略过

2.maven引入poi

<!--数据导出依赖 excel--> <!-- mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <!-- mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <!-- 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> <!--数据导出依赖 End excel-->

三、代码实现

此处以导出云端mysql数据中的用户表为例（数据为虚假数据）

1.配置xls表格表头

此处我创建一个class(ColumnTitleMap)来维护需要导出的mysql表和xls表头显示的关系

代码注释已经清晰明了，就不再赘述

/** * @desc:数据导出，生成excel文件时的列名称集合 * @author: chao * @time: 2018.6.11 */ public class ColumnTitleMap { private Map<String, String> columnTitleMap = new HashMap<String, String>(); private ArrayList<String> titleKeyList = new ArrayList<String> (); public ColumnTitleMap(String datatype) { switch (datatype) { case "userinfo": initUserInfoColu(); initUserInfoTitleKeyList(); break; default: break; } } /** * mysql用户表需要导出字段--显示名称对应集合 */ private void initUserInfoColu() { columnTitleMap.put("id", "ID"); columnTitleMap.put("date_create", "注册时间"); columnTitleMap.put("name", "名称"); columnTitleMap.put("mobile", "手机号"); columnTitleMap.put("email", "邮箱"); columnTitleMap.put("pw", "密码"); columnTitleMap.put("notice_voice", "语音通知开关"); columnTitleMap.put("notice_email", "邮箱通知开关"); columnTitleMap.put("notice_sms", "短信通知开关"); columnTitleMap.put("notice_push", "应用通知开关"); } /** * mysql用户表需要导出字段集 */ private void initUserInfoTitleKeyList() { titleKeyList.add("id"); titleKeyList.add("date_create"); titleKeyList.add("name"); titleKeyList.add("mobile"); titleKeyList.add("email"); titleKeyList.add("pw"); titleKeyList.add("notice_voice"); titleKeyList.add("notice_email"); titleKeyList.add("notice_sms"); titleKeyList.add("notice_push"); } public Map<String, String> getColumnTitleMap() { return columnTitleMap; } public ArrayList<String> getTitleKeyList() { return titleKeyList; } }

2.controller

提供对外接口，ExportDataController.java

package com.mcrazy.apios.controller; import com.mcrazy.apios.service.ExportDataService; import com.mcrazy.apios.service.UserInfoService; import com.mcrazy.apios.util.datebase.columntitle.ColumnTitleMap; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import javax.servlet.mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.mcrazy.apios.mapper.UserInfoMapper"> <select id="queryUserInfoResultListMap" resultType="HashMap"> select * from user_info </select> </mapper>

UserInfoService.java

package com.mcrazy.apios.service; import com.mcrazy.apios.mapper.UserInfoMapper; import com.mcrazy.apios.model.UserInfo; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; import java.util.Map; @Service public class UserInfoService { @Autowired UserInfoMapper userInfoMapper; /** * @desc 查询所有用户信息 * @return 返回多个用户List * */ public List<Map<String,Object>> queryUserInfoResultListMap() { List<Map<String,Object>> list = userInfoMapper.queryUserInfoResultListMap(); return list; } }

(2). 生成excel文件和导出

ExportDataService.java

package com.mcrazy.apios.service; import com.mcrazy.apios.util.datebase.ExportExcelUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.192.168.1.70:8080/apios/exportdata/excel/?time_start=2018-12-19&end_start=2018-12-19

效果

得到xlsx文件，查看数据

以上这篇SpringBoot 导出数据生成excel文件返回方式就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持易盾网络。