导出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

/**
 * 导出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();
}