EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板

news/2024/11/23 1:37:56/

文章目录

  • 1.背景
  • 2.实现功能的Excel特性
    • 2.1.特性介绍
    • 2.2.下拉框联动
    • 2.3.单元格自动匹配Id
    • 2.4.错误提示
  • 3.代码实现
    • 3.1.基础流程代码
    • 3.2.名称管理器配置
    • 3.3.有效性配置
    • 3.4.函数填充
    • 3.5.其他补充
  • 4.总结

1.背景

最近在做一个CRM系统的人员销售目标导入的相关需求，需要将销售人员的目标导入到系统中，就要求在Excel导入模板中填写销售人员Id和销售人员姓名。在使用的时候，这是一个易错的点，因为这两个字段交给了使用者去自由填写的话，是很容易填错的。除了文字本身填多填少以外，两个字段的映射关系还可能填错。

为了处理这个问题呢，去查了查资料，发现Excel中有几个特性可以将销售人员的姓名和id做成一个下拉联动的效果，这样就不会存在填错的问题了。

实现了这个功能之后，觉得比较有意思，网上这方面的资料也比较少，索性就在这里记录和分享一下。

2.实现功能的Excel特性

2.1.特性介绍

在实现代码之前，先了解一下这个功能需要涉及到的3个Excel功能特性：名称管理器、indirect公式、数据有效性，我这里使用的是WPS，所以下面会通过WPS来进行举例，微软的Office在类似的位置也有一样的功能，使用Office的同学可以自行研究一下。

  • 名称管理器：
    类似于一个数据字典的功能，有名称(key)和引用位置(value的引用)两个主要字段，所谓的引用位置就是需要引用的单元格坐标，单元格可以是1个，也可以是1行或者1列。在当前的需求中姓名和id是一一对应的，所以我们这里只需要填写一个单元格的引用即可，配置方式如下图所示：
    在这里插入图片描述
  • indirect公式
    这个公式可以用来引用名称管理器的配置，通过=indirect(名称)可以获取到对应的值，例如在Sheet1中通过这个公式获取到张三的id，如下图所示：
    在这里插入图片描述
  • 数据有效性
    数据是用来校验当前单元格的数据是否满足要求，在不满足要求时可以给出一定的提示，此外还有一些附加功能，例如：用来做一个下拉列表，所以我们可以考虑直接将单元格做成下拉列表，通过下拉来选择姓名。
    在这里插入图片描述操作完成之后，A列的单元格就可以下拉选择了。
    在这里插入图片描述
    用同样的方式，可以把id列表页做成下拉放在B列。
    在这里插入图片描述

但是这种实现的方式，姓名和id各选各的，虽然不会因为手动输入输错了，但是还是会有映射关系不匹配的问题。咱接着往下看，可以通过下拉联动来解决这个问题。

2.2.下拉框联动

有了上面的基础之后，实现下拉框的联动就比较简单了，我们只需将上面所说的三种特性结合起来使用即可，在B列修改有效性，如下图：
在这里插入图片描述
这么配置之后，B列选择Id的时候，就只会出现当前已选姓名对应的Id，如图：
在这里插入图片描述

2.3.单元格自动匹配Id

上面已经实现了下拉选择框的联动，但是这种方式还需要手动的一个一个选择，有没有一种方式可以在选中A列的姓名时，B列就自动填充Id呢？

熟悉Excel公式的同学应该知道怎么做了，其实我们只需要在单元格上再写一次名称管理器的引用公式即可:
在这里插入图片描述
这么写了之后，在A列的单元格选中数据时，B列就可以自动填充Id了，但是如果A列没有选择数据，那么B列就会出现#REF!错误，我们可以修改一下公式，处理一下这个错误：=IFERROR(INDIRECT($A1),"")或=IF(ISERROR(INDIRECT($A1)),"",INDIRECT($A1))，这两个公式是等效的，都会判断引用是否正常，如果不正常就填充空串。

修改过后就不会出现报错了：
在这里插入图片描述

2.4.错误提示

有效性配置完成之后，可以配置自定义的错误提示，在单元格输入了其他的信息之后弹出，配置位置还是在有效性那里，以A列来举例：
在这里插入图片描述

3.代码实现

接下来会先提供基础的流程代码，然后再按照名称管理器、下拉列表配置（含数据校验）、公式填充的顺序依次进行实现。

由于EasyExcel的包里面已经引入了POI，我们这里只需要引入EasyExcel的jar包，我这里使用的是3.1.0版本。

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.0</version>
</dependency>

3.1.基础流程代码

为了方便后续的实现，这里会写一部分基础导出代码，没有用过EasyExcel的同学可以看看，如果已经比较熟悉EasyExcel的同学，可以直接看下面的3.2。

首先提供一个导出对象用于下载导入模板，这里简单处理只有名称、id两个字段：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.Getter;
import lombok.Setter;/*** 销售人员Excel导入模板对象*/
@Getter
@Setter
public class MemberExcelTemplateModel {@ExcelProperty("销售人员姓名")private String name;@ExcelProperty("销售人员id")private WriteCellData<String> memberIdFormula;
}

这里的id字段使用了WriteCellData而不是Long、String之类的字段，主要是为了后续填充公式，下面会详细讲到。

然后写一个处理器，使用上面的模板生成Excel，并将生成好的Excel文件写入到HttpServletResponse中：

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;/*** Excel模板下载处理器*/
@Slf4j
@Component
public class ExcelTemplateDownloadHandler {public void buildExcelTmpl(HttpServletResponse response) {List<MemberExcelTemplateModel> list = new ArrayList<>();try {EasyExcelFactory.write(disposeExportSetting(response).getOutputStream(), MemberExcelTemplateModel.class).excelType(ExcelTypeEnum.XLSX).sheet("销售目标导入模板").doWrite(list);} catch (IOException e) {log.error("线索统计整体分析导出失败", e);}}/*** 设置导出Excel的响应头、类型、编码等*/private HttpServletResponse disposeExportSetting(HttpServletResponse response) throws UnsupportedEncodingException {response.setContentType("application/x-xls");response.setCharacterEncoding("utf-8");String name = URLEncoder.encode("template", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");return response;}}

最后提供一个controller用于发起Http请求，下载导入模板：

@RestController
@RequestMapping("/excel")
public class ExcelController {@Resourceprivate ExcelTemplateDownloadHandler excelTemplateDownloadHandler;/*** 导出excel模板*/@PostMapping("/getExcelTmpl")public void getExcelTmpl(HttpServletResponse response) {excelTemplateDownloadHandler.buildExcelTmpl(response);}}

一个简单的下载流程就写完了，通过调试工具下载一个Excel文件，效果如下：
在这里插入图片描述

3.2.名称管理器配置

有了一个基础的模板之后，进入第二步，创建一个新的sheet保存销售人员信息并创建名称管理器。

首先要将数据库中的销售人员信息查出来，提供一个Member对象来接收：

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;import java.util.Arrays;
import java.util.List;/*** 销售人员*/
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Member {/*** 销售人员id*/private String id;/*** 销售人员姓名*/private String name;/*** 模拟从数据库中获取销售人员列表*/public static List<Member> getMemberList() {return Arrays.asList(new Member("1", "张三"),new Member("2", "李四"),new Member("3", "王五"),new Member("4", "赵六"),new Member("5", "田七"));}
}

接下来需要使用到EasyExcel的一个拓展点：SheetWriteHandler
在这里插入图片描述
我们需要在销售目标导入模板这个sheet创建完成之后，做进一步的操作，所有需要使用afterSheetCreate这个方法，说一下两个形参的作用：

  • WriteWorkbookHolder：获取当前操作的Excel对象
  • WriteSheetHolder：获取当前操作的sheet对象，这里指的就是销售目标导入模板

写一个自定义处理器继承SheetWriteHandler：

/*** 自定义下拉列表处理器*/
public class MySheetWriteHandler implements SheetWriteHandler {@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();// 创建sheet，保存下拉数据源，这里主要是销售人员姓名和销售人员idString sheetName = "dataSource";Sheet workbookSheet = workbook.createSheet(sheetName);List<Member> memberList = Member.getMemberList();for (int i = 0; i < memberList.size(); i++) {Member member = memberList.get(i);// 写入销售人员数据，row表示开始得行数，cell表示开始得列数Row row = workbookSheet.createRow(i);row.createCell(0).setCellValue(member.getName());row.createCell(1).setCellValue(member.getId());// 创建名称管理器Name workbookName = workbook.createName();// 加入下划线，避免000001这种数字开头的命名workbookName.setNameName("_" + member.getName());workbookName.setRefersToFormula(sheetName + "!$B$" + (i + 1));}}
}

这里和上面的Excel演示有个不同的点，就是名称处理器中使用了下划线开头，这是我踩中的一个坑，有数字开头的名字会导致创建名称处理器报错。使用了下划线之后，同步修改函数INDIRECT("_"&$A1)，也加入下划线就可以了。

处理器写好了之后，需要再导出的位置注册一下：
在这里插入图片描述
注册好后再次导出，就会发现销售人员数据源和名称管理器已经正确的写入了：
在这里插入图片描述

3.3.有效性配置

接下来就是在销售目标导入里面，将姓名选择置为下拉选择，也就是有效性的配置：

public class MySheetWriteHandler implements SheetWriteHandler {/*** 设置下拉框的起始行，默认为第二行*/private static final int FIRST_ROW = 1;/*** 设置下拉框得结束行行*/private static final int LAST_ROW = 10000;@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {/// 省略名称管理器代码……// 有效性处理帮助对象DataValidationHelper validationHelper = writeSheetHolder.getSheet().getDataValidationHelper();// 销售人员姓名下拉数据源匹配CellRangeAddressList nameRange = new CellRangeAddressList(FIRST_ROW, LAST_ROW, 0, 0);DataValidationConstraint nameConstraint = validationHelper.createFormulaListConstraint(sheetName + "!$A$1:$A$" + (memberList.size() + 1)); // 数据源的第一列DataValidation nameValidation = validationHelper.createValidation(nameConstraint, nameRange);nameValidation.setShowErrorBox(true);nameValidation.createErrorBox("错误", "请选择正确的姓名");writeSheetHolder.getSheet().addValidationData(nameValidation);// 销售人员id下拉联动CellRangeAddressList idRange = new CellRangeAddressList(FIRST_ROW, LAST_ROW, 1, 1);DataValidationConstraint idConstraint = validationHelper.createFormulaListConstraint("=INDIRECT(\"_\"&$A2)"); // 函数加入下划线DataValidation idValidation = validationHelper.createValidation(idConstraint, idRange);idValidation.setShowErrorBox(true);idValidation.createErrorBox("错误", "请选择正确的id");writeSheetHolder.getSheet().addValidationData(idValidation);}
}

查询下载后的效果：
在这里插入图片描述

3.4.函数填充

最后剩下在销售人员id的单元格上填充公式了，由于销售目标导入模板的数据，已经通过EasyExcel写入了，这里不能再使用POI重复写入，所以需要将公式填充前置到EasyExcel的写入里面。这也是为什么上面提供的MemberExcelTemplateModel中的销售id字段是WriteCellData就是为了填充公式。

在下载导入模板之前，处理一下需要导出的数据：

    public void buildExcelTmpl(HttpServletResponse response) {List<MemberExcelTemplateModel> list = new ArrayList<>();// 默认填充10000行公式for (int i = 0; i < 10000; i++) {// 定义函数FormulaData formulaData = new FormulaData();formulaData.setFormulaValue("IFERROR(INDIRECT(\"_\"&$A" + (i + 2) + "),\"\")");// 将函数对象设置到模板对象中WriteCellData<String> formula = new WriteCellData<>();formula.setFormulaData(formulaData);MemberExcelTemplateModel memberExcelTemplateModel = new MemberExcelTemplateModel();memberExcelTemplateModel.setMemberIdFormula(formula);list.add(memberExcelTemplateModel);}try {EasyExcelFactory.write(disposeExportSetting(response).getOutputStream(), MemberExcelTemplateModel.class).excelType(ExcelTypeEnum.XLSX).sheet("销售目标导入模板")// 注册自定义处理器.registerWriteHandler(new MySheetWriteHandler()).doWrite(list);} catch (IOException e) {log.error("线索统计整体分析导出失败", e);}}

查看导出结果，销售人员id列已经正常填充了函数。
在这里插入图片描述

3.5.其他补充

上面的例子中只有姓名和id两种字段，实际的开发中可能还会有年份、月份、销售小组、金额等等限制，可以参照上面的例子进行拓展。

4.总结

本文主要探讨的是如何制作一个有下拉、下拉联动、数据校验、自动填充功能的Excel模板。

从Excel本身的特性名称管理器、有效性、公式出发，讲解了功能实现的原理，并手动配置了一个模板。再通过EasyExcel与POI的组合使用代码实现了模板的生成和下载。

希望本篇能对大家的开发有所帮助！点赞、收藏！你的支持是我更新最大的动力！


http://www.ppmy.cn/news/1054291.html

相关文章

假如你是一名专业的程序员，你都知道哪些程序员是如何实现财富自由的？

假如你是一名专业的程序员，你都知道哪些程序员是如何实现财富自由的？

我可以分享一些程序员如何实现财富自由的一般原则和方法。然而&#xff0c;请注意每个人的情况都不同&#xff0c;成功的路径可能因个人技能、市场需求、机会和运气而异。以下是一些常见的方法&#xff1a; 1. **创业**&#xff1a;很多成功的程序员选择创业&#xff0c;开发自…
阅读更多...
学习ts（六）数据类型（元组、枚举、Symbol、never）与类型推论

学习ts（六）数据类型（元组、枚举、Symbol、never）与类型推论

1.元组 元组&#xff08;Tuple&#xff09;是固定数量的不同类型的元素的组合。是数组的变种。 元组与集合的不同之处在于&#xff0c;元组中的元素类型可以是不同的&#xff0c;而且数量固定。元组的好处在于可以把多个元素作为一个单元传递。如果一个方法需要返回多个值&…
阅读更多...
【C++奇遇记】内存模型

【C++奇遇记】内存模型

&#x1f3ac; 博客主页&#xff1a;博主链接 &#x1f3a5; 本文由 M malloc 原创&#xff0c;首发于 CSDN&#x1f649; &#x1f384; 学习专栏推荐&#xff1a;LeetCode刷题集 数据库专栏 初阶数据结构 &#x1f3c5; 欢迎点赞 &#x1f44d; 收藏 ⭐留言 &#x1f4dd; 如…
阅读更多...
Me-and-My-Girlfriend靶场

Me-and-My-Girlfriend靶场

靶机下载&#xff1a; https://www.vulnhub.com/entry/me-and-my-girlfriend-1,409/ 一、信息收集 查找ip nmap -sP 192.168.16.0/24获取详细端口等信息 nmap -sV -sC -A -p 1-65535 192.168.16.209目录扫描 gobuster dir -u http://192.168.16.209 -w /usr/share/word…
阅读更多...
数据结构与算法：通往编程高地的必修课（文末送书）

数据结构与算法：通往编程高地的必修课（文末送书）

&#x1f935;‍♂️ 个人主页&#xff1a;艾派森的个人主页 ✍&#x1f3fb;作者简介&#xff1a;Python学习者 &#x1f40b; 希望大家多多支持&#xff0c;我们一起进步&#xff01;&#x1f604; 如果文章对你有帮助的话&#xff0c; 欢迎评论 &#x1f4ac;点赞&#x1f4…
阅读更多...
RESTful API，以及如何使用它构建 web 应用程序

RESTful API，以及如何使用它构建 web 应用程序

RESTful API是一种基于HTTP协议的API设计风格&#xff0c;它的核心思想是将资源作为 API 的核心&#xff0c;使用 HTTP 的 GET、POST、PUT、DELETE 等方法对这些资源进行操作&#xff0c;并通过 URL 来定位资源。 RESTful API的特点包括&#xff1a; 资源是 API 的核心使用 H…
阅读更多...
【Python教程】3道循环结构练习题，都会了吗？

【Python教程】3道循环结构练习题，都会了吗？

嗨喽~大家好呀&#xff0c;这里是魔王呐 ❤ ~! 练习1&#xff1a;输入一个数判断是不是素数。 from math import sqrtnum int(input(请输入一个正整数: )) end int(sqrt(num)) is_prime True for x in range(2, end 1):if num % x 0:is_prime Falsebreak if is_prime an…
阅读更多...
面试常问：水平居中和垂直居中的方法

面试常问：水平居中和垂直居中的方法

水平居中 文本居中 如果元素为行内元素&#xff0c;可以将父元素的text-align属性设置为center&#xff0c;这样子元素就会水平居中对齐 .text{text-align: center; }固定宽度的居中 如果元素宽度已知并固定&#xff0c;可以通过将左右margin设置为auto来实现水平居中。 .…
阅读更多...
最新文章

Copyright @ 2022~2023