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
的组合使用代码实现了模板的生成和下载。文章来源:https://www.toymoban.com/news/detail-679315.html
希望本篇能对大家的开发有所帮助!点赞、收藏!你的支持是我更新最大的动力!文章来源地址https://www.toymoban.com/news/detail-679315.html
到了这里,关于EasyExcel+POI制作带有有效性校验及下拉联动的Excel模板的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!