<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):
// 慢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; }
manager代码：
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; } }
ExcelTemRDSExport:
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); } } }
ExcelExport:
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(); } }