这篇文章主要介绍了SpringBoot 导出数据生成excel文件返回方式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
一、基于框架
1.IDE
IntelliJ IDEA
2.软件环境
Spring boot
mysql
mybatis
org.apache.poi
二、环境集成
1.创建spring boot项目工程
略过
2.maven引入poi
org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 org.apache.poi poi-ooxml-schemas 3.17
三、代码实现
此处以导出云端mysql数据中的用户表为例(数据为虚假数据)
1.配置xls表格表头
此处我创建一个class(ColumnTitleMap)来维护需要导出的mysql表和xls表头显示的关系
代码注释已经清晰明了,就不再赘述
/** * @desc:数据导出,生成excel文件时的列名称集合 * @author: chao * @time: 2018.6.11 */ public class ColumnTitleMap { private MapcolumnTitleMap = new HashMap (); private ArrayList titleKeyList = new ArrayList (); 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 getColumnTitleMap() { return columnTitleMap; } public ArrayList 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.http.HttpServletResponse; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * @desc:数据导出api控制器 * @author: chao * @time: 2018.6.11 */ @Controller @RequestMapping(value = "/exportdata") public class ExportDataController { @Autowired UserInfoService userInfoService; @Autowired ExportDataService exportDataService; /** * @api: /apios/exportdata/excel/ * @method: GET * @desc: 导出数据,生成xlsx文件 * @param response 返回对象 * @param date_start 筛选时间,开始(预留,查询时并未做筛选数据处理) * @param date_end 筛选时间,结束(预留,查询时并未做筛选数据处理) */ @GetMapping(value = "/excel") public void getUserInfoEx( HttpServletResponse response, @RequestParam String date_start, @RequestParam String date_end ) { try { List
3.service
(1).用户表数据
UserInfoMapper.java
package com.mcrazy.apios.mapper; import com.mcrazy.apios.model.UserInfo; import org.apache.ibatis.annotations.Mapper; import java.util.List; import java.util.Map; @Mapper public interface UserInfoMapper { /** * @desc 查询所有用户信息 * @return 返回多个用户List * */ List
UserInfoMapper.xml
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
(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.http.HttpServletResponse; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * @desc:数据导出服务 * @author: chao * @time: 2018.6.11 */ @Service public class ExportDataService { @Autowired ExportExcelUtil exportExcelUtil; /*导出用户数据表*/ public void exportDataToEx(HttpServletResponse response, ArrayListtitleKeyList, Map titleMap, List > src_list) { try { exportExcelUtil.expoerDataExcel(response, titleKeyList, titleMap, src_list); } catch (Exception e) { System.out.println("Exception: " + e.toString()); } } }
导出工具封装,ExportExcelUtil.java
package com.mcrazy.apios.util.datebase; import com.mcrazy.apios.util.object.DateUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * @desc:数据导出,生成excel文件 * @author: chao * @time: 2018.6.12 */ @Service public class ExportExcelUtil { public void expoerDataExcel(HttpServletResponse response, ArrayListtitleKeyList, Map titleMap, List > src_list) throws IOException { String xlsFile_name = DateUtils.currtimeToString14() + ".xlsx"; //输出xls文件名称 //内存中只创建100个对象 Workbook wb = new SXSSFWorkbook(100); //关键语句 Sheet sheet = null; //工作表对象 Row nRow = null; //行对象 Cell nCell = null; //列对象 int rowNo = 0; //总行号 int pageRowNo = 0; //页行号 for (int k=0;k srcMap = src_list.get(k); //写入300000条后切换到下个工作表 if(rowNo%300000==0){ wb.createSheet("工作簿"+(rowNo/300000));//创建新的sheet对象 sheet = wb.getSheetAt(rowNo/300000); //动态指定当前的工作表 pageRowNo = 0; //新建了工作表,重置工作表的行号为0 // -----------定义表头----------- nRow = sheet.createRow(pageRowNo++); // 列数 titleKeyList.size() for(int i=0;i
三、运行
至此,所有代码工作已经做完,把程序运行起来,在浏览器调用接口,会自动下载到电脑中
浏览器打开:
http://192.168.1.70:8080/apios/exportdata/excel/?time_start=2018-12-19&end_start=2018-12-19
效果
得到xlsx文件,查看数据
以上这篇SpringBoot 导出数据生成excel文件返回方式就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持html中文网。
以上就是SpringBoot 导出数据生成excel文件返回方式的详细内容,更多请关注0133技术站其它相关文章!