- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

2、SpringBoot集成EXCEL导出导入

yd_272031863 发表于 2025/01/09 13:51:43 2025/01/09

380 0 0

【摘要】简易的excel导出

2.1、excel简单导出导入

1.1、依赖pom.xml

        <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>

        <!--easypoi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.0.0</version>
        </dependency>

2.1.2、配置文件 application.yml

server:
  port: 8697

spring:
  main:
    allow-bean-definition-overriding: true

  servlet:
    multipart:
      location=/your_temp_directory
  mvc:
    view:
      prefix: /WEB-INF/jsp/
      suffix: .jsp
  datasource:
    url: jdbc:mysql://localhost:3306/lxx?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

2.1.3、工具类

package com.lxx.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
 
import java.util.Map;
import java.util.NoSuchElementException;
 
public class ExcelUtil {
 
    protected static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final String SPLIT = ".";
 
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }
 
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }
 
    public static <T> List<T> importExcelMore(MultipartFile file, Class<T> pojoClass,ImportParams params){
        if (file == null){
            return null;
        }
 
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
 
    public static Workbook getWorkbook(MultipartFile file) {
        Workbook workbook=null;
        try {
            // 获取Excel后缀名
            String fileName = file.getOriginalFilename();
            if (StringUtils.isEmpty(fileName) || fileName.lastIndexOf(SPLIT) < 0) {
                logger.warn("解析Excel失败，因为获取到的Excel文件名非法！");
                return null;
            }
            String fileType = fileName.substring(fileName.lastIndexOf(SPLIT) + 1, fileName.length());
            // 获取Excel工作簿
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(file.getInputStream());
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(file.getInputStream());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
}

2.1.4、实体类entity

package com.lxx.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
 
public class User {
    @Excel(name = "学号", orderNum = "0")
    private Integer id;
    @Excel(name = "姓名", orderNum = "1")
    private String  userName;
    @Excel(name = "年龄", orderNum = "2")
    private String  userAge;
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", userAge='" + userAge + '\'' +
                '}';
    }
    public User() {
    }
    public User(Integer id, String userName, String userAge) {
        this.id = id;
        this.userName = userName;
        this.userAge = userAge;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserAge() {
        return userAge;
    }
    public void setUserAge(String userAge) {
        this.userAge = userAge;
    }
}

2.1.5、控制层controller

package com.lxx.controller;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.lxx.entity.User;
import com.lxx.util.ExcelUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RestController
public class testController {

    /**
     * 导出
     * @param response
     */
    @GetMapping("exportExcel")
    public void exportExcel(HttpServletResponse response) {
        List<User> userListOne = new ArrayList<>();
        User user1 = new User();
        user1.setId(1001);
        user1.setUserName("JCccc");
        user1.setUserAge("18");
        userListOne.add(user1);

        List<User> userListTwo = new ArrayList<>();
        User user2 = new User();
        user2.setId(2001);
        user2.setUserName("Mike");
        user2.setUserAge("18");
        userListTwo.add(user2);

        // 多个sheet配置参数---------总共导出的数据List
        final List<Map<String, Object>> sheetsList = Lists.newArrayList();//导出表的集合

        final String sheetNameOne = "sheet1-1班";//分表的名称-----第一个sheet
        Map<String, Object> exportMapOne = Maps.newHashMap();
        final ExportParams exportParamsOne = new ExportParams(null, sheetNameOne, ExcelType.HSSF);
        // 以下3个参数为API中写死的参数名 分别是sheet配置/导出类（注解定义）/数据集
        exportMapOne.put("title", exportParamsOne);
        exportMapOne.put("entity", User.class);//实体类
        exportMapOne.put("data", userListOne);

        final String sheetNameTwo = "sheet2-2班";//分表的名称-----第二个sheet
        Map<String, Object> exportMapTwo = Maps.newHashMap();
        final ExportParams exportParamsTwo = new ExportParams(null, sheetNameTwo, ExcelType.HSSF);
        // 以下3个参数为API中写死的参数名 分别是sheet配置/导出类（注解定义）/数据集
        exportMapTwo.put("title", exportParamsTwo);
        exportMapTwo.put("entity", User.class);//实体类
        exportMapTwo.put("data", userListTwo);

        // 加入多sheet配置列表-----每一个sheet就对应一个 Map，都加进去
        sheetsList.add(exportMapOne);
        sheetsList.add(exportMapTwo);

        //导出操作-----导出
        ExcelUtil.exportExcel(sheetsList, "userList.xls", response); //导出的总表的名称
    }

    /**
     * 导入
     * @param multipartFile
     */
    @PostMapping("importExcel")
    public void importExcel(@RequestParam("file") MultipartFile multipartFile) {
        try {
            //标题占几行
            Integer titleRows = 0;
            //表头占几行
            Integer headerRows = 1;
            //把excel文件转成workbook
            Workbook workBook = ExcelUtil.getWorkbook(multipartFile);

            //获取sheet数量
            int sheetNum = workBook.getNumberOfSheets();
            ImportParams params = new ImportParams();

            //表头在第几行
            params.setTitleRows(titleRows);//锁定数据从哪开始遍历
            params.setHeadRows(headerRows);//锁定数据从哪开始遍历

            for (int numSheet = 0; numSheet < sheetNum; numSheet++) {
                String sheetName = workBook.getSheetAt(numSheet).getSheetName();
                //第几个sheet页
                params.setStartSheetIndex(numSheet);
                List<User> result = ExcelUtil.importExcelMore(multipartFile, User.class, params);//捐定导入的sheet表
                System.out.println("sheetNum=" + numSheet + "   sheetName=" + sheetName);
                System.out.println("导入的数据=" + result.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.2、excel和mybatis-plus导入导出

2.2.1、依赖pom.xml

<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>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

        <!-- MyBatis-Plus 增强版 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version> <!-- 请检查最新版本 -->
        </dependency>
        <!-- 数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.21</version> <!-- 请检查最新版本 -->
        </dependency>
        <!-- 数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version> <!-- 请检查最新版本 -->
        </dependency>

2.2.2、配置文件 application.yml

server:
  port: 8678
  servlet:
    context-path: /api

spring:
  mvc:
    view:
      prefix: /WEB-INF/jsp/
      suffix: .jsp
  datasource:
    url: jdbc:mysql://localhost:3306/lxx?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

2.2.3、工具类util

package com.lxx.utils;
 
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
 
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
 
import java.util.Map;
import java.util.NoSuchElementException;
 
public class ExcelUtil {
 
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
 
    }
    //导出
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }
 
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }
 
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }
 
    //导入
    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }
}

2.2.4、实体类entity

package com.lxx.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

@Data
public class User {
    @Excel(name = "学号", orderNum = "0")
    private Integer id;
    @Excel(name = "姓名", orderNum = "1")
    private String  userName;
    @Excel(name = "年龄", orderNum = "2")
    private String  userAge;
}

2.2.5、持久层 mapper

package com.lxx.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lxx.entity.User;

import org.springframework.stereotype.Repository;

@Repository//在Spring中进行注册
public interface UserMapper extends BaseMapper<User> {
    
}

2.2.6、持久层的映射文件mapper

位置在resources-->mapper-->UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lxx.mapper.UserMapper">

</mapper>

2.2.7、业务层service

ackage com.lxx.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.lxx.entity.User;

import java.util.List;

public interface UserService extends IService<User> {

}

2.2.8、业务层实现类serviceImpl

package com.lxx.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.lxx.entity.User;
import com.lxx.mapper.UserMapper;
import com.lxx.service.UserService;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService {
    
}

2.2.9、控制层controller

package com.lxx.controller;

import com.lxx.entity.User;
import com.lxx.service.UserService;

import com.lxx.utils.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@RestController
@RequestMapping("test2")
public class TestController {

    @Autowired
    private UserService userService;
    @GetMapping("exportExcel")
    public void export(HttpServletResponse response){
        List<User> list = userService.list();
        //导出操作
        ExcelUtil.exportExcel(list,"用户信息","sheet1",User.class,"testDATA.xls",response);
    }

    @RequestMapping("importExcel")
    public String importExcel(){
        String filePath = "C:\\Users\\Administrator\\Downloads\\testDATA.xls";
        //解析excel，
        List<User> userList = ExcelUtil.importExcel(filePath,1,1,User.class);
        //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
        System.out.println("导入数据一共【"+userList.size()+"】行");

        //批量插入
        userService.saveBatch(userList);
        //查询全部
        List<User> userList2 = userService.list();
        return userList2.toString();
    }
}

2.2.10、启动类加上包扫描器

package com.lxx;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan(basePackages = "com.lxx.mapper")
public class Excel2Application {

    public static void main(String[] args) {
        SpringApplication.run(Excel2Application.class, args);
    }

}

2.3、excel根据模板进行导出

2.3.1、pom.xml

<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>

        <!--mybatis-plus 持久层-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>

        <!-- 导入和导出-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

2.3.2、配置文件 application.yml

server:
  port: 8678
  servlet:
    context-path: /api

spring:
  mvc:
    view:
      prefix: /WEB-INF/jsp/
      suffix: .jsp
  datasource:
    url: jdbc:mysql://localhost:3306/lxx?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

2.3.3、实体类entity

package com.lxx.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
 
/**
 * @Author: JCccc
 * @Date: 2022-7-14 10:58
 * @Description:
 */
public class UserExcelDTO {
    @Excel(name = "学号", height = 8, width = 13, isImportField = "true")
    private Integer id;
    @Excel(name = "姓名", height = 8, width = 13, isImportField = "true")
    private String  userName;
    @Excel(name = "年龄", height = 8, width = 13, isImportField = "true")
    private String  userAge;
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", userAge='" + userAge + '\'' +
                '}';
    }
    public UserExcelDTO() {
    }
    public UserExcelDTO(Integer id, String userName, String userAge) {
        this.id = id;
        this.userName = userName;
        this.userAge = userAge;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserAge() {
        return userAge;
    }
    public void setUserAge(String userAge) {
        this.userAge = userAge;
    }
}

2.3.4、导出模板

学号姓名年龄

{{$fe: list t.id t.userName t.userAge}}

模板的存放路径：static/学生信息表.xlsx（可以进行更改）

学号	姓名	年龄
{{$fe: list t.id	t.userName	t.userAge}}

2.3.5、控制层controller

package com.lxx.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.google.common.collect.Maps;
import com.lxx.entity.UserExcelDTO;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RestController
public class test3Controller {
    /**
     * excel导出 列表 指定模板
     *
     * @return
     */
    @GetMapping(value = "/exportAssignTemplateExcel")
    public void opportunityExport(HttpServletResponse response) {
        List<UserExcelDTO> exportList = new ArrayList<>();

        UserExcelDTO userExcel1=new UserExcelDTO(1001,"JCccc","18");
        UserExcelDTO userExcel2=new UserExcelDTO(1002,"ACccc","29");
        UserExcelDTO userExcel3=new UserExcelDTO(1003,"GCccc","50");

        exportList.add(userExcel1);
        exportList.add(userExcel2);
        exportList.add(userExcel3);

        Map map = Maps.newHashMap();
        map.put("list", exportList);//这个map，list作为key，数据List作为value，用于给ExcelExportUtil识别遍历。

        //获取导出模板地址
        ClassPathResource classPathResource = new ClassPathResource("static/学生信息表.xlsx");//自定义模板路径
        String path = classPathResource.getPath();
        TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
        Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
        String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
        String fileName = "用户数据"+time+".xlsx";//输出的.xlsx的名称
        try {
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

2.4、封装excel导入导出Utils

package com.lxx.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.lxx.entity.User;
import com.lxx.entity.UserExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author lxx
 * 日期: 2024/8/26
 */
@Slf4j
public class ExcelUtil {
    /**
     * 导出Excel文件
     * @param list List<?> 导出的数据列表
     * @param title String 导出数据的标题
     * @param sheetName String 工作表名称
     * @param pojoClass String 导出数据的VO对象
     * @param excelFileName String 导出文件的名称
     * @param response HttpServletResponse
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                   String excelFileName,
                                   HttpServletResponse response){
        if (list == null || pojoClass == null || excelFileName == null || response == null) {
            throw new IllegalArgumentException("List, POJO class, Excel file name, and HttpServletResponse cannot be null.");
        }
        // 设置导出参数，包括标题和工作表名称。
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(true);

        // 根据数据列表和导出参数生成Excel工作簿。
        // 直接在内存中创建Workbook对象
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);

        try {
            // 设置HTTP响应头，指示浏览器以下载方式打开Excel文件
            // 设置正确的MIME类型对应.xlsx文件
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            // 设置响应头，指定文件名，支持中文文件名
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + URLEncoder.encode(excelFileName, "UTF-8"));
            // 将工作簿写入HTTP响应的输出流中，实现文件导出
            workbook.write(response.getOutputStream());
            // 确保所有数据都被写入到输出流中
            response.flushBuffer();
            // 关闭工作簿，释放资源
            // 关闭Workbook释放资源
            workbook.close();
        } catch (Exception e) {
            log.error("导出Excel文件时发生错误", e);
            // 如果发生任何异常，抛出运行时异常，并包含异常消息。
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 导入数据
     * @param file 导入的excel
     * @param pojoClass 接收的对象.class
     * @param t 封装模板对象，例如：new User("张三"，"男",...)
     * @return List<T>
     * @param <T> 返回的数据类型
     */
    public static <T> List<T> upExcel(MultipartFile file, Class<T> pojoClass,T t){
        ImportParams params = new ImportParams();
        // 标题行数
        params.setTitleRows(1);
        // 表头行数
        params.setHeadRows(1);
        List<T> list;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (Exception e) {
            log.error("导入数据格式错误",e);
            throw new RuntimeException("导入数据格式错误");
        }
        if (list.isEmpty()){
            log.error("导入数据为空");
            throw new RuntimeException("导入数据为空");
        }
        // 判断是否把模板行删除
        if (t.equals(list.get(0))){
            list.remove(0);
        }
        return list;
    }

    /**
     * 下载导入模板
     * @param response HttpServletResponse
     * @param templateName 模板文件名.xlsx
     * @param templateUrl 模板文件路径，例如："//templates//立项制度.xlsx"
     */
    public static void template(HttpServletResponse response,String templateName, String templateUrl){
        // 使用 URLEncoder 对文件名进行 UTF-8 编码
        String encodedFilename;
        try {
            encodedFilename = URLEncoder.encode(templateName, "UTF-8");
            // 设置 content type
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            // 设置 header
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFilename);
            // 获取文件输入流
            InputStream inputStream = new ClassPathResource(templateUrl).getInputStream();
            // 获取输出流
            OutputStream outputStream = response.getOutputStream();
            // 复制文件内容到输出流
            byte[] buffer = new byte[1024];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            // 关闭流
            outputStream.flush();
            inputStream.close();
            outputStream.close();
        } catch (IOException e) {
            throw new RuntimeException("导入模板下载失败");
        }
    }
}

package com.lxx.controller;

import com.lxx.entity.User;
import com.lxx.entity.UserExcel;
import com.lxx.utils.ExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author lxx
 * 日期: 2024/11/6
 */
@RestController
@RequestMapping("/test")
public class TestController {
    /**
     * 导入
     * @param file MultipartFile
     * @return List<User>
     */
    @PostMapping("/userImport")
    public List<UserExcel> userImport(MultipartFile file){
        // 校验文件是否为空
        if (file.isEmpty()) {
            return new ArrayList<>();
        }
        return ExcelUtil.upExcel(file, UserExcel.class, UserExcel.get());
    }
    /**
     * 导出
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response){
        List<User> list = User.getList();
        List<UserExcel> collect = list.stream().map(it -> new UserExcel(it.getName(), it.getAge(), it.getAddress(), new Date())).collect(Collectors.toList());
        ExcelUtil.exportExcel(collect, "用户信息", "用户信息", UserExcel.class, "用户信息.xlsx", response);
    }
    /**
     * 下载模板
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response){
        String templateName = "立项制度.xlsx";
        String templateUrl = "\\templates\\立项制度.xlsx";
        ExcelUtil.template(response,templateName,templateUrl);
    }
}

【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com

点赞
收藏
关注作者

作者其他文章

1、SpringBoot集成qq邮件

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

开发者空间

了解空间

工作台

开发工具

实战案例

空间活动

空间论坛

开发平台

软件开发生产线 CodeArts

AI平台ModelArts

数据治理中心 DataArts Studio

数字内容生产线 MetaStudio

精选服务

云数据库 GaussDB

云数据库 RDS for MySQL

MapReduce服务 MRS

数据仓库服务 DWS

分布式缓存服务Redis版

分布式消息服务 DMS

华为云实时音视频 SparkRTC

媒体处理 MPC

主机迁移服务 SMS

对象存储迁移服务 OMS

云消息服务 KooMessage

云手机服务 KooPhone

企业搜索服务 KooSearch

云地图服务 KooMap

更多开放能力

开发工具

API生态

CodeArts API

API Explorer

Terraform Explorer

KooCLI

API 中心

SDK 中心

开发服务

Codelabs

DevStar

低代码平台Astro

CodeArts IDE

Huawei Cloud Toolkit

Classroom

开发资源

开源镜像站

开源资源

开发语言

开发实践

入门精选

分发资源

企业应用中心

企业通用专区

教育专区

华为应用专区

政务云专区

硬件云服务商城

医疗健康专区

跳蚤市场

华为云开发者日

直播专区

开发者精品活动

DTSE Tech Talk

加入HCDE

加入HCDG

加入HCSD

加入HCWD

鲁班会

沃土云创计划·企业

沃土云创计划·高校

沃土云创计划·个人

沃土云创计划·开源共创

博客

论坛

专题

开发者榜单

学习路径

在线课程

动手实验

考试认证

培训服务