Java 使用反射和Poi将任意类导出为Excel

公司项目里面有后台管理，经常需要将结果导出到Excel，所以写了个将任意类导出为Excel文件的工具类，可自定每个属性的列名。个人经验不多，大神勿喷。

直接上代码

package com.zain.demo;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author Zain
 * 
 */
public class ExcelUtilDemo {

    public static final Logger log = Logger.getLogger(ExcelUtil.class);
    public static String excelFileType;
    public static String excelSavePath;
    public static String excelSaveUrl;
    static SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd");
    public static final int COL_WIDTH = 13000;  
    public static final int ROW_HEIGHT = 5000;  

    /**
     * 导出List对象到Excel文件
     * 
     * @param name
     *            文件名（不带后缀）
     * @param data
     *            数据（标题对象和list对象），（"listData" , list对象）（"titles" , 标题对象）
     * @return URL和文件名
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static List<String> objListToExcel(String name, Map data) {
        Map<String, String> titles = (Map<String, String>) data.get("titles");
        List listData = (List) data.get("listData");
        List<String> result = new ArrayList<String>();
        Object[] keys = titles.keySet().toArray();
        String[] titleNames = new String[keys.length];
        boolean setTitle = true;
        for (int k = 0; k < keys.length; k++) {
            String temp = keys[k].toString();
            String[] subStr = StringUtil.split(temp, "-");
            int index = Integer.valueOf(subStr[0]);
            titleNames[index] = subStr[1];
        }
        try {
            String path = excelSavePath + name + excelFileType;
            FileUtil.createPath(excelSavePath);
            File newFile = new File(path);
            newFile.createNewFile();
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet();
            // 创建绘图的顶层容器
            Drawing drawing = sheet.createDrawingPatriarch();
            CreationHelper helper = wb.getCreationHelper();
            for (int i = 0; i <= listData.size(); i++) {
                XSSFRow row = sheet.createRow(i);
                Object obj = null;
                if (i != 0) {
                    obj = listData.get(i - 1);
                } else {
                    obj = listData.get(i);
                }
                for (int j = 0; j < titleNames.length; j++) {
                    XSSFCell cell = row.createCell(j);
                    if (setTitle) {
                        sheet.setColumnWidth(j, 6000);
                        cell.setCellValue(new XSSFRichTextString(titles.get(j
                                + "-" + titleNames[j])));
                    } else {
                        String titleName = (String) titleNames[j];
                        titleName = titleName.replaceFirst(
                                titleName.substring(0, 1),
                                titleName.substring(0, 1).toUpperCase());
                        titleName = "get" + titleName;
                        Class clazz = Class.forName(obj.getClass().getName());
                        Method[] methods = clazz.getMethods();
                        Pattern pattern = Pattern.compile(titleName);
                        Matcher mat = null;
                        for (Method m : methods) {
                            mat = pattern.matcher(m.getName());
                            if (mat.find()) {
                                Object attr = m.invoke(obj, null);
                                if (attr != null) {
                                    if (attr instanceof byte[]) {
                                        int pictureIdx = wb.addPicture(
                                                (byte[]) attr,
                                                Workbook.PICTURE_TYPE_PNG);
                                        ClientAnchor anchor = helper
                                                .createClientAnchor();
                                        anchor.setAnchorType(0);
                                        // 设置图片显示位置
                                        anchor.setRow1(i);  
                                        anchor.setCol1(j);  
                                        anchor.setDx1(getAnchorX(5,COL_WIDTH));  
                                        anchor.setDy1(getAnchorY(5,ROW_HEIGHT));  

                                        anchor.setRow2(i);  
                                        anchor.setCol2(j);  
                                        anchor.setDx2(getAnchorX(405,COL_WIDTH));  
                                        anchor.setDy2(getAnchorY(405,ROW_HEIGHT));
                                        Picture picture = drawing
                                                .createPicture(anchor,
                                                        pictureIdx);
                                        picture.resize(0.5);
                                        row.setHeight((short) getRowHeight(400));
                                        sheet.setColumnWidth(j, (short) getColWidth(400));
                                    } else {
                                        cell.setCellValue(attr.toString());
                                    }
                                } else {
                                    cell.setCellValue("");
                                }
                                break;
                            }

                        }
                    }
                }
                setTitle = false;
            }
            OutputStream out = new FileOutputStream(path);
            wb.write(out);
            out.flush();
            out.close();
            result.add(excelSaveUrl);
            result.add(name + excelFileType);
            return result;
        } catch (Exception e) {
            log.error(MiscUtil.traceInfo(e));
            return null;
        }
    }

    /**
     * 设置Excel标题
     * 
     * @param name
     *            导出类的全名
     * @param exportAllColum
     *            是否导出全部对应字段的列
     * @param titles
     *            自定义标题,与导出类的属性个数对应，为空时默认设置为属性名（"对应属性名-自定义名"）
     * @return 标题对象
     */
    @SuppressWarnings({ "rawtypes" })
    public static Map<String, Object> setTitles(String name, String[] titles) {
        Map<String, Object> title = new HashMap<String, Object>();
        String[] newTitles;
        Class clazz;
        int index = 0;
        try {
            clazz = Class.forName(name);
            Field[] fields = clazz.getDeclaredFields();
            newTitles = new String[fields.length];
            System.arraycopy(titles, 0, newTitles, 0, titles.length);
            for (Field f : fields) {
                if (!f.getName().equals("serialVersionUID")) {
                    if (newTitles[index] == null) {
                        newTitles[index] = f.getName();
                    }
                    title.put(index + "-" + f.getName(), titles[index]);
                    index++;
                }
            }
            // System.out.println(title);
            return title;
        } catch (IndexOutOfBoundsException e) {
            log.error(MiscUtil.traceInfo(e));
        } catch (ClassNotFoundException e) {
            log.error(MiscUtil.traceInfo(e));
        } catch (Exception e) {
            log.error(MiscUtil.traceInfo(e));
        }
        return title;
    }

    public static int getAnchorX(int px, int colWidth) {
        return (int) Math.round(((double) 701 * 16000.0 / 301)
                * ((double) 1 / colWidth) * px);
    }

    public static int getAnchorY(int px, int rowHeight) {
        return (int) Math.round(((double) 144 * 8000 / 301)
                * ((double) 1 / rowHeight) * px);
    }

    public static int getRowHeight(int px) {
        return (int) Math.round(((double) 4480 / 300) * px);
    }

    public static int getColWidth(int px) {
        return (int) Math.round(((double) 10971 / 300) * px);
    }
}

调用方法

Map titles = ExcelUtil.setTitles(Demo.class.getName(),
                    new String[] { "姓名", "性别"});//指定对象每个属性对应的列名
            List<Demo> exportList = new ArrayList<Demo>();//需要导出的类数据集合
            Map data = new HashMap();
            data.put("titles", titles);
            data.put("listData", exportList);
            ExcelUtil.objListToExcel(
                    "测试导出类到Excel" , data));

工具类中所用到的jar包下载地址：poi 3.9