flex AdvanceDatagrid 通用导出到excel

先说说思路：flex端把表头（包含：dataField、headText、children）组织成json格式传到后台。后台提供List<Map<String,Object>>格式的数据源（Map的key值和dataField一一对应）调用通用导出模块。

下面上代码：

前台提供的json：

[{"children":"","dataField":"index","headerText":"序号"},{"children":[{"children":"","dataField":"name","headerText":"昵称"},{"children":"","dataField":"userName","headerText":"用户名"}],"dataField":"","headerText":"用户信息"},"dataField":"requestUri","headerText":"URI"},"dataField":"ip","headerText":"IP"},"dataField":"accessDate","headerText":"日期"}]

通过调用servlet传到后台

package?com.sddl.action;

import?java.io.IOException;
import?java.io.InputStream;
import?java.io.OutputStream;
import?java.util.List;
import?java.util.Map;

import?javax.servlet.ServletException;
import?javax.servlet.http.HttpServlet;
import?javax.servlet.http.HttpServletRequest;
import?javax.servlet.http.HttpServletResponse;

import?net.sf.json.JSONArray;
import?org.springframework.util.StringUtils;

import?com.sddl.bean.ExpSimpleAdvanceDataGridBean;
import?com.sddl.util.Tool;

public?class?ExpSimpleAdvanceDataGridAction?extends?HttpServlet?{
	private?static?final?long?serialVersionUID?=?1L;
	public?void?service(HttpServletRequest?req,?HttpServletResponse?rep)
			throws?ServletException,?IOException?{
		rep.setContentType("text/xml;charset=utf-8");
		
		String?tmp?=?req.getParameter("header");//前台传过来的json表头
		JSONArray?ja?=?JSONArray.fromObject(tmp);
		
		List<Map<String,?Object>>?dataProvideList?=?DAO.findAll("from?LogBook");//伪代码。。。
		ExpSimpleAdvanceDataGridBean?bean?=?new?ExpSimpleAdvanceDataGridBean(ja);
		bean.setExcelHeaderTitle("日志列表");//表头
		bean.setWorkSheetTitle("日志");//sheet?name
		bean.setDataProvideList(dataProvideList);
		//bean.getExcelInputStream();
		
		InputStream?in?=?bean.getExcelInputStream();
		OutputStream?out?=?rep.getOutputStream();
		byte[]?by?=?new?byte[1024];
		int?len?=?-1;
		while((len=in.read(by))!=-1){
			out.write(by,?0,?len);
		}
		in.close();
		out.close();
	}
	
	private?String?checkString(String?str){
		if(StringUtils.hasLength(str)?&&?!str.trim().equals("null"))
			return?str;
		return?null;
	}
}

package?com.sddl.bean;

import?java.io.ByteArrayInputStream;
import?java.io.ByteArrayOutputStream;
import?java.io.InputStream;
import?java.text.DecimalFormat;
import?java.util.ArrayList;
import?java.util.HashMap;
import?java.util.List;
import?java.util.Map;
import?java.util.regex.Pattern;

import?org.springframework.util.StringUtils;

import?com.sddl.util.Tool;

import?net.sf.json.JSONArray;
import?net.sf.json.JSONObject;

import?jxl.Workbook;
import?jxl.format.Alignment;
import?jxl.format.VerticalAlignment;
import?jxl.write.Label;
import?jxl.write.Number;
import?jxl.write.WritableCellFormat;
import?jxl.write.WritableFont;
import?jxl.write.WritableSheet;
import?jxl.write.WritableWorkbook;

public?class?ExpSimpleAdvanceDataGridBean?{
	private?List<AdvanceDataGridColumnBean>?headerList;
	private?List<Map<String,?Object>>?dataProvideList;
	private?String?excelHeaderTitle;
	private?String?workSheetTitle;
	private?int?maxLevel;
	
	Map<String,?Integer>?maxLevelMap?=?new?HashMap<String,?Integer>();
	public?ExpSimpleAdvanceDataGridBean(JSONArray?ja){
		this.headerList?=?getHeaderList(ja,?null);
		maxLevelMap.put("level",?0);
		getMaxLevel(ja);
		this.maxLevel?=?maxLevelMap.get("level");
	}
	
	public?InputStream?getExcelInputStream()?{
		ByteArrayOutputStream?out?=?new?ByteArrayOutputStream();
		exportPassedData(out);
		return?new?ByteArrayInputStream(out.toByteArray());
	}	

	WritableCellFormat?format1?=?null;
	Map<String,?RowColumnBean>?rangeMap?=?null;
	private?void?exportPassedData(ByteArrayOutputStream?os)?{
		try?{
			????WritableWorkbook?workbook?=?Workbook.createWorkbook(os);
			
			????//定义格式
			????WritableFont?font?=?new?WritableFont(WritableFont.TIMES,?14,?WritableFont.BOLD);
		????????WritableCellFormat?format?=?new?WritableCellFormat(font);
		????????format.setAlignment(Alignment.CENTRE);

??		????????WritableFont?font1?=?new?WritableFont(WritableFont.TIMES,?10,?WritableFont.BOLD);
		????????format1?=?new?WritableCellFormat(font1);
		????????format1.setVerticalAlignment(VerticalAlignment.CENTRE);
		????????format1.setAlignment(Alignment.CENTRE);

		????????WritableCellFormat?format4?=?new?WritableCellFormat();
		????????format4.setAlignment(Alignment.LEFT);
		????????format4.setVerticalAlignment(VerticalAlignment.BOTTOM);
		????????format4.setWrap(false);
		??????
		????????WritableSheet?sheet?=?workbook.createSheet(workSheetTitle,?0);
		??????
		????????Label?labelTitle?=?new?Label(0,?excelHeaderTitle,?format);
		????????
		????????int?columnIndex?=?0;
		????????int?rowIndex?=?1;
		????????rangeMap?=?getRangeList(headerList,?columnIndex,?rowIndex);
		????????setLabel(sheet,?headerList,?rowIndex);

		????????//设置表头的长度
		????????for?(AdvanceDataGridColumnBean?columnBean:?headerList)?{
		????????	columnIndex?+=?columnBean.getColumnSize();
		????????}
		????????sheet.mergeCells(0,?columnIndex?-?1,?0);
		????????sheet.addCell(labelTitle);

		????????Pattern?pa?=?Pattern.compile("[0-9.]*");
		????????rowIndex?=?maxLevel+1;
		????????for(int?i=0;?i<dataProvideList.size();?i++){
			????	??Label?dataLabel?=?null;
			????	??Number?cellNum?=?null;
			????	??Map<String,?Object>?dpMap?=?dataProvideList.get(i);
			????	??for(int?j=0;?j<matchMap.size();?j++){
			????		??Object?data?=?dpMap.get(matchMap.get(j));
			????		??String?cellStr?=?stringTool(data);
//			????		??if(StringUtils.hasLength(cellStr)?&&?pa.matcher(cellStr).matches()){
//			????			??DecimalFormat?df?=?new?DecimalFormat("0.##");
//		????				??cellNum?=?new?Number(j,?rowIndex,?Tool.sToF(df.format(Tool.sToF(cellStr,?0)),?0),?format4);
//		????				??sheet.addCell(cellNum);
//			????		??}?else?{
			????			??dataLabel?=?new?Label(j,?cellStr,?format4);
		????				??sheet.addCell(dataLabel);
//			????		??}
			????	??}
			????	??rowIndex++;
			??????}
		????????
		???????workbook.write();
		???????workbook.close();
		}?catch?(Exception?e)?{
			e.printStackTrace();
		}
		
	}
	
	/***
	?*?为非首行设置“坐标”
	?*?@param?columnBean
	?*/
	private?void?setRange(AdvanceDataGridColumnBean?columnBean){
		AdvanceDataGridColumnBean?parent?=?columnBean.getParent();
		if(rangeMap.get(parent.getHeaderText()+parent.getDataField())?==?null){?//递归
			setRange(parent);
		}?else?{
			RowColumnBean?range?=?rangeMap.get(parent.getHeaderText()+parent.getDataField());
			for(int?i=0;?i<parent.getChildren().size();?i++){
				AdvanceDataGridColumnBean?childrenBean?=?parent.getChildren().get(i);
				int?childrenIndex?=?parent.getChildren().indexOf(childrenBean);
				
				if(childrenIndex?==?0){//?判断子节点在父节点的位置：老大还是老二？
					RowColumnBean?rangeBean?=?getRange(childrenBean.getColumnSize(),?range.getBeginColumnIndex(),?range.getBeginRowIndex()+1);?//?判断子节点在父节点的位置：老大还是老二？
					rangeMap.put(childrenBean.getHeaderText()+childrenBean.getDataField(),?rangeBean);
				}?else?{
					range?=?rangeMap.get(parent.getChildren().get(i-1).getHeaderText()+?parent.getChildren().get(i-1).getDataField());?//得到哥哥的“坐标”,弟弟排在哥哥后面！
					RowColumnBean?rangeBean?=?getRange(childrenBean.getColumnSize(),?range.getEndColumnIndex()+1,?range.getEndRowIndex());
					rangeMap.put(childrenBean.getHeaderText()+childrenBean.getDataField(),?rangeBean);
				}
				
				if(childrenBean.getChildren()?!=?null){?//如果有子节点，就再调用以给子节点赋值
					for(AdvanceDataGridColumnBean?childrenChildrenBean?:childrenBean.getChildren()){
						if(rangeMap.get(childrenChildrenBean.getHeaderText()+childrenChildrenBean.getDataField())?==?null){
							setRange(childrenChildrenBean);
						}
					}
				}
			}
		}
	}
	
	/***
	?*?写入excel
	?*?@param?sheet
	?*?@param?headerList
	?*?@param?rowIndex
	?*?@return
	?*?@throws?Exception
	?*/
	private?List<Label>?setLabel(WritableSheet?sheet,?List<AdvanceDataGridColumnBean>?headerList,?int?rowIndex)?throws?Exception{
		List<Label>?list?=?new?ArrayList<Label>();
		Label?label?=?null;
		for?(AdvanceDataGridColumnBean?columnBean?:?headerList)?{
????		RowColumnBean?range?=?rangeMap.get(columnBean.getHeaderText()+columnBean.getDataField());
????		if(range?==?null){
????			setRange(columnBean);
????			range?=?rangeMap.get(columnBean.getHeaderText()+columnBean.getDataField());
????		}
????		sheet.mergeCells(range.getBeginColumnIndex(),?range.getBeginRowIndex(),?range.getEndColumnIndex(),?range.getEndRowIndex());
????		label?=?new?Label(range.getBeginColumnIndex(),?columnBean.getHeaderText(),?format1);

????		sheet.addCell(label);
	????	if(columnBean.getChildren()?!=?null){
	????		setLabel(sheet,?columnBean.getChildren(),?rowIndex++);
	????	}
		}
		return?list;
	}
	
	/***
	?*?获取首行的”坐标“
	?*?@param?headerList
	?*?@param?columnIndex
	?*?@param?rowIndex
	?*?@return?Map<String,?RowColumnBean>
	?*/
	private?Map<String,?RowColumnBean>?getRangeList(List<AdvanceDataGridColumnBean>?headerList,?int?columnIndex,?int?rowIndex){
		Map<String,?RowColumnBean>?map?=?new?HashMap<String,?RowColumnBean>();
		
		for?(AdvanceDataGridColumnBean?columnBean?:?headerList)?{
			List<AdvanceDataGridColumnBean>?childrenColumnList?=?columnBean.getChildren();
	????	if?(childrenColumnList?==null?||?!childrenColumnList.isEmpty())?{
????			map.put(columnBean.getHeaderText()+columnBean.getDataField(),?getRange(columnBean.getColumnSize(),?rowIndex));
	????		columnIndex?+=?columnBean.getColumnSize();
	????	}
		}
		return?map;?
	}
	
	/***
	?*?设置“坐标”
	?*?@param?columnSize
	?*?@param?columnIndex
	?*?@param?rowIndex
	?*?@return?RowColumnBean
	?*/
	private?RowColumnBean?getRange(int?columnSize,?int?rowIndex){
		RowColumnBean?bean?=?new?RowColumnBean();
		bean.setBeginColumnIndex(columnIndex);
		bean.setBeginRowIndex(rowIndex);
		bean.setEndColumnIndex(columnIndex?+?columnSize?-?1);
		if(columnSize==1){
			bean.setEndRowIndex(maxLevel);
		}?else?{
			bean.setEndRowIndex(rowIndex);
		}
		return?bean;
	}
	
	/***
	?*?获取单元格的长度
	?*?@param?jb
	?*?@return?Integer
	?*/
	private?Integer?getColumnSize(JSONObject?jb){
		int?columnSize?=?0;
		if(StringUtils.hasLength(jb.get("children").toString())?&&?!jb.get("children").toString().equals("null")){
			JSONArray?childrenJA?=?jb.getJSONArray("children");
			for(int?i=0;?i<childrenJA.size();?i++){
				columnSize?+=?getColumnSize(JSONObject.fromObject(childrenJA.get(i)));	
			}
		}?else?{
			++columnSize;
		}
		return?columnSize;?
	}
	
	/**
	?*?表头最多有多少“级别”
	?*?@param?JSONArray?ja
	?*?@return?Integer
	?*/
	private?Integer?getMaxLevel(JSONArray?ja){
		int?level?=?1;
		for(int?i=0;?i<ja.size();?i++){
			JSONObject?jb?=?JSONObject.fromObject(ja.get(i));
			int?tmp?=?1;
			
			if(StringUtils.hasLength(jb.get("children").toString())?&&?!jb.get("children").toString().equals("null")){
				tmp?+=?getMaxLevel(jb.getJSONArray("children"));
				level?=?tmp;
			}?
			if(maxLevelMap.get("level")<?level){
				maxLevelMap.put("level",?level);
			}
		}
		return?level;
	}
	
	
	Map<Integer,?String>?matchMap?=?new?HashMap<Integer,?String>();?//用于和数据源对应的map
	int?matchIndex?=?0;
	/***
	?*?遍历表头
	?*?@param?JSONArray?ja?需要遍历的数据源
	?*?@param?AdvanceDataGridColumnBean?parent?父节点
	?*?@return??List<AdvanceDataGridColumnBean>
	?*/
	private?List<AdvanceDataGridColumnBean>?getHeaderList(JSONArray?ja,?AdvanceDataGridColumnBean?parent){
		List<AdvanceDataGridColumnBean>?headerList?=?new?ArrayList<AdvanceDataGridColumnBean>();
		for(int?i=0;?i<ja.size();?i++){
			AdvanceDataGridColumnBean?bean?=?new?AdvanceDataGridColumnBean();
			JSONObject?jb?=?JSONObject.fromObject(ja.get(i));
			bean.setHeaderText(jb.getString("headerText"));
			bean.setDataField(jb.getString("dataField"));
			bean.setColumnSize(getColumnSize(jb));
			
			if(StringUtils.hasLength(jb.get("children").toString())?&&?!jb.get("children").toString().equals("null")){
				bean.setChildren(getHeaderList(jb.getJSONArray("children"),?bean));
			}?else?{
				matchMap.put(matchIndex++,?bean.getDataField());?//设置dataField和列的对应关系
			}
			if(parent?!=?null){
				bean.setParent(parent);
			}
			headerList.add(bean);
		}
		return?headerList;
	}
	
	public?String?stringTool(Object?obj){
		if(obj?==?null){
			return?null;
		}?else?{
			return?obj.toString();
		}
	}
	
	public?List<Map<String,?Object>>?getDataProvideList()?{
		return?dataProvideList;
	}

	public?void?setDataProvideList(List<Map<String,?Object>>?dataProvideList)?{
		this.dataProvideList?=?dataProvideList;
	}

	public?String?getExcelHeaderTitle()?{
		return?excelHeaderTitle;
	}

	public?void?setExcelHeaderTitle(String?excelHeaderTitle)?{
		this.excelHeaderTitle?=?excelHeaderTitle;
	}
	public?String?getWorkSheetTitle()?{
		return?workSheetTitle;
	}

	public?void?setWorkSheetTitle(String?workSheetTitle)?{
		this.workSheetTitle?=?workSheetTitle;
	}

	public?List<AdvanceDataGridColumnBean>?getHeaderList()?{
		return?headerList;
	}

	public?void?setHeaderList(List<AdvanceDataGridColumnBean>?headerList)?{
		this.headerList?=?headerList;
	}
	protected?class?RowColumnBean{
		private?Integer?BeginColumnIndex;
		private?Integer?BeginRowIndex;
		private?Integer?endColumnIndex;
		private?Integer?endRowIndex;
		public?Integer?getBeginColumnIndex()?{
			return?BeginColumnIndex;
		}
		public?void?setBeginColumnIndex(Integer?beginColumnIndex)?{
			BeginColumnIndex?=?beginColumnIndex;
		}
		public?Integer?getBeginRowIndex()?{
			return?BeginRowIndex;
		}
		public?void?setBeginRowIndex(Integer?beginRowIndex)?{
			BeginRowIndex?=?beginRowIndex;
		}
		public?Integer?getEndColumnIndex()?{
			return?endColumnIndex;
		}
		public?void?setEndColumnIndex(Integer?endColumnIndex)?{
			this.endColumnIndex?=?endColumnIndex;
		}
		public?Integer?getEndRowIndex()?{
			return?endRowIndex;
		}
		public?void?setEndRowIndex(Integer?endRowIndex)?{
			this.endRowIndex?=?endRowIndex;
		}
	}
	public?int?getMaxLevel()?{
		return?maxLevel;
	}

	public?void?setMaxLevel(int?maxLevel)?{
		this.maxLevel?=?maxLevel;
	}

}

package?com.sddl.bean;

import?java.util.List;

public?class?AdvanceDataGridColumnBean?{
	private?String?dataField;
	private?String?headerText;
	private?AdvanceDataGridColumnBean?parent;
	private?List<AdvanceDataGridColumnBean>?children;
	private?Integer?columnSize;
	
	public?String?getDataField()?{
		return?dataField;
	}

	public?void?setDataField(String?dataField)?{
		this.dataField?=?dataField;
	}

	public?String?getHeaderText()?{
		return?headerText;
	}

	public?void?setHeaderText(String?headerText)?{
		this.headerText?=?headerText;
	}

	public?List<AdvanceDataGridColumnBean>?getChildren()?{
		return?children;
	}

	public?void?setChildren(List<AdvanceDataGridColumnBean>?children)?{
		this.children?=?children;
	}

	public?AdvanceDataGridColumnBean?getParent()?{
		return?parent;
	}

	public?void?setParent(AdvanceDataGridColumnBean?parent)?{
		this.parent?=?parent;
	}

	public?Integer?getColumnSize()?{
		return?columnSize;
	}

	public?void?setColumnSize(Integer?columnSize)?{
		this.columnSize?=?columnSize;
	}

}

导出的数据：