利用反射将对象导出为Excel表格、将Excel表中的数据转为Bean对象

注解：

package com.xue.code;

import java.lang.annotation.*;

@Target({ElementType.TYPE, ElementType.METHOD, ElementType.PARAMETER, ElementType.FIELD, ElementType.ANNOTATION_TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface AsClassName {

    String value();
}

package com.xue.code;

import java.lang.annotation.*;

@Target({ElementType.CONSTRUCTOR, ElementType.METHOD, ElementType.PARAMETER, ElementType.FIELD, ElementType.ANNOTATION_TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface AsName {

    String value();
}

实体类：

package com.xue.domain;

import com.xue.code.AsClassName;
import com.xue.code.AsName;

import java.math.BigDecimal;
import java.util.Date;

@AsClassName(value = "学生")
public class Student {

    @AsName(value = "编号")
    private String id;
    @AsName(value = "姓名")
    private String name;
    @AsName(value = "年龄")
    private Integer age;
    @AsName(value = "地址")
    private String address;
    @AsName(value = "性别")
    private String sex;
    @AsName(value = "生日")
    private Date birthday;
    @AsName(value = "薪资")
    private BigDecimal salary;
    @AsName(value = "身高")
    private Double height;
    @AsName(value = "体重")
    private Double weight;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date school) {
        this.birthday = school;
    }

    public BigDecimal getSalary() {
        return salary;
    }

    public void setSalary(BigDecimal hobby) {
        this.salary = hobby;
    }

    public Double getHeight() {
        return height;
    }

    public void setHeight(Double height) {
        this.height = height;
    }

    public Double getWeight() {
        return weight;
    }

    public void setWeight(Double weight) {
        this.weight = weight;
    }
}

工具类

package com.xue.util;

import com.xue.domain.Student;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.util.CollectionUtils;

import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    /**
     * 对象导出为Excel.xls文件
     *
     * @param objectList    对象集合
     * @param fileOutputUrl 文件输出路径
     */
    public static void ObjectToExcel(List<?> objectList, String fileOutputUrl) {
        if (!CollectionUtils.isEmpty(objectList)) {
            HSSFWorkbook workbook = new HSSFWorkbook(); //创建工作簿
            HSSFSheet sheet = workbook.createSheet(); //创建Sheet页
            HSSFRow row = sheet.createRow(0); //创建第一行
            Class<?> objectClass = objectList.get(0).getClass();
            Field[] declaredFields = objectClass.getDeclaredFields(); //反射获取对象的所有属性
            List<String> asNameList = ObjectUtils.getAsNameValue(objectList.get(0));
            int i = 0;
            for (String asName : asNameList) {
                row.createCell(i).setCellValue(asName);
                i++;
            }
            int rowNum = 1;
            for (Object object : objectList) {
                List<Object> valueList = ExcelUtils.getValue(declaredFields, object); //获取实体类属性值集合
                for (i = 0; i < objectList.size(); i++) {
                    HSSFRow row1 = sheet.createRow(rowNum); //从第二行开始写入数据
                    for (int j = 0; j < valueList.size(); j++) {
                        if (valueList.get(j) != null && !"".equals(valueList.get(j))) {
                            row1.createCell(j).setCellValue(valueList.get(j).toString());
                        }
                    }
                }
                rowNum = rowNum + 1;
            }
            try {
                //将文件保存到指定的位置
                FileOutputStream fos = new FileOutputStream(fileOutputUrl + File.separator +
                        ObjectUtils.getAsClassNameValue(objectList.get(0)) + ".xls");
                workbook.write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 把一个字符串的第一个字母大写、效率是最高的
     *
     * @param fildeName 属性名
     * @return
     */
    private static String getMethodName(String fildeName) {
        byte[] items = fildeName.getBytes();
        items[0] = (byte) ((char) items[0] - 'a' + 'A');
        return new String(items);
    }

    /**
     * 获取对象的属性值集合
     *
     * @param declaredFields 对象的属性数组
     * @param object         对象
     * @return 属性值集合
     */
    public static List<Object> getValue(Field[] declaredFields, Object object) {
        List<Object> valueList = new ArrayList<>();
        Method m = null;
        for (Field declaredField : declaredFields) {
            try {
                //String类型
                if (declaredField.getGenericType().toString().equals("class java.lang.String")) {
                    m = object.getClass().getMethod("get" + getMethodName(declaredField.getName()));
                    String value = (String) m.invoke(object);
                    if (value != null) {
                        valueList.add(value);
                    } else {
                        valueList.add(null);
                    }
                }
                //Integer类型
                if (declaredField.getGenericType().toString().equals("class java.lang.Integer")) {
                    m = object.getClass().getMethod("get" + getMethodName(declaredField.getName()));
                    Integer value = (Integer) m.invoke(object);
                    if (value != null) {
                        valueList.add(value);
                    } else {
                        valueList.add(null);
                    }
                }
                //Date日期类型
                if (declaredField.getGenericType().toString().equals("class java.util.Date")) {
                    m = object.getClass().getMethod("get" + getMethodName(declaredField.getName()));
                    Date date = (Date) m.invoke(object);
                    if (date != null) {
                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        String value = simpleDateFormat.format(date);
                        valueList.add(value);
                    } else {
                        valueList.add(null);
                    }
                }
                //Double类型
                if (declaredField.getGenericType().toString().equals("class java.lang.Double")) {
                    m = object.getClass().getMethod("get" + getMethodName(declaredField.getName()));
                    Double value = (Double) m.invoke(object);
                    if (value != null) {
                        valueList.add(value);
                    } else {
                        valueList.add(null);
                    }
                }
                //BigDecimal类型
                if (declaredField.getGenericType().toString().equals("class java.math.BigDecimal")) {
                    m = object.getClass().getMethod("get" + getMethodName(declaredField.getName()));
                    BigDecimal value = (BigDecimal) m.invoke(object);
                    if (value != null) {
                        valueList.add(value);
                    } else {
                        valueList.add(null);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return valueList;
    }

    /**
     * 获取对象的属性值
     *
     * @param fieldName 属性名称
     * @param object    对象
     * @return Object
     */
    public static Object getValue(String fieldName, Object object) {
        try {
            Class<?> clazz = object.getClass();
            Field declaredField = clazz.getDeclaredField(fieldName);
            declaredField.setAccessible(true);
            PropertyDescriptor pd = new PropertyDescriptor(declaredField.getName(), clazz);
            Method readMethod = pd.getReadMethod();
            Object value = readMethod.invoke(object);
            return value;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取 Excel表格数据转为 Object 对象
     *
     * @param object   实体类对象
     * @param fileUrl  Excel文件路径
     * @param fileName Excel文件名称
     */
    public static List<Object> excelToBean(Object object, String fileUrl, String fileName) {
        List<Object> objectList = new ArrayList<>();
        Class<?> objectClass = object.getClass();
        Field[] declaredFields = objectClass.getDeclaredFields();
        File file = new File(fileUrl + File.separator + fileName);
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(file);
            HSSFWorkbook book = new HSSFWorkbook(fileInputStream);
            HSSFSheet sheet = book.getSheetAt(0);
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                Row temp = sheet.getRow(i);  //获取行
                if (temp == null) {  //判断是否行为空
                    continue;
                } else {
                    int j = 0;
                    while (j < cells) {
                        HSSFCell cell = sheet.getRow(i).getCell(j);
                        if (cell != null) {
                            String cellValue = cell.getStringCellValue();
                            switch (declaredFields[j].getGenericType().toString()) {
                                case "class java.lang.String":
                                    ObjectUtils.invokeSetMethod(object, declaredFields[j].getName(), cellValue);
                                    break;
                                case "class java.lang.Integer":
                                    Integer value = Integer.valueOf(cellValue);
                                    ObjectUtils.invokeSetMethod(object, declaredFields[j].getName(), value);
                                    break;
                                case "class java.util.Date":
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    Date dateValue = simpleDateFormat.parse(cellValue); //指定字符串的时间转Date
                                    ObjectUtils.invokeSetMethod(object, declaredFields[j].getName(), dateValue);
                                    break;
                                case "class java.lang.Double":
                                    Double doubleValue = Double.valueOf(cellValue);
                                    ObjectUtils.invokeSetMethod(object, declaredFields[j].getName(), doubleValue);
                                    break;
                                case "class java.math.BigDecimal":
                                    BigDecimal bigDecimal = new BigDecimal(cellValue);
                                    ObjectUtils.invokeSetMethod(object, declaredFields[j].getName(), bigDecimal);
                                    break;
                                default:
                                    break;
                            }
                        }
                        j++;
                    }
                }
                objectList.add(object);
                object = objectClass.newInstance();
            }
            return objectList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

package com.xue.util;

import com.xue.code.AsClassName;
import com.xue.code.AsName;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ObjectUtils {

    /**
     * 获取实体类别名（自定义属性注解的 value ）
     *
     * @param object 实体类
     */
    public static String getAsClassNameValue(Object object) {
        Class<?> objectClass = object.getClass();
        Class<AsClassName> asClassNameClass = AsClassName.class; //注解反射
        AsClassName annotation = objectClass.getAnnotation(asClassNameClass);
        if (annotation != null) {
            return annotation.value();
        }
        return "未知名称";
    }

    /**
     * 获取实体类属性别名（自定义属性注解的 value ）
     *
     * @param object 实体类对象
     */
    public static List<String> getAsNameValue(Object object) {
        List<String> list = new ArrayList<>();
        Class<?> objectClass = object.getClass();
        Field[] declaredFields = objectClass.getDeclaredFields(); //获取实体类属性数组
        if (declaredFields.length > 0) {
            for (Field declaredField : declaredFields) {
                Class<AsName> asNameClass = AsName.class; //注解反射
                AsName annotation = declaredField.getAnnotation(asNameClass); //获取当前属性上的注解
                if (annotation != null) {
                    String value = annotation.value();
                    list.add(value);
                }
            }
        }
        return list;
    }

    /**
     * 执行实体类对象的Set方法
     *
     * @param object       实体对象
     * @param propertyName 属性名
     * @param arg          参数
     */
    public static void invokeSetMethod(Object object, String propertyName, Object arg) {
        String str = ObjectUtils.toUpperFirstCode(propertyName); //将首字母大写
        try {
            Class<?> clazz = object.getClass();
            Field declaredField = clazz.getDeclaredField(propertyName); //获取指定的属性
            Type genericType = declaredField.getGenericType(); //获取属性类型
            Method method = null;
            switch (genericType.toString()) {
                case "class java.lang.String":
                    method = clazz.getMethod("set" + str, String.class);
                    method.invoke(object, arg);
                    break;
                case "class java.lang.Integer":
                    method = clazz.getMethod("set" + str, Integer.class);
                    method.invoke(object, arg);
                    break;
                case "class java.util.Date":
                    method = clazz.getMethod("set" + str, Date.class);
                    method.invoke(object, arg);
                    break;
                case "class java.lang.Double":
                    method = clazz.getMethod("set" + str, Double.class);
                    method.invoke(object, arg);
                    break;
                case "class java.math.BigDecimal":
                    method = clazz.getMethod("set" + str, BigDecimal.class);
                    method.invoke(object, arg);
                    break;
                default:
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 每个单词第一个字母大写
     *
     * @param str 字母字符串
     * @return
     */
    public static String toUpperFirstCode(String str) {
        String[] strs = str.split(" ");
        StringBuilder sb = new StringBuilder();
        for (String strTmp : strs) {
            char[] ch = strTmp.toCharArray();
            if (ch[0] >= 'a' && ch[0] <= 'z') {
                ch[0] = (char) (ch[0] - 32);
            }
            String strT = new String(ch);
            sb.append(strT).append(" ");
        }
        return sb.toString().trim();
    }
}

带格式，直接导出，不存服务器

/**
 * 对象导出为Excel.xls文件
 *
 * @param objectList 对象集合
 * @param strArr     首行标题
 * @param fileName   文件名称
 * @param response   HttpServletResponse
 */
public static void ObjectToExcel(List<?> objectList, String[] strArr, String fileName, HttpServletResponse response) {
    if (!CollectionUtils.isEmpty(objectList)) {
        //创建工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建Sheet页
        HSSFSheet sheet = workbook.createSheet();
        CellRangeAddress address = new CellRangeAddress(0, 0, 0, 7);
        sheet.addMergedRegion(address);
        HSSFCell hssfCell = sheet.createRow(0).createCell(0);
        FileUtil.createStyle(workbook, hssfCell, "黑体", (short) 16);
        hssfCell.setCellValue("信息表");
        //创建第二行
        HSSFRow row = sheet.createRow(1);
        //行高
        row.setHeight((short) 600);
        for (int i = 0; i < strArr.length; i++) {
            HSSFCell cell = row.createCell(i);
            //设置边框、字体样式
            FileUtil.createStyle(workbook, cell, "黑体", (short) 14);
            cell.setCellValue(strArr[i]);
            if (i == 4) {
                sheet.setColumnWidth(i, 6000);
            } else if (i == 5) {
                sheet.setColumnWidth(i, 3000);
            }
        }
        Class<?> objectClass = objectList.get(0).getClass();
        //反射获取对象的所有属性
        Field[] declaredFields = objectClass.getDeclaredFields();
        int rowNum = 2;
        for (Object object : objectList) {
            //获取实体类属性值集合
            List<Object> valueList = FileUtil.getValue(declaredFields, object);
            for (int i = 0; i < objectList.size(); i++) {
                //从第三行开始写入数据
                FileUtil.setSizeColumn(sheet);
                HSSFRow row1 = sheet.createRow(rowNum);
                row1.setHeight((short) 600);
                for (int j = 0; j < valueList.size(); j++) {
                    if (valueList.get(j) != null && !"".equals(valueList.get(j))) {
                        HSSFCell cell = row1.createCell(j);
                        FileUtil.createStyle(workbook, cell, "宋体", (short) 12);
                        cell.setCellValue(valueList.get(j).toString());
                    }
                }
            }
            rowNum = rowNum + 1;
        }
        try {
            OutputStream os = response.getOutputStream();
            // 重置输出流
            response.reset();
            // 设定输出文件头
            response.setHeader("Content-disposition",
                    "attachment; filename=" + new String(fileName.getBytes("GB2312"), "8859_1") + ".xls");
            // 定义输出类型
            response.setContentType("application/json");
            workbook.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

/**
 * 设置边框、字体样式
 *
 * @param workbook HSSFWorkbook
 * @param cell     HSSFCell
 * @param fontName 字体名称
 * @param fontSize 字体大小
 */
public static void createStyle(HSSFWorkbook workbook, HSSFCell cell, String fontName, short fontSize) {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setBorderBottom(BorderStyle.MEDIUM);//下边框
    style.setBorderLeft(BorderStyle.MEDIUM);//左边框
    style.setBorderRight(BorderStyle.MEDIUM);//右边框
    style.setBorderTop(BorderStyle.MEDIUM);//上边框
    HSSFFont font = workbook.createFont();
    font.setFontName(fontName);
    //字体大小
    font.setFontHeightInPoints(fontSize);
    //设置字体
    style.setFont(font);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    cell.setCellStyle(style);
}

/**
 * 中文列宽自适应
 *
 * @param sheet HSSFSheet
 */
private static void setSizeColumn(HSSFSheet sheet) {
    for (int columnNum = 0; columnNum <= 8; columnNum++) {
        int columnWidth = sheet.getColumnWidth(columnNum) / 256;
        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
            HSSFRow currentRow;
            //当前行未被使用过
            if (sheet.getRow(rowNum) == null) {
                currentRow = sheet.createRow(rowNum);
            } else {
                currentRow = sheet.getRow(rowNum);
            }
            if (currentRow.getCell(columnNum) != null) {
                HSSFCell currentCell = currentRow.getCell(columnNum);
                if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                    int length = currentCell.getStringCellValue().getBytes().length;
                    if (columnWidth < length) {
                        columnWidth = length;
                    }
                }
            }
        }
        sheet.setColumnWidth(columnNum, columnWidth * 256);
    }
}