导出EXCEL表格
需要导入poi.jar包
<%@page import="com.founder.commons.web.login.dto.LoginUser"%> <%@page import="com.founder.commons.constant.AppConstant"%> <%@ page language="java" contentType="text/html; charset=UTF-8"%> <%@ include file="/include/meta.jsp"%> 水位终端信息
<% LoginUser user = (LoginUser) request.getSession().getAttribute(AppConstant.CURRENT_USER); String userName = user.getUserName(); String departmentID = user.getDepartmentID(); %>
/**
* 导出EXCEL操作
*/
function xiazaiexcel(){
/*var simserach = $("#serch_sim").val();
var stime = $("#serch_stime").val();
var otime = $("#serch_otime").val();*/
var dept = $("#serch_dept_hide").val();
var simserach = $("#navistatus").val();
var stime = $("#createtime1").val();
var otime = $("#createtime2").val();
if(createtime1>createtime2){
$("#stime").val("");
$("#otime").val("");
return alert("开始时间不能大于结束时间");
}
/*if(stime != ""){
if(otime == ""){
return alert("结束时间不能为空");
}
}else if(otime != ""){
if(stime == ""){
return alert("开始时间不能为空");
}
}
if(stime>otime){
$("#serch_stime").val("");
$("#serch_otime").val("");
return alert("开始时间必须小于结束时间");
}*/
var ajaxObj = {url : contextPath+"/business/waterdynamichistory/xiazaiexcel.do" ,
loading : true,
data:{
simserach :simserach,
stime : stime,
otime : otime,
dept : dept
},
sucF :function(data){
if(data == 1){
alert("已导出到桌面！","ok");
}else{
alert("导出失败");
}
},
errorF:function(data){
alert("导出失败");
},
};
App.ajax(ajaxObj);
}
/** * 下载excel * @param dept * @return */ @RequestMapping(value = "/xiazaiexcel.do") @ResponseBody public int xiazaiexcel(String simserach, String stime, String otime, String dept) { return waterExportExcelService.xiazaiexcel(simserach, stime, otime, dept); }
package com.founder.szhd.business.water.service; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.List; import javax.annotation.Resource; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.stereotype.Service; import com.founder.commons.constant.AppConstant; import com.founder.szhd.business.massage.msgrecordsendcount.dto.MsgRecordSendCountDTO; import com.founder.szhd.business.massage.msgrecordsendunsual.dao.MsgRecordSendUnsualDAO; import com.founder.szhd.business.massage.msgrecordsendunsual.service.IMsgRecordSendUnsualService; import com.founder.szhd.business.water.dao.WaterDynamicHistoryDAO; import com.founder.szhd.business.water.dto.WaterDynamicHistoryDTO; @Service(value = "WaterExportExcelService") public class WaterExportExcelServiceImpl implements WaterExportExcelService { @Resource(name="waterDynamicHistoryDAO") private WaterDynamicHistoryDAO waterDynamicHistoryDAO; public String sql(String simserach, String stime, String otime, String dept) { String dataSql = " select b.swzmc,a.id,a.sim,a.clsw,a.scsw,a.clsj,a.zddy,a.dybj,a.sjsbfs,a.ylyl,c.datum, c.code,b.lc,a.report_mode from T_SZHD_SWDTSLXX a left join T_SZHD_SWJBXX b on (a.sim = b.sim or a.swzbh = b.swzbh) and a.swzid = b.id left join T_SZHD_WATER_DATUM c on a.cjjm = c.id "; String whereSql = "where 1 = 1"; if (stime != null && !stime.equals("")) { stime = stime + " 00:00:00"; otime = otime + " 23:59:59"; whereSql+= " and (TO_CHAR(a.clsj,'yyyy/MM/dd hh24:mi:ss') BETWEEN '"+stime+"' AND '"+otime+"') "; } if (simserach != null && !simserach.equals("")) { whereSql += " and ( b.swzmc = '" + simserach + "' or b.id = '" + simserach + "' ) "; } if(dept!=""&&!AppConstant.DEPARTMENT_ROOTID.equals(dept)){ whereSql += " and b.gldw = '" + dept + "' "; } StringBuffer dataHQL = new StringBuffer(dataSql); dataHQL.append(whereSql + " order by a.clsj desc"); return dataHQL.toString(); } @SuppressWarnings("deprecation") public int xiazaiexcel(String simserach, String stime, String otime, String dept) { int back = 0; // 第一步，创建一个webbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet("水位站历史信息统计记录"); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 第四步，创建单元格，并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 HSSFCell cell = row.createCell((short) 0); cell.setCellValue("水位站名称"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("终端SIM卡号"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue("水位站基准面"); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("测量水位(m)"); cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue("测量时间"); cell.setCellStyle(style); cell = row.createCell((short) 5); cell.setCellValue("终端电压(V)"); cell.setCellStyle(style); cell = row.createCell((short) 6); cell.setCellValue("上传通道模式"); cell.setCellStyle(style); cell = row.createCell((short) 7); cell.setCellValue("电压报警"); cell.setCellStyle(style); // 第五步，写入实体数据 实际应用中这些数据从数据库得到， List listobjectList = waterDynamicHistoryDAO.findList(this.sql(simserach, stime, otime,dept)); List<WaterDynamicHistoryDTO> list = WaterDynamicHistoryDTO.toListAboutDatum(listobjectList,4); for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); WaterDynamicHistoryDTO stu = list.get(i); // 第四步，创建单元格，并设置值 row.createCell((short) 0).setCellValue(stu.getSwzmc()); row.createCell((short) 1).setCellValue(stu.getSwzdid()); row.createCell((short) 2).setCellValue(stu.getSwzjzmmc()); row.createCell((short) 3).setCellValue(stu.getClsw()); row.createCell((short) 4).setCellValue(stu.getClsj()); row.createCell((short) 5).setCellValue(stu.getZddy()); row.createCell((short) 6).setCellValue(stu.getSctdms()); String dybj =""; if("0".equals(stu.getDybj())){ dybj="正常"; }else if("1".equals(stu.getDybj())){ dybj="报警"; } row.createCell((short) 7).setCellValue(dybj); } Calendar calendar = Calendar.getInstance(); java.util.Date date = calendar.getTime(); SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmmss"); String a = sdf.format(date); // 第六步，将文件存到指定位置 try {
FileSystemView fsv=FileSystemView.getFileSystemView();
//将桌面的那个文件目录赋值给file
File file=fsv.getHomeDirectory();
//输出桌面那个目录的路径
/* System.out.println("桌面路径"+file.getPath());*/
FileOutputStream fout = new FileOutputStream(file.getPath()+"/水位站历史信息统计记录_" + a
+ ".xls"); // FileOutputStream fout = new FileOutputStream("E:/水位站历史信息统计记录_" + a+".xls"); wb.write(fout); fout.close(); back = 1; } catch (Exception e) { e.printStackTrace(); back = 2; } return back; } }
/**
* 查询
*
* @return List
*/
public List findList(String sql) {
Query query = getHibSession().createSQLQuery(sql);
return query.list();
}