SpringBoot 导出数据生成excel文件返回方式

1.IDE

IntelliJ IDEA

2.软件环境

Spring boot

mysql

mybatis

org.apache.poi

1.创建spring boot项目工程

略过

2.maven引入poi

<!--数据导出依赖 excel--><!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version></dependency><!-- 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><!--数据导出依赖 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.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<Map<String,Object>> userList = userInfoService.queryUserInfoResultListMap(); ArrayList<String> titleKeyList= new ColumnTitleMap('userinfo').getTitleKeyList(); Map<String, String> titleMap = new ColumnTitleMap('userinfo').getColumnTitleMap(); exportDataService.exportDataToEx(response, titleKeyList, titleMap, userList); } catch (Exception e) { // System.out.println(e.toString()); } }}

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;@Mapperpublic interface UserInfoMapper { /** * @desc 查询所有用户信息 * @return 返回多个用户List * */ List<Map<String,Object>> queryUserInfoResultListMap();}

UserInfoMapper.xml

<?xml version='1.0' encoding='UTF-8'?><!DOCTYPE mapper PUBLIC '-//mybatis.org//DTD Mapper 3.0//EN' 'http://mybatis.org/dtd/mybatis-3-mapper.dtd' ><mapper namespace='com.mcrazy.apios.mapper.UserInfoMapper'> <select 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;@Servicepublic 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.http.HttpServletResponse;import java.util.ArrayList;import java.util.List;import java.util.Map;/** * @desc:数据导出服务 * @author: chao * @time: 2018.6.11 */@Servicepublic class ExportDataService { @Autowired ExportExcelUtil exportExcelUtil; /*导出用户数据表*/ public void exportDataToEx(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> 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 */@Servicepublic class ExportExcelUtil { public void expoerDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> 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<src_list.size();k++) { Map<String,Object> 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<titleKeyList.size();i++){ Cell cell_tem = nRow.createCell(i); cell_tem.setCellValue(titleMap.get(titleKeyList.get(i))); } rowNo++; // --------------------------- } rowNo++; nRow = sheet.createRow(pageRowNo++); //新建行对象 // 行，获取cell值 for(int j=0;j<titleKeyList.size();j++){ nCell = nRow.createCell(j); if (srcMap.get(titleKeyList.get(j)) != null) { nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString()); } else { nCell.setCellValue(''); } } } response.setContentType('application/vnd.ms-excel;charset=utf-8'); response.setHeader('Content-disposition', 'attachment;filename=' + xlsFile_name); response.flushBuffer(); OutputStream outputStream = response.getOutputStream(); wb.write(response.getOutputStream()); wb.close(); outputStream.flush(); outputStream.close(); }}三、运行

至此，所有代码工作已经做完，把程序运行起来，在浏览器调用接口，会自动下载到电脑中

浏览器打开：

http://192.168.1.70:8080/apios/exportdata/excel/?time_start=2018-12-19&end_start=2018-12-19

效果

得到xlsx文件，查看数据

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