天易26----java导出excel表格(支持wps和office excel)

一:页面图片显示预览:

1)


2)点击导出按钮后预览:


3)最终生成的excel表格图片预览:


二:代码演示:

说明:执行操作时,请先引进导出excel表格的jar文件包,在本人的资源里面可以下载,网上搜索下载也即可,

找到导出按钮所执行的js方法,在java后天查看该方法的实现即可

1)jsp代码:

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@taglib prefix="s" uri="/struts-tags" %>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>
<html>
<head>
<base href="<%=basePath%>">
<title>驾校合格率排名</title>

<link href="jsp/commonstyle/css/tabStyle.css" rel="stylesheet" type="text/css">
<link rel="STYLESHEET" type="text/css" href="<%=basePath%>jsp/hgltj/js/tablesort.css">
<script type="text/javascript" src="<%=basePath%>jsp/system/common/js/publicColor.js"></script>
<script type="text/javascript" src="jsp/commonstyle/js/js/My97DatePicker/WdatePicker.js" defer="defer"></script>
<script type="text/javascript" src="<%=basePath%>jsp/hgltj/js/tablesort.js"></script>
<script language="JavaScript">
function load()
{
//根据分辨率设置表格大小
maxw=document.getElementById("maintb").offsetWidth;
	if(maxw<824){//1024分辨率未展开
	   mainbox.width="98%";
	} else if(maxw<1013){//1024分辨率展开
	   mainbox.width="98%";
	} else  if(maxw<1081){//1280分辨率未展开
	   mainbox.width="95%";
	} else  if(maxw<1270){//1280分辨未展开
	   mainbox.width="95%";
	}else{//1280以上分辨展开
	   mainbox.width="98%";
	}
}
</script>
<script>
function overIt(){
   var the_obj = event.srcElement;
   if(the_obj.tagName.toLowerCase() == "td"){
       the_obj=the_obj.parentElement;
       the_obj.oBgc=the_obj.currentStyle.backgroundColor;            
	   the_obj.oFc=the_obj.currentStyle.color;
       the_obj.style.backgroundColor='#4073C4';
       the_obj.style.color='#ffffff';
       the_obj.style.textDecoration='underline';
   }
}
 
function outIt(){
   var the_obj = event.srcElement;
   if(the_obj.tagName.toLowerCase() == "td"){
       the_obj=the_obj.parentElement;
       the_obj.style.backgroundColor=the_obj.oBgc;
       the_obj.style.color=the_obj.oFc;
       the_obj.style.textDecoration='';
   }
}
function serch(){
document.getElementById("formName").action="<%=basePath %>hgltj.action?method=getHglpm";
document.getElementById("formName").submit();
}
	
function tbbt(){
var jzrq=document.getElementById("jzrqId").value;
//var jxmc=document.getElementById("jxmcId").value;
window.open('<%=basePath %>hgltj.action?method=getHglpmTb&tjjxkshgl.jzrq='+jzrq+'&tjjxkshgl.zt='+1,'','height=650,width=1250,top=150,left=200,toobar=no,menubar=no,scrollbars=yes,resizable=no,location=no,');
}

function openwd(){
document.getElementById("formName").action="<%=basePath %>hgltj.action?method=downJxhglPm";
document.getElementById("formName").submit();
}
</script>
</head>
<BODY onLoad="load()" style="background: url(images/cont_bg.gif); background-repeat: repeat-y">
  <input type="hidden" name="method" value="getDriverInfoList"/>
  <table border="0" width="100%" cellspacing="0" cellpadding="0" id="maintb">
	<tr>
	  <td align="center">
		<table width="90%"  border="0"  cellspacing="0" cellpadding="0" id="mainbox" style="background:url(jsp/commonstyle/images/usermessage_02.gif); background-repeat:repeat-x;" >
		<!--宽度可变内容框-->
			<tr >
              <td width="33" height="27" style=" background:url(jsp/commonstyle/images/usermessage_01.gif); background-position:left; background-repeat:no-repeat;" ></td>
              <td width="965" class="style1"><font color="black">驾校合格率排名</font></td>
          	  <td width="14" height="27" style=" background:url(jsp/commonstyle/images/usermessage_03.gif); background-position:right; background-repeat:no-repeat;"> </td>
		  	</tr>
            <tr>
			  <td colspan="3">
			  <!--页面主体内容开始-->
			  <!--查询条件-->
			  <form action="" name="formName" method="post" style="margin:0px" id="formName" theme="simple">
	           <table width="100%" border="0" cellpadding="0" cellspacing="0"   style="border-collapse:collapse;" id="tj" align="center">
	             <tr align="left">
	                <td class="tjbg1" style="text-align: left">
	                <!-- <input type="hidden" id="method"  name="method" value="getHglpm"/> -->
				     统计日期:
				    <input type="text" name="tjjxkshgl.jzrq" id="jzrqId" value="<s:property value="tjjxkshgl.jzrq"/>" οnclick="WdatePicker({skin:'whyGreen',dateFmt:'yyyy-MM'})"/>        
				    <!-- 驾校名称:
				    <s:select id="jxmcId"  name="tjjxkshgl.jxxh"  value="tjjxkshgl.jxxh" list="schoolList" listKey="jxxh" listValue="jxmc" headerKey="" headerValue="--请选择--" theme="simple"></s:select>        -->
				    <input name="input" value=" 统 计 " type="button" class="normalbtn" onClick="serch()" style="margin-bottom: 5px" />        
				    <input name="input" type="button" value=" 合格率图表 " οnclick="tbbt()" class="normalbtn" style="margin-bottom: 5px" />        
				    <input id="Button1" type="button" value=" 导 出 " οnclick="openwd();" class="normalbtn" style="margin-bottom: 5px" />
				    </td>
	           </tr>
	         </table><!--查询结果-->
	           </form>
				<!--查询结果-->
			    <table width="100%" border="0" cellpadding="4" cellspacing="1" bgcolor="#abcfff" id="cxjg" align="center">
				<thead>
					<tr class="tbtitle">
						<td width="4%" align="center" class="t1">名次</td>
						<td width="8%" align="center" class="t1">名称</td>
						<td class="t1" width="5%" align="center">科目一</td>
						<td class="t1" width="5%" align="center">科目二</td>
						<td class="t1" width="5%" align="center">科目三</td>
						<td class="t1" width="5%" align="center">平均合格率</td>
						<td class="t1" width="5%" align="center">操作</td>
					</tr>
				</thead>
				<s:iterator id="jxhgl" value="jxhelpmList" status="st">
				<tr class="changeColor" onMouseOver="overIt()" onMouseOut="outIt()" style="cursor: hand" align="center">
					<td><s:property value="#st.index+1"/></td>
					<td><s:property value="#jxhgl.jxmc"/></td>
					<td><s:property value="#jxhgl.km1hgl"/></td>
					<td><s:property value="#jxhgl.km2hgl"/></td>
					<td><s:property value="#jxhgl.km3hgl"/></td>
					<td><s:property value="#jxhgl.avghgl"/>%</td>
					<td>
					<a href="javascript:" οnclick="openWin('<%=basePath %>hgltj.action?method=getTbForJxxh&tjjxkshgl.jxxh=<s:property value="#jxhgl.jxxh"/>&tjjxkshgl.zt=1','',1250,750);">图表</a>
					</td>
				</tr>
				</s:iterator>
			 </table>
			</table>
				</td>
			</tr>
		</table>
	</body>
</html>

2)java代码演示:

/**
	 * 驾校合格率导出excel图表
	 */
	public void downJxhglPm(){
		codeList=codesService.getcode("1042");//码表查值
		jxhelpmList=hglTjService.getJxhglpm(tjjxkshgl);//驾校合格率排名
		for(int i =0;i<jxhelpmList.size();i++){
			TjJxkshgl tt = (TjJxkshgl)jxhelpmList.get(i);
		    tt.setJxmc(CodeUtil.getJxjcByJxxh(codeList,tt.getJxxh()));
		}
		
		OutputStream os=null;
		WritableWorkbook wbook=null;
		try 
		{ 
		os = new BufferedOutputStream(response.getOutputStream());//得到输出流的语句 
         //response.getOutputStream();// 取得输出流   
        response.reset();// 清空输出流   
        String tmptitle = "驾校合格率排名"; // 标题   
        response.setHeader("Content-disposition", "attachment; filename="+new String(tmptitle.getBytes(),"iso8859-1")+".xls");// 设定输出文件头   
        response.setContentType("application/vnd.ms-excel");// 定义输出类型 
         wbook = Workbook.createWorkbook(os); // 建立excel文件   
        WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称  
		// 设置excel标题
		WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK); //设置表格样式  
		WritableCellFormat wcfFC = new WritableCellFormat(wfont); 
		//wcfFC.setBackground(Colour.AQUA);
		Label label=new Label(0, 0, tmptitle, wcfFC);    
		WritableCellFormat cellFormat=new WritableCellFormat();     
		cellFormat.setAlignment(jxl.format.Alignment.CENTRE);     
	        cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 
		cellFormat.setWrap(true);  
		//cellFormat.setBackground(Colour.AQUA);
		cellFormat.setFont(wfont);
		label.setCellFormat(cellFormat);       
		wsheet.addCell(label);
		//wsheet.addCell(new Label(0, 0, tmptitle, wcfFC));
		wsheet.setRowView(0,500); //第一行高度
		wsheet.mergeCells(0, 0, 6, 1);  //合并单元格(第一列的第一行和第七列的第二行合并)    
		//wsheet.mergeCells(0, 1, 9, 1);    
//		wsheet.mergeCells(0, 2, 0, 4);     
//		wsheet.mergeCells(1, 2, 3, 2);     
//		wsheet.mergeCells(4, 2, 6, 2);     
//		wsheet.mergeCells(7, 2, 9, 2);     
		wsheet.setColumnView(0,10); //宽度
		wsheet.setColumnView(1,25); //宽度
		wsheet.setColumnView(2,10); //宽度
		wsheet.setColumnView(3,10); //宽度
		wsheet.setColumnView(4,10); //宽度
		wsheet.setColumnView(5,10); //宽度
		// 开始生成主体内容       
		wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
		wcfFC = new WritableCellFormat(wfont);
		wsheet.addCell(new Label(0, 2, "名次",wcfFC));   
		wsheet.addCell(new Label(1, 2, "驾校名称",wcfFC));   
		wsheet.addCell(new Label(2, 2, "科目一",wcfFC));  
		wsheet.addCell(new Label(3, 2, "科目二",wcfFC));
		wsheet.addCell(new Label(4, 2, "科目三",wcfFC)); 
		wsheet.addCell(new Label(5, 2, "合格率",wcfFC));  
		
			int count=jxhelpmList.size();
				if(count>0){  判断集合是否不为0
					TjJxkshgl tjhgl=null;
					for(int i=0;i<jxhelpmList.size();i++){ 
						tjhgl=(TjJxkshgl)jxhelpmList.get(i);
						wsheet.addCell(new Label(0, i+3, (i+1)+""));
						wsheet.addCell(new Label(1, i+3, tjhgl.getJxmc()));
						wsheet.addCell(new Label(2, i+3, tjhgl.getKm1hgl()));
						wsheet.addCell(new Label(3, i+3, tjhgl.getKm2hgl()));
						wsheet.addCell(new Label(4, i+3, tjhgl.getKm3hgl()));
						wsheet.addCell(new Label(5, i+3, tjhgl.getAvghgl()));
						}
					}   
					wbook.write(); // 写入文件  
				}
				catch(Exception ex) 
				{ 
					ex.printStackTrace(); 
				}finally{
					if(os!=null){
						try {
							os.close();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}if(wbook!=null){
						try {
							wbook.close();
						} catch (WriteException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
					
				} 
				
				
			}


  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值