Jqgrid表格控件导出excel文件

Jqgrid导出excel实现思路：

1、根据jqgrid表头样式，使用jquery获取表头样式。为保证对合并单元格表头的兼容，实现中没有使用到jqgrid原声api获取colName等信息。

2、导出文件内容使用xml格式文件。

3、在前头页面生成文件内容，然后将生成文件内容发送到服务端，由服务端生成文件下载。（也有很多直接使用前台下载方式，前台下载目前大多具有浏览器限制问题）

/**
 * Jqgrid导出功能扩展
 * @param gridTable id
 * @param fileName  文件名
 * @param tagIndex  标识列
 */
function exportExcel(gridTable,fileName,tagIndex){
	var rownumbers = $("#"+gridTable).jqGrid("getGridParam","rownumbers");
	//获取class包含ui-jqgrid-htable，平且aria-labelledby属性值为gbox_gridTable的table元素
	var titleTable=$('table.ui-jqgrid-htable[aria-labelledby="gbox_'+gridTable+'"]');
	//在table元素中查询可见的tr或td节点
	var titleRows=titleTable.find('tr:not([aria-hidden="true"])');
	var header = [],response = [];
	var colIds = [];
	if(titleRows!=null && titleRows.length>0){
		for(var i=0 ; i < titleRows.length; i++){
			header.push("<Row>");
			var row=titleRows[i];
			$(row).find('td:visible,th:visible').each(function(index,element){
				if(true==rownumbers && 0===index){
					return ;
				}
				var colspan = $(this).prop('colspan');
				colspan=(colspan!=undefined)?(colspan-1):0;
				var rowspan = $(this).prop('rowspan');
				rowspan=(rowspan!=undefined)?(rowspan-1):0;
				header.push('<Cell ss:MergeAcross="'+colspan+'" ss:MergeDown="'+rowspan+'" ss:StyleID="s17"><Data ss:Type="String"><![CDATA[' + $(this).text() + ']]></Data></Cell>');
				if((i+1)==titleRows.length){
					colIds.push($(this).prop('id').replace(gridTable+'_',''));
				}
			});
			header.push("</Row>");
		}
	}
	var url=$("#"+gridTable).jqGrid('getGridParam', 'url');
	var userData=$("#"+gridTable).jqGrid("getGridParam","postData");
	var records=$("#"+gridTable).jqGrid('getGridParam', 'records');
	userData['page']=1;
	userData['rows']=(records!=undefined && records!='undefined')?records:-1;
	$.post(sy.contextPath +url,userData,function(data){
		if(data!=undefined && data!=null){
			var excelData=null;
			if(data instanceof Array){
				excelData=formatterDataRow(data,colIds,tagIndex);
			}else{
				excelData=formatterDataRow(data.rows,colIds,tagIndex);
			}
			excelData= excelData!=null?excelData:'';
			
			var excel=createExcelData(fileName,header.join('\n'),excelData);
			console.info(excel);
		}
		
	},'json');
	
	
}

function formatterDataRow(list,rowIndexs,status){
	var response = [];
	if(list!=null && list.length>0){
		for(var i=0;i<list.length;i++){
			var item=list[i];
			response.push("<Row>");
			for (var j = 0; j < rowIndexs.length; j++) {
				var column = rowIndexs[j];
				if('add'==item[status] && j==0){
					response.push('<Cell ss:StyleID="s22"><Data ss:Type="String"><![CDATA[' + (item[column]!=undefined?item[column]:'') + "]]></Data></Cell>")
				}else if('delete'==item[status] && j==0){
					response.push('<Cell ss:StyleID="s21"><Data ss:Type="String"><![CDATA[' + (item[column]!=undefined?item[column]:'') + "]]></Data></Cell>")
				}else if('update'==item[status]){
					response.push('<Cell ss:StyleID="s20"><Data ss:Type="String"><![CDATA[' + (item[column]!=undefined?item[column]:'') + "]]></Data></Cell>")
				}else{
					response.push('<Cell ss:StyleID="s18"><Data ss:Type="String"><![CDATA[' + (item[column]!=undefined?item[column]:'') + "]]></Data></Cell>")
				}
			}
			response.push("</Row>")
		}
	}
	return response.join('\n');
}

function createExcelData(sheetName,header,response){
	var styles=[];
	styles.push('<Styles>');
	styles.push('<Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/> </Style>');
	styles.push('<Style ss:ID="s17"> <Alignment ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/><Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/></Borders><Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000" ss:Bold="1"/></Style>');
	styles.push('<Style ss:ID="s18"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/> </Style>');
	styles.push('<Style ss:ID="s20"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/> <Interior ss:Color="#FFFF00" ss:Pattern="Solid"/></Style>');
	styles.push('<Style ss:ID="s21"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/> <Interior ss:Color="#FF0000" ss:Pattern="Solid"/></Style>');
	styles.push('<Style ss:ID="s22"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#000000"/> <Interior ss:Color="#00B050" ss:Pattern="Solid"/></Style>');
	styles.push('</Styles>');
	
	var excelDoc = ['<?xml version="1.0"?>', 
	'<?mso-application progid="Excel.Sheet"?>', 
	'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"', 
	' xmlns:o="urn:schemas-microsoft-com:office:office"', 
	' xmlns:x="urn:schemas-microsoft-com:office:excel"', 
	' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"', 
	' xmlns:html="http://www.w3.org/TR/REC-html40">',
	styles.join('\n'),
	'<Worksheet ss:Name="', sheetName, '">', "<Table>", header, response, "</Table>", "</Worksheet>", "</Workbook>"];
	
	excelDoc.push();
	
	return excelDoc.join("\n")
	
}