java导出Excel并对表头做定制

1、核心代码

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.cicada.seo.query.impl.QryIntegReportImpl;
import com.doofen.ctrl.rpt.RptConst;
import com.doofen.ctrl.rpt.Impl.RptBase;
import com.zxt.framework.export.RptExcelDomain;

/**
 * 
 * @author Andrew
 *
 * PulishDate: 2015年9月28日
 * Function: 各科平均分比较表
 * ChangeLog:
 */
public class ExportRpt10012Impl extends RptBase{
	
	DecimalFormat df=new DecimalFormat("#.00"); 
	
	


	
	private Object[]  joHeads = null;
	private Object[]  joHeads2 = null;
	
	/**
	 * 构建excel
	 * @param os
	 * @param examId
	 * @throws Exception
	 */
	public void createRptExcel_10012( OutputStream os, Long schId, Long examId , Long clsId, Long gradeId, JSONArray jarr, Long xueke, Long leibie) throws Exception{
		QryIntegReportImpl opr = new QryIntegReportImpl();
		
		String title = null;
		
		if(leibie == 10){
			title = "行政班——"+RptConst.RPT_2000_NAME;
		}else if (leibie == 11){
			title = "选课班——"+RptConst.RPT_2000_NAME;
		}
		
		try{
			JSONArray ja = new JSONArray();
			ja = opr.getExecutiveTable(schId, examId, clsId, gradeId, jarr, xueke, leibie);
			RptExcelDomain rptDo = new RptExcelDomain();
			rptDo.setSheetName( title);
			rptDo.setSheetTitle( SHEET_TITLE+title );

			//绘制表头
			 joHeads = ja.getJSONObject(0).getJSONArray( "xkTitle").toArray();
			 joHeads2 = ja.getJSONObject(0).getJSONArray( "secTitle").toArray();
			//绘制表格内容行
			JSONArray jaDatas = new JSONArray();
			
			JSONArray joDatas = ja.getJSONObject(0).getJSONArray( "data");
			jaDatas = setRowData( joDatas );
			
			rptDo.setSheetData( jaDatas);
			List<RptExcelDomain> sheets = new ArrayList<RptExcelDomain>();
			sheets.add( rptDo);
			setSheets( sheets);
			writeExcel(os, true);
		}catch( Exception e){
//			e.printStackTrace();
		}finally{
			if (os != null ) os.close();
		}
	}
	
	/**
	 * 构建数据excel结构
	 * @param joDatas
	 * @return
	 */
	private JSONArray setRowData( JSONArray joDatas ){
		
		JSONArray result = new JSONArray(5);
		for( int i = 0; i < joDatas.size(); i++){
			JSONObject jo = joDatas.getJSONObject( i);
			ArrayList<Object> arr = new ArrayList<Object>();
			
			arr.add( jo.getString( "className"));
			arr.add( jo.getString( "stuCount") == null? " ": jo.getString( "stuCount"));
			arr.add( jo.getString( "clsSvg") == null? " ": jo.getString( "clsSvg"));
			arr.add( jo.getString( "clsRank") == null||jo.getIntValue( "clsRank") == 9999? " ": jo.getString( "clsRank"));
			arr.add( jo.getString( "clsSvg1") == null? " ": jo.getString( "clsSvg1"));
			arr.add( jo.getString( "clsRank1") == null||jo.getIntValue( "clsRank1") == 9999? " ": jo.getString( "clsRank1"));
			
			Object[] datas = jo.getJSONArray( "scores").toArray();
			for( int j = 0; j < datas.length; j++){
				Object v = datas[j];
				if(v.equals(-1.0)){
					arr.add( "");
				}else{
					arr.add( v);
				}
			}
			
			JSONObject rjo = new JSONObject();
			rjo.put( "data", arr);
			rjo.put( "sort", jo.getString( "clsRank1") );
			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 = joHeads2.length + 6;

		ArrayList<String> headers = new ArrayList<String>();
		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, 2, i, i));
			cell_header.setCellStyle( headerStyle);
			cell_header.setCellValue( headers.get( i));
		}

		HSSFCell cell_header = row.createCell( baseL );
		sheet.addMergedRegion(new CellRangeAddress(1, 1, baseL, baseL+1));
		cell_header.setCellStyle( headerStyle);
		cell_header.setCellValue( "总分平均分");
		HSSFCell cell_header1 = row.createCell( baseL+2 );
		sheet.addMergedRegion(new CellRangeAddress(1, 1, baseL+2, baseL+3));
		cell_header1.setCellStyle( headerStyle);
		cell_header1.setCellValue( "总分平均分(折后)");
		
		int offset = baseL + 4;
		for( int i = 0; i < joHeads.length; i++){
			String head =(String) joHeads[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;
		}
		

		row = sheet.createRow(2);
		for( int i = 0; i < 3; i++ ){
			cell_header = row.createCell(i);

			cell_header.setCellStyle( headerStyle);
			cell_header.setCellValue( " ");
		}
		cell_header = row.createCell(2);
		cell_header.setCellStyle( headerStyle);
		cell_header.setCellValue( "总分");
		cell_header = row.createCell(3);
		cell_header.setCellStyle( headerStyle );
		cell_header.setCellValue( "排名");
		cell_header = row.createCell(4);
		cell_header.setCellStyle( headerStyle);
		cell_header.setCellValue( "总分");
		cell_header = row.createCell(5);
		cell_header.setCellStyle( headerStyle );
		cell_header.setCellValue( "排名");
		
		for( int i = 0; i < joHeads2.length; i++ ){
			cell_header = row.createCell(i+6);

			cell_header.setCellStyle(headerStyle );
			cell_header.setCellValue( (String)joHeads2[i] );
		}
		
		/*---------------------------------------
		 * 创建sheet的数据
		 *--------------------------------------*/
		JSONArray datas = rptDomain.getSheetData();
		
		//数据按照排名排序
		List<Integer> lst = new ArrayList<Integer>();
		for( int i = 0; i < datas.size(); i++){
			JSONObject rowData = datas.getJSONObject( i);
			Integer sort = rowData.getInteger( "sort");
			lst.add(sort);
		}
		TableComparator tcomp = new TableComparator();
		Collections.sort(lst, tcomp );

		HSSFCellStyle cellStyle = this.getStyleCellDefault();
		for( int s = 0; s < lst.size(); s++){
			for( int i = 0; i < datas.size(); i++){
				JSONObject rowData = datas.getJSONObject( i);
				int sort = rowData.getIntValue( "sort");
				if( sort == lst.get(s).intValue() ){
					row = sheet.createRow( 3+s);
					Object[] _cellDatas = rowData.getJSONArray( "data").toArray();

					for( int j = 0; j < _cellDatas.length; j++){

						HSSFCell cell_Data = row.createCell( j);
						cell_Data.setCellStyle( cellStyle);
//						cell_Data.setCellStyle( this.getStyleCellDefault());
						// 给Excel的单元格设置样式和赋值
						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);
						}
					}//完成行数据装载
					break;
				}
			}
		}//完成排序筛选
	}
}