实现各种文件的下载方法：用于 WEB上的报表导出（jxl 实现Excel报表下载），核心思想就是将文件的输出流传给HttpResponseOutputStream 里就成了。

 

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
//import java.util.Random;

import javax.servlet.http.HttpSession;

import jxl.Cell;
import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
//import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.StringUtils;
import org.dom4j.Element;

import com.aspire.reportSystem.common.constant.LogConstant;
import com.aspire.reportSystem.common.dao.DBdao;
import com.aspire.reportSystem.common.exception.FrameException;
import com.aspire.reportSystem.common.log.LogWriter;
import com.aspire.reportSystem.common.log.ReportLog;
//import com.aspire.reportSystem.common.util.ServerDetector;
import com.aspire.reportSystem.common.util.ServerUtility;
import com.aspire.reportSystem.common.util.StringUtil;
import com.aspire.reportSystem.common.util.TimeUtil;
import com.aspire.reportSystem.common.util.XMLUtility;
import com.aspire.reportSystem.common.util.ZipUtils;
import com.aspire.reportSystem.portal.report.constant.Constant;
import com.aspire.reportSystem.portal.report.constant.ReportConstant;
import com.aspire.reportSystem.portal.report.dao.ReportDao;
import com.aspire.reportSystem.portal.report.exportReport.dao.ExcelDao;
import com.aspire.reportSystem.portal.report.exportReport.vo.ExcelReportDisplayVO;
import com.aspire.reportSystem.portal.report.sql.SQLAssembled;
import com.aspire.reportSystem.portal.report.util.ExcelFormatUtil;
import com.aspire.reportSystem.portal.report.util.OperateXML;
import com.aspire.reportSystem.portal.report.vo.ColumnFormatVO;
import com.aspire.reportSystem.portal.report.vo.ContentFormatVO;
import com.aspire.reportSystem.portal.report.vo.ExportFileVO;
import com.aspire.reportSystem.portal.report.vo.ReportConfigVO;
import com.aspire.reportSystem.portal.report.vo.ReportPageVO;
/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 * @author Lee
 * @version 1.0
 */
public class ReportEngine4Excel {

    private int newColumn;
    private ExcelReportDisplayVO reportDisplayVO;
    private ReportConfigVO reportConfigVO;
    private ReportPageVO reportPageVO;
    private List result = null;
    private HttpSession session = null;
   
    public static final int EXCEL_EXPORT_ERROR = 11;

    public ReportEngine4Excel(HttpSession session,ReportPageVO reportPageVO,ReportConfigVO reportConfigVO) {
  this.reportConfigVO = reportConfigVO;
  this.reportPageVO = reportPageVO;
  this.session = session;
  restoreToReportPageVO();
 }
 
    private void excuteExcelSQL(String sql){
     
     ReportDao reportDAO = new ReportDao();
     
     result = reportDAO.getResultList(sql, null, 0, 0);
     
//     /*************************    for test start    *************************************/
//     
//     result = new ArrayList();
//     
//     for(int i=0;i<32201;i++) {
//      result.add(new String[]{"111","222","333"});
//     }
     
     
      
     /*******************************   for test end    **********************************/ 
      
     ReportLog.sql(sql);
     ReportLog.info("result.size() ::::::::::" + result.size());
    }
   
    /**
     *  导出csv 格式的文件
     * @param queryPageVO
     * @param sql
     * @param excelDao
     * @param exportFileVO
     * @return
     * @throws Exception
     */
    private String exportCSVFile(ReportPageVO queryPageVO,String sql,
     ExcelDao excelDao,ExportFileVO exportFileVO) throws Exception{
     
     int fileNum = 0;
     
     int maxNum = 0;
     
     int minNum = 0;
     
     int maxRecordOnePage = exportFileVO.getMaxRecordOnePage();
     
     int totalNum = exportFileVO.getTotalNum();
     
     boolean isMod0 = false;
     
     if(totalNum % maxRecordOnePage != 0) {
      fileNum = totalNum/maxRecordOnePage + 1;
      } else {
       isMod0 = true;
       fileNum = totalNum/maxRecordOnePage;
      }
     
     String tempFileFolderName = ServerUtility.getWeblogicPath()
         + File.separator+ReportConstant.TEMP_FOLDER_NAME;
      
     File tempFolder = new File(tempFileFolderName);
     
     if(!tempFolder.exists()) {
      tempFolder.mkdir();
     }
        
     tempFileFolderName = tempFileFolderName   + File.separator
         + "ep" + TimeUtil.getCurrentlyTimeByMillSecond();
 
     File tempFileFolder = new File(tempFileFolderName);
 
     if(!tempFileFolder.exists()) {
      tempFileFolder.mkdir();
     }
    
     String tempFileZipName = tempFileFolderName + File.separator
        + queryPageVO.getTitle() + TimeUtil.getCurrentlyTime()+ ".zip";
    
     OutputStream os=null;
    
     List list = new ArrayList();
     
     for(int i = 0;i<fileNum;i++) {
      
      String tempFileName = tempFileFolderName + File.separator
          + queryPageVO.getTitle() + "_"
          + (i + 1) +"." + exportFileVO.getExtendName();
//      tempFileName = new String(tempFileName.getBytes("ISO8859-1"),"GBK");
      list.add(tempFileName);
      
      File tempFile = new File(tempFileName);
      
   try {
    os = new FileOutputStream(tempFile);
   
    maxNum = maxRecordOnePage * (i + 1);
    
    minNum = maxRecordOnePage * i ;
    
    if( i ==  fileNum - 1 && !isMod0) {
     
     minNum = maxRecordOnePage * i;
     maxNum = minNum + totalNum % maxRecordOnePage;
    }
    
    getHeadTitleString(queryPageVO,os);
    
    getTitleCSVString(queryPageVO,os);
//    if( ServerDetector.getServerId().equals("weblogic") ) {
     excelDao.queryTable(sql, maxNum, minNum,os);
//    } else {
//     excelDao.queryTableForTomcat(sql, maxNum, minNum,os);
//    }
            
   } catch (IOException ex) {
    ex.printStackTrace();
             LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
             throw new FrameException(EXCEL_EXPORT_ERROR, "Excel文件操作错误" + ex.getMessage());
       
   } finally{
    if(os!= null) {
     try {
      os.flush();
      os.close();
     } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
    }
   }
     }
     
     ZipUtils.gerZipFilesBySpeed(list, tempFileZipName,exportFileVO.getZipCompressLevel());
     
     return tempFileZipName;
    }
   
   
    private void getHeadTitleString(ReportPageVO queryPageVO,OutputStream os) throws IOException{
     
     ExcelReportDisplayVO rdVO = this.getReportDisplayVO();
     
     os.write((queryPageVO.getTitle()+"/n").getBytes());
     
     os.write((rdVO.getFromTime()+"/n").getBytes());
     
     os.write((rdVO.getCreateTime()+"/n").getBytes());
     
     os.write((rdVO.getQueryTerm()+"/n").getBytes());
     
    }
   
    /**
     * 将 报表文件打成zip包，并导出
     * @param out
     * @param inputZipFile
     */
    private void exportZipFile(OutputStream out,String inputZipFile) {
     
     FileInputStream in = null;
     
     try {
   in = new FileInputStream(inputZipFile); // 读入文件
   out.flush();
   int aRead = 0;
   while ((aRead = in.read()) != -1 & in != null) {
    out.write(aRead);
   }
   out.flush();
  } catch (Throwable e) {
   // log.error("FileDownload doGet() IO error!",e);
  } finally {
   try {
    in.close();
    out.close();
   } catch (Throwable e) {
    // log.error("FileDownload doGet() IO close error!",e);
   }
  }
    }
   
    /**
     * 画 csv文件剃头
     * @param queryPageVO
     * @param os
     * @throws IOException
     */
    private void getTitleCSVString(ReportPageVO queryPageVO,OutputStream os) throws IOException {
     
     //画报表 列头 !!!!!!
        List titleList = queryPageVO.getPageLayoutHeadList();
       
        for(int i = 0; i <titleList.size()-1;i++) {
         os.write((((ColumnFormatVO)titleList.get(i)).getCloumnName() +",").getBytes());
        }
       
        os.write((((ColumnFormatVO)titleList.get(titleList.size()-1)).getCloumnName()+"/n").getBytes());
       
    }
   
 public void restoreToReportPageVO() {
  String pageLayoutKey = reportPageVO.getPageLayoutKey();  
  String sqlConfigKey = reportPageVO.getSqlConfigKey();
  String roleright = reportPageVO.getRoleRight();
  List nodeList = null;
  Element pageLayout = null;
  
  if (sqlConfigKey==null||"".equalsIgnoreCase(sqlConfigKey)){
   sqlConfigKey = "default";
  }   
  nodeList = XMLUtility.selectNodes(reportConfigVO.getSqlConfigListNode(),
               "sqlConfig[@key='" + sqlConfigKey  +  "']");  
  reportPageVO.setSqlConfig(OperateXML.getElementByRole(nodeList, roleright));
  
  nodeList = null;
  
  if (pageLayoutKey==null||"".equalsIgnoreCase(pageLayoutKey)){
   pageLayoutKey = "default";
  }
  
  nodeList = XMLUtility.selectNodes(reportConfigVO.getPageLayoutLstNode(),
               "pageLayout[@key='" + pageLayoutKey  +  "']");
  
  pageLayout = OperateXML.getElementByRole(nodeList, roleright);
  
  if (!"default".equals(pageLayoutKey)&&pageLayout==null){
   nodeList = XMLUtility.selectNodes(reportConfigVO.getPageLayoutLstNode(), "pageLayout[@key='default']");
   pageLayout = OperateXML.getElementByRole(nodeList, roleright);   
  }  
  
  String pagination = XMLUtility.getAttributeValue(pageLayout, "pagination",false);
  
  if (pagination!=null&&!"".equalsIgnoreCase(pagination)){
   if (pagination.equalsIgnoreCase("true")){
    reportPageVO.setPagination(true);
   }
  }
  
  if (reportPageVO.getPageSize()==0){
   String pageSize = XMLUtility.getAttributeValue(pageLayout, "pageSize", false);
   if (pageSize!=null&&!"".equalsIgnoreCase(pageSize)){
    reportPageVO.setPageSize(Integer.parseInt(pageSize));
   }
  }  
  List headList = XMLUtility.selectNodes(pageLayout,"headConfig/head");
  List contentList = XMLUtility.selectNodes(pageLayout,"contentConfig/content");
  reportPageVO.setPageLayoutHeadNodeList(headList);
  reportPageVO.setPageLayoutContentNodeList(contentList); 
  
  if (headList!=null&&!headList.isEmpty()){
   Iterator iter = headList.iterator();
   Element nowElement = null;
   String name = null;
   String join = null;
   String newLine = null;
//   ColumnFormatVO columnFormatVO = null;
   List headArrayList = new ArrayList();
   
   while (iter.hasNext()){
        
    name = null;
    join = null;
    newLine = null;
    nowElement = (Element) iter.next();
    name = XMLUtility.getAttributeValue(nowElement,"name",false);
    join = XMLUtility.getAttributeValue(nowElement,"join",false);
    newLine = XMLUtility.getAttributeValue(nowElement,"newLine",false);
    
    if (newLine!=null&&!"".equalsIgnoreCase(newLine)&&"true".equalsIgnoreCase(newLine)){
     ColumnFormatVO columnFormatVO = new ColumnFormatVO();
     columnFormatVO.setNewLine(true);
     headArrayList.add(columnFormatVO);
    }else {          
     
     if (name!=null&&Constant.REPORT_PARAM_DAY_BEGIN_TO_END.equals(name)){
      
      Date fromTime = TimeUtil.toDate(reportPageVO.getFromTime(), "yyyy-MM-dd");      
      Date toTime = TimeUtil.toDate(reportPageVO.getToTime(), "yyyy-MM-dd");
      
      int dayNum = TimeUtil.compareDate(fromTime, toTime);
      
      for (int i = 0 ; i < dayNum + 1;i++){
       
       ColumnFormatVO columnFormatVO = new ColumnFormatVO();
       name = TimeUtil.toChar(TimeUtil.addDay(fromTime, i),"yyyyMMdd");
       if (name!=null){
        columnFormatVO.setCloumnName(name);
       }
       if (join!=null){
        columnFormatVO.setJoinMethod(join);
       }
       headArrayList.add(columnFormatVO);
      }
     } else if (name!=null&&Constant.REPORT_PARAM_DYNAMIC_SQL.equals(name)){
      String sql = XMLUtility.getNodeText(nowElement, null,false);
      
      sql = StringUtils.replace(sql, "{FROM_TIME}", StringUtils.replace(reportPageVO.getFromTime(), "-", ""));
      sql = StringUtils.replace(sql, "{TO_TIME}", StringUtils.replace(reportPageVO.getToTime(), "-", ""));
         DBdao dBdao = new DBdao();
         List list = null;
//         if( ServerDetector.getServerId().equals("weblogic") ) {
         list = dBdao.queryTable(sql);
//         } else {
//          list = dBdao.queryTableForTomcat(sql);
//         }
       
      this.reportPageVO.setDynamicPageLayoutHeadList(list);
      
//      Iterator it = list.iterator();
      String headColumnArray[] = null;
                            
      for (int k = 0; k< list.size(); k ++){ 
       headColumnArray = (String[])list.get(k);
       ColumnFormatVO columnFormatVO = new ColumnFormatVO();
       columnFormatVO.setCloumnName(headColumnArray[1]);
       headArrayList.add(columnFormatVO);
      }     
     }else {
      ColumnFormatVO columnFormatVO = new ColumnFormatVO();      
      if (name!=null){
       columnFormatVO.setCloumnName(name);
      }
      if (join!=null){
       columnFormatVO.setJoinMethod(join);
      }      
      headArrayList.add(columnFormatVO);
     }     
   
    }   
   }
   
   reportPageVO.setPageLayoutHeadList(headArrayList);
  }
  
  if (contentList!=null&&!contentList.isEmpty()){
   Iterator iter = contentList.iterator();
   Element nowElement = null;
   int columnNum = 0;
   String dimension = null;
   String unite = null;
   String relate = null;
   String type = null;
   String href = null;
//   ContentFormatVO contentFormatVO = null;
   List contentArrayList = new ArrayList();
   
   while (iter.hasNext()){
    dimension = null;
    unite = null;
    relate = null;
    nowElement = (Element) iter.next();
    columnNum ++;    
    dimension = XMLUtility.getAttributeValue(nowElement,"dimension",false);
    unite = XMLUtility.getAttributeValue(nowElement,"unite",false);
    relate = XMLUtility.getAttributeValue(nowElement,"relate",false);
    type = XMLUtility.getAttributeValue(nowElement,"type",false);
    href = XMLUtility.getAttributeValue(nowElement,"href",false);
    
    if (type!=null&&Constant.REPORT_PARAM_DAY_BEGIN_TO_END.equals(type)){
     Date fromTime = TimeUtil.toDate(reportPageVO.getFromTime(), "yyyy-MM-dd");
     Date toTime = TimeUtil.toDate(reportPageVO.getToTime(), "yyyy-MM-dd");
     
     int dayNum = TimeUtil.compareDate(fromTime, toTime);
     for (int i = 0 ; i < dayNum + 1;i++){
      ContentFormatVO contentFormatVO = new ContentFormatVO();
      contentFormatVO.setColumnNum(columnNum);
      contentFormatVO = new ContentFormatVO();
      if (dimension!=null&&!"".equalsIgnoreCase(dimension)&&"true".equalsIgnoreCase(dimension)){
       contentFormatVO.setDimension(true);
      }
      if (unite!=null&&!"".equalsIgnoreCase(unite)&&"true".equalsIgnoreCase(unite)){
       contentFormatVO.setNeedJoin(true);
      }    
      if (relate!=null&&!"".equalsIgnoreCase(relate)){
       contentFormatVO.setReleateCloumn(relate);
      }
      if (href!=null&&!"".equalsIgnoreCase(href)){
       contentFormatVO.setHref(href);
      }
      contentArrayList.add(contentFormatVO);
      columnNum ++;
     }
     columnNum --;
    } else if(type!=null&&Constant.REPORT_PARAM_DYNAMIC_SQL.equals(type)) {
     List list = this.reportPageVO.getDynamicPageLayoutHeadList();

     for (int i = 0 ; i < list.size();i++){
      ContentFormatVO contentFormatVO = new ContentFormatVO();
      contentFormatVO.setColumnNum(columnNum);
      contentFormatVO = new ContentFormatVO();
      contentArrayList.add(contentFormatVO);
      columnNum ++;
     }
     columnNum --; 
    }else {
     ContentFormatVO contentFormatVO = new ContentFormatVO();
     contentFormatVO.setColumnNum(columnNum);
     if (dimension!=null&&!"".equalsIgnoreCase(dimension)&&"true".equalsIgnoreCase(dimension)){
      contentFormatVO.setDimension(true);
     }
     if (unite!=null&&!"".equalsIgnoreCase(unite)&&"true".equalsIgnoreCase(unite)){
      contentFormatVO.setNeedJoin(true);
     }    
     if (relate!=null&&!"".equalsIgnoreCase(relate)){
      contentFormatVO.setReleateCloumn(relate);
     }  
     if (href!=null&&!"".equalsIgnoreCase(href)){
      contentFormatVO.setHref(href);
     }     
     contentArrayList.add(contentFormatVO);
    }
   }
   
   
   
   reportPageVO.setPageLayoutContentList(contentArrayList);
  }  
  
  
 }
 
    /**
     * 导出excel到输入流
     *
     * @param queryPageVO QueryPageVO
     * @param os OutputStream
     * @throws OmrException
     */
    public String exportExcelReport(OutputStream os,ExportFileVO exportFileVO) throws FrameException{
     
     ReportPageVO queryPageVO = reportPageVO;
     
     String zipFile = null;
     
        if (queryPageVO == null) {
            //记录异常日志
            LogWriter.appendLog(this.getClass().getName(),
                 LogConstant.LOG_LEVEL_ERROR, "queryPageVO is null!");
            throw new FrameException(EXCEL_EXPORT_ERROR,
                                     "ReportPageVO is null!");
        }
        if(os == null){
            LogWriter.appendLog(this.getClass().getName(),
              LogConstant.LOG_LEVEL_ERROR, "outputStream is null");
            throw new FrameException(EXCEL_EXPORT_ERROR,
                                     "outputStream is null");
        }
        try {
         String sql = null;
         SQLAssembled sqlAssembled = new SQLAssembled();
         sql = sqlAssembled.getSQL(session,reportPageVO);
         
         ExcelDao excelDao = new ExcelDao();
         
         //文件类型
         String fileType = exportFileVO.getFileType();
         
         if(ReportConstant.TEXT_TYPE.equals(fileType)) {
          
          zipFile = exportCSVFile(queryPageVO,sql,excelDao,exportFileVO);
          
          exportZipFile(os,zipFile);
         } else if(ReportConstant.EXCEL_TYPE.equals(fileType)) {
             
          excuteExcelSQL(sql);         
          zipFile = getExcelReport(queryPageVO,exportFileVO);
          exportZipFile(os,zipFile);
         }

        } catch (WriteException ex) {
         ex.printStackTrace();
            LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
            throw new FrameException(EXCEL_EXPORT_ERROR, "Excel写入错误" + ex.getMessage());
        } catch (IOException ex) {
         ex.printStackTrace();
            LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
            throw new FrameException(EXCEL_EXPORT_ERROR, "Excel文件操作错误" + ex.getMessage());
        } catch (BiffException ex) {
         ex.printStackTrace();
            LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
            throw new FrameException(EXCEL_EXPORT_ERROR, "Excel工作薄读写错误" + ex.getMessage());
        } catch (Exception ex) {
            ex.printStackTrace();
            LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
            throw new FrameException(EXCEL_EXPORT_ERROR, "导出Excel过程错误" + ex.getMessage());
        }
        return zipFile;
    }
    /**
     * 画Excel 文件模板
     * @param queryPageVO
     * @param f
     * @param sheetNum
     * @return
     */
    private int drawExcelStencilFile(ReportPageVO queryPageVO,File f,int sheetNum) {
     
      int dimensionNum = 0;
     
     int sheetNameNum = 1;
     try {
      WritableWorkbook wwb = Workbook.createWorkbook(f);
      
      for(int i=0;i<sheetNum;i++) {
       
        WritableSheet sheet = null;
        
        if(sheetNum == 1){
         sheet = wwb.createSheet(queryPageVO.getTitle() , i);
        } else {
         sheet = wwb.createSheet(queryPageVO.getTitle() + sheetNameNum , i);
        }
 
        CellView cellview = new CellView();
        
        cellview.setSize(100);
        
        int drawCellColumnNum = 0;
             
              int drawCellRowNum = 5;
              //画报表 列头 !!!!!!
              List titleList = queryPageVO.getPageLayoutHeadList();
             
              Iterator titleIterator = titleList.iterator();
       
              while(titleIterator.hasNext()) {
               ColumnFormatVO columnFormatVO = (ColumnFormatVO)titleIterator.next();
                  WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
               wf.setColour(Colour.BLUE);
               WritableCellFormat wcfF = new WritableCellFormat(wf);
               wcfF.setBackground(Colour.ICE_BLUE);
               wcfF.setAlignment(Alignment.CENTRE);
               wcfF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置细边框
               sheet.addCell(new Label(drawCellColumnNum,drawCellRowNum,columnFormatVO.getCloumnName(),wcfF));
               if(columnFormatVO.getJoinMethod()!=null) {
                ExcelFormatUtil.formatCell(sheet,columnFormatVO.getJoinMethod(),drawCellColumnNum,drawCellRowNum);
               }
              
               drawCellColumnNum++;
              }
             
              drawCellRowNum ++;
             
              drawCellColumnNum = 0;
             
              //画 join auto percent 或 参照合并的参照列数
              List contentFormatList = queryPageVO.getPageLayoutContentList();
             
              Iterator contentIterator = contentFormatList.iterator();
             
              WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
              WritableCellFormat wcfF = new WritableCellFormat(wf);
              wcfF.setAlignment(Alignment.CENTRE);
              wcfF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置细边框
            
              while(contentIterator.hasNext()) {
               
                 ContentFormatVO contentFormatVO = (ContentFormatVO)contentIterator.next();
                 
                 boolean  isNeedJoin= contentFormatVO.isNeedJoin();
               
                 if(isNeedJoin) {
                  
                  sheet.addCell(new Label(drawCellColumnNum,drawCellRowNum,ReportConstant.EXCEL_JOIN,wcfF));
                  drawCellColumnNum ++;
                 } else {
                  sheet.addCell(new Label(drawCellColumnNum,drawCellRowNum,ReportConstant.NO_JOIN,wcfF));
                  drawCellColumnNum ++;
                 }
                 
               if(contentFormatVO.isDimension() && i ==0) {
                dimensionNum ++;
               }
              }
       
       sheetNameNum ++;
      }
     
        wwb.write();
        
         wwb.close();
     } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (RowsExceededException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (WriteException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return dimensionNum;
     
    }
     /**
      * 画文件内容
      * @param dimensionNum
      * @param file
      * @param os
      * @param queryPageVO
      * @param sheetNum
      * @throws BiffException
      * @throws IOException
      * @throws WriteException
      */
     private void drawMainTitleContent(int dimensionNum , File file,
      OutputStream os,ReportPageVO queryPageVO,int sheetNum) throws BiffException,
      IOException, WriteException {
     
         int pageNum = 1;
        
         Workbook rwb = Workbook.getWorkbook(file);
         WritableWorkbook wwb = null;

//       String tempFileName = "d:/gaga.xls";
//       wwb = Workbook.createWorkbook(new FileOutputStream(new File(tempFileName)),rwb);
        
         wwb = Workbook.createWorkbook(os,rwb);
        
      for(int k = 0;k<sheetNum; k++) {
          
              WritableSheet sheet = wwb.getSheet(k);
              //行
              int row = 0;
              //需要合并的列
              List joinList = new ArrayList();

              row = sheet.getRows();
//              column = sheet.getColumns();
              String[] data = null;
              Label label = null;
              jxl.write.Number number = null;
//              WritableCell wc = null;
//              Cell[] cells = null;
              Cell[] styles = null;
//              cells = sheet.getRow(row - 2);
              styles = sheet.getRow(row - 1);
              Cell cell = null;
              Cell style = null;
             
              List newCell = new ArrayList();
         
              //显示对象
             ExcelReportDisplayVO rdVO = this.getReportDisplayVO();
            
           WritableFont wf = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false);
           wf.setColour(Colour.BLUE);
           WritableCellFormat wcfF = new WritableCellFormat(wf);
           wcfF.setAlignment(Alignment.CENTRE);
          
           wcfF.setShrinkToFit(true);
          
           sheet.addCell(new Label(0,0, queryPageVO.getTitle(),wcfF));
          

           WritableFont wf1 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false);
           WritableCellFormat wcfF1 = new WritableCellFormat(wf1);
           wcfF1.setAlignment(Alignment.CENTRE);
          
           WritableFont wf2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
           wf2.setColour(Colour.RED);
           WritableCellFormat wcfF2 = new WritableCellFormat(wf2);
           wcfF2.setAlignment(Alignment.LEFT);
          
           WritableFont wf3 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false);
           WritableCellFormat wcfF3 = new WritableCellFormat(wf3);
           wcfF3.setAlignment(Alignment.RIGHT);
          
           sheet.addCell(new Label(0,1,"",wcfF1));
              sheet.addCell(new Label(0,2,rdVO.getFromTime(),wcfF1));
             
              sheet.addCell(new Label(0,3,rdVO.getCreateTime(),wcfF3));
            
              sheet.addCell(new Label(0,4,rdVO.getQueryTerm(),wcfF2));
             
              Iterator iterator = queryPageVO.getPageLayoutHeadList().iterator();
             
              int maxColumnLehgth = 5;
             
              while(iterator.hasNext()) {
               
               ColumnFormatVO columnFormatVO = (ColumnFormatVO)iterator.next();
               
               int tempColumnLehgth = columnFormatVO.getCloumnName().toCharArray().length;
               
               if(maxColumnLehgth < tempColumnLehgth) {
                maxColumnLehgth = tempColumnLehgth;
               }
              }
             
              int columnNum = queryPageVO.getPageLayoutHeadList().size();
             
              int needDrawColumnLength =  maxColumnLehgth*3;
             
              if(needDrawColumnLength >= 80) {
               needDrawColumnLength = needDrawColumnLength/3;
              }
             
              for(int i = 0; i< columnNum;i++) {
               sheet.setColumnView(i,needDrawColumnLength);
              }
             
             
              //动态列的数
              int autoSize = -1;
              List percentList = new ArrayList();
              //分析
              for(int i = 0; i < styles.length; i++){
                  style = styles[i];
                  if(style.getContents().indexOf(ReportConstant.EXCEL_JOIN) > -1){//合并
                      joinList.add(new Integer(i));
                  }
                  if(style.getContents().indexOf(ReportConstant.HTML_AUTO) > -1){//动态列
                      autoSize = i;
                  }
                  if(style.getContents().indexOf(ReportConstant.PERCENT) > -1){//百分号
                      percentList.add(new Integer(i));
                  }
              }
              newColumn = styles.length;//赋值。(bug:CQID:PSP_OMR_36)
              //有动态列
              if(autoSize >= 0){
//                  excelDynStyle(queryPageVO, row, cells, styles, label, cell, newCell, autoSize);
               
//               //如果是数据统计分析报表,就调用处理非特定业务的方法划剃头
//               if(ReportConstant.PFR_STAT29.equals(queryPageVO.getReportId())) {
      //
//                   excelQueryServDynStyle(queryPageVO, row, cells, styles, label, cell, newCell, autoSize, percentList);
      //
//                  //否则调用处理特定业务的方法划剃头
//               } else {
//                   excelQueryDynStyle(queryPageVO, row, cells, styles, label, cell, newCell, autoSize, percentList);             
//                  }
              }

              //重写表头
              //有动态列
              if(autoSize >= 0){
                  Iterator itor = newCell.iterator();
                  while(itor.hasNext()){
                      label = (Label)itor.next();
                      sheet.addCell(label);
                  }
              }else{//没有动态列
                  //
              }
           
                int index = row - 1;
             
              //数据单元格格式
           WritableFont contentWF = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false);
           WritableCellFormat contentWCF = new WritableCellFormat(contentWF);
           contentWCF.setAlignment(Alignment.CENTRE);
           contentWCF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);//设置细边框
             
           jxl.format.CellFormat cfData = contentWCF;
              /**
               * 测试代码
               */
              //转换日期格式
////              formatDate(queryPageVO);
//              try {
////                  result = getTestData();
//               
////                  result = QueryUtil.query(queryPageVO);
//               /*************    For Test start         ************/
//               ArrayList al = new ArrayList();
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               al.add(new String[]{"111","222","333"});
//               
//               
//              result = al;
//             
//              /*************    For Test end         ************/
//             
//             
//              } catch (Exception ex) {
////                  OmrLog.error("查询出错", ex);
//                  LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
//                  throw ex;
//              }

//              if(ReportConstant.PFR_STAT08.equals(queryPageVO.getReportId())) {
//               if(result != null){
//                int len = result.size() -1;
//                     
//                      String content = null;
//                      for(int j=0;j<len;j++){
//                          data = (String[])result.get(j);
//                         
//                          //baoyu 算法
//                          for(int i = 0; i < dimensionNum; ++i){
//                              content = formatString(percentList, data[i], i);
//                              label = new Label(i, index, content, cfData);
//                              sheet.addCell(label);                                 
//                          }
//                      
//                          for(int i = dimensionNum; i < data.length - 1; ++i){
//                           
//                              content = formatString(percentList, data[i], i);
//                             
//                              if(!StringUtil.isNumeric(content)){
//                                   label = new Label(i, index, content, cfData);
//                                   sheet.addCell(label);
//                              }else{
//                                   number = new jxl.write.Number(i, index, Double.parseDouble(content), cfData);
//                                   sheet.addCell(number);
//                              }
//                          }
//                          index++;
//                      }
//                     
//                      //取和
//                      data = (String[]) result.get(result.size() - 1);
//                     
//                      //baoyu 算法
//                      for(int i = 0; i < dimensionNum; ++i){
//                          content = formatString(percentList, data[i], i);
//                          label = new Label(i, index, content, cfData);
//                          sheet.addCell(label);                                 
//                      }
//                  
//                      for(int i = dimensionNum; i < data.length ; ++i){
//                       
//                          content = formatString(percentList, data[i], i);
//                         
//                          if(!StringUtil.isNumeric(content)){
//                               label = new Label(i, index, content, cfData);
//                               sheet.addCell(label);
//                          }else{
//                               number = new jxl.write.Number(i, index, Double.parseDouble(content), cfData);
//                               sheet.addCell(number);
//                          }
//                      }
//                      index++;
//                     
//                  }
//              } else {
          
           int shouldGetNum = 0;
           int totalNum = 65000;
          
                if(sheetNum > 1 && k > 0) {
                 shouldGetNum = 65000 * k;
                }
               
                if(sheetNum > 1 && k == sheetNum -1) {
                 shouldGetNum = 65000 * k ;
                 totalNum = (result.size()%65000);
                }
               
                if(k==0 && sheetNum == 1) {
                 totalNum = result.size();
                }
          
           for(int l=0;l<totalNum;l++) {
             
                    String content = null;
                   
                    data = (String[])result.get(shouldGetNum);
                 
                 //baoyu 算法
                 for(int i = 0; i < dimensionNum; ++i){
                  content = formatString(percentList, data[i], i);
                  content = StringUtils.replace(content, "<span style=/"COLOR:RED/">", "");
                  content = StringUtils.replace(content, "</span>", "");
                  label = new Label(i, index, content, cfData);
                  sheet.addCell(label);                                 
                 }
             
                 for(int i = dimensionNum; i < data.length; ++i){
                  
                  content = formatString(percentList, data[i], i);
                       
                  content = StringUtils.replace(content, "<span style=/"COLOR:RED/">", "");
                  
                  content = StringUtils.replace(content, "</span>", "");
                  
                  if(!StringUtil.isNumeric(content)){
                   label = new Label(i, index, content, cfData);
                   sheet.addCell(label);
                  }else{
                   number = new jxl.write.Number(i, index, Double.parseDouble(content), cfData);
                   sheet.addCell(number);
                  }
                 }
                 
                 if(sheetNum != 1) {
                  StringBuffer pageInfo = new StringBuffer("第 ");
                     
                     pageInfo.append(pageNum);
                     
                     pageInfo.append("/");
                     
                     pageInfo.append(sheetNum);
                     
                     pageInfo.append(" 页 共 ");
                     
                     pageInfo.append(sheetNum);
                     
                     pageInfo.append(" 页");
                     
                     //画一共几页 第几页
                     label = new Label(columnNum -1, totalNum + 6, pageInfo.toString(), wcfF1);
               sheet.addCell(label);                  
                 }
                 

                 shouldGetNum ++;
                 index++;
             
           } 
           
                //合并表信息
          for(int i = 0; i < row -2; ++i){
             sheet.mergeCells(0, i, newColumn - 1, i);
          }
          //合并数据
          if(!joinList.isEmpty()){
              excelJoin(row, joinList, sheet, cell);
          }
       
          pageNum ++ ;
          

      }
         wwb.write();
         wwb.close();
     }

    //导出Excel，如果传入的OutputStream为空，表示要生成文件，返回生成的文件路径及文件名。
    //如果OutputStream不为空，则直接生成输出到OutputStream
    private String getExcelReport(ReportPageVO queryPageVO,ExportFileVO exportFileVO) throws BiffException,
            WriteException, FrameException,Exception {
       
     List list = new ArrayList();
     
     int maxRecordOnePage = exportFileVO.getMaxRecordOnePage();
     
     String tempFileFolderName = ServerUtility.getWeblogicPath()
  + File.separator + ReportConstant.TEMP_FOLDER_NAME;

     File tempFolder = new File(tempFileFolderName);

     if (!tempFolder.exists()) {
      tempFolder.mkdir();
     }
     
     tempFileFolderName = tempFileFolderName + File.separator + "ep" +
          TimeUtil.getCurrentlyTimeByMillSecond();
   
       File tempFileFolder = new File(tempFileFolderName);

       if(!tempFileFolder.exists()) {
        tempFileFolder.mkdir();
       }
      
     String tempFileZipName = tempFileFolderName + File.separator
        + queryPageVO.getTitle() + TimeUtil.getCurrentlyTime()+ ".zip";
   
     //模版文件
        String stencilFileName = tempFileFolderName+File.separator+queryPageVO.getTitle()+TimeUtil.getCurrentlyTimeByMillSecond()+"." + exportFileVO.getExtendName();
       
        File fileStencil = new File(stencilFileName);
       
        String needDrawFileName = tempFileFolderName+File.separator+queryPageVO.getTitle()+TimeUtil.getCurrentlyTime()+"." + exportFileVO.getExtendName();
       
        File needDrawFile = new File(needDrawFileName);
       
        OutputStream os = null;
       
        try {
       
        os = new FileOutputStream(needDrawFile);
       
        list.add(needDrawFileName);
       
        boolean isBeyond = false;
       
        if(result != null && exportFileVO.getTotalNum() > maxRecordOnePage){
         isBeyond = true;                            
        }
       
        int dimensionNum;
       
        if(!isBeyond) {
         
        dimensionNum = drawExcelStencilFile(queryPageVO,fileStencil,1);
          
        drawMainTitleContent(dimensionNum , fileStencil,
          os,queryPageVO,1);
         
        } else {
         
          int sheetNum;
          
          if(result.size()%65000 != 0) {
           sheetNum = result.size()/maxRecordOnePage + 1;
          } else {
           sheetNum = result.size()/maxRecordOnePage;
          }
          
          dimensionNum = drawExcelStencilFile(queryPageVO,fileStencil,sheetNum);
          
         drawMainTitleContent(dimensionNum , fileStencil,
           os,queryPageVO,sheetNum);
          
        }
       
         ZipUtils.gerZipFilesBySpeed(list, tempFileZipName,exportFileVO.getZipCompressLevel());
        } catch (IOException ex) {
         ex.printStackTrace();
            LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, ex);
            throw new FrameException(EXCEL_EXPORT_ERROR, "Excel文件操作错误" + ex.getMessage());
        }  finally{
         if(os!= null) {
                try {
      os.flush();
      os.close();
    } catch (IOException e) {
     e.printStackTrace();
              LogWriter.appendLog(this.getClass().getName(),LogConstant.LOG_LEVEL_ERROR, e);
              throw new FrameException(EXCEL_EXPORT_ERROR, "Excel文件操作错误" + e.getMessage());
         }
              
            }
        }
       
        return tempFileZipName;
    }

    private void excelJoin(int row, List joinList, WritableSheet sheet,
                           Cell cell) throws WriteException {
        //记录哪一行合并了
        List markList1 = new ArrayList();
        List markList2 = new ArrayList();
        //是否第一列
        Iterator itor = joinList.iterator();
        String thisData = "";
        String preData = null;
        int totalRow = sheet.getRows();
        int cur = 0;
        int joinIndex = 0;
        //上次合并的行位
        int lastJoin = row - 2;
        while(itor.hasNext()){
            joinIndex = ((Integer) itor.next()).intValue();
            cur = row - 1;
            for(; cur < totalRow; ++cur){
                cell = sheet.getCell(joinIndex, cur);
                thisData = cell.getContents();
                if (!thisData.equals(preData)) {
                    if (cur - lastJoin > 1) {
                        sheet.mergeCells(joinIndex, lastJoin, joinIndex, cur - 1);
                    }
                    lastJoin = cur;
                    markList2.add(new Integer(cur));
                }else{
                    if(isJoin(markList1,cur)){
                        if(cur - lastJoin > 1){
                            sheet.mergeCells(joinIndex, lastJoin, joinIndex,
                                             cur - 1);
                            lastJoin = cur;
                        }else{
                            lastJoin = cur;
                        }
                        markList2.add(new Integer(cur));
                    }
                }
                preData = new String(thisData);
            }
            //最后一次合并
            if (cur - lastJoin > 1) {
                sheet.mergeCells(joinIndex, lastJoin, joinIndex, cur - 1);
            }
            markList1.clear();
            markList1.addAll(markList2);
            markList2.clear();
        }
    }

    /**
     * formatString
     *
     * @param percentList List
     * @param string String
     * @param index int
     * @return String
     */
    private String formatString(List percentList, String s, int index) {
        if(s != null){
            if(!s.equals(""))
                ;
            else
                s = "-";
        }else{
            s = "-";
        }
        if(isPercent(percentList, index) && !s.startsWith("-") && !s.startsWith("无")){
            if(s.startsWith("."))
                s = "0" + s;
            if(s!= null)
            s = s + "%";
        }
        return s;

    }

    private boolean isJoin(List join, int i) {
        if(join.isEmpty())
            return false;
        if(join.contains(new Integer(i)))
            return true;
        return false;
    }

    private boolean isPercent(List percentList, int i) {
        if(percentList.contains(new Integer(i)))
            return true;
        return false;
    }
    public void setReportDisplayVO(ExcelReportDisplayVO reportDisplayVO) {
        this.reportDisplayVO = reportDisplayVO;
    }

    public void setReportDisplayVO(String stat_timeDesc,String createTime,String paraDesc) {
        this.reportDisplayVO = new ExcelReportDisplayVO();
        this.reportDisplayVO.setReportName(reportPageVO.getTitle());
        this.reportDisplayVO.setFromTime(stat_timeDesc);       
        this.reportDisplayVO.setCreateTime(createTime);
        this.reportDisplayVO.setQueryTerm(paraDesc);
    }

    public ExcelReportDisplayVO getReportDisplayVO() {
        return reportDisplayVO;
    }
           
    public static void main(String[] args) {
 
    }
   
}