excel文件的读出和写入

package com.wyj.excel.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.wyj.bo.InsuranceCosts;
import com.wyj.constant.ImportConstant;
import com.wyj.utils.ImportUtils;
import com.wyj.utils.StringUtils;

/**
 * excel读取和写入 <功能详细描述>
 * 
 * @author lenovo
 * @version [版本号, Apr 14, 2013]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class ExcelImportAndExport<T>
{
 
    
    public static void main( String[] args )
    {
        File file = new File( ImportConstant.filePath2003);

        List list;
        try
        {
            String extension = getExtension( file );
            FileOutputStream os = new FileOutputStream(
                    "file\\excel\\insurance_style." + extension );
            List objectList = readExcel( file, ImportConstant.headers.length,
                    extension );
            importExcelData( objectList );
            // 文件输出流
            ExcelImportAndExport<InsuranceCosts> excelReadAndWritex = new ExcelImportAndExport<InsuranceCosts>();
            // 利用反射解析excel
            excelReadAndWritex.exportExcel( extension, "车辆保险",
                    ImportConstant.headers, null, objectList, os,
                    ImportConstant.DATE_FORMATE );
            
        } catch ( Exception e )
        {
            e.printStackTrace();
        }
    }

    /**
     * 得到文件的扩展名 <功能详细描述>
     * 
     * @param file
     *            文件
     * @return [参数说明]
     * @return String [返回类型说明]
     * @throws IOException
     * @exception throws
     *                [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    public static String getExtension( File file ) throws IOException
    {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf( "." ) == -1 ? "" : fileName
                .substring( fileName.lastIndexOf( "." ) + 1 );

        if ( !"xls".equalsIgnoreCase( extension ) && !"xlsx".equals( extension ) )

        {
            throw new IOException( "不支持的文件类型" );
        }
        return extension;
    }

    /**
     * 打印保险数据 <功能详细描述>
     * 
     * @param file
     * @param printType
     *            [参数说明]
     * @return void [返回类型说明]
     * @throws Exception
     * @exception throws
     *                [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    public static List importExcelData( List objectList ) throws Exception
    {
        for ( Iterator iterator = objectList.iterator(); iterator.hasNext(); )
        {
            InsuranceCosts insuranceCosts = (InsuranceCosts)iterator.next();
            System.out.println( insuranceCosts.getInsuranceId()
                    + ","
                    + insuranceCosts.getInsuranceTypeId()
                    + ","
                    + insuranceCosts.getVehicleNo()
                    + ","
                    + insuranceCosts.getInsuranceCompany()
                    + ","
                    + insuranceCosts.getInsuranceType()
                    + ","
                    + insuranceCosts.getContecter()
                    + ","
                    + insuranceCosts.getPhone()
                    + ","
                    + insuranceCosts.getMoney()
                    + ","
                    + insuranceCosts.getUsetax()
                    + ","
                    + StringUtils.getTextDateStr( insuranceCosts.getBuyDate(),
                            "yyyy-MM-dd" )
                    + ","
                    + StringUtils.getTextDateStr( insuranceCosts.getEndDate(),
                            "yyyy-MM-dd" ) + "," + insuranceCosts.getImg() );

        }

        return objectList;
    }

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param extension
     *            文件的扩展名
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param regions
     *            要合并的单元格
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    @SuppressWarnings( "unchecked" )
    public void exportExcel( String extension, String title,
            String[] headers, List<CellRangeAddress> regions,
            Collection<T> dataset, OutputStream out, String pattern )
    {
        // 声明一个工作薄
        Workbook workbook = null;
        if ( "xls".equals( extension ) )
        {
            workbook = new HSSFWorkbook();
        } else if ( "xlsx".equals( extension ) )
        {
            workbook = new XSSFWorkbook();
        }

        // 生成一个表格
        Sheet sheet = workbook.createSheet( title );

        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth( (short)15 );

        //设置excel标题的样式
        CellStyle headerStyle =ExcelStyle.setHeadStyle( workbook );
        
        //设置excel体部样式
        CellStyle bodyStyle =ExcelStyle.setBodyStyle( workbook );        
        
        //设置标题注释内容
        ExcelStyle.setComment( sheet, regions, extension );

        // 产生表格标题行
        Row row = sheet.createRow( 0 );
        
        row.setHeight( (short)500 );
      
        //设置excel标题的内容
        for ( short j = 0; j < headers.length; j++ )
        {
            Cell cell = row.createCell( j );
            cell.setCellStyle( headerStyle );
            RichTextString text = null;
            if ( "xls".equals( extension ) )
            {
                text = new HSSFRichTextString( headers[j] );
            } else if ( "xlsx".equals( extension ) )
            {
                text = new XSSFRichTextString( headers[j] );
            }
            cell.setCellValue( text );
            sheet.setColumnWidth( (short)j,
                    (short)(35.7 * headers[j].length() * 18 + 35.7 * 25) );
        }

        // 遍历集合数据，产生数据行
        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 ( short i = 0; i < fields.length; i++ )
            {
                Cell cell = row.createCell( i );
                cell.setCellStyle( bodyStyle );
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring( 0, 1 ).toUpperCase()
                        + fieldName.substring( 1 );
                try
                {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod( getMethodName,
                            new Class[] {} );
                    Object value = getMethod.invoke( t, new Object[] {} );
                    // 判断值的类型后进行强制类型转换
                   ImportUtils.setCellValue( value, cell );
                   
                } catch ( Exception e )
                {
                    e.printStackTrace();
                } finally
                {
                    // 清理资源
                }
            }
        }
        try
        {
            workbook.write( out );
        } catch ( IOException e )
        {
            e.printStackTrace();
        }

    }
    /**
     * 读取excel
     * @param file  文件
     * @param cellLen  列标题长度
     * @param extension 文件扩展名
     * @return 集合
     */
    public static List<InsuranceCosts> readExcel( File file, int cellLen,
            String extension )
    {
        List<InsuranceCosts> list = new ArrayList<InsuranceCosts>();
        FileInputStream is = null;

        try
        {
            is = new FileInputStream( file );
            // 构造 Workbook 对象，strPath 传入文件路径
            Workbook xwb = null;
            
            if ( "xls".equals( extension ) )
            {
                xwb = new HSSFWorkbook( is );
            } else if ( "xlsx".equals( extension ) )
            {
                xwb = new XSSFWorkbook( is );
            }

            // 读取excel表格内容
            for ( int i = 0; i < xwb.getNumberOfSheets(); i++ )
            {// 循环sheet
                Sheet childSheet = xwb.getSheetAt( i );

                List headerList = ImportUtils.getHeader( childSheet );

                ImportUtils.validateHeader( headerList, ImportConstant.headers,
                        ImportConstant.headers.length );
              
                // 得到excel的头部
                Row headerRow = childSheet.getRow( 0 );
                // 校验excel头部内容
                validateHeaderContent( headerRow );
                
                // 统计空行的行数
                int blankLineCount = ImportUtils.countBlankLine( childSheet );

                for ( int j = 1; j <= childSheet.getLastRowNum()
                        - blankLineCount; j++ )
                {// 循环该子sheet
                    // row
                    Row row = childSheet.getRow( j );

                    if ( null != row )
                    {
                        if ( cellLen == 0 )
                        {
                            cellLen = row.getLastCellNum();
                        }
                        InsuranceCosts insurance = new InsuranceCosts();
                        
                        for ( int k = 0; k < cellLen; k++ )
                        {
                            // 循环该子sheet行对应的单元格项
                            Cell cell = row.getCell( k );// 单元格
                            // 获取excel单元格的数据
                            String data = ImportUtils.getCellData( cell ) + "";
                            // 单元格的数据校验
                            validateAndPackageData( k, data, insurance );

                        }
                        list.add( insurance );
                    }// if
                }// for
            }

        } catch ( Exception e )
        {
            e.printStackTrace();
        } finally
        {
            try
            {
                if ( is != null )
                {
                    is.close();
                }
            } catch ( Exception e )
            {
            }
        }
        return list;

    }

    /**
     * 校验头部的内容是否正确 <功能详细描述>
     * 
     * @param row
     * @throws Exception
     *             [参数说明]
     * @return void [返回类型说明]
     * @exception throws
     *                [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    public static void validateHeaderContent( Row row ) throws Exception
    {
        if ( null != row )
        {
            int cellLen = row.getLastCellNum();
            DataFormatter dataFormatter = new DataFormatter();
            for ( int k = 0; k < cellLen; )
            {// 循环该子sheet行对应的单元格项
                Cell cell = row.getCell( k );// 单元格

                String data = ImportUtils.getCellData( cell ) + "";
                if ( data.trim().equals( ImportConstant.headers[k].trim() ) )
                {
                    System.out.print( ImportConstant.headers[k] + " " );
                    k++;
                } else
                {
                    System.out.println( data );
                    // 抛出异常
                    throw new Exception( "导入保单文件的标题:" + data + ",不等于模板的内容："
                            + ImportConstant.headers[k] + "，请修改后再上传" );
                }
            }
            System.out.println();
        }
    }

    /**
     * 数据的校验和封装 <功能详细描述>
     * 
     * @param k
     *            单元格的位置
     * @param data
     *            单元格的数据
     * @param insurance
     *            保险
     * @throws Exception
     *             [参数说明]
     * @return void [返回类型说明]
     * @exception throws
     *                [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    public static void validateAndPackageData( int k, String data,
            InsuranceCosts insurance ) throws Exception
    {
        switch ( k )
        {
            case 0:

                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "保单号码不能保护特殊字符" );
                }
                if ( ImportUtils.containsSpecialChar( data,
                        ImportUtils.IMPORT_SPECIAL_CHAR_REG ) )
                {
                    throw new Exception( "保单号码不能包含特殊字符" );
                }
                // 设置保险单号id的数据
                insurance.setInsuranceId( data );
                // break掉
                break;
            case 1:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "保险种类id不能为空" );
                }
                if ( !ImportUtils.isDigital( data ) )
                {
                    throw new Exception( "保险种类id必须是数字" );
                }
                // 设置保险种类id的数据
                insurance.setInsuranceTypeId( data );
                break;
            case 2:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "车牌号码不能为空" );
                }
                if ( ImportUtils.containsSpecialChar( data,
                        ImportUtils.IMPORT_SPECIAL_CHAR_REG ) )
                {
                    throw new Exception( "车牌号码不能包含特殊字符" );
                }

                // 设置车牌号码的数据
                insurance.setVehicleNo( data );
                break;
            case 3:
                if ( ImportUtils.containsSpecialChar( data,
                        ImportUtils.IMPORT_SPECIAL_CHAR_REG ) )
                {
                    throw new Exception( "保险公司不能包含特殊字符" );
                }
                // 设置保险公司的数据
                insurance.setInsuranceCompany( data );
                break;
            case 4:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "保险种类不能为空" );
                }
                if ( ImportUtils.containsSpecialChar( data,
                        ImportUtils.IMPORT_SPECIAL_CHAR_REG ) )
                {
                    throw new Exception( "保险种类不能包含特殊字符" );
                }
                // 设置保险种类的数据
                insurance.setInsuranceType( data );
                break;
            case 5:
                if ( ImportUtils.containsSpecialChar( data,
                        ImportUtils.IMPORT_SPECIAL_CHAR_REG ) )
                {
                    throw new Exception( "联系人不能包含特殊字符" );
                }
                // 设置联系人的数据
                insurance.setContecter( data );
                break;
            case 6:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "联系电话不能为空" );
                }
                if ( !ImportUtils.isInteger( data ) )
                {
                    throw new Exception( "联系电话只能是数字" );
                }
                if ( data.contains( ".00" ) )
                {
                    data = data.replace( ".00", "" );
                }
                // 设置联系电话的数据
                insurance.setPhone( data );
                break;
            case 7:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "保险金额不能为空" );
                }
                if ( !ImportUtils.isDigital( data ) )
                {
                    throw new Exception( "保险金额只能是数字" );
                }
                // 设置保险金额的数据
                insurance.setMoney( new Double( data ) );
                break;
            case 8:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "使用税不能为空" );
                }
                if ( !ImportUtils.isDigital( data ) )
                {
                    throw new Exception( "使用税只能是数字" );
                }
                // 设置使用税的数据
                insurance.setUsetax( new Double( data ) );
                break;
            case 9:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "购置日期不能为空" );
                }
                // 设置购置日期的数据
                insurance.setBuyDate( StringUtils.getTextDate( data,
                        "yyyy-MM-dd" ) );
                break;
            case 10:
                if ( StringUtils.isNull( data ) )
                {
                    throw new Exception( "到期不能为空" );
                }
                // 设置到期日期的数据
                insurance.setEndDate( StringUtils.getTextDate( data,
                        "yyyy-MM-dd" ) );
                break;
            case 11:
                // 设置保险图片路径数据
                insurance.setImg( data );
                break;
            default:
                // 跳出
                break;
        }

    }
}