hutool工具导出excel代码示例

一、hutool工具导出excel代码示例

1.1、pom.xml依赖包

 <!--excel导出引入poi-->
<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>5.1.0</version>
  </dependency>
1
2
3
4
5
6

1.2、controller层代码(以查询user表数据为列导出excel)

• controller层代码

  package com.xz.thread.controller;
  
  import com.xz.thread.service.UserService;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.web.bind.annotation.RequestMapping;
  import org.springframework.web.bind.annotation.RestController;
  import javax.servlet.http.HttpServletResponse;
  /**
   * @description:
   * @author: xz
   * @create: 2022-08-23
   */
  @RestController
  @RequestMapping("/user")
  public class UserController {
  
      @Autowired
      private UserService userService;
  
      /**
       * 导出excel
       * */
      @RequestMapping("/exportExcel")
      public void exportExcel(HttpServletResponse response, String fileName){
          userService.exportExcel(response,fileName);
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27

1.3、service层代码(以查询user表数据为列导出excel)

• service层代码

  package com.xz.thread.service;
  
  import javax.servlet.http.HttpServletResponse;
  /**
   * @description:
   * @author: xz
   * @create: 2022-08-23
   */
  public interface UserService {
      /**
       * 导出excel
       * */
      void exportExcel(HttpServletResponse response, String fileName);
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

1.4、service实现层代码(以查询user表数据为列导出excel)

• service实现层代码

  package com.xz.thread.service.impl;
  
  import com.xz.thread.domain.UserEntity;
  import com.xz.thread.service.UserService;
  import com.xz.thread.util.excel.HuToolExcelUtil;
  import org.springframework.stereotype.Service;
  import javax.servlet.http.HttpServletResponse;
  import java.util.*;
  /**
   * @description:
   * @author: xz
   * @create: 2022-08-23
   */
  @Service
  public class UserServiceImpl implements UserService {
      @Override
      public void exportExcel(HttpServletResponse response, String fileName) {
          //模拟从数据库查询user数据，返回 List>
          List<Map<String, Object>> dataList =new ArrayList<>();
          for(int i=0;i<5;i++){
              Map<String, Object> map=new HashMap<>();
              map.put("name","张三"+i);
              map.put("age","2"+i);
              map.put("createtime",new Date());
              dataList.add(map);
          }
          //通过反射方式获取实体类头部信息
          Map<String, String> headerAlias = HuToolExcelUtil.getHeaderAlias(UserEntity.class);
          //调用导出excel工具类方法
          HuToolExcelUtil.exportExcel(response,fileName,headerAlias, dataList);
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32

1.5、实体对象代码(以查询user表数据为列导出excel)

• 实体对象代码

  package com.xz.thread.domain;
  
  import com.fasterxml.jackson.annotation.JsonFormat;
  import com.xz.thread.util.excel.ExcelField;
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  import java.util.Date;
  /**
   * @description:
   * @author: xz
   * @create: 2022-08-23
   */
  @NoArgsConstructor
  @AllArgsConstructor
  @Data
  public class UserEntity {
  
      private String id;
  
      @ExcelField(name="姓名")
      private String name;
  
      @ExcelField(name="年龄")
      private int age;
  
      @ExcelField(name="创建时间")
      @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
      private Date createtime;//任务创建时间
  
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31

1.6、hutool工具导出excel工具类代码

• hutool工具导出excel工具类

  package com.xz.thread.util.excel;
  
  import cn.hutool.poi.excel.ExcelUtil;
  import cn.hutool.poi.excel.ExcelWriter;
  import lombok.extern.slf4j.Slf4j;
  
  import javax.servlet.http.HttpServletResponse;
  import java.io.IOException;
  import java.lang.reflect.Field;
  import java.util.LinkedHashMap;
  import java.util.List;
  import java.util.Map;
  
  /**
   * @description:
   * @author: xz
   * @create: 2022-08-23
   */
  @Slf4j
  public class HuToolExcelUtil {
      /**
       * 导出Excel
       *
       * @param response        响应对象
       * @param fileName        文件名
       * @param headerAlias     字段别名
       * @param data            导出数据
       */
      public static <E> void exportExcel(HttpServletResponse response, String fileName, Map<String, String> headerAlias, List<E> data) {
          ExcelWriter writer = ExcelUtil.getWriter();
          //设置字段别名
          writer.setHeaderAlias(headerAlias);
          writer.write(data, true);
          // 设置所有列为自动宽度，不考虑合并单元格
          writer.autoSizeColumnAll();
          try {
              fileName = new String((fileName + ".xls").getBytes(), "ISO-8859-1");
              response.setHeader("content-Type", "application/vnd.ms-excel;charset=utf-8");
              response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
              // 将Excel写入到response的输出流中，并关闭输出流
              writer.flush(response.getOutputStream(), true);
          } catch (IOException e) {
              log.error("处理Excel文件异常,异常详情:",e);
          } finally {
              writer.close();
          }
      }
      /** 
       - @Description: 通过反射方式获取实体类头部信息
       - @Author: xz
      */ 
      public static Map<String, String> getHeaderAlias(Class<?> tClass) {
          Field[] declaredFields = tClass.getDeclaredFields();
          Map<String, String> stringMap = new LinkedHashMap<>();
          for (Field declaredField : declaredFields) {
              declaredField.setAccessible(true);
              ExcelField annotation = declaredField.getAnnotation(ExcelField.class);
              if (annotation != null) {
                  String name = annotation.name();
                  stringMap.put(declaredField.getName(), name);
              }
          }
          return stringMap;
      }
  
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61
  62
  63
  64
  65
  66

1.7、自定义excel列标题注解类

• 自定义excel列标题注解类

  package com.xz.thread.util.excel;
  
  import java.lang.annotation.*;
  /**
   * @description: 自定义excel注解
   * @author: xz
   * @create: 2022-08-23
   */
  @Documented
  @Inherited
  @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
  @Retention(RetentionPolicy.RUNTIME)
  public @interface ExcelField {
      /**
       * 别名，excel表头名称
       */
      String name() default "";
  
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19

二、浏览器访问导出excel成功截图

• 导出excel成功截图如下图所示：