ExcelUtil excel工具类

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
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 org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

import com.XXXX.bss.common.Constants;

public class ExcelUtil<T>
{

    private static final Logger log = Logger.getLogger(ExcelUtil.class);

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     *
     * @param title表格标题名
     * @param headers表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */

    @SuppressWarnings("unchecked")
    public int exportExcel(int index, HSSFSheet sheet, String title, String[] headers, Collection<T> dataset,
            OutputStream out) throws Exception
    {

        if (index == 0)
        {// 第一次建立Excel表头
            HSSFRow row = sheet.createRow(0);
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            for (short i = 0; i < headers.length; i++)
            {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        while (it.hasNext())
        {
            index++;
            HSSFRow row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < fields.length; i++)
            {
                HSSFCell cell = row.createCell(i);
                // cell.setCellStyle(style2);
                Field field = fields[i];
                field.setAccessible(true);
                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 = "";
                if (value != null)
                {
                    if (value instanceof Date)
                    {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        textValue = sdf.format(date);
                    }
                    else
                    {
                        // 其它数据类型都当作字符串简单处理
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                        textValue = value.toString();
                    }
                    cell.setCellValue(textValue);
                }

            }

        }
        return index;
    }

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以csv 的形式输出到指定IO设备上
     *
     * @param title表格标题名
     * @param headers表格属性列名数组
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将csv文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */

    @SuppressWarnings("unchecked")
    public int exportCsv(int index, String title, String[] headers, Collection<T> dataset,
            BufferedWriter csvFileOutputStream) throws Exception
    {

        System.out.println("dataset.size = " + dataset.size());

        if (index == 0)
        {// 第一次建立csv表头

            // 写入文件头部
            for (short i = 0; i < headers.length; i++)
            {
                csvFileOutputStream.write("\"" + headers[i] + "\"");
                if (i != headers.length - 1)
                {
                    csvFileOutputStream.write(Constants.COMMA);
                }
            }
        }

        csvFileOutputStream.newLine();

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        while (it.hasNext())
        {
            index++;
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < fields.length; i++)
            {

                Field field = fields[i];
                field.setAccessible(true);
                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 = "";
                if (value != null)
                {
                    if (value instanceof Date)
                    {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        textValue = sdf.format(date);
                    }
                    else
                    {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }
                    csvFileOutputStream.write("\"" + textValue + "\"");
                }

                if (i != fields.length - 1)
                {
                    csvFileOutputStream.write(Constants.COMMA);
                }
            }
            csvFileOutputStream.newLine();
        }
        return index;
    }

    /**
     * 根据文件目录 读取excel的第一个sheet
     *
     * @param file
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public static HSSFSheet createSheet(File file) throws IOException, FileNotFoundException
    {
        FileInputStream fileInputStream = null;
        HSSFSheet sheet = null;
        try
        {
            fileInputStream = new FileInputStream(file);
            POIFSFileSystem excelFile = new POIFSFileSystem(fileInputStream);
            HSSFWorkbook book = new HSSFWorkbook(excelFile);
            sheet = book.getSheetAt(0);
        }
        finally
        {
            if (null != fileInputStream)
            {
                fileInputStream.close();
            }
        }

        return sheet;
    }

    /**
     * 校验单元格
     *
     * @param cell
     * @return
     */
    public static String checkCellValue(HSSFCell cell)
    {
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && cell.getCellType() != Cell.CELL_TYPE_STRING)
            return "cellWrong";
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || "".equals(cell.getStringCellValue().trim()))
        {
            return "cellNull";
        }
        if (cell != null && !(cell.getCellType() == Cell.CELL_TYPE_BLANK)
                && !"".equals(cell.getStringCellValue().trim()))
            return "right";
        return null;
    }

    /**
     * 创建表头
     *
     * @param sheet
     * @param headName
     * @return
     */
    public static HSSFRow creatRow(HSSFSheet sheet, String[] cellContent, int rowNumber)
    {
        if (null == cellContent || cellContent.length == 0)
        {
            return null;
        }

        HSSFRow row = sheet.createRow(rowNumber);

        int length = cellContent.length;
        for (int i = 0; i < length; i++)
        {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(cellContent[i]);
        }

        return row;

    }
}