SpringBoot将数据放入Excel里面通过浏览器直接下载到本地
以前做java方面的导出下载的时候，都是先把数据导出到linux服务器里面的某个目录，然后才是通过浏览器下载linux服务器里面的文件。这次是直接跳过了导出步骤，直接通过浏览器下载。这里是做了一个简单点的下载示例。......
·
说明
以前做java方面的导出下载的时候，都是先把数据导出到linux服务器里面的某个目录，然后才是通过浏览器下载linux服务器里面的文件。
这次是直接跳过了导出步骤，直接通过浏览器下载。
这里是做了一个简单点的下载示例。
依赖
这里的依赖其实只需要一个poi-ooxml依赖就行,
poi依赖是用来操作excel为2003之前的版本,扩展名为.xls,
poi-ooxml则是用来操作2007之后的版本,扩展名为.xlsx,
poi-ooxml-schemas好像是用来处理导入的报错问题。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
代码
我这里是用的get请求方式,因为浏览器是只能用get请求,我就用get对浏览器下载做测试。
这里可能因为架构差异，而导致参数存放有区别，这里的请求参数是都放入QueryCriteria这个对象里面。重点看将数据存入excel后下载到本地那部分。
这里是要将14天数据或者14周数据或者14个月数据导出到excel里面并下载到本地。
import com.cicd.project.core.model.QueryCriteria;
import com.cicd.project.core.utils.StringUtils;
import com.cicd.project.modules.industry5g.manager.IPerformanceMonitorManager;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
@RestController
public class PerformanceMonitorController extends BaseController{
@Autowired
private IPerformanceMonitorManager iPerformanceMonitorManager;
@GetMapping("performanceMonitorNewExport")
public Map<String,Object> performanceMonitorNewExport(HttpServletRequest request,HttpServletResponse response){
Map<String,Object> returnMap=new HashMap<>();
QueryCriteria criteria = new QueryCriteria();
String city = request.getParameter("city");//地市
String industry = request.getParameter("industry");//行业
String partner = request.getParameter("partner");//客户
String project = request.getParameter("project");//项目名称
String level = request.getParameter("level");//优专尊等级
String timeType = request.getParameter("timeType");//时间粒度,day、week、month
String date = request.getParameter("date");//日期,月粒度为每月1号,周粒度为每周一,格式为yyyy-MM-dd
//非空验证
if(StringUtils.isBlank(partner)||partner.equals("全部")) {
returnMap.put("message", "客户不能为空!");
returnMap.put("result", "");
returnMap.put("status", "999");
return returnMap;
}
if(StringUtils.isBlank(project)||project.equals("全部")) {
returnMap.put("message", "项目名称不能为空!");
returnMap.put("result", "");
returnMap.put("status", "999");
return returnMap;
}
if(StringUtils.isBlank(timeType)) {
returnMap.put("message", "时间粒度不能为空!");
returnMap.put("result", "");
returnMap.put("status", "999");
return returnMap;
}
if(StringUtils.isBlank(date)) {
returnMap.put("message", "日期不能为空!");
returnMap.put("result", "");
returnMap.put("status", "999");
return returnMap;
}
try {
criteria.put("city",city);
criteria.put("industry",industry);
criteria.put("sign_level",level);
criteria.put("partner",partner);
criteria.put("project_name",project);
criteria.put("time_type",timeType);
criteria.put("date",date);
List<Map<String, Object>> dataList = null;
//导出的excel文件名
String name="";
if(timeType.equals("day")){
name="性能监控-天粒度.xlsx";
//一共查询14天数据
criteria.put("start_time",getBeforeXDayDate(date,-13));
criteria.put("end_time",date);
dataList =iPerformanceMonitorManager.performanceMonitorExport(criteria);
}else if(timeType.equals("week")){
name="性能监控-周粒度.xlsx";
//一共查询14周数据
criteria.put("start_time",getBeforeXWeekDate(date,-13));
criteria.put("end_time",date);
dataList =iPerformanceMonitorManager.performanceMonitorExport(criteria);
}else {
name="性能监控-月粒度.xlsx";
//一共查询14月数据
criteria.put("start_time",getBeforeXMonthDate(date,-13));
criteria.put("end_time",date);
dataList =iPerformanceMonitorManager.performanceMonitorExport(criteria);
}
//以下才是下载需要的代码
// 创建Excel工作簿,XSSFWorkbook为2007之后的版本,扩展名为.xlsx,HSSFWorkbook为2003之前的版本,扩展名为.xls
XSSFWorkbook workbook = new XSSFWorkbook();
//创建第一个sheet页
Sheet sheet=workbook.createSheet();//不填值则为sheet默认名
//第一行列名
String[] header={"时间","地市","行业","客户","项目","优专尊","无线掉线率","无线接通率","传输丢包率","初始注册成功率","AMF鉴权成功率","分切片的会话建立成功率","上行峰值速率","下行峰值速率","上行平均速率","下行平均速率","双向平均时延","双向时延抖动","双向网络丢包率"};
//因为返回的是一个hashmap,是无序的，所以需要写这个来对数据有序写入
//将查询的数据库字段作为key值,这样就能按顺序写入excel里面
String[] keys={"starttime","city","industry","partner","project_name","sign_level","wireless_drop_rate","wireless_connection_rate","trans_packet_loss_rate","registration_success_cnt_rate","amf_auth_success_rate","smf_succcreatepdusession_ns_rate","flow_ns_uplink_max","flow_ns_dlink_max","flow_ns_uplink_avg","flow_ns_dlink_avg","tcp_aveddir_delay_time","stdevddir_delay_jitter_ava","packetlossddir_ava_rate"};
//创建第一行,索引从0开始
Row row=sheet.createRow(0);
for (int i = 0; i < header.length; i++) {
Cell cell = row.createCell(i);//创建第一行第i列单元格,索引从0开始
cell.setCellValue(header[i]);//给单元格填写数据
}
//填写数据
if(dataList.size()>0){
for (int i = 0; i < dataList.size(); i++) {
Map<String, Object> map = dataList.get(i);
row = sheet.createRow(i + 1);//从第二行开始填写数据
for (int j = 0; j < keys.length; j++){
Cell cell = row.createCell(j);
cell.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
}
}
}
//下载
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");//以流的形式对文件进行下载
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(name, "UTF-8"));//对文件名编码,防止文件名乱码
workbook.write(response.getOutputStream());
response.getOutputStream().close();
}catch (Exception e){
e.printStackTrace();
returnMap.put("message",e.getMessage());
returnMap.put("status","500");
returnMap.put("result","");
}
return returnMap;
}
//将天粒度日期格式yyyy-MM-dd格式向前推X天或向后推X天的方法
public String getBeforeXDayDate(String date,int day){
Calendar calendar=Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
//将传过来的日期设置给calendar
calendar.setTime(sdf.parse(date));
//将传过来的日期减去X天或者加上X天
calendar.add(Calendar.DATE, day);
}catch (Exception e){
e.printStackTrace();
}
return sdf.format(calendar.getTime());
}
//将天粒度日期格式yyyy-MM-dd格式向前推X周或向后推X周的方法
public String getBeforeXWeekDate(String date,int week){
Calendar calendar=Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
//将传过来的日期设置给calendar
calendar.setTime(sdf.parse(date));
//将传过来的日期减去X周或者加上X周
calendar.add(Calendar.WEEK_OF_MONTH, week);
}catch (Exception e){
e.printStackTrace();
}
return sdf.format(calendar.getTime());
}
//将月粒度日期格式yyyy-MM格式向前推X个月或向后推X个月的方法
public String getBeforeXMonthDate(String date,int month){
Calendar calendar=Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
//将传过来的日期设置给calendar
calendar.setTime(sdf.parse(date));
//将传过来的月份减去X个月或者加上X个月
calendar.add(Calendar.MONTH, month);
}catch (Exception e){
e.printStackTrace();
}
return sdf.format(calendar.getTime());
}
}
中间service层和dao层没有业务逻辑,直接请求到mapper.xml里面的sql语句:
这里用的数据库是pgsql，语法和mysql是差不多的。
coalesce函数意思是当字段为空或者null时，给这个值默认设置为0。
<select id="performanceMonitorExport" parameterType="com.cicd.project.core.model.QueryCriteria" resultType="java.util.HashMap">
select
to_char(starttime,'yyyy-MM-dd') starttime,
city,
industry,
partner,
project_name,
sign_level,
coalesce(wireless_drop_rate,0) wireless_drop_rate,
coalesce(wireless_connection_rate,0) wireless_connection_rate,
coalesce(trans_packet_loss_rate,0) trans_packet_loss_rate,
coalesce(registration_success_cnt_rate,0) registration_success_cnt_rate,
coalesce(amf_auth_success_rate,0) amf_auth_success_rate,
coalesce(smf_succcreatepdusession_ns_rate,0) smf_succcreatepdusession_ns_rate,
coalesce(flow_ns_uplink_max,0) flow_ns_uplink_max,
coalesce(flow_ns_dlink_max,0) flow_ns_dlink_max,
coalesce(flow_ns_uplink_avg,0) flow_ns_uplink_avg,
coalesce(flow_ns_dlink_avg,0) flow_ns_dlink_avg,
coalesce(tcp_aveddir_delay_time,0) tcp_aveddir_delay_time,
coalesce(stdevddir_delay_jitter_ava,0) stdevddir_delay_jitter_ava,
coalesce(packetlossddir_ava_rate,0) packetlossddir_ava_rate
<choose>
<when test="condition.time_type == 'day'">
from appdbv3.a_cm5g_keykpi_project_d
</when>
<when test="condition.time_type == 'week'">
from appdbv3.a_cm5g_keykpi_project_w
</when>
<otherwise>
from appdbv3.a_cm5g_keykpi_project_m
</otherwise>
</choose>
<where>
<if test="condition.partner != null and condition.partner != ''" >
and partner=#{condition.partner}
</if>
<if test="condition.project_name != null and condition.project_name != ''" >
and project_name=#{condition.project_name}
</if>
<if test="condition.start_time != null and condition.start_time != ''" >
<![CDATA[ and starttime >= cast(#{condition.start_time} as timestamp) and starttime <= cast(#{condition.end_time} as timestamp) ]]>
</if>
</where>
order by starttime
</select>
效果
在谷歌浏览器直接输入接口请求测试如下:
下载的结果如下:
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)