Excel导出工具类.--POI

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.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import org.apache.commons.lang3.ArrayUtils;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;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.slf4j.Logger;import org.slf4j.LoggerFactory;import com.anhry.app.util.bean.SystemBean;import com.anhry.app.util.excel.annoation.Excel;/** * Excel导出工具类. */public class ExportExcel
    
      {    public static final Logger LOG = LoggerFactory.getLogger(ExportExcel.class);    /**     *      * @param title     Sheet名字     * @param pojoClass Excel对象Class     * @param dataSet   Excel对象数据List     * @param out       输出流     */    public void exportExcel(String title, Class
     
       pojoClass,            Collection
      
        dataSet, OutputStream out) {        // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出        exportExcelInUserModel(title, pojoClass, dataSet, out);        // 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现    }    private void exportExcelInUserModel(String title, Class
       
         pojoClass,            Collection
        
          dataSet, OutputStream out) {        try {            // 首先检查数据看是否是正确的            if (dataSet == null || dataSet.size() == 0) {                throw new Exception("导出数据为空！");            }            if (title == null || out == null || pojoClass == null) {                throw new Exception("传入参数不能为空！");            }            // 声明一个工作薄            Workbook workbook = new HSSFWorkbook();            // 生成一个表格            Sheet sheet = workbook.createSheet(title);            // 标题            List
         
           exportFieldTitle = new ArrayList
          
           ();            List
           
             exportFieldWidth = new ArrayList
            
             ();            // 拿到所有列名，以及导出的字段的get方法            List
             
               methodObj = new ArrayList
              
               ();            Map
               
                 convertMethod = new HashMap
                
                 ();            // 得到所有字段            Field fileds[] = pojoClass.getDeclaredFields();            // 遍历整个filed            for (int i = 0; i < fileds.length; i++) {                Field field = fileds[i];                Excel excel = field.getAnnotation(Excel.class);                // 如果设置了annottion                if (excel != null) {                    // 添加到标题                    exportFieldTitle.add(excel.exportName());                    // 添加标题的列宽                    exportFieldWidth.add(excel.exportFieldWidth());                    // 添加到需要导出的字段的方法                    String fieldname = field.getName();                    // System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());                    StringBuffer getMethodName = new StringBuffer("get");                    getMethodName.append(fieldname.substring(0, 1)                            .toUpperCase());                    getMethodName.append(fieldname.substring(1));                    Method getMethod = pojoClass.getMethod(getMethodName                            .toString(), new Class[] {});                    methodObj.add(getMethod);                    if (excel.exportConvert() == true) {                        StringBuffer getConvertMethodName = new StringBuffer(                                "get");                        getConvertMethodName.append(fieldname.substring(0, 1)                                .toUpperCase());                        getConvertMethodName.append(fieldname.substring(1));                        getConvertMethodName.append("Convert");                        Method getConvertMethod = pojoClass                                .getMethod(getConvertMethodName.toString(),                                        new Class[] {});                        convertMethod.put(getMethodName.toString(),                                getConvertMethod);                    }                }            }            int index = 0;            // 产生表格标题行            Row row = sheet.createRow(index);            for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {                Cell cell = row.createCell(i);                // cell.setCellStyle(style);                RichTextString text = new HSSFRichTextString(exportFieldTitle                        .get(i));                cell.setCellValue(text);            }            // 设置每行的列宽            for (int i = 0; i < exportFieldWidth.size(); i++) {                // 256=65280/255                sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));            }            Iterator its = dataSet.iterator();            // 循环插入剩下的集合            while (its.hasNext()) {                // 从第二行开始写，第一行是标题                index++;                row = sheet.createRow(index);                Object t = its.next();                for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {                    Cell cell = row.createCell(k);                    Method getMethod = methodObj.get(k);                    Object value = null;                    if (convertMethod.containsKey(getMethod.getName())) {                        Method cm = convertMethod.get(getMethod.getName());                        value = cm.invoke(t, new Object[] {});                    } else {                        value = getMethod.invoke(t, new Object[] {});                    }                    cell.setCellValue(value == null ? "" : value.toString());                }            }            workbook.write(out);        } catch (Exception e) {            e.printStackTrace();        }    }            /**     *      * @param title     Sheet名字     * @param pojoClass Excel对象Class     * @param dataSet   Excel对象数据List     * @param out       输出流     */    public HSSFWorkbook  exportExcel(String title, Class
                 
                   pojoClass,            Collection
                  
                    dataSet) {        // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出        return exportExcelInUserModel2File(title, pojoClass, dataSet, null);    }    /**     *      * @param title     Sheet名字     * @param pojoClass Excel对象Class     * @param dataSet   Excel对象数据List     * @param exportFields   Excel对象选择要导出的字段      * @param out       输出流     */    public HSSFWorkbook  exportExcel(String title, Class
                   
                     pojoClass,            Collection
                    
                      dataSet,List
                     
                       exportFields) {        // 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出        return exportExcelInUserModel2File(title, pojoClass, dataSet, exportFields);    }    HSSFWorkbook exportExcelInUserModel2File(String title, Class
                      
                        pojoClass,            Collection
                       
                         dataSet)  {        return exportExcelInUserModel2File(title, pojoClass, dataSet, null);    }    private HSSFWorkbook exportExcelInUserModel2File(String title, Class
                        
                          pojoClass,            Collection
                         
                           dataSet, List
                          
                            exportFields) {        // 声明一个工作薄        HSSFWorkbook  workbook = null;        try {            // 声明一个工作薄            workbook = new HSSFWorkbook();            // 生成一个表格            Sheet sheet = workbook.createSheet(title);            // 标题            List
                           
                             exportFieldTitle = new ArrayList
                            
                             ();            List
                             
                               exportFieldWidth = new ArrayList
                              
                               ();            // 拿到所有列名，以及导出的字段的get方法            List
                               
                                 methodObj = new ArrayList
                                
                                 ();            Map
                                 
                                   convertMethod = new HashMap
                                  
                                   ();            Class superClazz = null;            Field fileds[] = new Field[0];            boolean flag = true;            while (flag) {                if(superClazz != null){                    superClazz = superClazz.getSuperclass();                }else{                    superClazz = pojoClass.getSuperclass();                }                if(superClazz.isInstance(Object.class)){                    flag = false;                }else{                    Field[] sf = superClazz.getDeclaredFields();                    if(sf != null && sf.length >0){                        for(int m = 0;m
                                   
                                    0){                for(int n = 0;n
                                    
                                      0) {                exportFieldCount = exportFields.size() ;             }            for (int i = 0; i < fileds.length; i++) {                Field field = fileds[i];                Excel excel = field.getAnnotation(Excel.class);                // 如果设置了annottion                if (excel != null) {                    if(exportFieldCount > 0) {                        for(String eField: exportFields) {                            if(eField.equals(excel.exportName())) {                                addExportField(exportFieldTitle, exportFieldWidth, excel, field, methodObj, pojoClass, convertMethod);                                exportFieldCount -=1;                                LOG.debug("exportFieldCount  --- > " + exportFieldCount);                                break;                            }                        }                        if(exportFieldCount <= 0) {                            LOG.debug("Break Field Loop!   ------------ !@!");                            break;                        }                    } else {                        addExportField(exportFieldTitle, exportFieldWidth, excel, field, methodObj, pojoClass, convertMethod);                    }                                    }            }            int index = 0;            // 产生表格标题行            Row row = sheet.createRow(index);            row.setHeight((short)450);            CellStyle titleStyle = getTitleStyle(workbook);            for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {                Cell cell = row.createCell(i);                // cell.setCellStyle(style);                RichTextString text = new HSSFRichTextString(exportFieldTitle                        .get(i));                cell.setCellValue(text);                cell.setCellStyle(titleStyle);            }            // 设置每行的列宽            for (int i = 0; i < exportFieldWidth.size(); i++) {                // 256=65280/255                sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));            }            Iterator its = dataSet.iterator();            // 循环插入剩下的集合                        HSSFCellStyle oneStyle = getOneStyle(workbook);            HSSFCellStyle twoStyle = getTwoStyle(workbook);            while (its.hasNext()) {                // 从第二行开始写，第一行是标题                index++;                row = sheet.createRow(index);                row.setHeight((short)350);                Object t = its.next();                for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {                    Cell cell = row.createCell(k);                    Method getMethod = methodObj.get(k);                    Object value = null;                    if (convertMethod.containsKey(getMethod.getName())) {                        Method cm = convertMethod.get(getMethod.getName());                        value = cm.invoke(t, new Object[] {});                    } else {                        value = getMethod.invoke(t, new Object[] {});                    }                    cell.setCellValue(value==null?"":value.toString());                                        if(index%2==0)                        cell.setCellStyle(twoStyle);                    else                        cell.setCellStyle(oneStyle);                }            }        } catch (Exception e) {            e.printStackTrace();        }        return workbook;    }        private void addExportField(List
                                     
                                       exportFieldTitle, List
                                      
                                        exportFieldWidth, Excel excel,                             Field field, List
                                       
                                         methodObj, Class
                                        
                                          pojoClass, Map
                                         
                                           convertMethod) throws Exception{        // 添加到标题        exportFieldTitle.add(excel.exportName());        // 添加标题的列宽        exportFieldWidth.add(excel.exportFieldWidth());        // 添加到需要导出的字段的方法        String fieldname = field.getName();        // System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());        StringBuffer getMethodName = new StringBuffer("get");        getMethodName.append(fieldname.substring(0, 1)                .toUpperCase());        getMethodName.append(fieldname.substring(1));        Method getMethod = pojoClass.getMethod(getMethodName                .toString(), new Class[] {});        methodObj.add(getMethod);        if (excel.exportConvert() == true) {            //----------------------------------------------------------------            //update-begin--Author:Quainty  Date:20130524 for：[8]excel导出时间问题            // 用get/setXxxxConvert方法名的话， 由于直接使用了数据库绑定的Entity对象，注入会有冲突            StringBuffer getConvertMethodName = new StringBuffer("convertGet");            getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());            getConvertMethodName.append(fieldname.substring(1));            //getConvertMethodName.append("Convert");            //update-end--Author:Quainty  Date:20130524 for：[8]excel导出时间问题            //----------------------------------------------------------------            // System.out.println("convert: "+getConvertMethodName.toString());            Method getConvertMethod = pojoClass                    .getMethod(getConvertMethodName.toString(),                            new Class[] {});            convertMethod.put(getMethodName.toString(),                    getConvertMethod);        }    }        /**     * 取得一个类添加了@Excel 注解的所有属性中 该注解中的exportName     * @param pojoClass     * @return     */    public  List
                                          
                                            getExportFields(Class
                                           
                                             pojoClass) {        // 标题        List
                                            
                                              exportNames = new ArrayList
                                             
                                              ();        Class superClazz = null;        Field fileds[] = new Field[0];        boolean flag = true;        while (flag) {            if(superClazz != null){                superClazz = superClazz.getSuperclass();            }else{                superClazz = pojoClass.getSuperclass();            }            if(superClazz.isInstance(Object.class)){                flag = false;            }else{                Field[] sf = superClazz.getDeclaredFields();                if(sf != null && sf.length >0){                    for(int m = 0;m
                                              
                                               0){            for(int n = 0;n