Java 对象转Excel文件

所用到的依赖jar包.

1.阿帕奇的POI
2.阿里的fastjson
3.阿帕奇公共工具类

代码逻辑

1.通过反射获取对象成员变量名称作为Excel的标题.
2.反射获取到的对象成员变量值作为Excel的行数据.
3.写出文件.

代码

1.工具类

package my.test.org;

import com.alibaba.fastjson.JSON;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.transaction.TransactionRequiredException;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Author: xinyi.pan
 * Date: 2017/3/1
 * <p>
 * 工具类:excel 导出
 */
public class AccessUtils {

    /**
     * 将对象List转换为HSSFWorkbook对象
     *
     * @param objectList
     * @return
     */
    public static <T> HSSFWorkbook ListObject2HSSFWorkbook(List<T>... objectList) {
        if (objectList == null || objectList.length == 0) {
            throw new IllegalArgumentException("待转换的对象为空");
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        for (int i = 0; i < objectList.length; i++) {
            List<T> tList = objectList[i];
            if (CollectionUtils.isNotEmpty(tList)) {
                String sheetName = objectList[i].get(0).getClass().getSimpleName();
                if (StringUtils.isNotBlank(sheetName)) {
                    ListObject2HSSFWorkbook(objectList[i], sheetName, workbook);
                }
            }
        }
        return workbook;
    }

    /**
     * 将List对象添加到对应的页
     *
     * @param objectList
     * @param sheetName
     * @param workbook
     * @return
     */
    public static <T> HSSFWorkbook ListObject2HSSFWorkbook(List<T> objectList, String sheetName, HSSFWorkbook workbook) {
        //创建该页的首行
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //创建基本样式居中
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        boolean isFirstRow = true;
        Map<String, Integer> keyMap = new HashMap<String, Integer>();
        for (int i = 0; i < objectList.size(); i++) {
            T t1 = objectList.get(i);
            if (t1 == null) continue;
            Class oClass = t1.getClass();
            Field[] fields = oClass.getDeclaredFields();
            if (fields == null || fields.length == 0) continue;
            String key;
            String valueStr;
            if (isFirstRow) {
                //创建标题
                HSSFRow row = sheet.createRow(0);
                for (int j = 0, k = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    if (field == null) continue;
                    HSSFCell cell = row.createCell(k);
                    key = field.getName();
                    if (StringUtils.isBlank(key)) continue;
                    if ("serialVersionUID".equalsIgnoreCase(key)) continue;
                    cell.setCellStyle(style);
                    cell.setCellValue(key);
                    keyMap.put(key, k);
                    k++;
                }
                isFirstRow = false;
            }
            //添加数据
            HSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < fields.length; j++) {
                Field field = fields[j];
                if (field == null) continue;
                try {
                    key = field.getName();
                    HSSFCell cell = row.createCell(keyMap.get(key));
                    if (StringUtils.isBlank(key)) continue;
                    if ("serialVersionUID".equalsIgnoreCase(key)) continue;
                    field.setAccessible(true);
                    Object value = field.get(t1);
                    if (value == null) continue;
                    //日期格式化
                    if (value instanceof Calendar) {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        valueStr = dateFormat.format(((Calendar) value).getTime());
                    } else if (value instanceof Date) {
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        valueStr = dateFormat.format(value);
                    } else {
                        //其他转换为JSON字符串,去掉引号
                        valueStr = JSON.toJSONString(value).replaceAll("\"", "");
                    }
                    cell.setCellValue(valueStr);
                } catch (IllegalAccessException e) {
                    //异常继续执行下一个成员变量
                    continue;
                } catch (Exception e) {
                    //异常继续执行下一个成员变量
                    continue;
                }
            }
        }
        //列宽自适应
        for (int i = 0; i < keyMap.size(); i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        return workbook;
    }


    public static void main(String[] args) {
        List<Student> studentList = new ArrayList<Student>();
        List<String> stringList = new ArrayList<String>();
        stringList.add("qqqq");
        stringList.add("wwww");
        stringList.add("eeee");

        Student student1 = new Student();
        student1.set学生姓名("张三");
        student1.set学生成绩(new BigDecimal("90"));
        student1.setStringList(stringList);
        studentList.add(student1);

        Student student2 = new Student();
        student2.set学生姓名("李四");
        student2.set学生成绩(new BigDecimal("80"));
        studentList.add(student2);

        Student student3 = new Student();
        student3.set学生姓名("王五");
        student3.set学生成绩(new BigDecimal("50"));
        studentList.add(student3);

        Student student4 = new Student();
        student4.set学生姓名("陈翔");
        student4.set学生成绩(new BigDecimal("0.0"));
        studentList.add(student4);

        HSSFWorkbook workbook = new HSSFWorkbook();
        try {
            workbook = AccessUtils.ListObject2HSSFWorkbook(studentList);
            FileOutputStream outputStream = new FileOutputStream("E:\\downloads\\test.xls");
            workbook.write(outputStream);
        } catch (TransactionRequiredException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

2.测试用到的对象

package my.test.org;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * Author: xinyi.pan
 * Date: 2017/3/1
 */
public class Student implements Serializable {

    private static final long serialVersionUID = 7911823052873591951L;
    private String 学生姓名 ;
    private BigDecimal 学生成绩 ;
    private List<String> stringList ;


    public String get学生姓名() {
        return 学生姓名;
    }

    public void set学生姓名(String 学生姓名) {
        this.学生姓名 = 学生姓名;
    }

    public BigDecimal get学生成绩() {
        return 学生成绩;
    }

    public void set学生成绩(BigDecimal 学生成绩) {
        this.学生成绩 = 学生成绩;
    }

    public List<String> getStringList() {
        return stringList;
    }

    public void setStringList(List<String> stringList) {
        this.stringList = stringList;
    }
}
package my.test.org;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * Author: xinyi.pan
 * Date: 2017/3/1
 */
public class Student implements Serializable {

    private static final long serialVersionUID = 7911823052873591951L;
    private String 学生姓名 ;
    private BigDecimal 学生成绩 ;
    private List<String> stringList ;


    public String get学生姓名() {
        return 学生姓名;
    }

    public void set学生姓名(String 学生姓名) {
        this.学生姓名 = 学生姓名;
    }

    public BigDecimal get学生成绩() {
        return 学生成绩;
    }

    public void set学生成绩(BigDecimal 学生成绩) {
        this.学生成绩 = 学生成绩;
    }

    public List<String> getStringList() {
        return stringList;
    }

    public void setStringList(List<String> stringList) {
        this.stringList = stringList;
    }
}

输出的结果

学生姓名	学生成绩	stringList
张三	90	[qqqq,wwww,eeee]
李四	80
王五	50
陈翔	0.0

---

End