欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-05-26 11:13:14
...

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


导出动态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);
						}
					}//完成行数据装载
				
				
		}
		
		

	}
}