Java中利用POI优雅的导出Excel文件详解

  
   org.apache.poi
   poi-ooxml
   4.1.2
  

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelHeader {

 /**
  * 表头
  * @return
  */
 String value() default "";

 /**
  * 列索引
  * @return
  */
 int columnIndex() default 0;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {

 @ExcelHeader(value = "账号")
 private String username;

 @ExcelHeader(value = "密码")
 private String password;

 @ExcelHeader(value = "生日")
 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
 @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
 private LocalDateTime birthday;

 // 该字段没有添加注解, Excel不导出
 private Integer age;

 public User(String username, String password, LocalDateTime birthday) {
  this.username = username;
  this.password = password;
  this.birthday = birthday;
 }
}

@Slf4j
class IdcPoiApplicationTests {

 @Test
 void contextLoads() throws NoSuchFieldException, IllegalAccessException, IOException {
  // 创建模拟数据
  User user1 = new User("admin", "123456", LocalDateTime.now());
  User user2 = new User("test", "123456", LocalDateTime.now());
  List users = Arrays.asList(user1, user2);

  // 文件路径
  String basePath = "/Users/cuishiying/Documents/spring-cloud-learning/idc-poi/oss/";
  String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls";

  // 将Excel写入文件
  HSSFWorkbook workbook = exportExcel(users, User.class);
  workbook.setSheetName(0,"sheetName");//设置sheet的Name

  // 无论是通过HttpServletResponse导出还是导出到本地磁盘,本质都是IO操作，所以这里将IO操作提取到外层。
  workbook.write(new File(basePath + File.separator + fileName));
 }

 /**
  *
  * @param data 需要导出的数据
  * @param clz 数据对应的实体类
  * @param  泛型
  * @return Excel文件
  * @throws NoSuchFieldException
  * @throws IllegalAccessException
  */
 public  HSSFWorkbook exportExcel(List data, Class clz) throws NoSuchFieldException, IllegalAccessException {

  Field[] fields = clz.getDeclaredFields();
  List headers = new LinkedList<>();
  List variables = new LinkedList<>();

  // 创建工作薄对象
  HSSFWorkbook workbook=new HSSFWorkbook();//这里也可以设置sheet的Name
  // 创建工作表对象
  HSSFSheet sheet = workbook.createSheet();
  // 创建表头
  Row rowHeader = sheet.createRow(0);

  // 表头处理
  for (int h = 0; h  aClass = t.getClass();
   // 填充列数据
   for (int j = 0; j