ExcelUtil excel工具类

我会带着你远行 2022-08-08 06:44 224阅读 0赞

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;  
  
    \}  
\}