导出动态Excel表头、动态跨行、跨列

package cn.doofen.service.impl;

import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.doofen.ctrl.rpt.Impl.RptBase;
import com.doofen.ctrl.rpt.Impl.RptBase.TableComparator;
import com.zxt.framework.export.RptExcelDomain;

/**
 * 
 * @author Andrew
 *
 * PulishDate: 2015年9月28日
 * Function: 各科平均分比较表
 * ChangeLog:
 */
public class ExportJp1001Impl extends RptBase{
	
	
	DecimalFormat df=new DecimalFormat("#.00"); 
	private Object[]  joHeads1 = null;
	private Object[]  joHeads2 = null;
	private Object[]  joHeads3 = null;
	
	public void createJpExcel_1001( OutputStream os, Long schId, Long epId, Long yearIn, Long xkId, JSONObject jo) throws Exception{

//		FileOutputStream os = new FileOutputStream("c:\\"+fileName+".xls");
		String title ="教师评教分析表";
		try{
				
			
			RptExcelDomain rptDo = new RptExcelDomain();
			rptDo.setSheetName( title);
			rptDo.setSheetTitle(title );

			//绘制表头
			 joHeads1 = jo.getJSONArray( "head1").toArray();
			 joHeads2 = jo.getJSONArray( "head2").toArray();
			 joHeads3 = jo.getJSONArray( "head3").toArray();
			//绘制表格内容行
			JSONArray jaDatas = new JSONArray();
			
			JSONArray joDatas = jo.getJSONArray( "data");
			jaDatas = setRowData( joDatas );
			
			rptDo.setSheetData( jaDatas);
			List<RptExcelDomain> sheets = new ArrayList<RptExcelDomain>();
			sheets.add( rptDo);
			setSheets( sheets);
			writeExcel(os, true);
		}finally{
			if (os != null ) os.close();
		}
	}
	
	
	
	
	
	/**
	 * 构建数据excel结构
	 * @param joDatas
	 * @return
	 */
	private JSONArray setRowData( JSONArray joDatas ){
		
		JSONArray result = new JSONArray();
		for( int i = 0; i < joDatas.size(); i++){
			JSONObject jo = joDatas.getJSONObject( i);
			ArrayList<Object> arr = new ArrayList<Object>();
			
			arr.add( jo.getString( "tchName"));
			arr.add( jo.getString( "stuNum"));
			arr.add( jo.getString( "selectNum"));
			arr.add( jo.getString( "tchScore"));
			arr.add( jo.getString( "tchRank"));
			
			Object[] datas = jo.getJSONArray( "jpData").toArray();
			for( int j = 0; j < datas.length; j++){
				Object v = datas[j];
					arr.add( v);
			}
			
			JSONObject rjo = new JSONObject();
			rjo.put( "data", arr);
			result.add( rjo);
		}
		return result;
	}
	
	/**
	 * 重载excel创建
	 */
	protected void writeExcelSheetSelf( RptExcelDomain rptDomain){

		// 创建Excel的工作sheet,对应到一个excel文档的tab
		HSSFSheet sheet = wb.createSheet( rptDomain.getSheetName());
		// 设置excel每列宽度
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 3500);
		
		int colCount = joHeads3.length + 5;

		ArrayList<String> headers = new ArrayList<String>();
		headers.add( "教师姓名");
		headers.add( "应评数");
		headers.add( "已评数");
		headers.add( "总得分");
		headers.add( "排名");

		// 创建Excel的sheet的一行
		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) rptDomain.getSheetTitleHeight());// 设定行的高度
		// 创建一个Excel的单元格
		HSSFCell cell_title = row.createCell(0);

		// 合并单元格(startRow，endRow，startColumn，endColumn)
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colCount-1));

		// 给Excel的单元格设置样式和赋值
		cell_title.setCellStyle( this.getStyleTitle());
		cell_title.setCellValue( rptDomain.getSheetTitle());

		/*---------------------------------------
		 * 创建sheet的列名
		 *--------------------------------------*/
		HSSFCellStyle headerStyle = this.getStyleHeader();
		row = sheet.createRow(1);
		int baseL = headers.size();
		for( int i = 0; i < baseL; i++){
			HSSFCell cell_header = row.createCell(i);

			// 给Excel的单元格设置样式和赋值
			sheet.addMergedRegion(new CellRangeAddress(1, 3, i, i));
			cell_header.setCellStyle( headerStyle);
			cell_header.setCellValue( headers.get( i));
		}
		
		
		HSSFCell cell_header = row.createCell( baseL );
		
		
		int offset = baseL;
		for( int i = 0; i < joHeads1.length; i++){
			String head =(String) joHeads1[i];
			 cell_header = row.createCell( offset );
			 int merge = new Integer( head.split("\\|")[1] );
			sheet.addMergedRegion(new CellRangeAddress(1, 1, offset, offset+merge-1));
			cell_header.setCellStyle( headerStyle);
			cell_header.setCellValue( head.split("\\|")[0] );
			
			offset = offset + merge;
		}
	
		int offset1 = baseL;
		row = sheet.createRow(2);
		for( int i = 0; i < joHeads2.length; i++){
			 cell_header = row.createCell( offset1 );
			 int merge = 2;
			sheet.addMergedRegion(new CellRangeAddress(2, 2, offset1, offset1+merge-1));
			cell_header.setCellStyle( headerStyle);
			cell_header.setCellValue( (String)joHeads2[i]  );
			
			offset1 = offset1 + merge;
		}
	
		
		row = sheet.createRow(3);
		for( int i = 0; i < joHeads3.length; i++ ){
			cell_header = row.createCell(i+5);

			cell_header.setCellStyle(headerStyle );
			cell_header.setCellValue( (String)joHeads3[i] );
		}
		
		
		
		JSONArray datas = rptDomain.getSheetData();
		

		HSSFCellStyle cellStyle = this.getStyleCellDefault();
		for(int i = 0; i < datas.size(); i++){
				JSONObject rowData = datas.getJSONObject( i);
			
				row = sheet.createRow( 4+i);
					
					Object[] _cellDatas = rowData.getJSONArray( "data").toArray();
					for( int j = 0; j < _cellDatas.length; j++){
						HSSFCell cell_Data = row.createCell( j);
						cell_Data.setCellStyle( cellStyle);
						Object cellData = _cellDatas[j];
						if( cellData == null ) continue;
						if( "class java.lang.String".equalsIgnoreCase(cellData.getClass().toString())){
							cell_Data.setCellValue( (String)cellData);
							cell_Data.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_RIGHT);
						}
						if( "class java.lang.Double".equalsIgnoreCase( cellData.getClass().toString())){
							cell_Data.setCellValue( (Double)cellData);
						}
						if( "class java.lang.Integer".equalsIgnoreCase( cellData.getClass().toString())){
							cell_Data.setCellValue( (Integer)cellData);
						}
					}//完成行数据装载
				
				
		}
		
		

	}
}