使用Aspose.cells 设置多级下拉
- 多级级联下拉代码使用
- 参考案例1 使用三级下拉
- 参考案列2 使用五级下拉
Aspose.cells jar包
链接：https://pan.baidu.com/s/1sJzVYyQkTVX7YtD9XRaxGg
提取码：YESH
或使用maven
<repositories>
<repository>
<id>AsposeJavaAPI</id>
<name>Aspose Java API</name>
<url>http://repository.aspose.com/repo/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>com.aspose</groupId>
<artifactId>aspose-cells</artifactId>
<version>21.10</version>
</dependency>
<dependency>
<groupId>com.aspose</groupId>
<artifactId>aspose-cells</artifactId>
<version>21.10</version>
<classifier>javadoc</classifier>
</dependency>
<dependency>
<groupId>org.bouncycastle</groupId>
<artifactId>bcprov-jdk15on</artifactId>
<version>1.60</version>
</dependency>
<dependency>
<groupId>org.bouncycastle</groupId>
<artifactId>bcpkix-jdk15on</artifactId>
<version>1.60</version>
</dependency>
</dependencies>多级级联下拉代码使用
使用请参考main方法拿来即用 请使用该方法
/**
*返回一个apose.cells的workBook格式 可以使用第三点说明的方法来保存文件
*/
static Workbook addDropDownValidationsToSheet(
List<DropDownData> dropDownData,
int fromRow,
int endRow,
char... column)-
Aspose.cells 提供workBook的save方法、可以以各种格式存储文件转换太过于方便
比如：调用workBook.save(“filePath”,SaveFormat .HTML );
就可以将文件转换为html 并存放在指定的filePath下
/**
*fileName 存储路径加文件后缀
*saveFormat：如下
*/
public void save(String fileName, int saveFormat) ;
public void save(String fileName) throws Exception ;
public void save(String fileName, SaveOptions saveOptions);
public void save(OutputStream stream, SaveOptions saveOptions);
public void save(OutputStream stream, int saveFormat) ;
//源码参考
public final class SaveFormat {
public static final int CSV = 1;
public static final int XLSX = 6;
public static final int XLSM = 7;
public static final int XLTX = 8;
public static final int XLTM = 9;
public static final int XLAM = 10;
public static final int TSV = 11;
public static final int TAB_DELIMITED = 11;
public static final int HTML = 12;
public static final int M_HTML = 17;
public static final int ODS = 14;
public static final int EXCEL_97_TO_2003 = 5;
public static final int SPREADSHEET_ML = 15;
public static final int XLSB = 16;
public static final int AUTO = 0;
public static final int UNKNOWN = 255;
public static final int PDF = 13;
public static final int XPS = 20;
public static final int TIFF = 21;
public static final int SVG = 28;
public static final int DIF = 30;
public static final int NUMBERS = 56;
public static final int MARKDOWN = 57;
public static final int FODS = 59;
public static final int SXC = 60;
public static final int PPTX = 61;
public static final int DOCX = 62;
private SaveFormat() {
}
}工具类正文以及测试用例，参考main方法
import com.aspose.cells.*;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Queue;
/**
* @Author ：yeguojin
* @Date ：Created in 2021/10/25 16:02
* @Description：Excel下拉设置 使用aspose.cell 插件
* @Modified By：
*/
public class ExcelDorpDownValidate {
private static final String DEFAULT_ERROR_TITLE = "error";
private static final String DEFAULT_ERROR_MSG = "请填写下拉值！";
private static final Integer DEFAULT_FROM_ROW = 0;
private static final Integer DEFAULT_END_ROW = 1999;
private Workbook workbook;
private Worksheet targetSheet;
private List<DropDownData> dropDownData;
private Integer fromRow;
private Integer endRow;
private String errorTitle;
private String errorMsg;
private char[] column;
public ExcelDorpDownValidate setWorkbook(Workbook workbook) {
if (this.workbook == null) {
this.workbook = workbook;
}
return this;
}
public ExcelDorpDownValidate setWorkbook(String workbookPath) throws Exception {
if (this.workbook == null) {
this.workbook = new Workbook(workbookPath);
}
return this;
}
public ExcelDorpDownValidate setWorkbook(InputStream workbookStream) throws Exception {
if (this.workbook == null) {
this.workbook = new Workbook(workbookStream);
}
return this;
}
public ExcelDorpDownValidate setWorksheet(Worksheet worksheet) {
if (this.targetSheet == null) {
this.targetSheet = worksheet;
}
return this;
}
public ExcelDorpDownValidate setWorksheet(int worksheetIndex) {
if (this.workbook == null) {
this.workbook = new Workbook();
}
if (this.targetSheet == null) {
this.targetSheet = this.workbook.getWorksheets().get(worksheetIndex);
}
return this;
}
public ExcelDorpDownValidate setDropDownList(List<DropDownData> dropDownList) {
this.dropDownData = dropDownList;
return this;
}
public ExcelDorpDownValidate setFromRow(int fromRow) {
this.fromRow = fromRow;
return this;
}
public ExcelDorpDownValidate setEndRow(int endRow) {
this.endRow = endRow;
return this;
}
public ExcelDorpDownValidate setErrorTitle(String errorTitle) {
this.errorTitle = errorTitle;
return this;
}
public ExcelDorpDownValidate setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
return this;
}
public ExcelDorpDownValidate setColumn(char... column) {
this.column = column;
return this;
}
public Workbook build() {
if (this.workbook == null) {
this.workbook = new Workbook();
}
if (this.targetSheet == null) {
//默认取第一个sheet
this.targetSheet = this.workbook.getWorksheets().get(0);
}
if (this.dropDownData == null || this.dropDownData.isEmpty()) {
throw new RuntimeException("dropDownData is empty");
}
if (this.fromRow == null) {
//默认在第一行开始
this.fromRow = DEFAULT_FROM_ROW;
}
if (this.endRow == null) {
//两千行结束
this.endRow = DEFAULT_END_ROW;
}
if (this.errorTitle == null) {
this.errorTitle = DEFAULT_ERROR_TITLE;
}
if (this.errorMsg == null) {
this.errorMsg = DEFAULT_ERROR_MSG;
}
if (column == null) {
throw new RuntimeException("column is null");
}
return addDropDownValidationsToSheet(workbook, targetSheet, dropDownData, fromRow, endRow, errorTitle, errorMsg, column);
}
/**
* 使用aspose.cells 工具设置级联下拉不限制层数
*
* @param workbook 该工作薄
* @param targetSheet 设置下拉数据的sheet
* @param dropDownData 下拉数据对象
* @param fromRow 开始位置 行位置下表 从零开始
* @param endRow 结束位置 行位置下表 从零开始
* @param column 需要设置级联下拉的位置列 如 'A'、‘B’
* @param errorTitle 填错信息的提示
* @param errorMsg 错误提示信息信息
* <p>
* column 有多少个则dropDownData就需要有多少层数据。
* dropDownData的数据会新创建一个sheet放在最后的sheet里面 一级放一列 如有三层：放在 A、B、C三列下。
* column 如果是 A、B、C 则 A是B的父集，B是C的父集，按照顺序类推
* </p>
*/
public static Workbook addDropDownValidationsToSheet(Workbook workbook,
Worksheet targetSheet,
List<DropDownData> dropDownData,
int fromRow,
int endRow,
String errorTitle,
String errorMsg,
char... column) {
//最上层父集的名称
String uppermostParentSetRangName = "range";
//获取校验结合targetSheet
ValidationCollection validations = targetSheet.getValidations();
//通过workBook 创建新的sheet 用于存放下拉数据
int workSheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(workSheetIndex);
//数据单元格
Cells dataCells = worksheet.getCells();
//创建队列 将第一层数据加入进去
Queue<DropDownData> queue = new LinkedList<>();
queue = createQueue(queue, dropDownData);
//用于定位位置 记录设置的下拉数据是第几个
int i = 0;
List<Integer> size = new ArrayList<>();
size.add(dropDownData.size());
//用于记录使用多少个 儿子集合，最上层集合为第0个
int index = 0;
//创建最上层父集的下拉数据引用范围 参数解释：开始行、开始列、总行数、总列数
//这里是从第一行第一列（0,0 表示的单元格第一行第一列）开始，总行数为dropDownData.size()，总列数为1列
Range uppermostParentSet = dataCells.createRange(0, 0, dropDownData.size(), 1);
uppermostParentSet.setName(uppermostParentSetRangName + index);
dataCells.getRanges().add(uppermostParentSet);
//设置名称管理器数据和引用位置
//使用队列 保证效率为 O(n)
//利用树形结构层序遍历
int sizeSum = getSizeSum(size, index);//
int location = getSizeSum(size, index - 1);//设置数据的行位置
while (!queue.isEmpty()) {
DropDownData poll = queue.poll();
if (i >= sizeSum) {
index++;
sizeSum = getSizeSum(size, index);
location = getSizeSum(size, index - 1);
}
Range setValueRang = dataCells.getRanges().get(index);
setValueRang.get(index == 0 ? i : i - location, 0).setValue(poll.getCurrent());
if (poll.children != null && !poll.children.isEmpty()) {
queue = createQueue(queue, poll.children);
size.add(poll.children.size());
//只要有子集 就需要创建名称管理器下拉数据
Range createChildren = dataCells.createRange(getSizeLastTime(size), 1, poll.children.size(), 1);
createChildren.setName(poll.getCurrent());
dataCells.getRanges().add(createChildren);
}
//每循环一次加一
i++;
}
//设置校验空间 有多少个column就遍历多少次
for (int i1 = 0; i1 < column.length; i1++) {
//获取设置的列位置 转换成字符串
String c = column[i1] + "";
//设置CellArea区域 cellArea 使用的是字符 最小的是从1开始
CellArea cellArea = CellArea.createCellArea("" + c + (fromRow + 1), c + (endRow + 1) + "");
//创建校验Validation
int add = validations.add(cellArea);
Validation validation = validations.get(add);
if (i1 == 0) {
//设置最上层校验公式
validation.setFormula1(uppermostParentSetRangName + i1);
} else {
//设置下一级校验公式
validation.setFormula1(String.format("=INDIRECT(%s%s)", column[i1 - 1], (fromRow + 1)));
}
//设置是否下拉
validation.setInCellDropDown(true);
//设置校验类型
validation.setType(ValidationType.LIST);
//设置校验错误的提示信息 标题
validation.setErrorTitle(errorTitle);
//设置校验错误的提示信息 内容
validation.setErrorMessage(errorMsg);
//是否展示错误提示 是
validation.setShowError(true);
//设置警告风格
validation.setAlertStyle(ValidationAlertType.STOP);
//设置校验的区域 cellArea
validation.addArea(cellArea);
}
return workbook;
}
/**
* 获取前index个的加和
*
* @param integers
* @param index
* @return
*/
private static int getSizeSum(List<Integer> integers, int index) {
int m = 0;
for (int i = 0; i < index + 1; i++) {
m += integers.get(i);
}
return m;
}
/**
* 获取上一次的加和
*
* @param integers
* @return
*/
private static int getSizeLastTime(List<Integer> integers) {
int m = 0;
for (int i = 0; i < integers.size() - 1; i++) {
m += integers.get(i);
}
return m;
}
/**
* 级联下拉数据格式 树形结构
*/
static class DropDownData {
String current;
List<DropDownData> children;
public DropDownData() {
}
public DropDownData(String current, List<DropDownData> children) {
this.current = current;
this.children = children;
}
public String getCurrent() {
return current;
}
public void setCurrent(String current) {
this.current = current;
}
public List<DropDownData> getChildren() {
return children;
}
public void setChildren(List<DropDownData> children) {
this.children = children;
}
}
/**
* 创建队列 没有指定大小 遍历第一层
*
* @param dropDownData
* @return
*/
public static Queue<DropDownData> createQueue(Queue<DropDownData> queue, List<DropDownData> dropDownData) {
if (dropDownData == null || dropDownData.isEmpty()) {
return null;
}
dropDownData.forEach(a -> {
queue.offer(a);
});
return queue;
}
}参考案例1 使用三级下拉
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
List<DropDownData> dropDownData = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DropDownData dropDownData1 = new DropDownData();
dropDownData1.setCurrent("一级" + i);
dropDownData.add(dropDownData1);
}
for (int i = 0; i < dropDownData.size(); i++) {
List<DropDownData> dropDownData2 = new ArrayList<>();
if (i < 5 || i == 9) {
//测试大量数据
for (int i1 = 0; i1 < 1000; i1++) {
DropDownData dropDownData1 = new DropDownData();
dropDownData1.setCurrent("二级" + i + i1);
dropDownData2.add(dropDownData1);
}
dropDownData.get(i).setChildren(dropDownData2);
}
}
List<DropDownData> dropDownData3 = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DropDownData dropDownData1 = new DropDownData();
dropDownData1.setCurrent("三级测试二级一下拉" + i);
dropDownData3.add(dropDownData1);
}
List<DropDownData> dropDownData4 = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DropDownData dropDownData1 = new DropDownData();
dropDownData1.setCurrent("三级测试二级四下拉" + i);
dropDownData4.add(dropDownData1);
}
dropDownData.get(0).getChildren().get(0).setChildren(dropDownData3);
dropDownData.get(0).getChildren().get(3).setChildren(dropDownData4);
workbook = addDropDownValidationsToSheet(workbook, worksheet, dropDownData, 1, 100, "填写错误！", "请填入下拉值！", 'A', 'B', 'C');
workbook.save("E:\\file01.xlsx");
}参考案列2 使用五级下拉
//参考案列2 使用五级下拉
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
List<DropDownData> dropDownData1 = new ArrayList<>();
/**
* 四川省
* 贵州省
*/
String[] f1 = {
"四川省",
"贵州省",};
for (String s : f1) {
DropDownData dropDownData = new DropDownData();
dropDownData.setCurrent(s);
dropDownData1.add(dropDownData);
}
String[] f2 =
{
"成都市",
"自贡市",
"攀枝花市",
"泸州市",
"德阳市",
"绵阳市",
"广元市",
"遂宁市",
"内江市",
"乐山市",
"南充市",
"眉山市",
"宜宾市",
"广安市",
"达州市",
"雅安市",
"巴中市",
"资阳市",
"阿坝藏族羌族自治州",
"甘孜藏族自治州",
"凉山彝族自治州",};
List<DropDownData> dropDownData2 = new ArrayList<>();
for (String s : f2) {
DropDownData dropDownData = new DropDownData();
dropDownData.setCurrent(s);
dropDownData2.add(dropDownData);
}
dropDownData1.get(0).setChildren(dropDownData2);
String[] f3 =
{"市辖区",
"锦江区",
"青羊区",
"金牛区",
"武侯区",
"成华区",
"龙泉驿区",
"青白江区",
"新都区",
"温江区",
"双流区",
"郫都区",
"新津区",
"金堂县",
"大邑县",
"蒲江县",
"都江堰市",
"彭州市",
"邛崃市",
"崇州市",
"简阳市",};
List<DropDownData> dropDownData3 = new ArrayList<>();
for (String s : f3) {
DropDownData dropDownData = new DropDownData();
dropDownData.setCurrent(s);
dropDownData3.add(dropDownData);
}
dropDownData1.get(0).getChildren().get(0).setChildren(dropDownData3);
String[] f4 = {
"锦官驿街道",
"东湖街道",
"锦华路街道",
"春熙路街道",
"书院街街道",
"牛市口街道",
"沙河街道",
"狮子山街道",
"成龙路街道",
"柳江街道",
"三圣街道",};
List<DropDownData> dropDownData4 = new ArrayList<>();
for (String s : f4) {
DropDownData dropDownData = new DropDownData();
dropDownData.setCurrent(s);
dropDownData4.add(dropDownData);
}
dropDownData1.get(0).getChildren().get(0).getChildren().get(1).setChildren(dropDownData4);
String[] f5 = {
"水井坊社区居委会",
"崇德里社区居委会",
"合江亭社区居委会",
"大慈寺社区居委会",
"点将台社区居委会",
"交子社区居委会",};
List<DropDownData> dropDownData5 = new ArrayList<>();
for (String s : f3) {
DropDownData dropDownData = new DropDownData();
dropDownData.setCurrent(s);
dropDownData5.add(dropDownData);
}
dropDownData1.get(0).getChildren().get(0).getChildren().get(1).getChildren().get(0).setChildren(dropDownData5);
workbook = addDropDownValidationsToSheet(workbook, worksheet, dropDownData1, 1, 100, "填写错误！", "请填入下拉值！", 'A', 'B', 'C', 'D', 'E');
workbook.save("E:\\myTest\\file02.xlsx");
}创作不易，请打赏点赞
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
