java导出excel表头跨行,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 sheets = new ArrayList();

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 arr = new ArrayList();

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 headers = new ArrayList();

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 lst = new ArrayList();

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;

}

}

}//完成排序筛选

}

}