<div id="rds_list_toolbar" style="padding:5px;">
            <a id="slowLogs_excel_btn" href="javaScript:void(0);" class="easyui-linkbutton" data-options="iconCls:'icon-edit'">导出Excel</a>
        </div>

点击之后:

$("#slowLogs_excel_btn").click(function(){
                  //获取查询加载数据的总数
                  var total=dataGridTable.datagrid('getData').total;
                  console.info(total);
                  var url=webContext+'/rds/rds_exportSlowLogsList.action?rds.dbinstanceid='+dbinstanceid
                      +'&engine='+engine+'&start_time='+$('#start_time').datebox('getValue')
                      +'&end_time='+$('#end_time').datebox('getValue')+'&dBName='+$('#dBName').combobox('getValue')
                      +'&total='+total;
                  $('#slowLogs_excel_btn').attr("href", url);
                  /* Ajax('/rds/rds_exportSlowLogsList.action?rds.dbinstanceid='+dbinstanceid+'&engine='+engine,{
                      start_time:$('#start_time').datebox('getValue'),
                    end_time:$('#end_time').datebox('getValue'),
                    dBName:$('#dBName').combobox('getValue')
                  },function(data){
                      if(data.success){
                          //Alert('提示','删除成功!');
                      }else{
                          Alert('提示','导出excel失败:'+data.msg);  
                      }
                }); */
            });

java代码(action):

导出excel_数据库导出excel_数据库_02
// 慢sql统计-导出excel
    public String exportSlowLogsList() {
        HttpServletRequest request = ServletActionContext.getRequest();
        String engine = request.getParameter("engine");
        String dBName = request.getParameter("dBName");
        String start_time = request.getParameter("start_time");
        String end_time = request.getParameter("end_time");
        //要导出的总记录数
        String total = request.getParameter("total");
        if (StringUtils.isNotEmpty(start_time)) {
            rds.setStartTime(start_time + "Z");
        }
        if (StringUtils.isNotEmpty(end_time)) {
            rds.setEndTime(end_time + "Z");
        }
        rds.setDbName(dBName);
        rds.setPageNo(1);
        if(total==null||Integer.parseInt(total)<=30){
            //最小也要设置30条数据
            rds.setPageSize(30);
        }else{
            //大于30条
            rds.setPageSize(Integer.parseInt(total));
        }
        try {
            rdsManager.exportSlowLogsList(rds,engine,dBName);
            simpleJSON.put("success", true);
        } catch (Exception e) {
            simpleJSON.put("success", false);
            simpleJSON.put("msg", e.getMessage());
            e.printStackTrace();
        }
        return null;
    }
View Code

manager代码:

导出excel_数据库导出excel_数据库_02
public void exportSlowLogsList(Rds entity, String engine, String dBName)
            throws Exception {
        try {
            RdsDescribeErrorLogsVO errorLogsVO = new RdsDescribeErrorLogsVO();
            errorLogsVO.setdBInstanceId(entity.getDbinstanceid());
            errorLogsVO.setStartTime(entity.getStartTime());
            errorLogsVO.setEndTime(entity.getEndTime());
            errorLogsVO.setPageNumber((long) entity.getPageNo());
            errorLogsVO.setPageSize((long) entity.getPageSize());
            if ("SQLServer".equals(engine)) {
                errorLogsVO.setSortKey("TotalQueryTimes");
            }
            if (!"全部数据库".equals(dBName)) {
                errorLogsVO.setdBName(dBName);
            }
            errorLogsVO.setOwnerId(SysParametersUtil
                    .getParameterValueForDefault("AliyunAdminOwnerId",
                            "1467232540347717"));
            RdsDescribeErrorLogsVO response = (RdsDescribeErrorLogsVO) new RdsClientInterface()
                    .pcitcExecute(errorLogsVO, new DescribeSlowLogsRequest(),
                            new DescribeSlowLogsResponse());
            if (!response.isSuccess()) {
                throw new RuntimeException(" 错误代码:" + response.getErrorCode()
                        + "  描述:" + response.getMessage());
            } else {
                HttpServletResponse hresponse = ServletActionContext
                        .getResponse();
                List<SQLSlowLog> list = response.getItems();
                ExcelExport eet = new ExcelTemRDSExport(getExportTemplate(list,
                        entity.getDbinstanceid()), hresponse, entity);
                eet.export();
            }
        } catch (Exception e) {
            throw e;
        }

    }
View Code

ExcelTemRDSExport:

导出excel_数据库导出excel_数据库_02
package com.aliyun.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.aliyun.pcitcAliyun.rds.model.Rds;
import com.sinopec.common.exception.BaseRuntimeException;
import com.sinopec.common.util.PlatForm;

/**
 * excel导出RDS慢SQL信息
 *@Description 
 * @author longjiazuo
 * @created 2015年7月17日 下午1:48:05
 * @version 
 * @history
 * @see
 */
public class ExcelTemRDSExport extends ExcelExport{

    private Rds rds;
    
    public ExcelTemRDSExport(ExportDateTemplate eeb, HttpServletResponse response,Rds rds) {
        super(eeb, response);
        this.rds=rds;
        // TODO 自动生成的构造函数存根
    }
    /**
     * 如果使用模板的方式导出excel数据,该方法可以获取模板存放的路径
     */
    public String getFileName(){
        String webRoot=PlatForm.getAbsolutePath();    
        String filePath=webRoot+"/"+eeb.getPath()+"/"+eeb.getRelativePath()+"/"+eeb.getTemplateName();
        return filePath;
    }
    public  void createXSSFWorkbook() throws FileNotFoundException, IOException{
        String fileName = getFileName();
        File excel = new File(fileName);
        xworkbook = new XSSFWorkbook(new FileInputStream(excel));
        if(!excel.exists()){
            throw new BaseRuntimeException("模板文件:" + fileName + "  不存在");
        }    
        createSheet(0);
         
        List<List<Map<Integer,Object>>> sheetList=eeb.getSheetData();
        for(int i=1;i<sheetList.size();i++){
            createSheet(i);
        }
    }
    
    //创建工作簿并装配数据
    public  void createSheet(int sheetNumber) {
        if(sheetNumber<0)
            return;
        try {            
            XSSFSheet xssfSheet=null;
            //用指定模板,获取工作簿 
            xssfSheet=xworkbook.getSheetAt(sheetNumber); 
            List<Map<Integer,Object>> data=null;
            if(sheetNumber==0)
                data= eeb.getData();    
            else 
                data=eeb.getSheetData().get(sheetNumber);
            if(data!=null){
                //创建单元格样式
                XSSFCellStyle cellStyle=xworkbook.createCellStyle();
                cellStyle.setAlignment(XSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 
                //创建单元格字体
                XSSFFont font=xworkbook.createFont();
                font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
                font.setFontName("Arial");
                //font.setFontHeight((short) 300);
                font.setFontHeightInPoints((short)10);
                cellStyle.setFont(font); 
                //创建自定义行和列
                //数据库名称行
                XSSFRow row0=xssfSheet.createRow(0);
                XSSFCell cell0_0=row0.createCell(0);
                cell0_0.setCellValue("表格名称");
                cell0_0.setCellStyle(cellStyle);
                XSSFCell cell0_1=row0.createCell(1);
                cell0_1.setCellValue("慢日志汇总");
                cell0_1.setCellStyle(cellStyle);
                //数据库名称行
                XSSFRow row1=xssfSheet.createRow(1);
                XSSFCell cell1_0=row1.createCell(0);
                cell1_0.setCellValue("数据库名称");
                cell1_0.setCellStyle(cellStyle);
                XSSFCell cell1_1=row1.createCell(1);
                cell1_1.setCellValue(rds.getDbName());
                cell1_1.setCellStyle(cellStyle);
                //起始时间行
                XSSFRow row2=xssfSheet.createRow(2);
                XSSFCell cell2_0=row2.createCell(0);
                cell2_0.setCellValue("起始时间");
                cell2_0.setCellStyle(cellStyle);
                XSSFCell cell2_1=row2.createCell(1);
                cell2_1.setCellValue(rds.getStartTime().replace("Z", ""));
                cell2_1.setCellStyle(cellStyle);
                //结束时间行
                XSSFRow row3=xssfSheet.createRow(3);
                XSSFCell cell3_0=row3.createCell(0);
                cell3_0.setCellValue("结束时间");
                cell3_0.setCellStyle(cellStyle);
                XSSFCell cell3_1=row3.createCell(1);
                cell3_1.setCellValue(rds.getEndTime().replace("Z", ""));
                cell3_1.setCellStyle(cellStyle);
                for(int i=0,rowstart=eeb.getStartRow(sheetNumber); i<data.size(); i++,rowstart++){
                    Map<Integer,Object> rowdata = data.get(i);
                    XSSFRow row=xssfSheet.createRow(rowstart);
                     
                    for(int j=0,colstart=eeb.getStartCol(sheetNumber); j<rowdata.size(); j++,colstart++){
                        XSSFCell cell=row.createCell(colstart);
                        Object value=rowdata.get(Integer.valueOf(j));
                        if(value==null||value.equals("")){
                            cell.setCellValue("");    
                        }
                        else if (value instanceof Date) {
                            String pattern="yyyy-MM-dd";
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            cell.setCellValue(sdf.format(date));
                        } 
                        else{ 
                            //System.out.println(value);
                            cell.setCellValue(value.toString());    
                        }
                    }
                }
            } 
        } catch (Exception e) {
            throw new BaseRuntimeException("创建工作薄组装数据时发生异常", e);
        }
    }
}
View Code

ExcelExport:

导出excel_数据库导出excel_数据库_02
package com.aliyun.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



import com.sinopec.common.excel.exp.ExcelExportColAttr.CellValType;
import com.sinopec.common.exception.BaseRuntimeException;
import com.sinopec.common.util.PlatForm;
import com.sinopec.system.SysConstants;
import com.sinopec.system.workflow.service.imp.WkflwManagerImp;

@SuppressWarnings("unused")
public abstract class ExcelExport {
    private static Logger log = Logger.getLogger(WkflwManagerImp.class.getName());
     

    /**
     * 声明一个工作薄由子类完成其对象的创建
     */ 
    protected  XSSFWorkbook xworkbook = null;
    /**
     * 具体的导出excel数据的业务实现类
     */
    protected ExportDateTemplate eeb = null;
    protected ExportDateTemplate[] eebes = null;
    /**
     * 请求响应对象
     */
    private HttpServletResponse response;

    public ExcelExport(ExportDateTemplate eeb, HttpServletResponse response) {
        this.eeb = eeb;
        this.response = response;
    }
    public ExcelExport(ExportDateTemplate[] eebes, HttpServletResponse response) {
        this.eebes = eebes; 
        this.response = response;
    }
    public abstract void createXSSFWorkbook()throws Exception; 
    /**
     * 调用此方法,导出excel数据
     * @author hanxg
     * @date 2012-11-27
     */
    public void export(){
        OutputStream outs = null;
        try {
            outs = response.getOutputStream();
               this.setResponse();
        //    outs = new FileOutputStream(new File("d:\\testexport.xls"));
            createXSSFWorkbook();
            if (xworkbook == null)
                throw new BaseRuntimeException("创建工作薄失败");
            xworkbook.write(outs);
            outs.flush();
        } catch (Exception e) {
            throw new BaseRuntimeException("创建WritableWorkbook发生异常", e);
        }finally {
            try {
                if(outs!=null){
                  outs.close();
                }
            } catch (Exception e) {
                throw new BaseRuntimeException("导出excel关闭流时发生异常", e);
            } 
        }
    }
    
    private void setResponse() {
        String newName = null;
        try {
            if(eeb!=null)
                  newName =  eeb.getFileName();
            else
                newName =eebes[0].getFileName();
            newName=new String(newName.getBytes("GBK"), "iso8859-1");     
//            //weblogic
//            if(SysConstants.APPSERVER.equalsIgnoreCase("weblogic")){
//                newName=new String(newName.getBytes(), "iso8859-1");
//            }
//            else{//tomcat
//            if(eeb!=null)
//              newName = new String(newName.getBytes(), "iso8859-1");
//            else
//                 newName = new String(newName.getBytes(), "iso8859-1");
//            }             
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/octet-stream;");
        response.setHeader("Content-Disposition", "attachment;filename=" + newName);
    }

    public static void main(String[] args) throws FileNotFoundException{

//            InputStream    input=Thread.currentThread().getContextClassLoader().getSystemResourceAsStream("com/sinopec/common/excel/imp/testbean.xlsx");
//            FileOutputStream ost=new FileOutputStream(new File("d:testexport.xlsx"));
//            TestTemplate tt=new TestTemplate();
//            ExcelExport ee=new ExcelNoTemExport(tt,null);
//            ExcelExport eet=new ExcelTemExport(tt,null);    //有模板
//            ee.export();
//            eet.export();
            
    }
            

}
View Code

 

怀有希望!!