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")
}