SpringBoot 导出数据生成excel文件返回方式
作者:陌生谁家年少 时间:2023-09-01 11:29:27
一、基于框架
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;
@Mapper
public 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 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.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, 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
*/
@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文件名称
//内存中只创建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文件,查看数据
来源:https://blog.csdn.net/u010277446/article/details/85130087
标签:SpringBoot,导出,数据,excel
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
C#获取字符串后几位数的方法
2022-06-07 21:39:33
Java并发程序入门介绍
2022-03-05 12:05:26
c#(Socket)异步套接字代码示例
2021-06-28 11:06:03
java线程池ThreadPoolExecutor的八种拒绝策略示例详解
2021-06-24 11:31:10
![](https://img.aspxhome.com/file/2023/0/64390_0s.png)
Flutter进阶之实现动画效果(六)
2023-09-15 15:13:50
学习C#静态函数及变量的一个精典例子与代码
2021-10-08 18:53:52
SpringBoot中整合Minio文件存储的安装部署过程
2022-02-21 01:28:14
![](https://img.aspxhome.com/file/2023/8/101958_0s.jpg)
Java之InputStreamReader类的实现
2023-09-03 01:28:17
使用C#实现在屏幕上画图效果的代码实例
2022-09-11 05:12:32
Android提高之SQLite分页读取实现方法
2022-11-28 22:44:04
![](https://img.aspxhome.com/file/2023/4/138894_0s.gif)
java中Class.getMethods()和Class.getDeclaredMethods()方法的区别
2021-05-28 23:05:46
C#中Lambda表达式的用法
2022-12-22 15:59:17
Spring Security内置过滤器的维护方法
2022-07-30 18:10:16
![](https://img.aspxhome.com/file/2023/5/61285_0s.jpg)
Spring boot工具类静态属性注入及多环境配置详解
2022-10-02 16:12:37
详解C#中的System.Timers.Timer定时器的使用和定时自动清理内存应用
2022-11-07 14:09:54
Java 序列化和反序列化实例详解
2023-11-23 04:43:44
详解java调用python的几种用法(看这篇就够了)
2023-04-10 22:15:57
![](https://img.aspxhome.com/file/2023/2/104972_0s.png)
Eclipse中改变默认的workspace的方法及说明详解
2022-07-31 12:07:21
c#获取相同概率随机数的算法代码
2022-09-07 21:18:13
利用java反射机制实现自动调用类的简单方法
2023-11-29 15:57:22