SpringBoot 導(dǎo)出數(shù)據(jù)生成excel文件返回方式
一、基于框架
1.IDE
IntelliJ IDEA
2.軟件環(huán)境
Spring boot
mysql
mybatis
org.apache.poi
二、環(huán)境集成
1.創(chuàng)建spring boot項(xiàng)目工程
略過(guò)
2.maven引入poi
<!--數(shù)據(jù)導(dǎo)出依賴(lài) 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> <!--數(shù)據(jù)導(dǎo)出依賴(lài) End excel-->
三、代碼實(shí)現(xiàn)
此處以導(dǎo)出云端mysql數(shù)據(jù)中的用戶(hù)表為例(數(shù)據(jù)為虛假數(shù)據(jù))
1.配置xls表格表頭
此處我創(chuàng)建一個(gè)class(ColumnTitleMap)來(lái)維護(hù)需要導(dǎo)出的mysql表和xls表頭顯示的關(guān)系
代碼注釋已經(jīng)清晰明了,就不再贅述
/** * @desc:數(shù)據(jù)導(dǎo)出,生成excel文件時(shí)的列名稱(chēng)集合 * @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用戶(hù)表需要導(dǎo)出字段--顯示名稱(chēng)對(duì)應(yīng)集合 */ private void initUserInfoColu() { columnTitleMap.put("id", "ID"); columnTitleMap.put("date_create", "注冊(cè)時(shí)間"); columnTitleMap.put("name", "名稱(chēng)"); columnTitleMap.put("mobile", "手機(jī)號(hào)"); columnTitleMap.put("email", "郵箱"); columnTitleMap.put("pw", "密碼"); columnTitleMap.put("notice_voice", "語(yǔ)音通知開(kāi)關(guān)"); columnTitleMap.put("notice_email", "郵箱通知開(kāi)關(guān)"); columnTitleMap.put("notice_sms", "短信通知開(kāi)關(guān)"); columnTitleMap.put("notice_push", "應(yīng)用通知開(kāi)關(guān)"); } /** * mysql用戶(hù)表需要導(dǎo)出字段集 */ 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
提供對(duì)外接口,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:數(shù)據(jù)導(dǎo)出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: 導(dǎo)出數(shù)據(jù),生成xlsx文件 * @param response 返回對(duì)象 * @param date_start 篩選時(shí)間,開(kāi)始(預(yù)留,查詢(xún)時(shí)并未做篩選數(shù)據(jù)處理) * @param date_end 篩選時(shí)間,結(jié)束(預(yù)留,查詢(xún)時(shí)并未做篩選數(shù)據(jù)處理) */ @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).用戶(hù)表數(shù)據(jù)
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 查詢(xún)所有用戶(hù)信息 * @return 返回多個(gè)用戶(hù)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 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 查詢(xún)所有用戶(hù)信息 * @return 返回多個(gè)用戶(hù)List * */ public List<Map<String,Object>> queryUserInfoResultListMap() { List<Map<String,Object>> list = userInfoMapper.queryUserInfoResultListMap(); return list; } }
(2). 生成excel文件和導(dǎo)出
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:數(shù)據(jù)導(dǎo)出服務(wù) * @author: chao * @time: 2018.6.11 */ @Service public class ExportDataService { @Autowired ExportExcelUtil exportExcelUtil; /*導(dǎo)出用戶(hù)數(shù)據(jù)表*/ 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()); } } }
導(dǎo)出工具封裝,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:數(shù)據(jù)導(dǎo)出,生成excel文件 * @author: chao * @time: 2018.6.12 */ @Service public 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文件名稱(chēng) //內(nèi)存中只創(chuàng)建100個(gè)對(duì)象 Workbook wb = new SXSSFWorkbook(100); //關(guān)鍵語(yǔ)句 Sheet sheet = null; //工作表對(duì)象 Row nRow = null; //行對(duì)象 Cell nCell = null; //列對(duì)象 int rowNo = 0; //總行號(hào) int pageRowNo = 0; //頁(yè)行號(hào) for (int k=0;k<src_list.size();k++) { Map<String,Object> srcMap = src_list.get(k); //寫(xiě)入300000條后切換到下個(gè)工作表 if(rowNo%300000==0){ wb.createSheet("工作簿"+(rowNo/300000));//創(chuàng)建新的sheet對(duì)象 sheet = wb.getSheetAt(rowNo/300000); //動(dòng)態(tài)指定當(dāng)前的工作表 pageRowNo = 0; //新建了工作表,重置工作表的行號(hào)為0 // -----------定義表頭----------- nRow = sheet.createRow(pageRowNo++); // 列數(shù) 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++); //新建行對(duì)象 // 行,獲取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(); } }
三、運(yùn)行
至此,所有代碼工作已經(jīng)做完,把程序運(yùn)行起來(lái),在瀏覽器調(diào)用接口,會(huì)自動(dòng)下載到電腦中
瀏覽器打開(kāi):
http://192.168.1.70:8080/apios/exportdata/excel/?time_start=2018-12-19&end_start=2018-12-19
效果
得到xlsx文件,查看數(shù)據(jù)
以上這篇SpringBoot 導(dǎo)出數(shù)據(jù)生成excel文件返回方式就是小編分享給大家的全部?jī)?nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
使用MyBatisPlus自動(dòng)生成代碼后tomcat運(yùn)行報(bào)錯(cuò)的問(wèn)題及解決方法
這篇文章主要介紹了使用MyBatisPlus自動(dòng)生成代碼后tomcat運(yùn)行報(bào)錯(cuò)的問(wèn)題及解決方法,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-08-08學(xué)習(xí)在一臺(tái)新電腦上配置JAVA開(kāi)發(fā)環(huán)境
本文主要介紹了如何在一臺(tái)新電腦上配置JAVA開(kāi)發(fā)環(huán)境,每一個(gè)步驟都有對(duì)應(yīng)的截圖和文字說(shuō)明,需要的朋友可以參考下2015-07-07Maven方式構(gòu)建SpringBoot項(xiàng)目的實(shí)現(xiàn)步驟(圖文)
Maven是一個(gè)強(qiáng)大的項(xiàng)目管理工具,可以幫助您輕松地構(gòu)建和管理Spring Boot應(yīng)用程序,本文主要介紹了Maven方式構(gòu)建SpringBoot項(xiàng)目的實(shí)現(xiàn)步驟,具有一定的參考價(jià)值,感興趣的可以了解一下2023-09-09Springboot中項(xiàng)目的屬性配置的詳細(xì)介紹
很多時(shí)候需要用到一些配置的信息,這些信息可能在測(cè)試環(huán)境和生產(chǎn)環(huán)境下會(huì)有不同的配置,本文主要介紹了Springboot中項(xiàng)目的屬性配置的詳細(xì)介紹,感興趣的可以了解一下2022-01-01SpringData JPA中@OneToMany和@ManyToOne的用法詳解
這篇文章主要介紹了SpringData JPA中@OneToMany和@ManyToOne的用法詳解,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2021-10-10