ExportExcel

package com.wyj.excel.test;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Cell;

import com.wyj.annotation.ExcelAnnotation;
import com.wyj.bo.InsuranceCosts;
import com.wyj.bo.Statistics;
import com.wyj.utils.StringUtils;

public class ExportExcel<T>
{
    SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // 格式化日期

    /**
     * @param title
     *            标题
     * @param dataset
     *            集合
     * @param out
     *            输出流
     */
    public void exportExcel( String title, Collection<T> dataset,
            OutputStream out )
    {
        SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); // 格式化日期
        // 声明一个工作薄
        try
        {
            // 首先检查数据看是否是正确的
            Iterator<T> its = dataset.iterator();
            if ( dataset == null || !its.hasNext() || title == null
                    || out == null )
            {
                throw new Exception( "传入的数据不对！" );
            }
            // 取得实际泛型类
            T ts = (T)its.next();
            Class tCls = ts.getClass();
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet( title );
            // 设置表格默认列宽度为20个字节
            sheet.setDefaultColumnWidth( 20 );
            // 生成一个样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置标题样式
            style = ExcelStyle.setHeadStyle( workbook, style );

            // 得到所有字段

            Field filed[] = ts.getClass().getDeclaredFields();
            // 标题
            List<String> exportfieldtile = new ArrayList<String>();
            // 导出的字段的get方法
            List<Method> methodObj = new ArrayList<Method>();
            // 遍历整个filed
            for ( int i = 0; i < filed.length; i++ )
            {
                Field f = filed[i];
                f.isAnnotationPresent( ExcelAnnotation.class );

                ExcelAnnotation excelAnnotation = f
                        .getAnnotation( ExcelAnnotation.class );

                // 如果设置了annottion
                if ( excelAnnotation != null )
                {
                    String exprot = excelAnnotation.exportName();
                    // 添加到标题
                    exportfieldtile.add( exprot );
                    // 添加到需要导出的字段的方法
                    String fieldname = f.getName();
                    String getMethodName = "get"
                            + fieldname.substring( 0, 1 ).toUpperCase()
                            + fieldname.substring( 1 );

                    Method getMethod = tCls.getMethod( getMethodName,
                            new Class[] {} );

                    methodObj.add( getMethod );
                }
            }
            // 产生表格标题行
            HSSFRow row = sheet.createRow( 0 );
            for ( int i = 0; i < exportfieldtile.size(); i++ )
            {
                HSSFCell cell = row.createCell( i );
                cell.setCellStyle( style );
                HSSFRichTextString text = new HSSFRichTextString(
                        exportfieldtile.get( i ) );
                cell.setCellValue( text );
            }

            int index = 0;

            // 循环整个集合
            its = dataset.iterator();
            while ( its.hasNext() )
            {
                // 从第二行开始写，第一行是标题
                index++;
                row = sheet.createRow( index );
                T t = (T)its.next();
                for ( int k = 0; k < methodObj.size(); k++ )
                {
                    HSSFCell cell = row.createCell( k );
                    Method getMethod = methodObj.get( k );
                    Object value = getMethod.invoke( t, new Object[] {} );
                    cell = (HSSFCell)getValue( value, cell );
                }

            }
            workbook.write( out );
        } catch ( Exception e )
        {
            e.printStackTrace();
        }

    }

    @SuppressWarnings( {"static-access"} )
    private Cell getValue( Object value, Cell cell ) throws ParseException
    {
        String textValue = "";

        if ( value instanceof Integer )
        {
            int intValue = (Integer)value;
            cell.setCellValue( intValue );
        } else if ( value instanceof Float )
        {
            float fValue = (Float)value;
            cell.setCellValue( fValue );
        } else if ( value instanceof Double )
        {
            double dValue = (Double)value;
            cell.setCellValue( dValue );
        } else if ( value instanceof Long )
        {
            long longValue = (Long)value;
            cell.setCellValue( longValue );
        } else if ( value instanceof Date )
        {
            Date date = (Date)value;
            SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );
            textValue = sdf.format( date );
            cell.setCellValue( textValue );
        } else if ( value instanceof String )
        {
            // 其它数据类型都当作字符串简单处理
            textValue = value.toString();
            cell.setCellValue( textValue );
        } else if ( value instanceof Boolean )
        {
            boolean bValue = (Boolean)value;
            textValue = "是";
            if ( !bValue )
            {
                textValue = "否";
            }
        } else if ( value instanceof GregorianCalendar )
        {
            GregorianCalendar calendar = (GregorianCalendar)value;
            Date d = calendar.getTime();
            textValue = sdf.format( d );
        } else
        {
            textValue = "";
            cell.setCellValue( textValue );
        }
        
        return cell;
    }

    @SuppressWarnings( "unchecked" )
    public static void main( String[] args ) throws Exception
    {
        List list = queryStatistictsList();
        // 构造输出对象，可以从response输出，直接向用户提供下载
        OutputStream out = null;
        if ( StringUtils.isNotNullList( list ) )
        {
            if ( list.get( 0 ).getClass().equals( InsuranceCosts.class ) )
            {
                out = new FileOutputStream(
                        "file\\excel\\exportInsuranceCosts.xls" );
            } else if ( list.get( 0 ).getClass().equals( Statistics.class ) )
            {
                out = new FileOutputStream( "file\\excel\\exportStatistics.xls" );
            } else
            {
                out = new FileOutputStream( "file\\excel\\test.xls" );
            }
            // 开始时间
            Long l = System.currentTimeMillis();
            // 注意
            new ExportExcel().exportExcel( "测试", list, out );
            out.close();
            // 结束时间
            Long s = System.currentTimeMillis();
            System.out.println( "总共耗时：" + (s - l) );
        }
    }
    

    
    public static List<Statistics> queryStatistictsList()
    {
        // 构造一个模拟的List来测试，实际使用时，这个集合是从数据库中查出来的
        List<Statistics> list = new ArrayList<Statistics>();

        Statistics statistics = new Statistics();
        statistics.setName( "手机终端统计" );
        statistics.setType( "1" );
        statistics.setVendor( "三星" );
        statistics.setSubtype( "S201" );
        statistics.setSoftver( "安卓4.1.3" );
        statistics.setOid( "d100000000" );
        statistics.setCardId( "125123456" );
        statistics.setStatisticsTime( System.currentTimeMillis() + "" );
        statistics.setStatus( "闲置" );

        list.add( statistics );
        return list;
    }

    public static List<InsuranceCosts> queryInsuranceCostsList()
    {
        // 构造一个模拟的List来测试，实际使用时，这个集合是从数据库中查出来的
        List<InsuranceCosts> list = new ArrayList<InsuranceCosts>();

        InsuranceCosts insuranceCosts = new InsuranceCosts();
        insuranceCosts.setInsuranceId( "b007" );
        insuranceCosts.setInsuranceTypeId( "0" );
        insuranceCosts.setVehicleNo( "粤B43F96" );
        insuranceCosts.setInsuranceCompany( "深圳保险公司" );
        insuranceCosts.setInsuranceType( "车强险" );
        insuranceCosts.setContecter( "王经理" );
        insuranceCosts.setPhone( "13517474694" );
        insuranceCosts.setMoney( 12.0 );
        insuranceCosts.setUsetax( 12.0 );
        insuranceCosts.setBuyDate( StringUtils.getTextDate( "2010-09-02",
                "yyyy-MM-dd" ) );
        insuranceCosts.setEndDate( StringUtils.getTextDate( "2010-10-01",
                "yyyy-MM-dd" ) );
        list.add( insuranceCosts );
        return list;
    }
}