ps做网站学到什么程度冠县哪做网站

当前位置： 首页 > news >正文

ps做网站学到什么程度,冠县哪做网站,谷歌推广开户,wordpress主题怎么制作系列文章 EasyExcel生成带下拉列表或多级级联列表的Excel模版自定义校验导入数据(修订) 目录 系列文章前言仓库一、实现1.1 下拉元数据对象1.2 构建下拉元数据的映射关系1.3 框架方式1.3.1 框架实现1.3.2 框架用例模版类加载下拉业务导出接口 1.4 EasyExcel方式1.4.1 EasyExce…系列文章 EasyExcel生成带下拉列表或多级级联列表的Excel模版自定义校验导入数据(修订) 目录 系列文章前言仓库一、实现1.1 下拉元数据对象1.2 构建下拉元数据的映射关系1.3 框架方式1.3.1 框架实现1.3.2 框架用例模版类加载下拉业务导出接口 1.4 EasyExcel方式1.4.1 EasyExcel用例导出接口 二、效果2.1 下拉sheet2.2 业务sheet 附录SpELHelper 前言 有同好提过这问题, 有一定的使用场景, 最近设计了一下并落地了, 支持直接使用框架方式或直接使用EasyExcel方式. 该迭代仅限使用SpEL能力最新版本代码, 历史版本忽略. 仓库 仓库: excel-common-spring-boot-starter (请参考最新代码, 文档内容更新不会很勤快, 没License看得上就随便用) 一、实现 核心逻辑: 创建下拉元数据对象时需要通过解析SpEL表达式获取下拉数据, 该能力提供的上下文对象EvaluationContext支持传入自定义参数, 并可以根据表达式指定的参数名从上下文中获取到指定参数并传给指定的调用方法. 调用方式: 使用框架和直接使用EasyExcel的两种方式, 在传入动态参数的方式上有所不同, 用例 部分会通过代码说明. 例如: SpEL表达式为 xxxBean.findAllByTypeAndCode(#type, #code) 开头指定选择SpringBean的名称.findAllByTypeAndCode指定调用该bean的方法#type和#code指定从表达式上下文中解析名为 type 和 code 的 变量, 并传入findAllByTypeAndCode()方法. 1.1 下拉元数据对象 解析表达式时, 通过Visitor将参数设置到上下文中. SpELHelper 见 附录 部分 /*** author hp/ Slf4j Getter Setter public abstract class AbstractExcelSelectModelT {protected int headLayerCount;protected T options;protected String columnName;protected int columnIndex;protected String parentColumnName;protected int parentColumnIndex;protected int firstRow;protected int lastRow;public AbstractExcelSelectModel(Nonnull Field field, Nonnull ExcelSelect excelSelect, Nullable ExcelProperty excelProperty, int defaultSort, Nullable MapString, Object parameters) {final OptionalExcelProperty excelPropertyOpt Optional.ofNullable(excelProperty);this.headLayerCount excelPropertyOpt.map(property - property.value().length).orElse(1);this.firstRow Math.max(excelSelect.firstRow(), this.headLayerCount);this.lastRow excelSelect.lastRow();this.parentColumnName excelSelect.parentColumnName();this.columnName excelPropertyOpt.map(property - property.value()[this.headLayerCount - 1]).orElse(field.getName());this.columnIndex excelPropertyOpt.map(property - property.index() -1 ? property.index() : defaultSort).orElse(defaultSort);this.options resolveOptions(excelSelect, parameters);}public boolean hasParentColumn() {return StrUtil.isNotEmpty(this.parentColumnName);}SuppressWarnings(unchecked)Nullableprotected T resolveOptions(Nonnull ExcelSelect excelSelect, Nullable MapString, Object parameters) {final ExcelOptions excelOptions excelSelect.options();if (StrUtil.isEmpty(excelOptions.expression())) {log.warn(The ExcelSelect on {} has no options whatsoever., this.columnName);return null;}final SpELHelper spELHelper SpringUtil.getBean(SpELHelper.class);return (T) spELHelper.newGetterInstance(excelOptions.expression()).apply(null,// 在这里向上下文中设置自定义变量.(evaluationContext - Optional.ofNullable(parameters).ifPresent(map - map.forEach(evaluationContext::setVariable))));} }1.2 构建下拉元数据的映射关系 Convention: Nullable MapString, Object parameters 参数定义为Map集合类型 提交动态数据的入口, 同时根据实际列注解情况构建下拉元数据的映射关系 /** author hp/ Slf4j UtilityClass public class ExcelSelectHelper {Nullablepublic static T MapInteger, ? extends AbstractExcelSelectModel? createSelectionMapping(Nonnull ClassT dataClass) {return createSelectionMapping(dataClass, null);}Nullablepublic static T MapInteger, ? extends AbstractExcelSelectModel? createSelectionMapping(Nonnull ClassT dataClass, Nullable MapString, Object parameters) {final Field[] fields ReflectUtil.getFields(dataClass);final AtomicInteger fieldIndex new AtomicInteger(0);final MapInteger, ? extends AbstractExcelSelectModel? selectionMapping Arrays.stream(fields).map(field - {final ExcelSelect excelSelect AnnotatedElementUtils.getMergedAnnotation(field, ExcelSelect.class);if (Objects.isNull(excelSelect)) {log.debug(No ExcelSelect annotated on {}, skip processing, field.getName());fieldIndex.getAndIncrement();return null;}final ExcelProperty excelProperty AnnotatedElementUtils.getMergedAnnotation(field, ExcelProperty.class);AbstractExcelSelectModel? excelSelectModel;if (StrUtil.isNotEmpty(excelSelect.parentColumnName())) {excelSelectModel new ExcelCascadeModel(field, excelSelect, excelProperty, fieldIndex.getAndIncrement(), parameters);} else {excelSelectModel new ExcelSelectModel(field, excelSelect, excelProperty, fieldIndex.getAndIncrement(), parameters);}return excelSelectModel;}).filter(Objects::nonNull).collect(Collectors.toMap(AbstractExcelSelectModel::getColumnIndex, Function.identity(), (a, b) - a));if (MapUtil.isEmpty(selectionMapping)) {return null;}// 设置父列索引final MapString, Integer columnNamedMapping selectionMapping.values().stream().collect(Collectors.toMap(AbstractExcelSelectModel::getColumnName, AbstractExcelSelectModel::getColumnIndex));selectionMapping.forEach((k, v) - {if (v.hasParentColumn() columnNamedMapping.containsKey(v.getParentColumnName())) {v.setParentColumnIndex(columnNamedMapping.get(v.getParentColumnName()));}});return selectionMapping;} }1.3 框架方式 框架方式基于SpringAOP能力, 纯静态配置方式导出模版或数据, 所以无法通过人为调用的方式提交动态参数. 1.3.1 框架实现 同动态指定导出文件名称的方式相同, 在导出时, 通过向HttpServletRequest对象中设置指定Key(ExcelConstants.DROPDOWN_QUERY_PARAMS_ATTRIBUTE_KEY)的Attributes, 根据约定, 框架将通过该指定Key查询是否存在参数, 有则使用. 框架通过Enhance类配置ExcelWriterBuilder和ExcelWriterSheetBuilder, 并且框架考虑自动导出多sheet情况, 所以在enhanceExcel()中获取动态参数并处理. /** author hp/ Slf4j public class ExcelSelectExcelWriterBuilderEnhance implements ExcelWriterBuilderEnhance {protected final AtomicInteger selectionColumnIndex new AtomicInteger(0);protected MapClass?, MapInteger, ? extends AbstractExcelSelectModel? selectionMapMapping Maps.newHashMap();SuppressWarnings(unchecked)Overridepublic ExcelWriterBuilder enhanceExcel(ExcelWriterBuilder writerBuilder,ResponseExcel responseExcel,Collection? extends Class? dataClasses,HttpServletRequest request,HttpServletResponse response) {final Object attribute Objects.requireNonNull(request).getAttribute(ExcelConstants.DROPDOWN_QUERY_PARAMS_ATTRIBUTE_KEY);final MapString, Object parameters Optional.ofNullable(attribute).map(attr - {Preconditions.checkArgument(attr instanceof Map?, ?);return (MapString, Object) attribute;}).orElse(null);dataClasses.forEach(dataClass - selectionMapMapping.put(dataClass, ExcelSelectHelper.createSelectionMapping(dataClass, parameters)));return writerBuilder.registerWriteHandler(new SelectDataWorkbookWriteHandler());}Overridepublic ExcelWriterSheetBuilder enhanceSheet(ExcelWriterSheetBuilder writerSheetBuilder,Integer sheetNo,String sheetName,Class? dataClass,Class? extends HeadGenerator headEnhancerClass,String templatePath) {if (selectionMapMapping.containsKey(dataClass)) {final MapInteger, ? extends AbstractExcelSelectModel? selectionMapping selectionMapMapping.get(dataClass);writerSheetBuilder.registerWriteHandler(new SelectDataSheetWriteHandler(selectionColumnIndex, selectionMapping));}return writerSheetBuilder;} }1.3.2 框架用例 模版类 /** author hp/ EqualsAndHashCode(callSuper true) Data public class DynamicParametersExcelTemplate extends ExcelTemplate {ExcelSelect(options ExcelOptions(expression excelSelectDynamicDataHandler.findAll()))ExcelProperty(动态单列下拉列表)private LocalDate dynamicSelectColumn;ExcelProperty(非下拉列No3)private String noneDropdownNo3;ExcelSelect(options ExcelOptions(expression excelSelectDynamicParameterDataHandler.findAllForParentByType(#type)))ExcelProperty(动态参数父列)private Integer dynamicParameterParentColumn;ExcelProperty(非下拉列No1)private String noneDropdownNo1;ExcelProperty(非下拉列No2)private String noneDropdownNo2;ExcelSelect(parentColumnName 动态参数父列,options ExcelOptions(expression excelSelectDynamicParameterDataHandler.findAllForChildrenByValueGt(#value)))ExcelProperty(动态参数子列)private Integer dynamicParameterChildColumn; }加载下拉业务 /** author hp/ Component public class ExcelSelectDynamicParameterDataHandler {public ListString findAllForParentByType(String type) {final MapString, ListString map Maps.newHashMap();map.put(TYPE-A, List.of(1, 2, 3, 4));map.put(TYPE-B, List.of(一, 二, 三, 四));return map.getOrDefault(type, Collections.emptyList());}public MapString, ListInteger findAllForChildrenByValueGt(Integer limitation) {final MapString, ListInteger map Maps.newHashMap();map.put(1, Stream.of(10, 20, 30, 40, 50, 60).filter(i- i limitation).toList());map.put(2, Stream.of(30, 40, 50, 60, 70, 80, 90).filter(i- i limitation).toList());return map;} }导出接口 ResponseExcel(name DynamicParametersExcelTemplate,sheets {Sheet(sheetName sheet, sheetNo 0),},enhancement {ExcelSelectExcelWriterBuilderEnhance.class} ) PostMapping(template/sheet/single/DynamicParametersExcelTemplate) public ListDynamicParametersExcelTemplate singleSheet5(HttpServletRequest request) {final MapString, Object map Maps.newHashMap();map.put(type,TYPE-A);map.put(value,40);request.setAttribute(ExcelConstants.DROPDOWN_QUERY_PARAMS_ATTRIBUTE_KEY, map);return Collections.singletonList(new DynamicParametersExcelTemplate()); }1.4 EasyExcel方式 原生方式相当于很多框架做的配置工作都给人来完成, 那自然很容易拿到提交动态参数的入口, 可以直接提交参数. 1.4.1 EasyExcel用例 导出接口 在设置handler时, 直接将下拉元数据映射提交给 SelectDataSheetWriteHandler 即可, 此时可以提交动态参数, 用例里偷懒写了null. new SelectDataSheetWriteHandler(index, ExcelSelectHelper.createSelectionMapping(MultipleSheetNo1ExcelTemplate.class, null)) PostMapping(/easyexcel/template) public void template(HttpServletRequest request, HttpServletResponse response) {String filename 文件名称;final AtomicInteger index new AtomicInteger(0);String userAgent request.getHeader(User-Agent);if (userAgent.contains(MSIE) || userAgent.contains(Trident)) {// 针对IE或者以IE为内核的浏览器filename java.net.URLEncoder.encode(filename, StandardCharsets.UTF_8);} else {// 非IE浏览器的处理:filename new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);}response.setContentType(application/vnd.ms-excel);response.setHeader(Content-disposition, String.format(attachment; filename\%s\, filename .xlsx));response.setHeader(Cache-Control, no-cache);response.setHeader(Pragma, no-cache);response.setDateHeader(Expires, -1);response.setCharacterEncoding(UTF-8);final ExcelWriterBuilder excelWriterBuilder;try {excelWriterBuilder EasyExcel.write(response.getOutputStream());} catch (IOException e) {throw new RuntimeException(e);}try (ExcelWriter excelWriter excelWriterBuilder.registerWriteHandler(new SelectDataWorkbookWriteHandler()).build()) {WriteSheet writeSheet EasyExcel.writerSheet(0, sheet名称).head(MultipleSheetNo1ExcelTemplate.class).registerWriteHandler(new SelectDataSheetWriteHandler(index, ExcelSelectHelper.createSelectionMapping(MultipleSheetNo1ExcelTemplate.class, null))).build();excelWriter.write(new ArrayListString(), writeSheet);WriteSheet writeSheet2 EasyExcel.writerSheet(1, sheet名称2).head(MultipleSheetNo2ExcelTemplate.class).registerWriteHandler(new SelectDataSheetWriteHandler(index, ExcelSelectHelper.createSelectionMapping(MultipleSheetNo2ExcelTemplate.class, null))).build();excelWriter.write(new ArrayListString(), writeSheet2);excelWriter.finish();} catch (Exception e) {log.error(导出Excel文件异常, e);} }二、效果 2.1 下拉sheet 可见根据框架用例的条件和查询方法, TYPE-A 类型的数据和 大于40的数据被用于创建下拉选项
2.2 业务sheet 附录 SpELHelper package com.luban.common.base.utils;import cn.hutool.core.util.StrUtil; import com.luban.common.base.visitor.Visitor; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.BeansException; import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.context.annotation.Configuration; import org.springframework.context.expression.BeanFactoryResolver; import org.springframework.expression.; import org.springframework.expression.spel.standard.SpelExpressionParser; import org.springframework.expression.spel.support.StandardEvaluationContext; import org.springframework.lang.NonNull;import java.lang.reflect.Field; import java.util.Collection; import java.util.Objects; import java.util.Optional; import java.util.function.BiConsumer; import java.util.function.BiFunction;/*** 很多判断是Groovy语法* p* Tips:* ul* li字符串单引号. 可以调用方法或访问属性/li* li属性首字母大小写不敏感/li* li集合元素: Map用 {code map[key]} 获取元素, Array/List用 {code 集合名称[index]} 获取元素/li* li定义List: {code {1,2,3,4} 或 {{a,b},{x,y}} }/li* liinstance of: {code xyz instanceof T(int)}/li* li正则: {code 字符串 matches 正则表达式}/li* li逻辑运算符: {code !非 and与 or或}/li* li类型: {code java.lang包下直接用, 其他的要用T(全类名)}/li* li构造器: {code new 全类名(构造参数)}/li* li变量: StandardEvaluationContext当中的变量 {code #变量名称 }/li* li#this: 当前解析的对象/li* li#root: 上下文的根对象/li* liSpring Bean引用: {code beanName} /li* li三元表达式和Java一样/li* liElvis Operator: {code Names?:Unknown} Names为空提供默认值/li* li防NPE操作符: {code PlaceOfBirth?.City} 如果为NULL 防止出现NPE/li* li筛选集合元素: {code 集合.?[筛选条件]} 如果是Map集合,Map.Entry为当前判断对象/li* li筛选第一个满足集合元素: {code 集合.^[筛选条件]}/li* li筛选第一个满足集合元素: {code 集合.$[筛选条件]}/li* li集合映射,类似StreamAPI的map()再collect(): 使用语法 {code 集合.![映射规则]}, Map集合类似上述说明/li* li表达式模版: 默认{code #{} }, 指定解析模版内部的内容/li* /ul** author hp*/Slf4j Configuration public class SpELHelper implements ApplicationContextAware {private BeanResolver beanResolver;private final ExpressionParser expressionParser new SpelExpressionParser();private final ParserContext parserContext ParserContext.TEMPLATE_EXPRESSION;public T, R StandardSpELGetterT, R newGetterInstance(String expression) {return new StandardSpELGetter(expression, new StandardEvaluationContext());}public T, R StandardSpELSetterT, R newSetterInstance(Field field) {return new StandardSpELSetter(field);}Overridepublic void setApplicationContext(NonNull ApplicationContext applicationContext) throws BeansException {this.beanResolver new BeanFactoryResolver(applicationContext);}public class StandardSpELGetterT, R implements BiFunctionT, VisitorEvaluationContext, R {private final Expression expression;private final EvaluationContext evaluationContext;private StandardSpELGetter(String expression, EvaluationContext evaluationContext) {if (StrUtil.isNotEmpty(expression) expression.startsWith(parserContext.getExpressionPrefix())) {this.expression expressionParser.parseExpression(expression, parserContext);} else {this.expression expressionParser.parseExpression(expression);}this.evaluationContext Objects.requireNonNull(evaluationContext);if (this.evaluationContext instanceof StandardEvaluationContext standardEvaluationContext) {standardEvaluationContext.setBeanResolver(beanResolver);}}SuppressWarnings(unchecked)Overridepublic R apply(T data, VisitorEvaluationContext visitor) {Optional.ofNullable(visitor).ifPresent(v - v.visit(evaluationContext));return ® expression.getValue(evaluationContext, data);}public R apply(T data) {return apply(data, Visitor.defaultVisitor());}}public class StandardSpELSetterT, R implements BiConsumerT, CollectionR {private final String fieldName;private final boolean isCollection;private final Expression expression;private StandardSpELSetter(Field field) {this.fieldName Objects.requireNonNull(field).getName();this.expression expressionParser.parseExpression(fieldName);this.isCollection Collection.class.isAssignableFrom(Objects.requireNonNull(field).getType());}Overridepublic void accept(T data, CollectionR result) {if (isCollection) {this.expression.setValue(data, result);} else {int size result.size();if (size 1) {this.expression.setValue(data, result.stream().findFirst().get());} else {log.error(write join result to {} error: Too many results, field is {}, data is {}, data, fieldName, result);}}}} }