java代码导出excel
Control.java
@SuppressWarnings("deprecation")
@RequestMapping(value = "ddosAttackReport_Download.do")
public void Download(HttpServletRequest request,HttpServletResponse response,PagedRequest pagedRequest ,String ids ,Long corpid ,Long task ){
//先将数据查询出来
Map<String,Object> params = new HashMap<>();
params.put("task", task);
if (Validators.isNotnull(getCustomContext(request))) {
if(Validators.isNull(task)){
if(Validators.isNotnull(getCustomContext(request).getCorp())){
corpid = getCustomContext(request).getCorp().getCorpid();
}
}
params.put("corpid", corpid);
} else {
params.put("corpid", corpid);
}
if(Validators.isNotnull(task) && task == 1L){
Map<String, Date> previousMondayToSunday = DateUtils.getPreviousMondayToSunday();
Date beginTime = previousMondayToSunday.get("monday");
Date endTime = previousMondayToSunday.get("sunday");
params.put("begintime", beginTime);
params.put("endtime", endTime);
} else {
task = null;
Date begintime = DateUtils.str2Date(request.getParameter("begintime"),DateUtils.datetimeFormat);
params.put("begintime", begintime);
Date endtime = DateUtils.str2Date(request.getParameter("endtime"),DateUtils.datetimeFormat);
params.put("endtime", endtime);
params.put("attacktype", request.getParameter("attacktype"));
params.put("dstip", request.getParameter("dstip"));
params.put("receivebits", request.getParameter("receivebits"));
params.put("symbol", request.getParameter("symbol"));
}
List<Long> daeids = null;
if(Validators.isNotnull(ids)){
daeids = new ArrayList<Long>();
String[] cids = ids.split(",");
for (String id : cids) {
if(id != null){
long l = Long.parseLong(id);
daeids.add(l);
}
}
}
params.put("daeids", daeids);
PagedResult<Map<String,Object>> pageResult = ddosAttackReportService.getDdosAttackReportDownload(pagedRequest, params);
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet对象（excel的表单）
HSSFSheet sheet=wb.createSheet("详细攻击情况统计");
//设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1=sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("详细攻击情况统计");
cell.setCellStyle(style);
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,8));
//在sheet里创建第二行
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
row2.createCell(0).setCellValue("受攻击ip");
row2.createCell(1).setCellValue("端口");
row2.createCell(2).setCellValue("攻击类型");
row2.createCell(3).setCellValue("开始时间");
row2.createCell(4).setCellValue("结束时间");
row2.createCell(5).setCellValue("持续时间");
row2.createCell(6).setCellValue("攻击类型(bits)");
row2.createCell(7).setCellValue("流量峰值");
row2.createCell(8).setCellValue("包峰值");
List<Map<String,Object>> datas = pageResult.getDataList();
if(Validators.isNotnull(datas) && datas.size() > 0) {
for (int i = 0; i < datas.size(); i++) {
Map<String, Object> data = (Map<String, Object>) datas.get(i);
HSSFRow row=sheet.createRow(2);
row.createCell(0).setCellValue(data.get("DSTIP")+"");
row.createCell(1).setCellValue(data.get("PORT")+"");
row.createCell(2).setCellValue(data.get("ATTACKTYPE")+"");
row.createCell(3).setCellValue(data.get("BEGINTIME")+"");
row.createCell(4).setCellValue(data.get("ENDTIME")+"");
row.createCell(5).setCellValue(data.get("DURATION")+"");
row.createCell(6).setCellValue(NumberHelper.getTransformUnitBits(Double.parseDouble(data.get("RECEIVEBITS").toString())));
row.createCell(7).setCellValue(NumberHelper.getTransformUnit(Double.parseDouble(data.get("DROPMAXBPS").toString())));
row.createCell(8).setCellValue(NumberHelper.getTransformUnitPps(Double.parseDouble(data.get("DROPMAXPPS").toString())));
}
}
//输出Excel文件
try {
OutputStream output=response.getOutputStream();
response.reset();
response.setContentType("application/msexcel;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=\""
+ new String(("详细攻击情况统计" + ".xls").getBytes("GBK"),
"ISO8859_1") + "\"");
wb.write(output);
output.flush();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
//记录日志
String remoteIp = request.getRemoteAddr();
String username = "";
if(SessionCustom.getCustomContext(request).getCustom() != null) {
username = SessionCustom.getCustomContext(request).getCustom().getCustomname();
}
logService.addUserLog(new UserLog(username, remoteIp, "前台操作", "详细攻击情况统计_导出", new Date(), true,1L));
}
Service.java
public PagedResult<Map<String, Object>> getDdosAttackReportDownload( PagedRequest pagedRequest, Map<String, Object> params);
ServiceImpl.java
@SuppressWarnings({ "unchecked", "rawtypes" })
@Override
public PagedResult<Map<String, Object>> getDdosAttackReportDownload(
PagedRequest pagedRequest, Map<String, Object> params) {
List list = ddosAttackReportMapper.getDdosAttackReportList(params);
return BeanUtil.toPagedResult(list);
}
dao.java
public List getDdosAttackReportDownload(Map<String, Object> params);
dao.xml
<!-- 获取扫描任务 -->
<select id="getDdosAttackReportList" parameterType="Map" resultType="HashMap" useCache="false">
select dae.id daeid,dae.dst_ip dstip,dae.port port,dae.type attacktype,to_char(min(dae.begintime)
, 'YYYY-MM-DD HH24:MI:SS') begintime,to_char(max(dae.endtime)
, 'YYYY-MM-DD HH24:MI:SS') endtime,to_char(to_date('000000', 'HH24:MI:SS') + max(dae.duration) / 86400
, 'HH24:MI:SS') duration,max(dae.drop_bits) receivebits
, max(dae.drop_max_bps) dropmaxbps,max(dae.drop_max_pps) dropmaxpps
from ddos_attack_event dae left join corp_info ci on ci.corpid = dae.corpid
where 1 = 1
<choose>
<when test="task != null and task != '' and task == 1">
<if test="begintime != null">
and begintime >= #{begintime}
</if>
<if test="endtime != null">
and endtime <= #{endtime}
</if>
<if test="corpid != null and corpid!=''">
and ci.corpid = #{corpid}
</if>
</when>
<otherwise>
<if test="begintime != null">
and dae.begintime >= #{begintime}
</if>
<if test="endtime != null">
and dae.endtime <= #{endtime}
</if>
<if test="corpid != null and corpid!=''">
and ci.corpid = #{corpid}
</if>
<if test="attacktype != null and attacktype!=''">
and dae.type = #{attacktype}
</if>
<if test="dstip != null and dstip != ''">
and dae.dst_ip = #{dstip}
</if>
<if test="receivebits != null and receivebits!=''">
<choose>
<when test="symbol == null or symbol == ''">
and dae.receive_bits >= #{receivebits}
</when>
<when test="symbol == 0">
and dae.receive_bits >= #{receivebits}
</when>
<when test="symbol == 1">
and dae.receive_bits <= #{receivebits}
</when>
<otherwise>
and dae.receive_bits = #{receivebits}
</otherwise>
</choose>
</if>
</otherwise>
</choose>
group by dae.attack_event_id, dae.dst_ip,dae.port, dae.type, dae.id
order by duration desc
</select>
<select id="getattackTypes" resultType="String" useCache="false">
select distinct (dae.type) as types from DDOS_ATTACK_EVENT dae
</select>
页面按钮.jsp
<button type="button" class="btn btn-primary" onclick="Batch();" style="line-height:-10px;height: 25px;float:right;padding-top: 3px;/* background:#0E9AEF; */">导 出</button>
//导出
function Batch() {
var dstip = $("#dstip").val();
var begintime = $("#begintime").val();
var endtime = $("#endtime").val();
var attacktype = $('#attacktype').val();
var symbol = $('#symbol').val();
var receivebits = $('#receivebits').val();
var urlj = '&dstip='+dstip
+'&begintime='+begintime
+'&endtime='+endtime
+'&attacktype='+attacktype
+'&symbol='+symbol
+'&receivebits='+receivebits;
var url = path+'/front/flowclearing/ddosAttackReport/ddosAttackReport_Download.do?corpid=${corpid}&task=${task}'+urlj;
window.location.href = url;
}
设置excel样式.txt（需要的时候加）
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("搞笑");
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐;
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("详细攻击情况统计");
cell.setCellStyle(style);
//设置Excel中的边框(表头的边框)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setTopBorderColor(HSSFColor.BLACK.index);
//设置字体
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 16); // 字体高度
font.setFontName("黑体"); // 字体
style.setFont(font);
