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

这篇具有很好参考价值的文章主要介绍了EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1.背景

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

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

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

2.实现功能的Excel特性

2.1.特性介绍

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

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

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

2.2.下拉框联动

有了上面的基础之后,实现下拉框的联动就比较简单了,我们只需将上面所说的三种特性结合起来使用即可,在B列修改有效性,如下图:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot
这么配置之后,B列选择Id的时候,就只会出现当前已选姓名对应的Id,如图:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

2.3.单元格自动匹配Id

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

熟悉Excel公式的同学应该知道怎么做了,其实我们只需要在单元格上再写一次名称管理器的引用公式即可:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot
这么写了之后,在A列的单元格选中数据时,B列就可以自动填充Id了,但是如果A列没有选择数据,那么B列就会出现#REF!错误,我们可以修改一下公式,处理一下这个错误:=IFERROR(INDIRECT($A1),"")=IF(ISERROR(INDIRECT($A1)),"",INDIRECT($A1)),这两个公式是等效的,都会判断引用是否正常,如果不正常就填充空串。

修改过后就不会出现报错了:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

2.4.错误提示

有效性配置完成之后,可以配置自定义的错误提示,在单元格输入了其他的信息之后弹出,配置位置还是在有效性那里,以A列来举例:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

3.代码实现

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

由于EasyExcel的包里面已经引入了POI,我们这里只需要引入EasyExceljar包,我这里使用的是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 {

    @Resource
    private ExcelTemplateDownloadHandler excelTemplateDownloadHandler;

    /**
     * 导出excel模板
     */
    @PostMapping("/getExcelTmpl")
    public void getExcelTmpl(HttpServletResponse response) {
        excelTemplateDownloadHandler.buildExcelTmpl(response);
    }

}

一个简单的下载流程就写完了,通过调试工具下载一个Excel文件,效果如下:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

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
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot
我们需要在销售目标导入模板这个sheet创建完成之后,做进一步的操作,所有需要使用afterSheetCreate这个方法,说一下两个形参的作用:

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

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

/**
 * 自定义下拉列表处理器
 */
public class MySheetWriteHandler implements SheetWriteHandler {

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();

        // 创建sheet,保存下拉数据源,这里主要是销售人员姓名和销售人员id
        String 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),也加入下划线就可以了。

处理器写好了之后,需要再导出的位置注册一下:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot
注册好后再次导出,就会发现销售人员数据源和名称管理器已经正确的写入了:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

3.3.有效性配置

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

public class MySheetWriteHandler implements SheetWriteHandler {

    /**
     * 设置下拉框的起始行,默认为第二行
     */
    private static final int FIRST_ROW = 1;
    /**
     * 设置下拉框得结束行行
     */
    private static final int LAST_ROW = 10000;

    @Override
    public 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);
    }
}

查询下载后的效果:
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

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列已经正常填充了函数。
EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板,Java,java,excel,spring boot

3.5.其他补充

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

4.总结

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

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


希望本篇能对大家的开发有所帮助!点赞、收藏!你的支持是我更新最大的动力!文章来源地址https://www.toymoban.com/news/detail-679315.html

到了这里,关于EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 认识excel篇3之数据的有效性(数据验证)

      数据有效性不仅能够对单元格的输入数据进行条件限制,还可以在单元格中创建下拉列表菜单方便用户选择输入。如果没有做数据验证,单元格内默认可以输入任意类型的数据。数据验证就是限制单元格输入数据(必须输入符合要求的才能输入)。但是往往都希望有一定

    2024年02月12日
    浏览(38)
  • 人工智能生成文本检测在实践中使用有效性探讨

    人工智能辅助撰写文章的技术现在无处不在!ChatGPT已经解锁了许多基于语言的人工智能应用程序,人工智能在任何类型的内容生成中的使用都已经达到了以前前所未有的高度。 在诸如创意写作之类的工作中,人们被要求创造自己的内容。但是由于人工智能在这些任务中的普及

    2024年02月04日
    浏览(40)
  • 网络:如何使用curl命令测试HTTP代理的有效性

    在我们的日常工作中,代理服务器扮演着重要的角色,它帮助我们访问局部网络无法直接访问的资源。然而,代理的设置和验证有时候会比较复杂。幸运的是,作为一名开发者应该熟悉curl,一个强大的工具来测试和验证代理服务器的设置。本文将引导我们如何使用curl命令测

    2024年04月22日
    浏览(42)
  • 论证有效性分析专题[1] – 因果关系的可靠性

    专题[1] – 因果关系的可靠性 一、因果关系的过度简单化 特征:根据一些不够充分的必要因素来解释事件,过分强调某一个或某几个因素对事件的作用。可以联想到“板蓝根”思维。 例如:西方人普遍不接受中医,是因为他们不理解中国的传统文化。 解读:西方人不理解中

    2024年02月08日
    浏览(36)
  • 如何实现 Java SpringBoot 自动验证入参数据的有效性

    Java SpringBoot 通过javax.validation.constraints下的注解,实现入参数据自动验证 如果碰到 @NotEmpty 否则不生效,注意看下 @RequestBody 前面是否加上了 @Valid Validation常用注解汇总 Constraint 详细信息 @Null 被注释的元素必须为 null @NotNull 被注释的元素必须不为 null @NotBlank 被注释的元素不能

    2023年04月13日
    浏览(44)
  • 在阿里云上测试Web应用防火墙的有效性

    在实现公有云服务提供的各种安全服务时,您可能会有这样的疑问:我真的能防范来自外部的安全攻击吗? 在本文中,九河云将使用阿里云 Web 应用防火墙 (WAF) 来验证安全性。WAF可以实时识别和阻止Web攻击和恶意Web请求。我们将对网站服务发起攻击,以测试阿里云 WAF 是否

    2024年02月02日
    浏览(39)
  • 用Adobe Reader PDF阅读器来验证电子签名有效性

    正常情况下,Adobe的阅读器打开PDF会显示“已签名且所有签名都有效”,表明这份PDF是一份没有经过篡改的电子文档,即 该PDF上所添加的 数字证书 是有效的数字证书; 该PDF上所添加的 数字签名 没有经过篡改; 该PDF上的 所有内容 没有经过篡改。 通过Adobe Reader阅读器打开签

    2024年02月07日
    浏览(53)
  • Java 操作 Excel:生成数据、设置单元格样式、设置数据有效性(hutool)

    该篇文章,主要通过 Java 代码对 Excel 文件的常用操作,包括:生成表格、修改单元格样式、设置数据有效性。 该篇文章,在官网文献下增加个人的看法和理解,如文中有出现不符、错误或需要补充的地方,欢迎指正,非常感谢。 该篇文章操作 Excel 使用了 hutool 的工具包以及

    2024年02月04日
    浏览(36)
  • 【蓝图】p40-p43对象引用、变量有效性、实现键盘控制物体自转、简单点名系统

    当在一个节点上需要连的线太多,或者需要连接到另一个图标中时,可以创建一个变量,把引脚传到变量里面,则此变量代替了节点的蓝图 操作方法1:新建一个变量,在变量类型里选择想要代替的蓝图 操作方法2:拉出蓝图引脚,选择提升为变量 注意:操作方法12创建出的变

    2024年02月15日
    浏览(33)
  • 基于美日 627 例患者的临床数据,谷歌证实 AI 辅助肺癌筛查的群体有效性

    2024 年 2 月,国家癌症中心基于肿瘤登记及随访监测最新数据,形成了《2022年中国癌症发病率和死亡率》报告。报告显示,在我国,肺癌仍是恶性肿瘤发病和死亡的首位原因。 尽管医学界对于肺癌的关注度与日俱增,但在肺癌诊疗领域仍存在大量诊断延误、过度治疗等临床难

    2024年04月28日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包