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 {

    @Resource
    private 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 {

    @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)，也加入下划线就可以了。

处理器写好了之后，需要再导出的位置注册一下：

注册好后再次导出，就会发现销售人员数据源和名称管理器已经正确的写入了：

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);
    }
}

查询下载后的效果：

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的组合使用代码实现了模板的生成和下载。

---

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