從數據庫導出數據到excel之POI操做

import java.io.BufferedOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;

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.HSSFRichTextString;
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;

public class ExportDateTest implements Serializable{
    private String name;
    //private String gender;//性別
    private String weight;
    //private String grades;//班級
    private Double Networkprotocol;
    private Double javaEE;
    private Double Computerbasis;
    private Double Linuxoperatingsystem;
    private Double networksecurity;
    private Double SQLdatabase;
    private Double datastructure;
    public ExportDateTest() {
    
        // TODO Auto-generated constructor stub
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    /*
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    */
    public String getWeight() {
        return weight;
    }
    public void setWeight(String weight) {
        this.weight = weight;
    }
    /*
    public String getGrades() {
        return grades;
    }
    public void setGrades(String grades) {
        this.grades = grades;
    }
    */
    public Double getNetworkprotocol() {
        return Networkprotocol;
    }
    public void setNetworkprotocol(Double networkprotocol) {
        Networkprotocol = networkprotocol;
    }
    public Double getJavaEE() {
        return javaEE;
    }
    public void setJavaEE(Double javaEE) {
        this.javaEE = javaEE;
    }
    public Double getComputerbasis() {
        return Computerbasis;
    }
    public void setComputerbasis(Double computerbasis) {
        Computerbasis = computerbasis;
    }
    public Double getLinuxoperatingsystem() {
        return Linuxoperatingsystem;
    }
    public void setLinuxoperatingsystem(Double linuxoperatingsystem) {
        Linuxoperatingsystem = linuxoperatingsystem;
    }
    public Double getNetworksecurity() {
        return networksecurity;
    }
    public void setNetworksecurity(Double networksecurity) {
        this.networksecurity = networksecurity;
    }
    public Double getSQLdatabase() {
        return SQLdatabase;
    }
    public void setSQLdatabase(Double sQLdatabase) {
        SQLdatabase = sQLdatabase;
    }
    public Double getDatastructure() {
        return datastructure;
    }
    public void setDatastructure(Double datastructure) {
        this.datastructure = datastructure;
    }
    public ExportDateTest(String name, String gender, String weight, String grades, Double networkprotocol, Double javaEE,
            Double computerbasis, Double linuxoperatingsystem, Double networksecurity, Double sQLdatabase,
            Double datastructure) {
        super();
        this.name = name;
        //this.gender = gender;
        this.weight = weight;
        //this.grades = grades;
        Networkprotocol = networkprotocol;
        this.javaEE = javaEE;
        Computerbasis = computerbasis;
        Linuxoperatingsystem = linuxoperatingsystem;
        this.networksecurity = networksecurity;
        SQLdatabase = sQLdatabase;
        this.datastructure = datastructure;
    }
    @Override
    public String toString() {
        return "ExportDate [name=" + name + ""
                //+ ", gender=" + gender + ""
                + ", weight=" + weight + ""
            //    + ", grades=" + grades
                + ", Networkprotocol=" + Networkprotocol + ", javaEE=" + javaEE + ", Computerbasis=" + Computerbasis
                + ", Linuxoperatingsystem=" + Linuxoperatingsystem + ", networksecurity=" + networksecurity
                + ", SQLdatabase=" + SQLdatabase + ", datastructure=" + datastructure + "]";
    }
    

    

}

List<ExportDate> list=expot.GetStudentTest(gender.getGender());// 
System.out.println("listDate:"+list); 
//ExportExcelXSSF<ExportDate> ee= new ExportExcelXSSF<ExportDate>();
 ExportExcelHSSF<ExportDate> ee= new ExportExcelXSSF<ExportDate>();
 ExportExcelOutputStream ee=new ExportExcelOutputStream(); 

//String[] headers = { "姓名", "性別", "體重","班級","網絡協議","javaEE","計算機基礎","Linux操做系統","網絡安全","sql數據庫","數據結構" }; 
String[] headers = { "姓名","體重","網絡協議","javaEE","計算機基礎","Linux操做系統","網絡安全","sql數據庫","數據結構" }; 
String fileName = "信息表"; System.out.println(); 
ee.exportExcel(list, headers,fileName, response);

public class ExportExcelHSSFTest<T> {
     public void exportExcel(String[] headers,Collection<T> dataset, String fileName,HttpServletResponse response) {
            // 聲明一個工做薄  
            HSSFWorkbook workbook = new HSSFWorkbook();  
            // 生成一個表格  
            HSSFSheet sheet = workbook.createSheet(fileName);
            //樣式對象
            HSSFCellStyle style=workbook.createCellStyle();                            
            // 設置表格默認列寬度爲15個字節  
            sheet.setDefaultColumnWidth(15);  
            // 產生表格標題行  
            HSSFRow row = sheet.createRow(0);  //設置行高            
                row.setHeightInPoints(30);//設置行高    
            for (int i = 0; i < headers.length; i++) {                         
                HSSFCell cell=row.createCell(i);
                //設置背景
                style.setFillBackgroundColor((short)13);
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                //設置字體
                HSSFFont font2 = workbook.createFont();    
                font2.setFontName("仿宋_GB2312");    
                font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗體顯示    
                font2.setFontHeightInPoints((short) 12);  //字體大小              
                font2.setColor(HSSFColor.RED.index);//設置字體顏色
                style.setFont(font2);//選擇須要用到的字體格式                               
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellStyle(style);
                cell.setCellValue(text);               
            }  
            try {  
                // 遍歷集合數據，產生數據行  
                Iterator<T> it = dataset.iterator();  
                int index = 0;  
                while (it.hasNext()) {  
                    index++;  
                    row = sheet.createRow(index);  
                    T t = (T) it.next();  
                    // 利用反射，根據javabean屬性的前後順序，動態調用getXxx()方法獲得屬性值  
                    Field[] fields = t.getClass().getDeclaredFields();                    
                    for (int i = 0; i < headers.length; i++) {                          
                        HSSFCell cell = row.createCell(i);  
                        Field field = fields[i];                      
                        String fieldName = field.getName();                          
                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
                        Class tCls = t.getClass();  
                        Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
                        Object value = getMethod.invoke(t, new Object[] {});  
                        // 判斷值的類型後進行強制類型轉換  
                        String textValue = null;  
                        // 其它數據類型都看成字符串簡單處理  
                        if(value != null && value != ""){  
                            textValue = value.toString();  
                        }  
                        if (textValue != null) {  
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);  
                            cell.setCellValue(richString);  
                        }  
                    }  
                }  
                getExportedFile(workbook, fileName,response);  
            } catch (Exception e) {  
                e.printStackTrace();  
            }   
        }            
        /** 
         *  
         * 方法說明: 指定路徑下生成EXCEL文件 
         * @return 
         */  
        public void getExportedFile(HSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {  
            System.out.println("name:"+name);
            BufferedOutputStream fos = null;  
            try {  
                String fileName = name + ".xls";  
                response.setContentType("application/x-msdownload");  
                response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));  
                fos = new BufferedOutputStream(response.getOutputStream());  
                workbook.write(fos);  
            } catch (Exception e) {  
                e.printStackTrace();  
            } finally {  
                if (fos != null) {  
                    System.out.println("ok");
                    fos.close();  
                }  
            }  
        }  
      
    }