Ext4 实现grid多表头数据导出Excel

版本Ext 4.1

实现单行表头、多行表头、Store分组， 通用grid 数据导出功能。

 

 

上图：

 

导出grid列表显示数据，避免直接从数据库导出，标识符需要填写转换方法。如：

return v=='Y'?'是':'';

导出生成Excel

 

 

上代码：

 

	Ext.qy.ExtractionGridDate = function(){
		/** 列表头  text,align */
		var theadObj = {},
			theadArray = [],
			tabName,
			dataRows = [],
			form,
			_eachColumns function(cs,n){
				var num = 0 ;
				for(var j=0,l=cs.length;j<l;j++){
					num  += _col_z(cs[j],n);
					if((n+1)>theadObj.depth){
						theadObj.depth = n+1;
					}
				}
				return num;
			},
			_col_z = function(cols,n){
				var return_num = 0,
					child_cols = cols.columns,
					hidden_ = cols.hidden||false,
					child_len = child_cols?child_cols.length:0,
					align_ = cols.align||'left';
				if(child_cols){
					child_len = _eachColumns(child_cols,n+1);
					return_num = child_len;
				}else{
					if(!hidden_){
						return_num += 1;
					}
					theadArray.push({
						hidden:hidden_,
						align:align_
					});
				}
				theadObj["row"+n].push({
					text:cols.header||cols.text||'',
					align:align_,
					hidden:hidden_,
					child_len:child_len
				});
				return return_num;
			},
			_subForm = function (arrs, url){//提交
				var i,
					l,
					rowStyle=[],
					rows = [];
				for(i=0,l=theadArray.length;i<l;i++){
					if(!theadArray[i].hidden){
						rowStyle.push(theadArray[i].align);
					};
				};
				for(i=0,l=arrs.length;i<l;i++){
					var arr = arrs[i],
						row = [];
					for(var j=0,k = arr.length;j<k;j++){
						if(!theadArray[j].hidden){
							row.push(arr[j]);
						};
					};
					rows.push(row.join('<&$&>'))
				};
				l = '<#$#>';
				var inputs = [
				{name: 'filename', value: tabName},
				{name: 'depth', value: theadObj.depth},
				{name: 'headerStr', value: function(){
					var result = '';
					var je = '<*>',jg='!*!',jk='&$&';
					for(i=0;i<theadObj.depth;i++){
						for(var j=0,l = theadObj["row"+i].length;j<l;j++){
							var o = theadObj["row"+i][j];
							result += o.text +je+ o.align +je+ o.hidden +je+ o.child_len +je +jg;
						}
						result += jk;
					}
					return result;
				}()},
				{name: 'styleStr', value: rowStyle.join(l)},
				{name: 'rowsStr', value: rows.join(l)}
				];
				if(!form){
					form = document.createElement("form");
					form.method = 'post';
					form.target="_blank"
					document.body.appendChild(form);form.style.display="none";
				};
				form.action = url;
				while(form.firstChild){ //判断是否有子节点
					 form.removeChild(form.firstChild);
				};
				for(i=0,l=inputs.length;i<l;i++){
					var input = document.createElement("input");
					input.value = inputs[i].value;
					input.name = inputs[i].name;
					form.appendChild(input);
				};
				Ext.qy.unmask();
				form.submit();
			};
		return function(gridpanel, url){
			Ext.qy.mask("加载数据……");
			tabName = gridpanel.title||'';
			dataRows.splice(0,dataRows.length);
			theadArray.splice(0,theadArray.length);
			theadObj = {//表头最多5层
				row0:[],
				row1:[],
				row2:[],
				row3:[],
				row4:[],
				depth:1//判断深度
				};
			var columns = gridpanel.columns,
				i;
			for(i=0,l=columns.length;i<l;i++){
				var cols = columns[i].initialConfig;
				_col_z(cols,0);
			}
			var html = gridpanel.getView().getEl().getHTML();
			var rows = html.replace(/<([a-zA-Z1-6]+)(\s*[^>]*)?>/gi, '<$1>')
				.replace(/<\/?div.*?>|<\/?a.*?>|<\/?span.*?>/gi, '')
				.replace(/\s+/g, '')
				.replace(/<th><\/th>/gi, '')
				.replace(/<table><tbody><tr><\/tr><tr><td>/gi, '')
				.replace(/<\/td><\/tr><\/tbody><\/table>/gi, '')
				.split(/<\/td><\/tr><tr.*?><td.*?>/i);
			for(i=0,l=rows.length;i<l;i++){
				var row = rows[i].split(/<\/td><td.*?>/i)
				dataRows.push(row);
			}
			return _subForm(dataRows, url);
		};
	}();

 按钮调用：

 

 

{
	xtype: 'button',
	icon : 'images/tool_xls.gif',
	tooltip: {
		text : '导出Excel文件'
	},
	handler: function(btn){
		Ext.qy.ExtractionGridDate(btn.up('gridpanel'), "file/download/Excel.html");
	}
}

java 实现使用springmvc

 

 

	@RequestMapping("/file/download/{view}.html")
	public String view(
			@PathVariable("view") String view,
			@RequestParam(value = "filename",required=false)String filename,//文件名||标题
			@RequestParam(value = "depth",required=false)int depth,//列表头 深度
			@RequestParam(value = "headerStr",required=false)String headerStr,//列表头
			@RequestParam(value = "styleStr",required=false)String styleStr,//表格algin
			@RequestParam(value = "rowsStr",required=false)String rowsStr,//数据
			Model model
			){
		if(filename==null&&headerStr==null&&rowsStr==null){
			return null;
		}
		
		model.addAttribute("filename", filename);
		
		String regex = "\\<\\#\\$\\#\\>";//行分割符<#$#>
		
		headerStr = headerStr.replace("&nbsp;", " ");
		rowsStr = rowsStr.replace("&nbsp;", " ");
		
		model.addAttribute("headerStr", headerStr);
		model.addAttribute("depth", depth);
		
		List<String[]> rowsList = new ArrayList<String[]>();
		String[] rows = rowsStr.split(regex);
		for (int i = 0; i < rows.length; i++) {
			String[] row = rows[i].split("\\<\\&\\$\\&\\>");//列分割符<&$&>
			rowsList.add(row);
		}
		model.addAttribute("rowsList", rowsList);
		model.addAttribute("styleArray", styleStr.split(regex));
		
		return view+"View";
	}

 ExcelView 代码

 

 

public class ExcelView extends AbstractExcelView{

	@SuppressWarnings({ "unchecked", "deprecation" })
	@Override
	protected void buildExcelDocument(
			Map<String, Object> model,
			HSSFWorkbook workbook,
			HttpServletRequest request,
			HttpServletResponse response)
			throws Exception {
		
		String filename = "----";
		if(model.get("filename")!=null && !"".equals(model.get("filename"))){
			filename = (String) model.get("filename");
		}
		int depth = (Integer) model.get("depth");
		String headerStr =  (String) model.get("headerStr");
		String[] styleArray =  (String[]) model.get("styleArray");
		List<String[]> rowsList =  (List<String[]>) model.get("rowsList");
		
		int len = styleArray.length;
		HSSFSheet sheet = workbook.createSheet(filename);
		
		response.setHeader("Content-Disposition",
				"inline;filename="+new String((filename+"-"+DateUtil.getCalendarStr()+".xls").getBytes(),"iso8859-1"));
		HSSFCellStyle style = workbook.createCellStyle(); // 样式对象   
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直   
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平  
        HSSFCellStyle styleLeft = workbook.createCellStyle();
        styleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        styleLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直   
        
        HSSFCellStyle styleCenter = workbook.createCellStyle();
        styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        styleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直   
        
        HSSFCellStyle styleRight = workbook.createCellStyle();
        styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        styleRight.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直   
        
        int rowNum = 0;
        sheet.addMergedRegion(new Region(0,(short)0,1,(short)(len-1)));// 四个参数分别是：起始行，起始列，结束行，结束列   
		HSSFCell titleCell = sheet.createRow(rowNum).createCell((short) 0);
		titleCell.setCellStyle(styleCenter);
		titleCell.setCellType(HSSFCell.CELL_TYPE_STRING);
		titleCell.setCellValue(filename);
		
		String[] rows = headerStr.split("\\&\\$\\&");
		rowNum = 1;
		
		Map<String, Integer> map = new HashMap<String, Integer>();
		for (int i = 0; i < depth; i++) {
			map.put("row"+i, rowNum+1);
			map.put("col"+i, 0);
		}
		//生成多行表头
		for (int i = 0,l = rows.length;i<l;i++) {
			String row = rows[i];
			rowNum ++;
			HSSFRow header = sheet.createRow(rowNum);
			String[] rs = row.split("\\!\\*\\!");
			for (String strs : rs) {
				String[] o = strs.split("\\<\\*\\>");
				if(!"true".equals(o[2])){
					int child_len = Integer.parseInt(o[3]);
					if(child_len>0){
						sheet.addMergedRegion(new Region(rowNum,(short)(int)map.get("col"+i),rowNum,(short)(int)(map.get("col"+i)+(child_len-1))));// 四个参数分别是：起始行，起始列，结束行，结束列   
						this.makeCell(header, (int) map.get("col"+i), o[1], o[0], styleLeft, styleCenter, styleRight);
						map.put("col"+i, map.get("col"+i)+child_len);
					}else{
						if(i<depth){
							int a = depth-i;
							sheet.addMergedRegion(new Region(rowNum,(short)(int)map.get("col"+i),rowNum+(a-1),(short)(int)map.get("col"+i)));// 四个参数分别是：起始行，起始列，结束行，结束列   
							this.makeCell(header, (int) map.get("col"+i), o[1], o[0], styleLeft, styleCenter, styleRight);
							for (int j = 0; j < a; j++) {
								map.put("col"+(i+j), map.get("col"+(i+j))+1);
							}
						}
					}
				}
			}
		}
		rowNum++;
		for(String[] rowArr : rowsList){
			HSSFRow row = sheet.createRow(rowNum++);
			for (int i = 0; i < rowArr.length; i++) {
				this.makeCell(row, i, styleArray[i], rowArr[i], styleLeft, styleCenter, styleRight);
			}
		}
	}

	private HSSFCellStyle retrunStyle(String alignStr,HSSFCellStyle styleLeft,HSSFCellStyle styleCenter,HSSFCellStyle styleRight){
		if("center".equalsIgnoreCase(alignStr)){
			return styleCenter;
		}else if("right".equalsIgnoreCase(alignStr)){
			return styleRight;
		}else{
			return styleLeft;
		}
	}
	
	
	@SuppressWarnings("deprecation")
	private void makeCell(HSSFRow row, int column ,String alignStr, String value,HSSFCellStyle styleLeft,HSSFCellStyle styleCenter,HSSFCellStyle styleRight){
		HSSFCell cell = row.createCell((short) column);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(retrunStyle(alignStr, styleLeft, styleCenter, styleRight));
		cell.setCellValue(value);
	}

 http://170728576.iteye.com/blog/1980236