Java将对象写入Excel文件

本文通过poi依赖实现将java对象写入excel之中，核心理念是通过反射获取Java对象的getter方法和属性，通过getter方法获取要写入excel中的值，再通过属性上的自定义注解获取excel标题行，然后以文件流的方式写入excel。话不多说，直接上码。

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

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

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

@Slf4j
public class ExcelWriter {

  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<>();
    Map<String, Method> fieldMethodMap = buildFieldMethodMap(clazz);
    Map<String, String> fieldTitleMap = buildFieldTitleMap(clazz, fieldMethodMap);
    List<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++) {
        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());
    }
  }
}

这个工具类涉及到一个自定义的接口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();
  }
}

代码用到了lombok，还需要log4j，slf4j，总体依赖如下：

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.8</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.25</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.25</version>
    </dependency>

测试代码：

    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/zhuhuiyuan/Downloads/2.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;
    }

测试结果：

关于排序

有不少朋友留言或私信我说这个工具类将对象写入excel后列是混乱的，这是因为反射获取的成员顺序并不是跟源文件中成员顺序一致导致的。解决方案本来打算是留给读者自己思考的，鉴于文章时间已久，也没必要留着这个瑕疵不放。我这里给出个解决方案，如果你有更好的想法，不妨提出来我们一起探讨。

为了能让列顺序不被打乱，我们最直接的方法就是给成员指定它的位置是几，也就是定义顺序编号，首先声明一个注解@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);
  }

最后我们在初始的writeToExcel方法中的第二行前后添加两行代码即可，如下：

    ...
    @SuppressWarnings("MismatchedQueryAndUpdateOfCollection")
    List<Object[]> dataList = new ArrayList<>();
    Field[] fields = getAllFields(clazz);
    Map<String, Method> fieldMethodMap = buildFieldMethodMap(clazz);
    sortMethodMap(fields, fieldMethodMap);
    ...

最后做个简单的测试。

在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;
    }

再运行main方法：

可以看出顺序确实变了。