flex通过AdvancedDataGrid合并单元格,导出为excel表格

flex通过AdvancedDataGrid组件合并单元格,通过java端动态绑定AdvancedDataGrid的源数据,并导出AdvancedDataGrid源数据为excel表格

(注意:该文件适用于表头最多由4行组成的,若低于4行,AdvancedDataGrid组件下应全部使用AdvancedDataGridColumnGroup)

特别注意:若为表头为4行组成并有合并单元格,应注意AdvancedDataGridColumnGroup和AdvancedDataGridColumn的使用

 

1.创建一个.mxml文件

<?xml version="1.0" encoding="utf-8"?>
<viewer:BaseWidget xmlns:fx="http://ns.adobe.com/mxml/2009"
				   xmlns:s="library://ns.adobe.com/flex/spark"
				   xmlns:mx="library://ns.adobe.com/flex/mx"
				   xmlns:viewer="com.commpent.widget.*"
				   xmlns:quick="com.commpent.quick.*"		  
				   xmlns:esri="http://www.esri.com/2008/ags"				 
				   xmlns:cym="com.commpent.toc.utils.*"				 
				   layout="absolute" creationComplete="init(event)">
	<fx:Declarations>
		<!-- 将非可视元素(例如服务、值对象)放在此处 -->
	</fx:Declarations>
	<fx:Script>
		<![CDATA[
			import com.adobe.utils.StringUtil;
			import com.as3xls.xls.ExcelFile;
			import com.as3xls.xls.Sheet;
			import com.commpent.toc.utils.ExportToExcelTool;
			import com.kingtop.tasks.QueryDataBaseTask;
			import com.sys.GobalConfig;
			
			import mx.collections.ArrayCollection;
			import mx.controls.Alert;
			import mx.core.FlexGlobals;
			import mx.events.FlexEvent;
			
			import spark.events.IndexChangeEvent;

			protected function init(event:FlexEvent):void
			{
				//初始化县(市区)的下拉列表
				var url:String = GobalConfig.gobalXML.gobalWebgisUrl+"/cardEditAction.do?method=queryForListByPidOther";
				var querydatabasetask:QueryDataBaseTask = new QueryDataBaseTask(url);
				var xsq:String = FlexGlobals.topLevelApplication.userXZQH;
				querydatabasetask.webParameters = ["code","pid"];
				querydatabasetask.webValues = ["XMSJQ","0"];
				querydatabasetask.queryComplete = OnSearchTypeComplete;
				querydatabasetask.query();
				function OnSearchTypeComplete(event:Event):void
				{
					var loder:URLLoader = event.target as URLLoader; 	 
					try{       	  
						var jsonObject:Object = JSON.parse(loder.data);	      
						if(jsonObject.length == 0){
							return;
						}	      	      
						var array:Array = jsonObject.nodes as Array;
						var arrayxsq:Array = new Array();
						if(xsq == GobalConfig.gobalXML.deptCodeXM || xsq == GobalConfig.gobalXML.deptCodeXMZ){
							arrayxsq = array;
						}else{
							for(var i:int = 0; i<array.length;i++){
								if(array[i]['value'] == xsq){
									arrayxsq.push(array[i]);
									break;
								}
							} 
						}
						XSQ.dataProvider = new ArrayCollection(arrayxsq);
					}catch(error:Error){
						Alert(error.toString());
					}
				}
			}
			
			//随着下拉列表的选择的改变,初始化乡(镇场)或村(工区)的下拉列表
			protected function province_changeHandler(event:IndexChangeEvent):void  
			{  
				var url:String = GobalConfig.gobalXML.gobalWebgisUrl+"/cardEditAction.do?method=queryForListByPidOther";
				var querydatabasetask:QueryDataBaseTask = new QueryDataBaseTask(url);
				querydatabasetask.webParameters = ["code","pid"];
				var sin:int = event.target.selectedIndex;
				try{
					querydatabasetask.webValues = ["XMSJQ",event.target.selectedItem.id];
				}catch(error:Error){
					Alert.show("请从下拉列表中选取!","温馨提示:");
					event.target.selectedIndex = -1;
					event.target.textInput.setFocus();
					return;
				}
				querydatabasetask.queryComplete = OnSearchTypeComplete;
				querydatabasetask.query();
				var id:String = event.target.id;
				function OnSearchTypeComplete(event:Event):void
				{
					var loder:URLLoader = event.target as URLLoader; 	 
					try{       	  
						var jsonObject:Object = JSON.parse(loder.data);	      
						if(jsonObject.length == 0){
							return;
						}	      	      
						var array:Array = jsonObject.nodes as Array;
						if(id == 'XSQ')
						{
							//由于县(市区)下拉列表的选择的改变,所以需将乡(镇场)和村(工区)的下拉列表重置
							XZC.selectedIndex = -1;
							CGQ.selectedIndex = -1;
							XZC.dataProvider = new ArrayCollection(array);
							CGQ.dataProvider = new ArrayCollection();
						}
						else if(id == 'XZC')
						{
							//由于乡(镇场)下拉列表的选择的改变,所以需将村(工区)的下拉列表重置
							CGQ.selectedIndex = -1;
							CGQ.dataProvider = new ArrayCollection(array);
						}
					}catch(error:Error){
						Alert(error.toString());
					}
				}
			}  


			protected function queryData(event:MouseEvent):void
			{
				//根据选择的下拉列表值,统计对应的数据并显示在AdvancedDataGrid表格中
				var url:String = GobalConfig.gobalXML.gobalWebgisUrl+"/reportAction.do?method=queryWoodland";
				var querydatabasetask:QueryDataBaseTask = new QueryDataBaseTask(url);
				var xsq:String = XSQ.selectedIndex == -1 ? "NULL" : ((String)(XSQ.selectedItem.name)).substring(0,6);
				var xzc:String = XZC.selectedIndex == -1 ? "NULL" : ((String)(XZC.selectedItem.name)).substring(0,3);
				var cgq:String = CGQ.selectedIndex == -1 ? "NULL" : ((String)(CGQ.selectedItem.name)).substring(0,3);
				querydatabasetask.webParameters = ["STA_CODE_XSQ","STA_CODE_XZC","STA_CODE_CGQ"];
				querydatabasetask.webValues = [xsq,xzc,cgq];
				querydatabasetask.queryComplete = OnSearchTypeComplete;
				querydatabasetask.query();
				function OnSearchTypeComplete(event:Event):void
				{
					var loder:URLLoader = event.target as URLLoader; 	 
					try{       	  
						var jsonObject:Object = JSON.parse(loder.data);	      
						if(jsonObject.length == 0){
							return;
						}	      	      
						var array:Array = jsonObject.nodes as Array;
						dg.source = new ArrayCollection(array);
					}catch(error:Error){
						Alert(error.toString());
					}
				}				
			}
			
			protected function exportToExcel():void
			{
				//导出excel的名字以及excel表格的标题
				var title:String = "按行政统计林地面积现况统计表(计量单位:亩)" ;
				ExportToExcelTool.loadDGInExcel(this.myAdvancedDG,http://localhost:8080/webgis/ExcelExport.jsp',title);//在java端创建一个ExcelExport.jsp页面
			}
			
		]]>
	</fx:Script>
	<viewer:WidgetTemplate width="850" height="500">
		<s:VGroup width="100%" height="100%" id="_Content" top="20" chromeColor="#F9F6F6">
			<s:HGroup width="100%" verticalAlign="middle" horizontalAlign="center">
				<s:Label text="行政区划:"/><s:ComboBox labelField="name" id="XSQ" change="province_changeHandler(event)" width="120"/>
				<s:Label text="县(市区)"/><s:ComboBox labelField="name" id="XZC" change="province_changeHandler(event)" width="120"/>
				<s:Label text="乡(镇场)"/><s:ComboBox labelField="name" id="CGQ" width="120"/>
				<s:Label text="村(工区)"/><s:Button label="统计" click="queryData(event)"/>
				<s:Button label="导出" click="exportToExcel()"/>
			</s:HGroup>
			<mx:AdvancedDataGrid id="myAdvancedDG" designViewDataType="flat" sortExpertMode="true" sortableColumns="false" width="100%" height="100%"
								 folderClosedIcon="@Embed(source='assets/images/null.png')"	
								 folderOpenIcon="@Embed(source='assets/images/null.png')"
								 defaultLeafIcon="@Embed(source='assets/images/null.png')" horizontalScrollPolicy="on">
				<mx:dataProvider>
					<mx:HierarchicalData id="dg"/>
				</mx:dataProvider>
				<mx:groupedColumns>
					<mx:AdvancedDataGridColumnGroup headerText="统计单位" dataField="sta_unit"/>
					<mx:AdvancedDataGridColumnGroup headerText="统计单位区" dataField="sta_unit_area"/>
					<mx:AdvancedDataGridColumnGroup headerText="土地总面积" dataField="total_area"/>
					<mx:AdvancedDataGridColumnGroup headerText="林地">
						<mx:AdvancedDataGridColumnGroup headerText="林地总面积合计" dataField="ldmj_total_area"/>
						<mx:AdvancedDataGridColumnGroup headerText="有林地">
							<mx:AdvancedDataGridColumnGroup headerText="有林地计" dataField="yld_total_area"/>
							<mx:AdvancedDataGridColumnGroup headerText="林分">
								<mx:AdvancedDataGridColumn headerText="计" dataField="lf_total_area"/>
								<mx:AdvancedDataGridColumn headerText="防护林" dataField="fhl_area"/>
								<mx:AdvancedDataGridColumn headerText="特用林" dataField="tyl_area"/>
								<mx:AdvancedDataGridColumn headerText="用材林" dataField="ycl_area"/>
								<mx:AdvancedDataGridColumn headerText="薪炭林" dataField="xtl_area"/>
							</mx:AdvancedDataGridColumnGroup>
							<mx:AdvancedDataGridColumnGroup headerText="经济林地" dataField="jjl_area"/>
							<mx:AdvancedDataGridColumnGroup headerText="竹林" dataField="zl_area"/>
						</mx:AdvancedDataGridColumnGroup>
						<mx:AdvancedDataGridColumnGroup headerText="疏林地" dataField="sld_area"/>
						<mx:AdvancedDataGridColumnGroup headerText="灌木林地" dataField="gmld_area"/>
						<mx:AdvancedDataGridColumnGroup headerText="国家特别规定灌木林地" dataField="gjtbgd_gmld_area"/>
						<mx:AdvancedDataGridColumnGroup headerText="未成林造林地" dataField="wclzld_area"/>
						<mx:AdvancedDataGridColumnGroup headerText="苗圃地" dataField="mfd_area"/>
						<mx:AdvancedDataGridColumnGroup headerText="无林地">
							<mx:AdvancedDataGridColumn headerText="计" dataField="wlmld_total_area"/>
							<mx:AdvancedDataGridColumn headerText="宜林荒山荒地" dataField="ylhshd_area"/>
							<mx:AdvancedDataGridColumn headerText="采伐迹地" dataField="cfjd_area"/>
							<mx:AdvancedDataGridColumn headerText="火烧迹地" dataField="hsjd_area"/>
							<mx:AdvancedDataGridColumn headerText="宜林沙荒地" dataField="ylshd"/>
							<mx:AdvancedDataGridColumn headerText="可封育成林的荒山荒地" dataField="kfycldhs_area"/>
							<mx:AdvancedDataGridColumn headerText="林中林缘空地" dataField="lzlykd_area"/>
							<mx:AdvancedDataGridColumn headerText="暂未利用的荒山荒地" dataField="zwlyhshd"/>
						</mx:AdvancedDataGridColumnGroup>
					</mx:AdvancedDataGridColumnGroup>
					<mx:AdvancedDataGridColumnGroup headerText="非林地">
						<mx:AdvancedDataGridColumn headerText="计" dataField="fld_total_area"/>
						<mx:AdvancedDataGridColumn headerText="其中四旁树木面积" dataField="spsm_aera"/>
					</mx:AdvancedDataGridColumnGroup>
					<mx:AdvancedDataGridColumnGroup headerText="森林覆盖率%" dataField="slfgl"/>
					<mx:AdvancedDataGridColumnGroup headerText="林地绿化率%" dataField="ldlhl"/>
					<mx:AdvancedDataGridColumnGroup headerText="非林地">
						<mx:AdvancedDataGridColumn headerText="计" dataField="fghzld_total_area"/>
						<mx:AdvancedDataGridColumn headerText="其中:成林" dataField="fghzld_cl_area"/>
					</mx:AdvancedDataGridColumnGroup>
				</mx:groupedColumns>
			</mx:AdvancedDataGrid>
		</s:VGroup>
	</viewer:WidgetTemplate>
</viewer:BaseWidget>


2.再创建一个ExportToExcelTool.as文件

package com.commpent.toc.utils
{
	
	import com.as3xls.xls.ExcelFile;
	import com.as3xls.xls.Sheet;
	
	import flash.errors.*;
	import flash.events.*;
	import flash.external.*;
	import flash.net.FileReference;
	import flash.net.URLRequest;
	import flash.net.URLRequestMethod;
	import flash.net.URLVariables;
	import flash.net.navigateToURL;
	import flash.utils.ByteArray;
	
	import mx.collections.ArrayCollection;
	import mx.controls.AdvancedDataGrid;
	import mx.controls.Alert;
	import mx.controls.DataGrid;
	import mx.controls.advancedDataGridClasses.AdvancedDataGridColumn;
	import mx.controls.advancedDataGridClasses.AdvancedDataGridColumnGroup;
	import mx.controls.dataGridClasses.DataGridColumn;


	public class ExportToExcelTool
	{
		public function ExportToExcelTool()
		{
		}
				
		//AdvancedDataGrid导出为excel表格
		public static function loadDGInExcel(dg:AdvancedDataGrid,url:String,title:String=''):void {  
			
			//设置URLVariables参数变量,动态增加属性htmltable  
			var variables:URLVariables = new URLVariables();
			
			//将AdvancedDataGrid表头和源数据遍历出来组成一个含有表格的String字符串
			variables.htmltable = convertAdvancedDGToHTMLTable(dg,title); 
			if(title!=""){
				variables.title = encodeURIComponent(title);
			}
			
			//将数据传到java端的jsp页面
			var u:URLRequest = new URLRequest(url);  
			u.data = variables;  
			u.method = URLRequestMethod.POST;  
			
			navigateToURL(u,"_blank");
			//"_self" 指定当前窗口中的当前帧。   
			//"_blank" 指定一个新窗口。   
			//"_parent" 指定当前帧的父级。   
			//"_top" 指定当前窗口中的顶级帧。  
		} 
		
		public  static function convertAdvancedDGToHTMLTable(dg:AdvancedDataGrid,hStr:String):String {
			var font:String = dg.getStyle('fontFamily');
			var size:String = dg.getStyle('fontSize');
			var str:String = '';
			var colors:String = '';
			var style:String = 'style="font-family:'+font+';font-size:'+size+'pt;"';              
			var hcolor:Array;
			//Retrieve the headercolor
			if(dg.getStyle("headerColor") != undefined) {
				hcolor = [dg.getStyle("headerColor")];
			} else {
				hcolor = dg.getStyle("headerColors");
			}              
			//style="font-size:18pt;fontWeight=bold"
			//Set the htmltabel based upon knowlegde from the datagrid
			str+= '<table width="'+dg.width+'" border="1"><caption align=center ><span style="font-size:18pt;fontWeight=bold">'+hStr+'</span></caption><thead><tr width="'+dg.width+'" style="background-color:#' +Number((hcolor[0])).toString(16)+'">';
			//Set the tableheader data (retrieves information from the datagrid header       
			
			//获取AdvancedDataGrid的列
			if(dg.groupedColumns.length == dg.columns.length){ //表示没有合并单元格
				str = getAdvancedDGColMergeCellsNot(dg,style,str);
			} else { //表示合并了单元格
				str = getAdvancedDGColMergeCellsYes(dg,style,str,hcolor);
			}
			
			str += "</tr></thead><tbody>";
			colors = dg.getStyle("alternatingRowColors");
			//Loop through the records in the dataprovider and
			//insert the column information into the table
			for(var j:int =0;j<dg.dataProvider.length;j++) {                  
				str+="<tr>";
				for(var k:int=0; k < dg.columns.length; k++) {
					//Do we still have a valid item?                      
					if(dg.dataProvider.source.source[j] != undefined && dg.dataProvider.source.source[j] != null) {
						//Check to see if the user specified a labelfunction which we must
						//use instead of the dataField
						if(dg.columns[k].labelFunction != undefined) {
							str += "<td text-align='center' "+style+">"+dg.columns[k].labelFunction(dg.dataProvider.source.source[j],dg.columns[k])+"</td>";
						} else {
							//Our dataprovider contains the real data
							//We need the column information (dataField)
							//to specify which key to use.
							str += "<td text-align='center' "+style+">"+(dg.columns[k].dataField == null ? "" : dg.dataProvider.source.source[j][dg.columns[k].dataField.toString()])+"</td>";
						}
					}
				}
				str += "</tr>";
			}
			str+="</tbody></table>";
			return str;
		}
		
		private static function getAdvancedDGColMergeCellsNot(dg:AdvancedDataGrid,style:String,str:String):String{
			var colors:String = '';
			for(var i:int = 0;i<dg.columns.length;i++) {
				colors = dg.getStyle("themeColor");
				if(dg.columns[i].headerText != undefined) {
					str+="<th "+style+">"+dg.columns[i].headerText+"</th>";
				} else {
					str+= "<th "+style+">"+dg.columns[i].dataField+"</th>";
				}
			}
			return str ;
		}
		
		private static function getAdvancedDGColMergeCellsYes(dg:AdvancedDataGrid,style:String,str:String,hcolor:Array):String{
			var colors:String = '';
			var columnsNum:int = 0;
			for(var i:int = 0;i<dg.groupedColumns.length;i++) {
				colors = dg.getStyle("themeColor");
				columnsNum = 0;
				if( dg.groupedColumns[i].children.length != 0) { //表示有子列
					//遍历dg.groupedColumns[i],计算其下的列数总和
					columnsNum = foreachGroup1(dg.groupedColumns[i],columnsNum);
					str+= '<th colspan='+columnsNum+'><table border="1"><thead><tr>';
					//1.合并单元格
					if( (dg.groupedColumns[i].headerText!=undefined) && (dg.groupedColumns[i].children.length>0) ){
						str+="<th colspan="+columnsNum+">"+dg.groupedColumns[i].headerText+"</th>";
					}else {
						str+="<th colspan="+columnsNum+">"+dg.groupedColumns[i].dataField+"</th>";
					}
					str+= '</tr><tr>';
					//2.合并单元格
					str = foreachGroup3(dg.groupedColumns[i],str);
					str += "</tr></thead></table></th>";//获取子列完成3-------------------
				} else { //表示没有子列
					if(dg.groupedColumns[i].headerText != undefined) { 
						str+="<th>"+dg.groupedColumns[i].headerText+"</th>";
					} else {
						str+= "<th>"+dg.groupedColumns[i].dataField+"</th>";
					}
				}
			}
			return str ;
		}

		
		
		private static function foreachGroup1(adv:AdvancedDataGridColumnGroup, columnsNum:int):int{
			for(var j:int = 0;j<adv.children.length; j++ ) {
				try{
					if( adv.children[j].children.length != 0) { //表示有子列
						columnsNum = foreachGroup2(adv.children[j], columnsNum);
					}else{
						columnsNum = columnsNum + 1;
					}
				}catch(error:Error){
					columnsNum = columnsNum + 1;
					continue;	
				}
			}
			return columnsNum;
		}
		
		private static function foreachGroup2(adv:AdvancedDataGridColumnGroup, columnsNum:int):int{
			for(var k:int = 0;k<adv.children.length; k++ ) {
				try{
					if(adv.children[k].children.length != 0) { //表示有子列
						columnsNum = columnsNum + adv.children[k].children.length;
					}else{
						columnsNum = columnsNum + 1;
					}
				}catch(error:Error){
					columnsNum = columnsNum + 1;
					continue;	
				}
			}
			return columnsNum;
		}
		
		private static function foreachGroup3(adv:AdvancedDataGridColumnGroup, str:String):String{
			//合并单元格
			for(var j:int = 0;j<adv.children.length; j++ ) {
				try{
					//判断是否有子列
					if( adv.children[j].children.length != 0) { //表示有子列
						//遍历adv1,计算其下的列数总和
						var columnsNum:int = 0;
						columnsNum = foreachGroup2(adv.children[j],columnsNum);
						str+= '<th colspan='+columnsNum+'><table border="1"><thead><tr>';
						//1.合并单元格
						if( (adv.children[j].headerText!=undefined) && (adv.children[j].children.length>0) ){
							str+="<th colspan="+columnsNum+">"+adv.children[j].headerText+"</th>";
						}else {
							str+="<th>"+adv.children[j].dataField+"</th>";
						}
						str+= '</tr><tr>';
						//2.合并单元格
						str = foreachGroup4(adv.children[j],str);
						str += "</tr></thead></table></th>";//获取子列完成
					}else{
						if(adv.children[j].headerText != undefined){  
							str+="<th>"+adv.children[j].headerText+"</th>";
						} else {
							str+= "<th>"+adv.children[j].dataField+"</th>";
						}
					}
				}catch(error:Error){
					if(adv.children[j].headerText != undefined) { 
						str+="<th rowspan = '3'>"+adv.children[j].headerText+"</th>";
					} else {
						str+= "<th rowspan = '3'>"+adv.children[j].dataField+"</th>";
					}
				}
			}
			return str;
		}
		
		private static function foreachGroup4(adv:AdvancedDataGridColumnGroup, str:String):String{
			//合并单元格
			for(var k:int = 0;k<adv.children.length; k++ ) {
				try{
					//判断是否有子列
					if( adv.children[k].children.length != 0) { //表示有子列
						str= foreachGroup5(adv.children[k],str);
					}else{
						if(adv.children[k].headerText != undefined){
							str+="<th>"+adv.children[k].headerText+"</th>";
						} else {
							str+= "<th>"+adv.children[k].dataField+"</th>";
						}
					}
				}catch(error:Error){
					if(adv.children[k].headerText != undefined) { 
						str+="<th rowspan = '2'>"+adv.children[k].headerText+"</th>";
					} else {
						str+= "<th rowspan = '2'>"+adv.children[k].dataField+"</th>";
					}
					continue;	
				}
			}
			return str;
		}
		
		private static function foreachGroup5(adv:AdvancedDataGridColumnGroup, str:String):String{
			str+= '<th colspan='+adv.children.length+'><table border="1"><thead><tr>';
			//1.合并单元格
			if( (adv.headerText!=undefined) && (adv.children.length>0) ){
				str+="<th colspan="+adv.children.length+">"+adv.headerText+"</th>";
			}else {
				str+="<th>"+adv.dataField+"</th>";
			}
			str+= '</tr><tr>';
			//2.合并单元格
			for(var n:int = 0;n<adv.children.length; n++ ) {
				if(adv.children[n].headerText != undefined){   
					str+="<th>"+adv.children[n].headerText+"</th>";
				} else {
					str+= "<th>"+adv.children[n].dataField+"</th>";
				}
			}
			str += "</tr></thead></table></th>";//获取子列完成
			return str;
		}
		
	}
}


 3.在java端创建一个ExcelExport.jsp页面

<%@ page contentType="text/html; charset=GBK" %>
<% response.setContentType("application/msexcel;charset=UTF-8"); %>
<HTML>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<head><title>Test</title></head>
<body>
<%
request.setCharacterEncoding("UTF-8");
String title = ( (request.getParameter("title")!=null) || (request.getParameter("title")!="") )?request.getParameter("title"):"excel" ;
response.setHeader("Content-disposition","attachment; filename="+title+".xls");
String str = request.getParameter("htmltable");
out.print(str);
%>
</body>
</HTML>

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页