waysun一路陽光

1.JSP頁面
<td width=165>
<div align="left">
<!-- 添加  2008-04-11 16:42 -->
<bean:define id="scoreId" name='Info' property='scoreId' /> 
<a href="scoreSet.do?do=importScore&scoreid=<%=scoreId%>" target="scoreexel">統計導出</a> 
<!--
<html:link action="softevaluate/scoreSet.do?do=setScore" paramName="Info" paramProperty="scoreId" paramId="scoreId">
參數設置
</html:link>
<!-- 
<a href="scoreOperation.do?do=scoreSetE&scoreid=<%=scoreId%>" target="scoreexel">統計導出</a>   
<a href="scoreOperation.do?do=scoreSetE&scoreid=<%=scoreId%>">統計導出</a>
<a href="softevaluate/scoreSet.do?do=setScore">統計導出</a>
-->
</div>
</td> 
2.ScoreSetAction.java
public class ScoreSetAction extends DispatchAction
{
         /******實現EXCEL數據導出*******/
  public ActionForward importScore(ActionMapping mapping, ActionForm form,HttpServletRequest request,HttpServletResponse response)throws Exception
  {
 
   // 導出數據類型，此處是寫死的為3。
   String scoreid = request.getParameter("scoreid");
   String []str=new String[3];
   scoreid=scoreid.replace("/", "");
   String maxscore = "";
   String minscore = "";

   ScoreSetExport scoreSetExport = new ScoreSetExport();
   LinkedHashMap hm = new LinkedHashMap();
   SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
   org.hibernate.classic.Session session = sessionFactory.openSession();
   Query query =session.createQuery("from ScoreViable");
   List users=query.list();
   session.close();
   sessionFactory.close();
   for (ListIterator iterator = users.listIterator(); iterator.hasNext(); )
   {
    ScoreViable user = (ScoreViable) iterator.next();
    System.out.println(user.getBeforCount() +"\n\tAge: " + user.getEndCount());
    maxscore=user.getBeforCount()+"";
    minscore=user.getEndCount()+"";
   }
   str[0]=scoreid;
   str[1]=maxscore;
   str[2]=minscore;
   List<ScoreRecordDetail> list = scoreSetExport.exportScoreSet(str,hm);
   ExcelManagerIf eem = ExcelManagerFactory.getInstance();
   /******************************************************************/
   /**不去掉一行仍然調用export,去掉一行調用export1,只針對一種匯總導出*/
   //eem.export(response, list,hm);
   eem.export1(response, list,hm);
   /******************************************************************/
   return null;
  }
}
3.ExcelManagerIf.java
import java.io.InputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import com.gpdi.orm.object.ScoreRecordDetail;

public interface ExcelManagerIf
{
 
 /********************************************************************************************************/
 /** 2008-04-18 11:54*/
 public boolean export1(HttpServletResponse response, List<ScoreRecordDetail> list,LinkedHashMap hashMap);
 /********************************************************************************************************/
 
}
4.ExcelManagerFactory.java
public class ExcelManagerFactory
 {
   private static String key = "key";
   private ExcelManagerFactory() {
   }
  
   private static ExcelManagerIf instance = null;
   public static ExcelManagerIf getInstance(){
    if(instance == null){
             synchronized(key){
                 if(instance == null){
                     instance = new ExcelManagerImpl();
                 }
             }
         }
         return instance;
   }
}
5.ExcelManagerImpl.java
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.hibernate.Query;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;

public class ExcelManagerImpl implements ExcelManagerIf
{
         /***************************************************************************************************************/
/**2008-04-18 11:36   匯總導出去掉無用的空格*/
 public boolean export1(HttpServletResponse response, List<ScoreRecordDetail> list,LinkedHashMap hashMap) {
  Session session = null;
  try {
   // 生成電子表格文件
   HSSFWorkbook wb = new HSSFWorkbook();
   // 通過先建立表格，后命名可以定義使用的編碼，從而保證漢字的正常顯示
   HSSFSheet sheet1 = wb.createSheet();
   /********************************************/
   /**添加 2008-04-15導出打印默認方式,true：橫向     false：縱向*/
   sheet1.getPrintSetup().setLandscape(true);  
   /********************************************/
   sheet1.setColumnWidth((short) 0, (short) 5000);
   sheet1.setColumnWidth((short) 1, (short) 5000);
   sheet1.setColumnWidth((short) 2, (short) 5000);
   sheet1.setColumnWidth((short) 3, (short) 5000);
   // wb.setSheetName(0, "開發商資料",HSSFWorkbook.ENCODING_UTF_16);

   // HSSFRow row;

   // 先定義一個字體對象
   HSSFFont font = wb.createFont();
   font.setFontName("宋體");
   font.setFontHeightInPoints((short) 16);// 字體大小
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗

   // 定義表頭單元格格式
   HSSFCellStyle cellStyle = wb.createCellStyle();
   cellStyle.setFont(font);// 單元格字體
   cellStyle.setBorderBottom(cellStyle.BORDER_THIN);// 單元格邊框
   cellStyle.setBorderTop(cellStyle.BORDER_THIN);
   cellStyle.setBorderRight(cellStyle.BORDER_THIN);
   cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
   cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平對齊方式

   // 定義新的調色板以便定義自己的顏色
   HSSFPalette palette = wb.getCustomPalette();
   // 替換一個默認調色板中已經定義的顏色
   palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 230, (byte) 230, (byte) 255);
   // 使用剛才定義的顏色為填充色
   cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
   cellStyle.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);

   // 寫入表頭
   HSSFCell c;

   // String[] headerName = (String[]) aHeaderName.get(type);
   int[] maxLen = new int[4];
   int maxCol = list.size() + 3;
   ScoreRecordDetail first = list.get(0);
   Long scoreId = first.getScoreRecord().getScoreid();

   session = HibernateSessionFactory.getCurrentSession();

   ScoreSet scoreSet = (ScoreSet) session.createQuery("from ScoreSet where scoreid = :scoreid").setLong("scoreid",
     scoreId).uniqueResult();
   String titleName = scoreSet.getScoreLot().getLotName() + "-" + scoreSet.getScoreModel().getScoremodelname();
   //輸出文件名落孫山
   String outFileName = scoreSet.getScoreModel().getScoremodelname();

   // 在表單中新建0行
   HSSFRow row0 = sheet1.createRow((short) 0);
   row0.setHeight((short) 600);
   c = row0.createCell((short) 0);
   HSSFCellStyle cellStyle0 = wb.createCellStyle();
   cellStyle0.setFont(font);// 單元格字體0
   cellStyle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平對齊方式
   cellStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

   cellStyle0.setBorderBottom(cellStyle.BORDER_THIN);// 單元格邊框
   cellStyle0.setBorderTop(cellStyle.BORDER_THIN);
   cellStyle0.setBorderRight(cellStyle.BORDER_THIN);
   cellStyle0.setBorderLeft(cellStyle.BORDER_THIN);

   c.setCellStyle(cellStyle0);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue(titleName);// 單元格值
   sheet1.addMergedRegion(new Region(0, (short) 0, 0, (short) (maxCol-1)));

   // 在表單中新建1行
   /**
   HSSFRow row1 = sheet1.createRow((short) 1);
   row1.setHeight((short) 700);
   c = row1.createCell((short) 0);
   HSSFCellStyle cellStyle1 = wb.createCellStyle();
   cellStyle1.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平對齊方式
   cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

   cellStyle1.setBorderBottom(cellStyle.BORDER_THIN);// 單元格邊框
   cellStyle1.setBorderTop(cellStyle.BORDER_THIN);
   cellStyle1.setBorderRight(cellStyle.BORDER_THIN);
   cellStyle1.setBorderLeft(cellStyle.BORDER_THIN);

   c.setCellStyle(cellStyle1);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue("評標專家簽字：");// 單元格值

   c = row1.createCell((short) 1);
   c.setCellStyle(cellStyle1);// 單元格的樣式

   c = row1.createCell((short) 2);
   c.setCellStyle(cellStyle1);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue("日期：");// 單元格值

   c = row1.createCell((short) 3);
   c.setCellStyle(cellStyle1);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
   c.setCellValue(sdf.format(new Date()));// 單元格值
            */
   // 在表單中新建2行
   HSSFRow row2 = sheet1.createRow((short) 1);
   row2.setHeight((short) 600);

   HSSFCellStyle cellStyle2 = wb.createCellStyle();
   cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平對齊方式
   cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   cellStyle2.setFillBackgroundColor(HSSFColor.AQUA.index);

   cellStyle2.setBorderBottom(cellStyle.BORDER_THIN);// 單元格邊框
   cellStyle2.setBorderTop(cellStyle.BORDER_THIN);
   cellStyle2.setBorderRight(cellStyle.BORDER_THIN);
   cellStyle2.setBorderLeft(cellStyle.BORDER_THIN);

   
   
   c = row2.createCell((short) 0);
   c.setCellStyle(cellStyle2);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue("專家名稱");// 單元格值
   /*****************************************************************/
   sheet1.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));
   //sheet1.addMergedRegion(new Region(2, (short) 0, 2, (short) 1));
   /*****************************************************************/

   row2.setHeight((short) 400);
   c = row2.createCell((short) 1);
   c.setCellStyle(cellStyle2);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   //c.setCellValue("項目");// 單元格值

   row2.setHeight((short) 400);
   c = row2.createCell((short) 2);
   c.setCellStyle(cellStyle2);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue("分值");// 單元格值

   //c = row2.createCell((short) 3);
   //c.setCellStyle(cellStyle2);// 單元格的樣式
   
   //重復行
   int rows=0;
   if(hashMap!=null){
    int j=0;
    
    HSSFRow[]  row_repeat= null;
    for (Iterator iterator = hashMap.values().iterator(); iterator.hasNext();) {
     Map map= (LinkedHashMap) iterator.next();     
     rows =map.size();
     row_repeat=new HSSFRow[map.size()];
     for(int i=0;i<rows;i++){
      /****************************************************/
      row_repeat[i] = sheet1.createRow((short) (2+i));
      //row_repeat[i] = sheet1.createRow((short) (3+i));
      /****************************************************/
      row_repeat[i].setHeight((short) 400);      
     }
     break;
    }
    
    for (Iterator iterator = hashMap.values().iterator(); iterator.hasNext();) {
     Map map= (LinkedHashMap) iterator.next();     
     int i=0;
     for (Iterator iter_key = map.keySet().iterator(); iter_key.hasNext();) {
      SysUser sysUser = (SysUser) iter_key.next();
      if(j==0){
       c = row_repeat[i].createCell((short) 0);
       c.setCellStyle(cellStyle2);// 單元格的樣式
       c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
       c.setCellValue(sysUser.getUsername());// 單元格值
       /******************************************************************/
       sheet1.addMergedRegion(new Region(2+i, (short) 0, 2+i, (short) 1));
       //sheet1.addMergedRegion(new Region(3+i, (short) 0, 3+i, (short) 1)); 
       /******************************************************************/
       
       c = row_repeat[i].createCell((short) 1);
       c.setCellStyle(cellStyle2);// 單元格的樣式
  
       c = row_repeat[i].createCell((short) 2);
       c.setCellStyle(cellStyle2);// 單元格的樣式
       c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
       c.setCellValue("40");// 單元格值
      }
      
      c = row_repeat[i].createCell((short) (3+j));      
      c.setCellStyle(cellStyle2);// 單元格的樣式
      c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//數字型
      c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
      
      c.setCellValue(Double.parseDouble(map.get(sysUser).toString()));// 單元格值      
      i++;
     }
     
     j++;
    }
   }

   // 在表單中新建3行
   /************************************************/
   HSSFRow row3 = sheet1.createRow((short) (2+rows));
   //HSSFRow row3 = sheet1.createRow((short) (3+rows));
   /************************************************/
   row3.setHeight((short) 400);

   c = row3.createCell((short) 0);
   c.setCellStyle(cellStyle2);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue(scoreSet.getScoreModel().getScoremodelname() + " 評分");// 單元格值
   /******************************************************************************/
   //sheet1.addMergedRegion(new Region((3+rows), (short) 0,(3+rows), (short) 1));
   sheet1.addMergedRegion(new Region((2+rows), (short) 0,(2+rows), (short) 1));
   /******************************************************************************/

   c = row3.createCell((short) 1);
   c.setCellStyle(cellStyle2);// 單元格的樣式

   c = row3.createCell((short) 2);
   c.setCellStyle(cellStyle2);// 單元格的樣式
   c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
   c.setCellValue("40");// 單元格值

   for (int i = 0; i < list.size(); i++) {

    sheet1.setColumnWidth((short) (i + 3), (short) 5000);

    ScoreRecordDetail o = list.get(i);
    String name = "";
    if (o.getDevelopid() != null) {
     BasDevelop basDevelop = (BasDevelop) session.createQuery("from BasDevelop where developid = :developid")
       .setLong("developid", o.getDevelopid()).uniqueResult();
     name = basDevelop.getDename();
    } else {
     ProductType productType = (ProductType) session.createQuery("from ProductType where productid = :productid")
       .setLong("productid", o.getProductid()).uniqueResult();
     name = productType.getProdname();
    }
/****************************************************************************/
    //c = row1.createCell((short) (i + 3));
    //c.setCellStyle(cellStyle1);// 單元格的樣式
/****************************************************************************/

    c = row2.createCell((short) (i + 3));
    c.setCellStyle(cellStyle2);// 單元格的樣式
    c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
    c.setCellValue(name);// 單元格值

    c = row3.createCell((short) (i + 3));
    c.setCellStyle(cellStyle2);// 單元格的樣式
    c.setEncoding(HSSFCell.ENCODING_UTF_16);// 編碼
    c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//數字型
    c.setCellValue(o.getScore());// 單元格值
   }

   // 輸出Excel文件
   errorOrExportExcel(response, wb, 2 ,outFileName);