import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.hssf.util.Region;

//使用poi生成excel表格

public void ExportExcel(ArrayList arrList,OutputStream out){

        PublicFunction fun = new PublicFunction();

        // 声明一个工作薄

        HSSFWorkbook workbook = new HSSFWorkbook();

        // 生成一个表格

        HSSFSheet sheet = workbook.createSheet("Sheet1");

        // 设置表格默认列宽度为20个字节

        sheet.setDefaultColumnWidth();

        // 生成表格单元样式

        HSSFCellStyle style = workbook.createCellStyle();

        // 设置表格单元样式

        style.setFillForegroundColor(HSSFColor.WHITE.index);

        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        style.setBorderRight(HSSFCellStyle.BORDER_THIN);

        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 生成表格单元字体

        HSSFFont font = workbook.createFont();

        font.setColor(HSSFColor.BLACK.index);

        font.setFontHeightInPoints((short) );

        // 把字体应用到当前的样式

        style.setFont(font);

        // 生成标题样式

        HSSFCellStyle styletitle = workbook.createCellStyle();

        styletitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        styletitle.setFillBackgroundColor(HSSFColor.LIME.index);

        HSSFFont fonttitle = workbook.createFont();

        fonttitle.setColor(HSSFColor.BLACK.index);

        fonttitle.setFontHeightInPoints((short) );

        fonttitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 把字体应用到标题样式

        styletitle.setFont(fonttitle);

        // 生成表头样式

        HSSFCellStyle stylehead = workbook.createCellStyle();

        stylehead.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        stylehead.setFillBackgroundColor(HSSFColor.AQUA.index);

        HSSFFont fonthead = workbook.createFont();

        fonthead.setColor(HSSFColor.BLACK.index);

        fonthead.setFontHeightInPoints((short) );

        fonthead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 把字体应用到表头样式

        stylehead.setFont(fonthead);

        //创建空白表格

        for(int i=;i<arrList.size()+;i++){//行数(包括标题)

            HSSFRow row = sheet.createRow(i);

            if(i == ){

                row.setHeight((short)(15.625*));

                for(int j=;j<;j++){//列数

                    HSSFCell cell = row.createCell(j);

                    cell.setCellStyle(styletitle);

                }

            }else if(i == ){

                row.setHeight((short)(15.625*));

                for(int j=;j<;j++){//列数

                    HSSFCell cell = row.createCell(j);

                    cell.setCellStyle(stylehead);

                }

            }else{

                for(int j=;j<;j++){//列数

                    HSSFCell cell = row.createCell(j);

                    cell.setCellStyle(style);

                }

            }

        }

        sheet.setColumnWidth(, (short)(35.7*));

        sheet.setColumnWidth(, (short)(35.7*));

        //填充数据

        sheet.addMergedRegion(new Region(,(short),,(short)));

        HSSFCell celltitle = sheet.getRow().getCell();

        //设置标题

        celltitle.setCellValue("矿权到期检查报表");

        //设置表头

        sheet.getRow().getCell().setCellValue("序号");//

        sheet.getRow().getCell().setCellValue("许可证号");//

        sheet.getRow().getCell().setCellValue("申请人");//

        sheet.getRow().getCell().setCellValue("有效期止");//

        sheet.getRow().getCell().setCellValue("状态");//

        sheet.getRow().getCell().setCellValue("天数");//

        for(int i=;i<arrList.size()+;i++){//行数

            KQDQJC_Bean bean = new KQDQJC_Bean();

            bean = (KQDQJC_Bean)arrList.get(i-);

            for(int j=;j<;j++){//列数

                switch(j){

                    case :

                        sheet.getRow(i).getCell(j).setCellValue(String.valueOf(i-));//

                        break;

                    case :

                        sheet.getRow(i).getCell(j).setCellValue(bean.getXKZH());//

                        break;

                    case :

                        sheet.getRow(i).getCell(j).setCellValue(bean.getSQR());//

                        break;

                    case :

                        sheet.getRow(i).getCell(j).setCellValue(fun.setTimeFormat(bean.getYXQZ()));//

                        break;

                    case :

                        sheet.getRow(i).getCell(j).setCellValue(bean.getZT());//

                        break;

                    case :

                        sheet.getRow(i).getCell(j).setCellValue(bean.getTS());//

                        break;

                }

            }

        }

        try {

            workbook.write(out);

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

}

public Boolean dqjc_QueryForTz(String jcrq,String jcts,OutputStream out){

        Boolean isSuccess = false;

        String PagesExl = "";

        Connection dbConn = null;

        int iJcts = Integer.parseInt(jcts);//到期提示天数

        try{

            dbConn = ServiceContext.getLandDbConnection();

            KYQGL_Query query = new KYQGL_Query();

            ArrayList arrList = query.kqdqjc_GetQueryItemAll(dbConn,jcrq,iJcts);

            query.ExportExcel(arrList,out);

            isSuccess = true;

        }catch(Exception e){

            logger.error("[导出矿权到期检查报表失败:]" + e);

        }finally{

            ServiceContext.releaseLandDbConnection(dbConn);

        }

        return isSuccess;

}

<%@ page language="java" pageEncoding="GBK" %>

<%@page import="com.zhongzhi.gis.clientapp.kyqgl.kyqgl.query.KYQGL_DataQuery"%>

<%@page import="java.io.OutputStream"%>

<%

    String jcrq = "";//检查日期

    jcrq = request.getParameter("jcrq");

    jcrq = java.net.URLDecoder.decode(jcrq,"UTF-8");

    KYQGL_DataQuery query = new KYQGL_DataQuery();

    String fileName = "矿权到期检查.xls";

    OutputStream os = null;

    try{

        //设置文件头

        response.setContentType("application/msexcel");

        response.setHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes("GB2312"),"ISO-8859-1") + ";");

        out.clear();

        out=pageContext.pushBody();

        os = response.getOutputStream();

        query.dqjc_QueryForTz(jcrq,"30",os);//设置到期提醒天数为30天

        os.flush();

    }catch(Exception e){

        System.out.println(e);

    }

%>

function query_dqjc_dataForTz(){

    var jcrq = $("#dcjcrq").val();

    var url =  "pages/kyqgl/kyqgl/util/dqjc_QueryResultForTZ.jsp?jcrq="+jcrq;

    window.location.href = url;

}