poi 导出excel

工具类：

import java.lang.reflect.Field; 
import java.lang.reflect.Method; 
import java.text.SimpleDateFormat; 
import java.util.Collection; 
import java.util.Date; 
import java.util.Iterator; 
import java.util.regex.Matcher; 
import java.util.regex.Pattern; 

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
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.CellRangeAddressList;

/**
* 
* @author wanght
* @version v1.0
* @param <T>
*  应用泛型，代表任意一个符合javabean风格的类
*  注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
*/ 
@SuppressWarnings("deprecation")
public class ExportExcel<T>  { 
    public HSSFWorkbook exportExcel(HSSFWorkbook workbook, int sheetNum,String title,String[] headers, Collection<T> dataset){ 
        return exportExcel(workbook, sheetNum,title, headers, dataset,"yyyy-MM-dd"); 
    }
 
    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */ 
    public HSSFWorkbook exportExcel(HSSFWorkbook workbook, int sheetNum,String title, String[] headers, 
            Collection<T> dataset,String pattern){ 
       
        // 生成一个表格 
        HSSFSheet sheet = workbook.createSheet(); 
        workbook.setSheetName(sheetNum,title);
        // 设置表格默认列宽度为15个字节 
        sheet.setDefaultColumnWidth((short) 15); 
        // 产生表格标题行 
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        for (short i = 0; i < headers.length; i++){ 
            cell = row.createCell(i);
          //隐藏id
            sheet.setColumnHidden(2,true);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]); 
            cell.setCellValue(text);
        } 
        // 生成下拉列表 
        // 对第一列单元格有效 
        CellRangeAddressList regions = new CellRangeAddressList(1, dataset.size(), 0, 0); 
        // 生成下拉框内容 
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(new String[] { "通过", "不通过" ,""}); 
        // 绑定下拉框和作用区域 
        HSSFDataValidation data_validation = new HSSFDataValidation(regions,constraint); 
        // 对sheet页生效 
        sheet.addValidationData(data_validation);
        // 对第二列单元格有效 
        CellRangeAddressList regions1 = new CellRangeAddressList(1, dataset.size(), 1, 1); 
        // 生成下拉框内容 
        DVConstraint constraint1 = DVConstraint.createExplicitListConstraint(new String[] { "突变经验证后正常", "纯杂合验证前后不一致" ,"二代分析成多碱基替换突变的实际可能为2个SNP","基因组新旧模板不一致导致多下了一代验证任务","基因有重复序列或相似基因而找不到特异引物导致验证失败","序列内部有特殊结构导致正反向测序都不全"}); 
        // 绑定下拉框和作用区域 
        HSSFDataValidation data_validation1 = new HSSFDataValidation(regions1,constraint1); 
        // 对sheet页生效 
        sheet.addValidationData(data_validation1);
        // 遍历集合数据，产生数据行 
        Iterator<T> it = dataset.iterator(); 
        int index = 0; 
        while (it.hasNext()){ 
            index++; 
            row = sheet.createRow(index); 
            T t = (T) it.next(); 
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            //特别注意：javabean属性的先后顺序,否则会发生数据行错乱,与标题头不对应
            Field[] fields = t.getClass().getDeclaredFields(); 
            for (short i = 0; i < fields.length-2; i++){ 
                cell = row.createCell(i); 
                Field field = fields[i]; 
                String fieldName = field.getName(); 
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); 
                try{ 
                    Class<? extends Object> tCls = t.getClass(); 
                    Method getMethod = tCls.getMethod(getMethodName,new Class[]{}); 
                    Object value = getMethod.invoke(t, new Object[]{});
                    String textValue = null; 
                    // 判断值的类型后进行强制类型转换 
                    if(null != value){
                        if (value instanceof Date){ 
                            Date date = (Date) value; 
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern); 
                            textValue = sdf.format(date); 
                        }else{ 
                            // 其它数据类型都当作字符串简单处理 
                            textValue = value.toString();
                          //判断状态
                            if(getMethodName.equals("getVerifyResult")){
                                if("1".equals(textValue)){
                                    textValue = "通过";
                                }else if("2".equals(textValue)){
                                    textValue = "不通过";
                                }else{
                                    textValue = "";
                                }
                            }
                        } 
                    }
                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成 
                    if (textValue != null){ 
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$"); 
                        Matcher matcher = p.matcher(textValue); 
                        if (matcher.matches()){ 
                            // 是数字当作double处理 
                            cell.setCellValue(Double.parseDouble(textValue)); 
                        }else{ 
                            HSSFRichTextString richString = new HSSFRichTextString(textValue); 
                            cell.setCellValue(richString); 
                        } 
                    } 
                }catch (Exception e){ 
                    e.printStackTrace(); 
                }
            } 
        }
        return workbook;
    }

action类：

    private String fileName; // 下载文件名称
    private InputStream excelFile; // 下载文件流
    private FileInputStream inputStream;

//这三个实体必须给出set，get 方法

public String exportMutation(){
        // 声明一个工作薄 
        HSSFWorkbook workbook = new HSSFWorkbook();
        List<Mutation>  lists=new ArrayList<Mutation>();
            lists=mutationService.exportMutation();
                if(null != lists && lists.size() > 0){
                    ExportExcel<Mutation> ex = new ExportExcel<Mutation>(); 
                    String[] headers = {"验证结果","验证结果说明"}; 
                    try {
                        workbook = ex.exportExcel(workbook,0,"sheet1",headers, lists);
                        ByteArrayOutputStream output = new ByteArrayOutputStream();
                        workbook.write(output);
                        byte[] ba = output.toByteArray();
                        excelFile = new ByteArrayInputStream(ba);
                        output.flush();
                        output.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    } 
                }
        }

xml配置：

<action name="exportExcel" class="com.deyidf.lab.action.order.MutationSearchAction" method="exportMutation">
            <result name="success" type="stream"> 
               <param name="contentType">application/vnd.ms-excel,charset=ISO8859-1</param>
               <param name="contentDisposition">attachment;filename="${fileName}"</param> 
               <param name="bufferSize">8192</param>
               <param name="inputName">excelFile</param> 
            </result>
        </action>