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文件返回方式就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持好吧啦網。