数据库表导出成Excel工具类

package com.chinasofti.articlesys.util;


import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

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.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHeader;
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.ss.usermodel.HorizontalAlignment;
import org.apache.struts2.ServletActionContext;

import com.chinasofti.articlesys.bean.ArchiveBean;
import com.chinasofti.articlesys.bean.ArchiveBean;
import com.chinasofti.articlesys.service.ArchiveTypeService;




public class ExportUtilForQuery {
    
    
    public static void export(List list)throws Exception
    {  
    if(list==null){
    return;
    }

    
    /*HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("档案表");
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short)15);
        // 创建字体对象   
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont fOnt= wb.createFont();
        font.setFontHeightInPoints((short)12);
//       font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
       font.setBold(true);
       
        style.setAlignment(HorizontalAlignment.CENTER); // 居中
        style.setFont(font);       
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow(0);
        
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("档案编号");
        cell.setCellStyle(style);      
        cell=row.createCell(1);
        cell.setCellValue("档案标题");
        cell.setCellStyle(style);
        cell=row.createCell(2);
        cell.setCellValue("档案内容");
        cell.setCellStyle(style);
        cell=row.createCell(3);
        cell.setCellValue("创建人");        
        cell.setCellStyle(style);
        
        for (int i = 0; i         {  
            row = sheet.createRow(i+1);  
            ArchiveBean bean =list.get(i);  
            // 第四步，创建单元格，并设置值  
            row.createCell(0).setCellValue(bean.getArchiveNo());  
            row.createCell(1).setCellValue(bean.getTitle());  
            row.createCell(2).setCellValue(bean.getContent());  
            row.createCell(3).setCellValue(bean.getCreater().getName()); 
        }  */
    
     String []tableHeader={"档案编号","档案标题","档案内容","创建人"};
     /*
                  *下面的都可以拷贝不用编写
                  */
     short cellNumber=(short)tableHeader.length;//表的列数
     HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个excel
     HSSFCell cell = null; //Excel的列
     HSSFRow row = null; //Excel的行
     HSSFCellStyle style = workbook.createCellStyle(); //设置表头的类型
//     style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style.setAlignment(HorizontalAlignment.CENTER);
     HSSFCellStyle style1 = workbook.createCellStyle(); //设置数据类型
//     style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     style1.setAlignment(HorizontalAlignment.CENTER);
     HSSFFont fOnt= workbook.createFont(); //设置字体
     HSSFSheet sheet = workbook.createSheet("sheet1"); //创建一个sheet
     HSSFHeader header = sheet.getHeader();//设置sheet的头
     try {              /**
                          *根据是否取出数据，设置header信息
                          *
                          */
     if(list.size() <1 ){
     header.setCenter("查无资料");
     }else{
     header.setCenter("档案表");
     row = sheet.createRow(0);
     row.setHeight((short)400);
     for(int k = 0;k      cell = row.createCell(k);//创建第0行第k列
     cell.setCellValue(tableHeader[k]);//设置第0行第k列的值
     sheet.setColumnWidth(k,8000);//设置列的宽度
     font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
     font.setFontHeight((short)350); //设置单元字体高度
     style1.setFont(font);//设置字体风格
     cell.setCellStyle(style1);
     }
                             /*
                              * // 给excel填充数据这里需要编写
                              *    
                              */
     for(int i = 0 ;i       ArchiveBean beanType = list.get(i);//获取sign对象
         row = sheet.createRow((short) (i + 1));//创建第i+1行
         row.setHeight((short)400);//设置行高
        
         if(beanType.getArchiveNo()!= null){
         cell = row.createCell(0);//创建第i+1行第0列
         cell.setCellValue(beanType.getArchiveNo());//设置第i+1行第0列的值
     cell.setCellStyle(style);//设置风格
         }
         if(beanType.getTitle() != null){
         cell = row.createCell(1); //创建第i+1行第1列

         cell.setCellValue(beanType.getTitle());//设置第i+1行第1列的值

         cell.setCellStyle(style); //设置风格
         }
     //由于下面的和上面的基本相同，就不加注释了
         if(beanType.getContent() != null){
         cell = row.createCell(2);
         cell.setCellValue(beanType.getContent());
         cell.setCellStyle(style);
         }
         if(beanType.getCreater()!= null){
         cell = row.createCell(3);
         cell.setCellValue(""+beanType.getCreater());
         cell.setCellStyle(style);
         }
         /*if(sign.getSclass() != null){
         cell = row.createCell(4);
         cell.setCellValue(sign.getSclass());
         cell.setCellStyle(style);
         }
         if(sign.getMdate()!= null){
             cell = row.createCell(5);
             cell.setCellValue(sign.getMdate());
             cell.setCellStyle(style);
             }*/
        
     }

     }

     } catch (Exception e) {
     e.printStackTrace();
     }
    
    
        try  
        {   HttpServletResponse respOnse=null;
        OutputStream out = null;//创建一个输出流对象
    try {
    respOnse= ServletActionContext.getResponse();//初始化HttpServletResponse对象
    out = response.getOutputStream();//
            response.setHeader("Content-disposition","attachment; filename="+"SearchResult"+".xls");//filename是下载的xls的名，建议最好用英文
            response.setContentType("application/msexcel;charset=UTF-8");//设置类型
            response.setHeader("Pragma","No-cache");//设置头
            response.setHeader("Cache-Control","no-cache");//设置头
            response.setDateHeader("Expires", 0);//设置日期头
            workbook.write(out);
            out.flush();
       
    } catch (IOException e) {
    e.printStackTrace();
    }finally{
    try{

    if(out!=null){
    out.close();
    }

    }catch(IOException e){
    e.printStackTrace();
    }

    }
            
        }  
        catch (Exception e)  
        {  
            e.printStackTrace(); 
            
        } 
        
        
        
        
//        return null;
    } 
}