Java将List对象导入Excel文件

通过poi依赖将java对象写入excel之中，核心理念是通过反射获取Java对象的getter方法和属性，使用getter方法获取要写入excel中的值，再通过属性上的自定义注解获取excel标题行，然后以文件流的方式写入excel

代码用到poi依赖如下:

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

首先我们定义注解@ExcelTitle，用来初始化excel第一行作为标题行

/**
 * excel标题头注解，当Java属性没有使用此注解，则默认使用Java属性作为标题。
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelTitle {
  String value();
}

然后定义对象转excel工具类ExcelWriterUtil

import com.sc.ops.annotations.ExcelTitle;
import com.sc.ops.annotations.Order;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
import java.util.stream.Collectors;
/**
 * 对象转excel工具类
 */
@Slf4j
public class ExcelWriterUtil{

    private static ThreadLocal<ExcelValueFormatter> valueFormatter = ThreadLocal
            .withInitial(() -> new DateValueFormatter("yyyy-MM-dd"));

    public static void setExcelValueFormatter(ExcelValueFormatter excelValueFormatter) {
        valueFormatter.set(excelValueFormatter);
    }

    public static <E> void writeToExcel(List<E> list, Class<E> clazz, String fileName)
            throws InvocationTargetException, IllegalAccessException {
        @SuppressWarnings("MismatchedQueryAndUpdateOfCollection")
        List<Object[]> dataList = new ArrayList<>();
        Field[] fields = getAllFields(clazz);
        Map<String, Method> fieldMethodMap = buildFieldMethodMap(clazz);
        sortMethodMap(fields, fieldMethodMap);
        Map<String, String> fieldTitleMap = buildFieldTitleMap(clazz, fieldMethodMap);
        List<Map.Entry<String, Method>> methodEntrySet = new ArrayList<>(fieldMethodMap.entrySet());
        int addMark = 0;
        int itemSize = fieldTitleMap.size();
        String[] titleArr = new String[itemSize];
        for (E obj : list) {
            Object[] item = new Object[itemSize];
            for (int i = 0; i < methodEntrySet.size(); i++) {
                Map.Entry<String, Method> methodEntry = methodEntrySet.get(i);
                String field = methodEntry.getKey();
                if (addMark < itemSize) {
                    titleArr[addMark] = fieldTitleMap.get(field);
                    addMark++;
                }
                Method method = methodEntry.getValue();
                Object value = formatValue(method, obj, valueFormatter.get());
                if (value != null) {
                    item[i] = value;
                }
            }
            dataList.add(item);
        }
        writeObjectToExcel(dataList, titleArr, fileName);
    }

    private static Object formatValue(Method method, Object obj,
                                      ExcelValueFormatter excelValueFormatter)
            throws InvocationTargetException, IllegalAccessException {
        Object value = method.invoke(obj);
        if (value == null) {
            return null;
        }
        if(excelValueFormatter == null) {
            return value;
        }
        Class<?> returnType = method.getReturnType();
        return excelValueFormatter.formatValue(returnType, value);
    }

    private static <E> Map<String, Method> buildFieldMethodMap(Class<E> clazz) {
        List<Method> getMethods = Arrays.stream(clazz.getMethods())
                .filter(
                        method -> method.getName().startsWith("get") && !method.getName().equals("getClass"))
                .collect(
                        Collectors.toList());
        Map<String, Method> fieldMethodMap = new LinkedHashMap<>();
        for (Method getMethod : getMethods) {
            String m = getMethod.getName().replace("get", "");
            String field = m.substring(0, 1).toLowerCase() + m.substring(1);
            fieldMethodMap.put(field, getMethod);
        }
        return fieldMethodMap;
    }

    public static <E> Field[] getAllFields(Class<E> clazz){
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null){
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = (Class<E>) clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }

    private static <E> Map<String, String> buildFieldTitleMap(Class<E> clazz,
                                                              Map<String, Method> fieldMethodMap) {
        Map<String, String> fieldTitleMap = new LinkedHashMap<>();
        Field[] fields = getAllFields(clazz);
        Arrays.stream(fields).forEach(field -> {
            if (fieldMethodMap.containsKey(field.getName())) {
                ExcelTitle excelTitle = field.getAnnotation(ExcelTitle.class);
                String title = excelTitle == null ? field.getName() : excelTitle.value();
                fieldTitleMap.put(field.getName(), title);
            }
        });
        return fieldTitleMap;
    }

    private static void writeObjectToExcel(List<Object[]> list, String[]
            excelTitle, String fileName) {
        //在内存中创建Excel文件
        Workbook workbook;
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook();
        } else if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook();
        } else {
            throw new IllegalArgumentException("fileName not legal");
        }
        Sheet sheet = workbook.createSheet();
        //标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < excelTitle.length; i++) {
            titleRow.createCell(i).setCellValue(excelTitle[i]);
        }
        //创建数据行并写入值
        for (Object[] dataArr : list) {
            int lastRowNum = sheet.getLastRowNum();
            Row dataRow = sheet.createRow(lastRowNum + 1);
            for (int i = 0; i < dataArr.length; i++) {
                Cell cell = dataRow.createCell(i);
                Object cellValue = dataArr[i];
                if(cellValue != null) {
                    setCellValue(cellValue, cell);
                }
            }
        }
        //创建输出流对象
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(fileName));
        } catch (FileNotFoundException e) {
            log.error("file not found", e);
        }
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            log.error("write to file failed", e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException ignore) {
                }
            }
        }
    }

    private static void setCellValue(Object cellValue, Cell cell) {
        if (cellValue instanceof Boolean) {
            cell.setCellValue((boolean) cellValue);
        } else if (cellValue instanceof String) {
            cell.setCellValue(cellValue.toString());
        } else if (cellValue instanceof Double || cellValue instanceof Integer
                || cellValue instanceof Long) {
            cell.setCellValue(Double.valueOf(cellValue.toString()));
        } else if (cellValue instanceof Date) {
            cell.setCellValue((Date) cellValue);
        } else if (cellValue instanceof Calendar) {
            cell.setCellValue((Calendar) cellValue);
        } else if (cellValue instanceof RichTextString) {
            cell.setCellValue((RichTextString) cellValue);
        } else {
            cell.setCellValue(cellValue.toString());
        }
    }


    private static void sortMethodMap(Field[] fields, Map<String, Method> fieldMethodMap) {
        Set<String> fieldSet = fieldMethodMap.keySet();
        List<Field> fieldList = Arrays.stream(fields).filter(e -> fieldSet.contains(e.getName()))
                .collect(Collectors.toList());
        fields = fieldList.toArray(new Field[]{});
        Arrays.sort(fields, (o1, o2) -> {
            Order order1 = o1.getAnnotation(Order.class);
            Order order2 = o2.getAnnotation(Order.class);
            if (order1 == null && order2 == null) { //均不含注解时不排序
                return 0;
            }
            if (order1 == null) { //order1 == null && order2 != null 仅有一个含有注解时，默认排到不含注解的后面
                return -1;
            }
            if (order2 == null) { //order1 != null && order2 == null 仅有一个含有注解时，默认排到不含注解的后面
                return 1;
            }
            return order1.value() - order2.value();//均含有注解时，按照注解值从小到大排序
        });
        Map<String, Method> sortedMethodMap = new LinkedHashMap<>();
        Arrays.stream(fields).forEach(e -> {
            String key = e.getName();
            sortedMethodMap.put(key, fieldMethodMap.get(key));
        });
        fieldMethodMap.clear();
        fieldMethodMap.putAll(sortedMethodMap);
    }
}

这个工具类涉及到一个自定义的接口ExcelValueFormatter，它用来实现将不同类型的java属性映射到不同的excel单元格格式。由于ExcelValueFormatter是个接口，所以你可以实现它，自定义不同的映射策略。

public interface ExcelValueFormatter {
  Object formatValue(Class<?> returnType, Object value);
}

本案例提供一个默认的实现类DateValueFormatter，将Date类型的属性转换为格式为yyyy-MM-dd的文本。

@Data
@AllArgsConstructor
public class DateValueFormatter implements ExcelValueFormatter {
 
  private String dateFormat;
 
  @Override
  public Object formatValue(Class<?> returnType, Object value) {
    if (returnType.equals(Date.class)) {
      return DateTimeFormatter.ofPattern(dateFormat)
          .format(toLocalDateTime((Date) value));
    } else {
      return value;
    }
  }
 
  private static LocalDateTime toLocalDateTime(Date date) {
    Instant instant = date.toInstant();
    ZoneId zoneId = ZoneId.systemDefault();
    return instant.atZone(zoneId).toLocalDateTime();
  }
}

为了能让列顺序不被打乱，我们最直接的方法就是给成员指定它的位置是几，也就是定义顺序编号，首先声明一个注解@Order，它只能用于类成员上：

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Order {
 
    int value();
}

然后通过反射获取每个成员上的该注解，并进行排序：

  private static void sortMethodMap(Field[] fields, Map<String, Method> fieldMethodMap) {
    Set<String> fieldSet = fieldMethodMap.keySet();
    List<Field> fieldList = Arrays.stream(fields).filter(e -> fieldSet.contains(e.getName()))
            .collect(Collectors.toList());
    fields = fieldList.toArray(new Field[]{});
    Arrays.sort(fields, (o1, o2) -> {
      Order order1 = o1.getAnnotation(Order.class);
      Order order2 = o2.getAnnotation(Order.class);
      if (order1 == null && order2 == null) { //均不含注解时不排序
        return 0;
      }
      if (order1 == null) { //order1 == null && order2 != null 仅有一个含有注解时，默认排到不含注解的后面
        return -1;
      }
      if (order2 == null) { //order1 != null && order2 == null 仅有一个含有注解时，默认排到不含注解的后面
        return 1;
      }
      return order1.value() - order2.value();//均含有注解时，按照注解值从小到大排序
    });
    Map<String, Method> sortedMethodMap = new LinkedHashMap<>();
    Arrays.stream(fields).forEach(e -> {
      String key = e.getName();
      sortedMethodMap.put(key, fieldMethodMap.get(key));
    });
    fieldMethodMap.clear();
    fieldMethodMap.putAll(sortedMethodMap);
  }

最后做个简单的测试。

在Student的成员上添加@Order指定相对位置：

    @Data
    @AllArgsConstructor
    public static class Student {
        @ExcelTitle("id")
        @Order(1)
        private Integer id;
 
        @ExcelTitle("姓名")
        @Order(2)
        private String name;
 
        @ExcelTitle("薪水")
        @Order(4)
        private Double salary;
 
        @ExcelTitle("生日")
        @Order(3)
        private Date birthDay;
    }

测试代码：

    public static void main(String[] args) {
        try {
            List<Student> students = new ArrayList<>();
            for (int i = 0; i < 100; i++) {
                students.add(new Student(i, "member" + i, i * 55D, new Date()));
            }
            ExcelWriter.writeToExcel(students, Student.class, "/Users/Downloads/test.xlsx");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
 
    @Data
    @AllArgsConstructor
    public static class Student {
        @ExcelTitle("id")
        private Integer id;
 
        @ExcelTitle("姓名")
        private String name;
 
        @ExcelTitle("薪水")
        private Double salary;
 
        @ExcelTitle("生日")
        private Date birthDay;
    }

结果如下：