package com.automic.monitor.dailyWork.toExcel.amount.action;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.automic.monitor.remote.DataAmountVO;
import com.automic.monitor.power.util.Powers;
import com.automic.monitor.remote.busi.hisAmountData.* ;
import com.automic.monitor.dailyWork.toExcel.amount.busi.ToExcelAmountBusi;
import com.automic.monitor.dailyWork.toExcel.pvo.BackVO;
import com.automic.monitor.dailyWork.toExcel.util.ExcelUtil;
import com.automic.monitor.global.*;
import com.automic.monitor.util.CheckPowerAndSession;
import com.automic.monitor.util.Config;
public class ExcelAmountAction extends PagerAction {
@SuppressWarnings("deprecation")
public ActionForward execute(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) {
try {
response.setContentType("text/xml;charset=GBK");
response.setCharacterEncoding("GBK");
PrintWriter out = response.getWriter();
//
String fountId = request.getParameter("fountId");
String gprsId = request.getParameter("gprsId");
String typeId = request.getParameter("typeId");
String startDt = request.getParameter("startDt");
String startHou = request.getParameter("startHou");
String endDt = request.getParameter("endDt");
String endHou = request.getParameter("endHou");
String reName = request.getParameter("reName");
reName = new String(reName.getBytes("ISO8859-1"),"utf-8");
PagerQuery query = createQuery(fountId,gprsId,typeId,startDt,startHou,endDt,endHou);
// 得到纪录总数目
ArrayList<DataAmountVO> li = this.queryItems(query,0,0);
//start to excel//
String message = "";
String savePath = Config.getConfig(Config.amountExcelDir);
String excelName = null;
if(reName != null && !reName.equals("")){
excelName = reName + ".xls";
}else{
excelName = this.buildExcelName(startDt,startHou,endDt,endHou);
}
ExcelUtil exUtil = ExcelUtil.getInstance();
BackVO bvo = new BackVO();
if(exUtil.isExist(savePath, excelName)){//如果此文件名已经存在,提示
message = excelName + "已经存在!!!";
bvo.setMessage(message);
bvo.setFiles(exUtil.getFiles(savePath));
JSONObject jobj = new JSONObject();
jobj.put("jobj", bvo);
out.print(jobj.toString());
out.flush();
out.close();
return null;
}
/
try{
ArrayList<String> headerLis = new ArrayList<String>(0);//头栏目内容
headerLis.add("矿泉名称");
headerLis.add("工程名称");
headerLis.add("瞬时流量(m3/d)");
headerLis.add("累计流量(m3)");
headerLis.add("温度(℃)");
headerLis.add("水位(mm)");
headerLis.add("电源工作状态");
headerLis.add("蓄电池工作状态");
headerLis.add("测控器工作模式");
headerLis.add("上报时间");
ArrayList<ArrayList<Object>> contentLlis = new ArrayList<ArrayList<Object>>(0);//excel文件内容
for(int i = 0; li != null && i < li.size();i++){
DataAmountVO vo = li.get(i);
ArrayList<Object> temp = new ArrayList<Object>(0);
temp.add(vo.getFountName());
temp.add(vo.getProjectName());
temp.add(vo.getInstantFlux());
temp.add(vo.getAccumuFlux());
temp.add(vo.getWaterRemain());
temp.add(vo.getWaterLevel());
temp.add(vo.getPowerStatus().replaceAll("<font color='#FF0000'>","").replaceAll("</font>", ""));
temp.add(vo.getStorePowerVoltStatus().replaceAll("<font color='#FF0000'>","").replaceAll("</font>", ""));
temp.add(vo.getMeterModel().replaceAll("<font color='#FF0000'>","").replaceAll("</font>", ""));
temp.add(vo.getDateTime());
contentLlis.add(temp);
}
exUtil.createBookSheet(savePath + excelName, "sheet1", 0);//构建excel模板
exUtil.buildHeader(headerLis);//构建excel标题栏
exUtil.buildContent(contentLlis);
exUtil.closed();
message = "导出" + excelName + "成功!!!";
}catch(Exception e){
e.printStackTrace();
message = "导出文件时出错!!!";
}finally{;}
// 保存,返回
bvo.setMessage(message);
bvo.setFiles(exUtil.getFiles(savePath));
JSONObject jobj = new JSONObject();
jobj.put("jobj", bvo);
out.print(jobj.toString());
out.flush();
out.close();
return null;
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return null;
}
/**
*
* @param form
* @return
*/
public PagerQuery createQuery(String fountId,String gprsId,String typeId,String startDt,String startHou
,String endDt,String endHou) {
PagerQuery query = new PagerQuery();
query.set("fountId" , fountId) ;
query.set("typeId" , typeId) ;
query.set("gprsId" , gprsId) ;
query.set("startDt" , startDt) ;
query.set("startHou" , startHou) ;
query.set("endDt" , endDt) ;
query.set("endHou" , endHou) ;
return query ;
}
@Override
public PagerQuery createQuery(PagerForm form) {
// TODO Auto-generated method stub
return null;
}
/**
* @param form
* @return构建excel文档名称
*/
private String buildExcelName(String startDt,String startHou,String endDt,String endHou){
String name = "水量(" + startDt + startHou + "-" + endDt + endHou+ ")";
name += ".xls";
return name;
}
/**
*
* @param form
* @return
*/
public PagerQuery createQuery( PagerForm form , String userRoleId) {
return null ;
}
/**
* 得到总数
* @param query
* @return
*/
public int getItemTotal(PagerQuery query) {
return new HisDataSearch().getTotalHisData(query) ;
}
/**
* 查询
*/
public ArrayList<DataAmountVO> queryItems(PagerQuery query,int start,int count) {
return new ToExcelAmountBusi().getHisdataAmount(query);
}
/**
* 把不同矿泉分组
*
* @return
*/
private ArrayList<ArrayList<DataAmountVO>> cutListByFount(ArrayList<DataAmountVO> list) {
ArrayList<ArrayList<DataAmountVO>> l = new ArrayList<ArrayList<DataAmountVO>>();
if (list == null || list.size() == 0) {
return l;
} else {
while (list != null && !list.isEmpty()) {
ArrayList<DataAmountVO> lv = new ArrayList<DataAmountVO>();
DataAmountVO first = list.get(0);
lv.add(first);
list.remove(0);
for (int i = 0; i < list.size(); i++) {
DataAmountVO temp = ((DataAmountVO) list.get(i));
if (temp.getFountName().equals(first.getFountName())) {
lv.add(temp);
list.remove(i);
i--;
}
}
l.add(lv);
}
return l;
}
}
}
function exportExcel(dataType){ var fountId = document.getElementById("fountId").value; var gprsId = document.getElementById("gprsId").value; var startDt = document.getElementById("startDt").value; var startHou = document.getElementById("startHou").value; var endDt = document.getElementById("endDt").value; var endHou = document.getElementById("endHou").value; var reName = document.getElementById("reName").value; var url = ""; var params = "fountId=" + fountId + "&gprsId=" + gprsId + "&startDt=" + startDt + "&startHou=" + startHou + "&endDt=" + endDt + "&endHou=" + endHou + "&reName=" + reName; if(dataType == 'amount'){ var typeId = document.getElementById("typeId").value; url = "excelAmountAction.do"; params += "&typeId=" + typeId; } if(dataType == 'quality'){ url = "excelQualityAction.do"; } var ajax = new Ajax.Request(url,{method:"get",parameters:params,onLoading:load,onComplete:complete}); //loading function function load(){ var loadDiv = document.getElementById("load"); var button = document.getElementById("excelButton"); if(loadDiv != null && loadDiv != undefined){ button.disabled = true; loadDiv.style.display = ""; loadDiv.innerHTML = "<img src=\"images/loading5.gif\"/>正在导出excel文件,请稍等..."; } } function complete(response){//call back function var loadDiv = document.getElementById("load"); loadDiv.style.display = "none"; var xml = response.responseText; var objs = eval('(' + xml + ')'); var obj = objs.jobj; var alt = obj.message; var fobjs = obj.files; var tbDiv = document.getElementById("excelFile"); var tb = new Array(); tb.push("<table width=\"100%\">"); tb.push("<tr>"); tb.push("<td width=\"5%\">"); tb.push(" "); tb.push("</td>"); tb.push("<td width=\"85%\">"); tb.push("文件名称"); tb.push("</td>"); tb.push("<td width=\"10%\">"); tb.push("下载"); tb.push("</td>"); tb.push("</tr>"); for(var i = 0;fobjs != null && i < fobjs.length;i++){ var file = fobjs[i]; var fName = file.name; var fPath = file.path; tb.push("<tr>"); tb.push("<td>"); tb.push("<img src=\"images/excel.jpg\">"); tb.push("</td>"); tb.push("<td>"); tb.push(fName); tb.push("</td>"); tb.push("<td>"); tb.push("<a href=\"downloadAction.do?fileName=" + fPath + "\">") tb.push("<img src=\"images/download.gif\">"); tb.push("</a>"); tb.push("</td>"); tb.push("</tr>"); } tb.push("</table>"); tbDiv.innerHTML = tb.join(""); //返回信息提示 alert(alt); document.getElementById("excelButton").disabled = false; } }
附注:关于prototype.js +ajax 中文乱码问题,在action中标注:
request.setCharacterEncoding("GBK");
response.setContentType("text/xml;charset=GBK");
在得到参数后需要转码,从提交页面编码格式转成ajax编码格式(utf-8)
String city = request.getParameter("city");//获得城市名称
city = new String(city.getBytes("GBK"),"UTF-8");