java最简单的导出excel 表格(一学就会)

16.Excel导出数据（一学就会）

① 导入 Hutool 和 poi 的依赖（版本不易太低）

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

② 前端 只需要一个a标签即可

 <a href="/api/export">导出excel</a>

③ 准备 sql 脚本 表名为users

/*
 Navicat Premium Data Transfer

 Source Server         : 123
 Source Server Type    : MySQL
 Source Server Version : 80029 (8.0.29)
 Source Host           : 192.168.11.30:3306
 Source Schema         : mall

 Target Server Type    : MySQL
 Target Server Version : 80029 (8.0.29)
 File Encoding         : 65001

 Date: 14/03/2024 16:22:41
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, '11', 11, '11');
INSERT INTO `users` VALUES (2, '22', 22, '22');

SET FOREIGN_KEY_CHECKS = 1;

④ 准备实体类 Users

package com.beiyou.model;
import lombok.Data;
/**
* 用户表;
* @date : 2024-3-1
*/
@Data
public class Users {
   private Integer id ;
   private String name ;
   private Integer age ;
   private String sex ;
}

⑤ UsersController

package com.beiyou.controller;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.beiyou.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import com.beiyou.model.*;
import com.beiyou.service.UserService;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/api/export")
public class UsersController{
    @Autowired
    private UsersService usersService;
    /**
     * 导出Excel
     * @param response
     * @throws IOException
     */
    @GetMapping
    public void exportExcel(HttpServletResponse response) throws IOException {
        // 创建Excel写入器   参数 true 表示追加数据，即在已有的 Excel 文件上追加新数据。如果设为 false，则会覆盖已有的数据。
        List<Users> users = usersService.selectAll();
        // 创建ExcelWriter对象
        ExcelWriter writer = ExcelUtil.getWriter(true);
        int i = 0;
        while (true) {
            List<Users> list = users.stream().skip(i * 100000).limit(100000).parallel().collect(Collectors.toList());
            if (list.isEmpty()) {
                break;
            }
            writer.setSheet("users" + i);
            // 写入表头
            writer.addHeaderAlias("id", "Id");
            writer.addHeaderAlias("name", "姓名");
            writer.addHeaderAlias("age", "年龄");
            writer.addHeaderAlias("sex", "性别");
            // 写入当前批次的数据
            writer.write(list, true);
            i++;
        }
        //response为HttpServletResponse对象   设置响应的内容类型为Excel文件
//        response.setContentType("application/xlsx;charset=utf-8");
        response.setCharacterEncoding("UTF-8");

        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        //设置响应头，告诉浏览器以附件形式下载文件，文件名为test.xlsx。这样设置可以让浏览器弹出文件下载对话框。
        /**
         * 这种会导致中文表明变下划线
         */
//        response.setHeader("Content-Disposition", "attachment;filename="+"哈哈.xlsx");
        /**
         * 这种不会
         */
        response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode("学生评教数据导出表.xlsx", "UTF-8"));
        //获取响应输出流，它是用于将响应的数据发送给客户端的流。
        ServletOutputStream out = response.getOutputStream();
        //将Excel数据写入输出流。第二个参数为true表示追加写入，即将数据追加到已有的Excel文件中。
        writer.flush(out, true);
        writer.close();
        //关闭输出流
        out.close();
    }

}

⑥ UsersService

package com.beiyou.service;
/**
 * 用户表;(205_user)表服务实现类
 * @date : 2024-3-1
 */
@Service
@Slf4j
public class UsersService {
     
    @Autowired
    private UserDao userDao;
    public List<Users> selectAll() {
        return userDao.selectAll();
    }
}

⑦ UsersDao

    List<Users> selectAll();

⑧ UsersMapper.xml

<!--    导出excel-->
    <select id="selectAll" resultType="com.beiyou.model.Users">
        select * from users
    </select>
```![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/14c58e3f7f4344dd9a3ea21d3bcbcf55.png)