当前位置： 首页 > article >正文

Spring Boot 与 EasyExcel 携手：复杂 Excel 表格高效导入导出实战

article 2024/11/2 1:52:25

数据的并行导出与压缩下载：EasyExcel：实现大规模数据的并行导出与压缩下载

构建高效排队导出：解决多人同时导出Excel导致的服务器崩溃

SpringBoot集成EasyExcel 3.x：

前言

在企业级应用开发中，常常需要处理复杂的 Excel 表格数据。本方案将 Spring Boot 强大的后端框架与 EasyExcel 这一高效的 Excel 处理工具进行整合，实现了复杂 Excel 表格的导入与导出功能。

对于导入功能，能够轻松应对包含多种数据类型、复杂结构以及大量数据的 Excel 文件。通过合理的配置和处理流程，确保数据的准确性和完整性，将 Excel 中的数据快速导入到系统中，为后续的数据处理和业务逻辑提供有力支持。

在导出方面，能够根据系统中的数据生成结构复杂、格式规范的 Excel 表格。可以自定义表头、样式、格式等，满足不同业务场景下对 Excel 报表的需求。无论是生成详细的业务数据报表，还是复杂的统计分析结果，都能通过这个整合方案轻松实现。

组件介绍

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

Alibaba EasyExcel的核心类是EasyExcel类。

案例

1、简单的读操作

/**
 * 最简单的读
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * 3. 直接读即可
 */
@Test
public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

2、简单的写操作

/**
 * 最简单的写
 * 1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
 * 2. 直接写即可
 */
@Test
public void simpleWrite() {
    String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}

项目基本信息

一、单个sheet&表头合并

1、添加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.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>SpringBoot-easyexcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringBoot-easyexcel</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>

        <!-- Web组件 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

        <!-- commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>

        <!-- lombok插件 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
2. 创建数据模型

创建一个数据模型类，用于表示Excel中的一行数据。例如，我们有一个包含用户信息的复杂Excel表格：

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.Builder;
import lombok.Data;

import java.util.Date;

@Data
@Builder
public class UserData{

    @ExcelProperty(value = "用户ID", index = 0)
    @ColumnWidth(10)
    private Long userId;

    @ExcelProperty(value = "用户名称", index = 1)
    @ColumnWidth(10)
    private String userName;

    @ExcelProperty(value = {"基本信息", "手机号码"}, index = 2)
    @ColumnWidth(20)
    private String userPhone;

    @ExcelProperty(value = {"基本信息", "电子邮箱"}, index = 3)
    @ColumnWidth(20)
    private String userEmail;

    @ExcelProperty(value = {"基本信息", "地址"}, index = 4)
    @ColumnWidth(20)
    private String userAddress;

    @ExcelProperty(value = "注册时间", index = 5)
    @ColumnWidth(20)
    private Date registerTime;

    @ExcelProperty(value = "性别，男:红色/女:绿色")
    @ColumnWidth(30)
    private WriteCellData<String> gender;

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private Integer age;
}
3. 创建导出服务

创建一个服务类，用于实现Excel的导出功能：

import com.alibaba.excel.EasyExcel;  
import org.springframework.stereotype.Service;  
  
import javax.servlet.http.HttpServletResponse;  
import java.io.IOException;  
import java.net.URLEncoder;  
import java.util.List;  
  
@Service  
public class ExcelExportService {  
  
    public void exportUsers(List<UserData> userDataList, HttpServletResponse response) throws IOException {  
        // 设置响应类型
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 设置编码格式
        response.setCharacterEncoding("utf-8");
        // 设置URLEncoder.encode 防止中文乱码
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        // 设置响应头
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), UserData.class).inMemory(true)
            .sheet("用户信息表")
            .doWrite(userDataList);
    }


    private static WriteCellData<String> buildCellData(String gender) {
        // 设置单个单元格多种样式
        WriteCellData<String> cellData = new WriteCellData<>();
        // 设置单个单元格的填充类型
        cellData.setType(CellDataTypeEnum.RICH_TEXT_STRING);
        
        RichTextStringData richTextStringData = new RichTextStringData();
        cellData.setRichTextStringDataValue(richTextStringData);
        richTextStringData.setTextString(gender);
        WriteFont writeFont = new WriteFont();
        if ("男".equalsIgnoreCase(gender)) {
         //设置颜色为红色
            writeFont.setColor(IndexedColors.RED.getIndex());
        } else if ("女".equalsIgnoreCase(gender)) {
         //设置颜色为绿色
            writeFont.setColor(IndexedColors.GREEN.getIndex());
        }
        //应用颜色字体
        richTextStringData.applyFont(writeFont);
        return cellData;
    }  
}
4. 创建导入服务

导入的数据模型

import java.util.Date;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.data.WriteCellData;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@Builder
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class UserData {
    private Long userId;

    private String userName;

    private Integer age;

    private String userPhone;

    private String userEmail;

    private String userAddress;

    private Date registerTime;

    private String gender;
}

创建一个服务类，用于实现Excel的导入功能：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.regex.Pattern;

@Slf4j
@Service
public class ExcelImportService {

    /**
     * 每隔一定数量的数据存储到数据库，可根据实际情况调整。
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 手机号正则表达式校验。
     */
    private static final Pattern PHONE_REGEX = Pattern.compile("^1[0-9]{10}$");

    /**
     * 缓存要导入的数据列表。
     */
    private List<UserData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 错误信息列表。
     */
    private final List<String> errorMsgList = new ArrayList<>();

    // 假设这里有一个数据访问层的接口引用
    private UserDataRepository userDataRepository;

    public ExcelImportService(UserDataRepository userDataRepository) {
        this.userDataRepository = userDataRepository;
    }

    /**
     * 导入 Excel 文件并保存数据到数据库。
     *
     * @param excelFilePath Excel 文件路径。
     */
    public void importExcel(String excelFilePath) {
        EasyExcel.read(excelFilePath, UserData.class, new CustomAnalysisEventListener()).sheet().doRead();
    }

    class CustomAnalysisEventListener extends AnalysisEventListener<UserData> {

        @Override
        public void invoke(UserData userData, AnalysisContext analysisContext) {
            log.info("解析到一条数据:{}", userData);
            int rowIndex = analysisContext.readRowHolder().getRowIndex();
            String name = userData.getUserName();
            String phone = userData.getUserPhone();
            String gender = userData.getGender();
            String email = userData.getUserEmail();
            Integer age = userData.getAge();
            String address = userData.getUserAddress();
            // 只有全部校验通过的对象才能被添加到下一步
            if (nameValid(rowIndex, name) && phoneValid(rowIndex, phone) && genderValid(rowIndex, gender) &&
                    emailValid(rowIndex, email) && ageValid(rowIndex, age) && addressValid(rowIndex, address)) {
                cachedDataList.add(userData);
            }
            // 达到 BATCH_COUNT 了，存储到数据库并清理列表
            if (cachedDataList.size() >= BATCH_COUNT) {
                saveDataToDatabase();
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            log.info("所有数据解析完成！全部校验通过的数据有{}条", cachedDataList.size());
            // 保存剩余数据到数据库
            saveDataToDatabase();
        }

        @Override
        public void onException(Exception exception, AnalysisContext context) throws Exception {
            if (exception instanceof RuntimeException) {
                throw exception;
            }
            int index = context.readRowHolder().getRowIndex() + 1;
            errorMsgList.add("第" + index + "行解析错误");
        }

        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            int totalRows = context.readSheetHolder().getApproximateTotalRowNumber() - 1;
            int maxNum = 2000;
            if (totalRows > maxNum) {
                errorMsgList.add("数据量过大,单次最多上传2000条");
                throw new RuntimeException("数据量过大,单次最多上传2000条");
            }
        }
    }

    /**
     * 将缓存的数据保存到数据库。
     */
    private void saveDataToDatabase() {
        userDataRepository.saveAll(cachedDataList);
    }

    public List<String> getErrorMsgList() {
        return errorMsgList;
    }

    /**
     * 名称的校验。
     *
     * @param rowIndex 行数。
     * @param name     名称。
     */
    private Boolean nameValid(Integer rowIndex, String name) {
        if (StringUtils.isBlank(name)) {
            errorMsgList.add("第" + rowIndex + "行,'姓名'不能为空");
            return false;
        }
        return true;
    }

    /**
     * 手机号的校验。
     *
     * @param rowIndex 行数。
     * @param phone    手机号。
     */
    private Boolean phoneValid(int rowIndex, String phone) {
        if (StringUtils.isBlank(phone)) {
            errorMsgList.add("第" + rowIndex + "行,'手机号'不能为空");
            return false;
        }
        return true;
    }

    /**
     * 性别的校验。
     *
     * @param rowIndex 行数。
     * @param gender   性别。
     */
    private Boolean genderValid(int rowIndex, String gender) {
        if (StringUtils.isBlank(gender)) {
            errorMsgList.add("第" + rowIndex + "行,'性别'不能为空");
            return false;
        }
        return true;
    }

    /**
     * 地址的校验。
     *
     * @param rowIndex 行数。
     * @param address  地址。
     */
    private Boolean addressValid(int rowIndex, String address) {
        // 校验地址是否为空
        if (StringUtils.isBlank(address)) {
            errorMsgList.add("第 " + rowIndex + " 行,'地址'不能为空");
            return false;
        }
        return true;
    }

    /**
     * 年龄的校验。
     *
     * @param rowIndex 行数。
     * @param age      年龄。
     */
    private Boolean ageValid(int rowIndex, Integer age) {
        // 校验年龄是否为空
        if (Objects.isNull(age)) {
            errorMsgList.add("第 " + rowIndex + " 行'年龄'不能为空");
            return false;
        }
        return true;
    }

    /**
     * 邮箱的校验。
     *
     * @param rowIndex 行数。
     * @param email    邮箱。
     */
    private Boolean emailValid(int rowIndex, String email) {
        // 校验邮箱是否为空
        if (StringUtils.isBlank(email)) {
            errorMsgList.add("第 " + rowIndex + " 行'邮箱'不能为空");
            return false;
        }
        return true;
    }
}
5. 创建控制器

创建一个控制器类，用于处理Excel的导入与导出请求：

import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.web.bind.annotation.*;  
import org.springframework.web.multipart.MultipartFile;  
  
import javax.servlet.http.HttpServletResponse;  
import java.io.IOException;  
import java.util.List;  
  
@RestController  
@RequestMapping("/excel")  
public class ExcelController {  
  
    @Autowired  
    private ExcelExportService excelExportService;  
  
    @Autowired  
    private ExcelImportService excelImportService;

    @Autowired  
    private UserService userService;  
  
    
    @GetMapping("/export")  
    public void exportUsers(HttpServletResponse response,  
                            @RequestParam(value = "data", required = false) List<UserData> userDataList) throws IOException { 

        // 判断userDataList是否为空，为空就去数据库查询数据，后执行导出操作 
        if (userDataList == null || userDataList.isEmpty()) {  
            // 从数据库或其他地方获取数据  
            userDataList = userService.findAll();
            for (UserData userData: userDataList) {
                // 修改性别单元格的样式
                excelExportService.buildCellData(userData.getGender)
            }  
        }
  
        excelExportService.exportUsers(userDataList, response);  
    }  
  
    @PostMapping("/import")  
    public String importUsers(@RequestParam("file") MultipartFile file) {  
        try {  
            String filePath = "/path/to/upload/" + file.getOriginalFilename();  
            file.transferTo(new java.io.File(filePath));  
            excelImportService.importExcel(filePath);  
            return "导入成功";  
        } catch (Exception e) {  
            e.printStackTrace();  
            return "导入失败";  
        }  
    }  
}
6. 运行项目并测试

启动Spring Boot项目，然后你可以通过以下URL进行测试：

  • 导出Excel文件：http://localhost:8080/excel/export（可以传递一个data参数，包含要导出的用户数据）

  • 导入Excel文件：http://localhost:8080/excel/import（上传一个Excel文件）‘

二、多个sheet导出

1、创建数据模型

城市实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Builder;
import lombok.Data;

@Data
@Builder
public class City{

    @ExcelProperty(value = "城市名称", index = 0)
    @ColumnWidth(10)
    private String cityName;

    @ExcelProperty(value = "城市介绍", index = 1)
    @ColumnWidth(60)
    private String cityDesc;
}

公司实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Builder;
import lombok.Data;

@Data
@Builder
public class Company{
    @ExcelProperty(value = "公司名称", index = 0)
    @ColumnWidth(10)
    private String companyName;

    @ExcelProperty(value = "公司创始人", index = 1)
    @ColumnWidth(10)
    private String companyBoss;

    @ExcelProperty(value = "公司总基地", index = 2)
    @ColumnWidth(10)
    private String companyBase;

    @ExcelProperty(value = "公司简介", index = 3)
    @ColumnWidth(50)
    private String companyDesc;
}
2、创建导出服务

创建一个服务类，用于实现Excel的导出功能：

import com.alibaba.excel.EasyExcel;  
import org.springframework.stereotype.Service;  
  
import javax.servlet.http.HttpServletResponse;  
import java.io.IOException;  
import java.net.URLEncoder;  
import java.util.List;  
  
@Service  
public class ExcelExportAllService {  

        public void exportUsers(List<UserData> userDataList,List<CityList> cityList,List<CompanyList> companyList, HttpServletResponse response) throws IOException {  

            // 设置响应类型
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            // 设置编码格式
            response.setCharacterEncoding("utf-8");
            // 设置URLEncoder.encode 防止中文乱码
            String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
            // 设置响应头
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 多个sheet的输出需要使用ExcelWriter类，这里想要下载成功，需要输出到OutputStream中
            try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).inMemory(true).build()) {

                // 创建用户信息表的sheet，写入用户信息数据，1代表sheet的位置是第一个
                WriteSheet userInfoSheet = EasyExcel.writerSheet(0, "用户信息表").head(UserData.class).build();
                excelWriter.write(userDataList, userInfoSheet);

                // 创建城市信息表的sheet，写入城市信息数据，2代表sheet的位置是第二个
                WriteSheet cityInfoSheet = EasyExcel.writerSheet(1, "城市信息表").head(City.class).build();
                excelWriter.write(cityList, cityInfoSheet);

                // 创建公司信息表的sheet，写入公司信息数据，3代表sheet的位置是第三个
                WriteSheet companyInfoSheet = EasyExcel.writerSheet(2, "公司信息表").head(Company.class).build();
                excelWriter.write(companyList, companyInfoSheet);
           }
        }
}
3、创建控制器

创建一个控制器类，用于处理Excel的导入与导出请求

import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.web.bind.annotation.*;  
import org.springframework.web.multipart.MultipartFile;  
  
import javax.servlet.http.HttpServletResponse;  
import java.io.IOException;  
import java.util.List;  
  
@RestController  
@RequestMapping("/excelAll")  
public class ExcelAllController {  
  
    @Autowired  
    private ExcelExportAllService excelExportAllService;  

    @Autowired  
    private UserDataService userService;

    @Autowired  
    private CompanyService companyService;  

    @Autowired  
    private CityService cityService;  
  
    
    @GetMapping("/export")  
    public void exportUsers(HttpServletResponse response,  
                            @RequestParam(value = "userDataList", required = false) List<UserData> userDataList,
                            @RequestParam(value = "companyList", required = false) List<Company> companyList,
                            @RequestParam(value = "cityList", required = false) List<City> cityList
                            ) throws IOException { 

        // 判断userDataList是否为空，为空就去数据库查询数据，后执行导出操作 
        if (userDataList == null || userDataList.isEmpty()) {  
            // 从数据库或其他地方获取数据  
            userDataList = userService.findAll(); 
        }

        if (companyList== null || companyList.isEmpty()) {  
            // 从数据库或其他地方获取数据  
            companyList= companyService.findAll(); 
        }

        if (cityList== null || cityList.isEmpty()) {  
            // 从数据库或其他地方获取数据  
            cityList= cityService.findAll(); 
        }
  
        excelExportAllService.exportUsers(userDataList,cityList,companyList, response);  
    }  
  
}
4. 运行项目并测试

启动Spring Boot项目，然后你可以通过以下URL进行测试：

  • 导出Excel文件：http://localhost:8080/excelAll/export

多个sheet导出


http://www.kler.cn/a/375191.html

相关文章：

  • Qt指定程序编译生成文件的位置
  • Raspberry Pi 树莓派产品系列说明
  • 【Flask】四、flask连接并操作数据库
  • FineReport 单元格的特殊应用场景
  • Linux RAID 技术详解：原理、配置、管理及故障处理
  • c++/qt连接阿里云视觉智能开发平台
  • 基于 SM3 的密钥派生函数 (KDF)：国密合规的安全密钥生成方案
  • 低代码用户中心：简化开发，提升效率的新时代
  • es（1）（仅供自己参考）
  • 前端安全：构建坚不可摧的Web应用防线
  • redis的set如何实现的
  • 【WPF】BackgroundWorker类
  • 龙迅#LT8668EX显示器图像处理芯片 适用于HDMI1.4+VGA转4PORT LVDS,支持4K30HZ分辨率,可做OSD菜单亮度调节!
  • 什么是继电器干接点输出
  • 功能测试：方法、流程与工具介绍
  • 20万高薪专业-网络安全（非常详细）零基础入门到精通，收藏这一篇就够了
  • 【源码+文档】基于SpringBoot+Vue旅游网站系统【提供源码+答辩PPT+参考文档+项目部署】
  • 基于企业微信与开源 AI 智能名片 2 + 1 链动模式 S2B2C 商城小程序的客户运营模型优化研究
  • docker file容器化部署Jenkins（一）
  • 【笔面试常见题：三门问题】用条件概率、全概率和贝叶斯推导
  • 语音IC方案，在交通信号灯语音提示器的应用解析，NV040D
  • Windows 基础 （二）：系统目录与环境变量
  • 搭建SRS流媒体服务器处理多路无人机视频流
  • 跨境行业的客户运营利器：构建在线培训知识库
  • mac下使用docker安装php7.4环境
  • 网络编程基础-Reactor线程模型-原理剖析