今天记录一下基于Hutool的poi导出excel表格，这次代码是升级更新版本的代码。推荐各位收藏

1.先看导出excel模版截图

2.准备环境

我贴出整个的pom文件，如果不需要的，可以根据自己要求进行删减

<?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 http://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.liu</groupId>
  <artifactId>test-springboot</artifactId>
  <version>1.0-SNAPSHOT</version>

  <properties>
    <maven.compiler.source>8</maven.compiler.source>
    <maven.compiler.target>8</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>

    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.8.21</version>
    </dependency>

    <!--mp依赖-->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.2.0</version>
    </dependency>

    <!--mysql-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.39</version>
    </dependency>

    <!--druid-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.21</version>
    </dependency>

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

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>5.2.3</version>
      <exclusions>
        <!-- 排除老旧Xalan处理器 -->
        <exclusion>
          <groupId>xalan</groupId>
          <artifactId>xalan</artifactId>
        </exclusion>
      </exclusions>
    </dependency>

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

    <dependency>
      <groupId>org.apache.xmlbeans</groupId>
      <artifactId>xmlbeans</artifactId>
      <version>5.1.1</version>
    </dependency>

    <dependency>
      <groupId>com.deepoove</groupId>
      <artifactId>poi-tl</artifactId>
      <version>1.11.1</version>
    </dependency>

    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.11.0</version>
    </dependency>

    <dependency>
      <groupId>com.squareup.okhttp3</groupId>
      <artifactId>okhttp</artifactId>
      <version>4.11.0</version>
    </dependency>

    <dependency>
      <groupId>com.google.code.gson</groupId>
      <artifactId>gson</artifactId>
      <version>2.10.1</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-security</artifactId>
    </dependency>

    <dependency>
      <groupId>io.jsonwebtoken</groupId>
      <artifactId>jjwt</artifactId>
      <version>0.9.1</version>
    </dependency>

    <dependency>
      <groupId>net.coobird</groupId>
      <artifactId>thumbnailator</artifactId>
      <version>0.4.20</version>
    </dependency>

    <dependency>
      <groupId>org.bouncycastle</groupId>
      <artifactId>bcprov-jdk18on</artifactId>
      <version>1.80</version>
    </dependency>

    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <scope>runtime</scope>
    </dependency>

  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <configuration>
          <excludes>
            <exclude>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
            </exclude>
          </excludes>
        </configuration>
      </plugin>
    </plugins>
  </build>

</project>

3.完整的测试代码

package com.liuyang;


import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class AppTest
{
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 阶段1
    @Test
    public void test() throws IOException {

        // 1. 强制使用兼容的XML处理器
        System.setProperty("javax.xml.transform.TransformerFactory",
                "com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl");
       
        String sql = "SELECT \n" +
                "    b.bjmc,\n" +
                "    c.ygxm,\n" +
                "    MAX(CASE WHEN d.homework_id = 8 THEN d.homework_score END) AS \"ES6\",\n" +
                "    MAX(CASE WHEN d.homework_id = 10 THEN d.homework_score END) AS \"VUE\",\n" +
                "\t\tMAX(CASE WHEN d.homework_id = 14 THEN d.homework_score END) AS \"Form\",\n" +
                "\t\tMAX(CASE WHEN d.homework_id = 17 THEN d.homework_score END) AS \"Bigtable\",\n" +
                "\t\tMAX(CASE WHEN e.exam_id = 2 THEN e.exam_score END) AS \"exam_1\",\n" +
                "\t\tMAX(CASE WHEN e.exam_id = 1 THEN e.exam_score END) AS \"exam_2\",\n" +
                "\t\tMAX(CASE WHEN f.proj_id = 5 THEN f.proj_score END) AS \"proj\",\n" +
                "\t\tMAX(CASE WHEN g.course_id = 1 THEN g.course_score END) AS \"summary\"\n" +
                "FROM \n" +
                "    yhykt_gp_trainee_class_relative a\n" +
                "    JOIN yhykt_gp_classes b ON a.bjbh = b.bjbh\n" +
                "    JOIN yhykt_gp_trainee c ON a.ygbh = c.ygbh\n" +
                "    JOIN yhykt_gp_trainee_homework d ON c.ygbh = d.ygbh AND b.bjbh = d.bjbh\n" +
                "\t\tJOIN yhykt_gp_trainee_exam e on c.ygbh = e.ygbh and b.bjbh = e.bjbh\n" +
                "\t\tJOIN yhykt_gp_trainee_project f on c.ygbh = f.ygbh and b.bjbh = f.bjbh\n" +
                "\t\tJOIN yhykt_gp_trainee_course_relative g on c.ygbh = g.ygbh and b.bjbh = g.bjbh\n" +
                "WHERE \n" +
                "    b.bjbh = '202306280000001'\n" +
                "    AND d.homework_id IN (8, 10, 14, 17)\n" +
                "\t\tand e.exam_id in (2,1)\n" +
                "\t\tand f.proj_id in (5)\n" +
                "\t\tand g.course_id in (1)\n" +
                "GROUP BY \n" +
                "    b.bjmc, c.ygxm, g.course_score\n" +
                "ORDER BY \n" +
                "    g.course_score desc, b.bjmc, c.ygxm;";

        // 使用 try-with-resources 自动关闭资源
        // 使用getBigWriter()创建支持大数据量的XLSX格式写入器
        try (ExcelWriter writer = ExcelUtil.getBigWriter();
             FileOutputStream outputStream = new FileOutputStream("D:/阶段1.xlsx")) {

            // 写入表头
            writer.writeRow(Arrays.asList("班级编号", "姓名", "ES6作业", "VUE作业", "Form作业", "table作业", "作业汇总", "前端笔试", "1阶段项目", "1阶段汇总"));

            // 查询数据
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);

            // 写入数据行
            for (Map<String, Object> map : maps) {
                BigDecimal exam_1 = (BigDecimal) map.get("exam_1");
                BigDecimal exam_2 = (BigDecimal) map.get("exam_2");
                BigDecimal es6 = (BigDecimal) map.get("ES6");
                BigDecimal vue = (BigDecimal) map.get("VUE");
                BigDecimal bigtable =(BigDecimal)map.get("Bigtable");
                writer.writeRow(Arrays.asList(
                        map.get("bjmc"),
                        map.get("ygxm"),
                        map.get("ES6"),
                        map.get("VUE"),
                        map.get("Form"),
                        map.get("Bigtable"),
                        es6.multiply(new BigDecimal("0.3")).add(vue.multiply(new BigDecimal("0.3"))).add(bigtable.multiply(new BigDecimal("0.4"))),
                        exam_1,
                        map.get("proj"),
                        map.get("summary")
                ));
            }

            // 刷新到文件
            writer.flush(outputStream, true);
        } // 自动关闭 writer 和 outputStream
    }


}