easyexcel 导出 excel 表格数据

• 创建一个 Springboot 项目

• easyexcel 导出 excel 表格数据

• 创建之后，pom.xml 配置

• <?xml version="1.0" encoding="UTF-8"?>
  <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <parent>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-parent</artifactId>
          <version>2.7.5</version>
          <relativePath/> <!-- lookup parent from repository -->
      </parent>
      <groupId>com.example</groupId>
      <artifactId>shangchuangExcel</artifactId>
      <version>0.0.1-SNAPSHOT</version>
      <name>shangchuangExcel</name>
      <description>shangchuangExcel</description>
      <properties>
          <java.version>1.8</java.version>
      </properties>
      <dependencies>
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-jdbc</artifactId>
          </dependency>
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-web</artifactId>
          </dependency>
  
          <dependency>
              <groupId>com.mysql</groupId>
              <artifactId>mysql-connector-j</artifactId>
              <scope>runtime</scope>
          </dependency>
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-test</artifactId>
              <scope>test</scope>
          </dependency>
  
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>2.2.3</version>
          </dependency>
          <dependency>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
              <version>RELEASE</version>
              <scope>compile</scope>
          </dependency>
          <dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.12</version>
              <scope>compile</scope>
          </dependency>
  
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>3.0.5</version>
          </dependency>
  
  
          <dependency>
              <groupId>com.alibaba</groupId>
              <artifactId>easyexcel</artifactId>
              <version>2.2.10</version>
          </dependency>
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi</artifactId>
              <version>3.17</version>
          </dependency>
          <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml</artifactId>
              <version>3.17</version>
          </dependency>
  
  
  
  
      </dependencies>
  
  
  
      <build>
          <plugins>
              <plugin>
                  <groupId>org.springframework.boot</groupId>
                  <artifactId>spring-boot-maven-plugin</artifactId>
              </plugin>
          </plugins>
      </build>
  
  </project>

  创建一个实体类 Student.java

• @Data
  @ToString
  @AllArgsConstructor
  @NoArgsConstructor  // 一定要有无参构造方法
  public class Student {
      @ExcelProperty(value = "姓名")
      private String name;
  
      @ExcelProperty(value = "性别", converter = SexConverter.class)
      private Integer sex;
  
      @ExcelProperty(value = "生日")
      private Date birthday;
  
      @ExcelProperty(value = "体重KG")
      // 会以字符串形式生成单元格，要计算的列不推荐
      @NumberFormat("0.##")
      private BigDecimal weight;
  
  //    @ContentStyle(dataFormat = 2)
  //    private BigDecimal weight2;
  
      private String memo;
  }

  创建    generateStudentUtil.java

• public class generateStudentUtil {
      public static List<Student> generateStudent(int size) {
          List<Student> stues = new ArrayList<>();
          for (int i = 0; i < size; i++) {
              stues.add(new Student("姓名" + i, (int) (Math.random() * 2), randomDate(), randomWeight(), "备注"));
          }
          return stues;
      }
  
      public static Date randomDate() {
          LocalDateTime localDateTime = LocalDateTime.of(randomInt(1990, 2022), randomInt(1, 12), randomInt(1, 28), randomInt(0, 23), randomInt(0, 59), randomInt(0, 59), randomInt(0, 999));
          ZonedDateTime zdt = localDateTime.atZone(ZoneId.systemDefault());
          return Date.from(zdt.toInstant());
      }
  
      public static int randomInt(int min, int max) {
          int de = max - min;
          // 二进制长度
          int bitCount = Integer.toBinaryString(de).length();
          int ans = 0;
          do {
              ans = 0;
              for (int i = 0; i < bitCount; i++) {
                  ans += random0_1() << i;
              }
          } while (ans > de);
          return ans + min;
      }
  
      public static int random0_1() {
          return (int) (Math.random() * 2);
      }
  
      public static BigDecimal randomWeight() {
          return BigDecimal.valueOf((Math.random() * 10));
      }
  }

  创建一个 SexConverter.java 

• package com.example.shangchuangexcel.entity;
  
  import com.alibaba.excel.converters.Converter;
  import com.alibaba.excel.enums.CellDataTypeEnum;
  import com.alibaba.excel.metadata.CellData;
  import com.alibaba.excel.metadata.GlobalConfiguration;
  import com.alibaba.excel.metadata.property.ExcelContentProperty;
  
  /*
  * 显示男女
  * */
  public class SexConverter implements Converter<Integer> {
  
      @Override
      public Class<Integer>supportJavaTypeKey() {
          return Integer.class;
      }
  
      @Override
      public CellDataTypeEnum supportExcelTypeKey() {
          return CellDataTypeEnum.STRING;
      }
  
      @Override
      public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
          return "男".equals(cellData.getStringValue()) ? 1 : 0;
      }
  
      @Override
      public CellData<String> convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
          return new CellData<>(integer.equals(1)? "男" : "女");
      }
  }

  创建测试类 测试一下是否成功

• BaseTest.java

• package com.example.shangchuangexcel.test;
  
  import com.alibaba.excel.EasyExcel;
  import com.alibaba.excel.ExcelWriter;
  import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
  
  import java.util.function.Consumer;
  
  public class BaseTest {
      /**
       * 导出方法
       *
       * @param fileName       文件
       * @param writerConsumer consumer
       */
      public static void export(String fileName, Consumer<ExcelWriter> writerConsumer) {
          ExcelWriter writer = EasyExcel.write(fileName)
                  .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                  .build();
          writerConsumer.accept(writer);
          writer.finish();
      }
  }

   

  TestEasyExcel.java

• package com.example.shangchuangexcel.test;
  
  import com.alibaba.excel.EasyExcel;
  import com.alibaba.excel.ExcelWriter;
  import com.example.shangchuangexcel.entity.Student;
  import org.junit.Test;
  
  import java.util.function.Consumer;
  
  import static com.example.shangchuangexcel.entity.generateStudentUtil.generateStudent;
  
  public class TestEasyExcel extends BaseTest{
      @Test
      public void export1() {
          Consumer<ExcelWriter> consumer = writer -> {
              writer.write(generateStudent(10), EasyExcel.writerSheet("学生信息")
                      .head(Student.class)
                      .build());
          };
          export("E:/报表.xlsx", consumer);
      }
  }

  export("E:/报表.xlsx", consumer);  // 这是表的路径，如果执行后，第二次要删掉路径导出的表在继续执行

执行成功