越秀移动网站建设,南宁建设厅网站,重庆建筑模板,室内设计效果图制作教程文章目录 1.背景2.实现功能的Excel特性2.1.特性介绍2.2.下拉框联动2.3.单元格自动匹配Id2.4.错误提示 3.代码实现3.1.基础流程代码3.2.名称管理器配置3.3.有效性配置3.4.函数填充3.5.其他补充 4.总结 1.背景
最近在做一个CRM系统的人员销售目标导入的相关需求#xff0c;需要… 文章目录 1.背景2.实现功能的Excel特性2.1.特性介绍2.2.下拉框联动2.3.单元格自动匹配Id2.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版本。
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.1.0/version
/dependency3.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 WriteCellDataString 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) {ListMemberExcelTemplateModel 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 ListMember 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);ListMember 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) {ListMemberExcelTemplateModel list new ArrayList();// 默认填充10000行公式for (int i 0; i 10000; i) {// 定义函数FormulaData formulaData new FormulaData();formulaData.setFormulaValue(IFERROR(INDIRECT(\_\$A (i 2) ),\\));// 将函数对象设置到模板对象中WriteCellDataString 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的组合使用代码实现了模板的生成和下载。 希望本篇能对大家的开发有所帮助点赞、收藏你的支持是我更新最大的动力
