Excel动态下拉

一、为什么要做动态的excel下拉模板？

        动态的Excel下拉模板可以通过使用JavaScript、JQuery、Ajax等技术来实现。这种模板的主要目的是为了方便用户选择和管理数据，提高数据的可读性和易用性。
        以下是一些为什么要做动态的Excel下拉模板的原因：
        提高数据的可读性：动态的下拉模板可以让用户更容易地理解和管理数据。当用户需要选择一个选项时，他们可以很快地找到他们需要的选项，而不需要浏览整个数据集。
减少错误：动态的下拉模板可以减少用户输入错误的可能性。当用户选择一个选项时，他们可以选择一个已经存在的选项，而不是输入一个错误的值。
        提高易用性：动态的下拉模板可以提高用户的易用性。用户可以更快地完成任务，而不需要花费太多时间和精力去理解如何使用模板。
增强用户体验：动态的下拉模板可以增强用户的体验。用户可以更快地找到他们需要的信息，而不需要浏览整个数据集。
        方便数据管理：动态的下拉模板可以方便地管理数据。当需要添加或删除选项时，用户可以很快地完成操作，而不需要手动修改整个数据集。
        总之，动态的Excel下拉模板可以提高数据的可读性、减少错误、提高易用性、增强用户体验和方便数据管理。这些原因使得动态的Excel下拉模板成为了一个非常有用的工具。

二、编写代码

1.pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.lqy</groupId>
    <artifactId>easyExcel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>easyExcel</name>
    <description>easyExcel</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--EasyExcel相关依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.8</version>
            <scope>compile</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.定义注解

package cn.lqy.easyexcel.annocation;

import java.lang.annotation.*;

/**
 * LQY
 */

@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownField {
	
	/**
	 * 固定下拉内容
	 */
	String[] source() default {};
	
	/**
	 * 根据type指定下拉内容
	 */
	int type() default 0;
	
}

3.实体定义

package cn.lqy.easyexcel.entity;


import cn.lqy.easyexcel.annocation.DropDownField;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @Author: 神荼
 * @CreateTime: 2023-12-28  17:03
 * @Description:
 */
@Data
public class ExcelTemplate {
	
	
	@ExcelProperty(value = "序号", index = 0)
	private String index;
	
	@ExcelProperty(value = "姓名", index = 1)
	private String name;
	
	@ExcelProperty(value = "性别", index = 2)
	@DropDownField(source = {"男", "女", "未知"})
	private String sex;
	
	@ExcelProperty(value = "所属部门", index = 3)
	@DropDownField(source = {}, type = 3)
	private String department;
	
	
	@ExcelProperty(value = "员工或领导", index = 4)
	@DropDownField(type = 1)
	private String employeesOrLeaders;
	
	@ExcelProperty(value = "上级领导", index = 5)
	@DropDownField(type = 2)
	private String superiorLeader;
}

4.接口层定义

package cn.lqy.easyexcel.service;

import cn.lqy.easyexcel.annocation.DropDownField;

/**
 * @Author: 神荼
 * @CreateTime: 2023-12-28  17:21
 * @Description:
 */
public interface DropDownFieldService {
	/**
	 * 数据填充
	 *
	 * @param dropDownField
	 * @return
	 */
	String[] dataFilling(DropDownField dropDownField);
}

5.接口实现层定义

package cn.lqy.easyexcel.service.impl;


import cn.lqy.easyexcel.annocation.DropDownField;
import cn.lqy.easyexcel.service.DropDownFieldService;
import org.springframework.stereotype.Service;

import java.util.LinkedList;
import java.util.List;

/**
 * @Author: 神荼
 * @CreateTime: 2023-12-28  17:21
 * @Description:
 */
@Service
public class DropDownFieldServiceImpl implements DropDownFieldService {
	@Override
	public String[] dataFilling(DropDownField dropDownField) {
		
		String[] source = new String[0];
		if (dropDownField.source().length > 0) {
			//字段对应注解中有值
			System.out.println("souece设置了默认值");
			source = dropDownField.source();
		} else if (dropDownField.type() != 0) {
			List<String> list = new LinkedList<>();
			/**
			 * 根据type值填充的下拉数据
			 */
			System.out.println("根据type值填充的下拉数据");
			switch (dropDownField.type()) {
				case 1:
					list.add("领导");
					list.add("员工");
					//source = list.toArray(new String[0]);
					break;
				case 2:
					list.add("领导1");
					list.add("领导2");
					list.add("领导3");
					list.add("领导4");
					//source = list.toArray(new String[0]);
					break;
				case 3:
					list.add("部门1");
					list.add("部门2");
					list.add("部门3");
					list.add("部门4");
					//source = list.toArray(new String[0]);
					break;
				default:
					System.out.println("没有设置任何数据");
					//source =  list.toArray(new String[0]);
			}
			source = list.toArray(new String[0]);
		}
		return source;
	}
}

6.excel函数处理handler

package cn.lqy.easyexcel.handler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

public class ExcelSheetWriteHandler implements SheetWriteHandler {
	
	private final Map<Integer, String[]> map;
	
	public ExcelSheetWriteHandler(Map<Integer, String[]> map) {
		this.map = map;
	}
	
	@Override
	public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
	
	}
	
	@Override
	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
		// 这里可以对cell进行任何操作
		Sheet sheet = writeSheetHolder.getSheet();
		DataValidationHelper helper = sheet.getDataValidationHelper();
		
		// k 为存在下拉数据集的单元格下表 v为下拉数据集
		map.forEach((k, v) -> {
			// 设置下拉单元格的首行 末行 首列 末列
			CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k - 1, k - 1);
			//定义sheet的名称
			//1.创建一个隐藏的sheet 名称为 hidden + k
			String sheetName = "hidden" + k;
			Workbook workbook = writeWorkbookHolder.getWorkbook();
			Sheet hiddenSheet = workbook.createSheet(sheetName);
			for (int i = 0, length = v.length; i < length; i++) {
				// 开始的行数i，列数k
				hiddenSheet.createRow(i).createCell(k).setCellValue(v[i]);
			}
			Name category1Name = workbook.createName();
			category1Name.setNameName(sheetName);
			String excelLine = getExcelLine(k);
			// =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
			String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1);
			// 将刚才设置的sheet引用到你的下拉列表中
			DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
			DataValidation dataValidation = helper.createValidation(constraint, rangeList);
			// 阻止输入非下拉选项的值
			dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
			dataValidation.setShowErrorBox(true);
			dataValidation.setSuppressDropDownArrow(true);
			dataValidation.createErrorBox("提示", "此值与单元格定义格式不一致");
			writeSheetHolder.getSheet().addValidationData(dataValidation);
			// 设置存储下拉列值得sheet为隐藏
			int hiddenIndex = workbook.getSheetIndex(sheetName);
			if (!workbook.isSheetHidden(hiddenIndex)) {
				workbook.setSheetHidden(hiddenIndex, true);
			}
		});
	}
	
	/**
	 * 返回excel列标A-Z-AA-ZZ
	 *
	 * @param num 列数
	 * @return java.lang.String
	 */
	private String getExcelLine(int num) {
		String line = "";
		int first = num / 26;
		int second = num % 26;
		if (first > 0) {
			line = (char) ('A' + first - 1) + "";
		}
		line += (char) ('A' + second) + "";
		return line;
	}
}

7.控制层

package cn.lqy.easyexcel.controller;


import cn.lqy.easyexcel.annocation.DropDownField;
import cn.lqy.easyexcel.entity.ExcelTemplate;
import cn.lqy.easyexcel.handler.ExcelSheetWriteHandler;
import cn.lqy.easyexcel.service.DropDownFieldService;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;

/**
 * @Author: 神荼
 * @CreateTime: 2023-12-28  18:00
 * @Description:
 */
@RestController
@RequestMapping("/easyExcel")
public class EasyExcelController {
	
	@Resource
	private DropDownFieldService dropDownFieldService;
	
	@GetMapping("/downTemplate")
	public void downloadTemplate(HttpServletResponse response) throws IOException {
		String fileName = URLEncoder.encode("部门下拉.xlsx", StandardCharsets.UTF_8.toString());
		//获取指定类的所有声明字段（包括继承来的字段），并将其存储在一个Field数组中。这里使用了getClass().getDeclaredFields() 方法来获取所有声明字段，包括私有字段。
		Field[] declaredFields = ExcelTemplate.class.getDeclaredFields();
		// 响应字段对应的下拉集合
		Map<Integer, String[]> map = new HashMap<>();
		// 循环判断哪些字段有下拉数据集，并获取
		for (int i = 0; i < declaredFields.length; i++) {
			// 解析注解信息
			DropDownField dropDownField = declaredFields[i].getAnnotation(DropDownField.class);
			if (null != dropDownField) {
				String[] sources = dropDownFieldService.dataFilling(dropDownField);
				if (null != sources && sources.length > 0) {
					map.put(i + 1, sources);
				}
			}
			
		}
		response.setContentType("application/force-download");
		response.setCharacterEncoding("utf-8");
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
		EasyExcel
				.write(response.getOutputStream(), ExcelTemplate.class)
				.autoCloseStream(true)
				.registerWriteHandler(new ExcelSheetWriteHandler(map))
				.excelType(ExcelTypeEnum.XLSX)
				.sheet("部门人员")
				.doWrite(new LinkedList());
	}
}